SQL语句练习

三、根据要求写出SQL语句。

student（s_no,sname,sage,sex）学生表

teacher（t_no,tname）教师表

course（c_no,cname,t_no）课程表

sc(s_no,c_no,score)成绩表

 

CREATE TABLE `student` (
  `s_no` int(11) DEFAULT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

CREATE TABLE `teacher` (
  `t_no` int(11) DEFAULT NULL,
  `tname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

CREATE TABLE `sc` (
  `s_no` int(11) DEFAULT NULL,
  `c_no` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

 CREATE TABLE `course` (
  `c_no` int(11) DEFAULT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `t_no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

 

1、查询“001”课程比“002”课程成绩高的所有学生的学号。

select a.s_no

from (select * from sc where c_no=1) a,(select * from sc where c_no=2) b

where a.s_no=b.s_no

AND a.score>b.score;

 

2、查询平均成绩大于60分的同学的学号和平均成绩。

select s.s_no,avg(score)

from student s,sc m

where s.s_no=m.s_no

group by s.s_no

having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩。

select s.s_no,s.sname,count(1),sum(ifnull(score,0))

from student s

join sc

on sc.s_no=s.s_no;

group by sc.s_no,s.s_no,s.sname;

4、查询姓李的老师的个数。

select count(1) from teacher where tname like '李%';

 

5、查询没学过“叶平”老师课的同学的学号、姓名

select s_no,sname from student

where s_no not in (SELECT m.s_no

FROM sc m,course c,teacher t

WHERE t.t_no=c.t_no AND c.c_no=m.c_no

AND t.tname='叶平');

 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。

select s.s_no,s.sname

from (select * from sc where c_no=1) a,(select * from sc where c_no=2) b,student s

where a.s_no=b.s_no and s.s_no=a.s_no;

7、查询所有课程成绩小于60分的同学的学号、姓名。

select s.s_no,s.sname

from student s,sc m

where m.score<60

and m.s_no=s.s_no;

 

8、查询没有学全所有课的同学的学号、姓名。

SELECT s_no,sname

FROM student

WHERE s_no NOT IN (SELECT s_no

FROM sc

GROUP BY s_no

HAVING COUNT(1)=(SELECT COUNT(1) FROM course));

10、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名。

SELECT s_no,sname

FROM student

WHERE s_no IN (SELECT s_no

FROM sc

WHERE c_no IN (SELECT c_no FROM sc WHERE s_no=1)

AND s_no !=1

GROUP BY s_no);

 

 

 

12、查询和“1002”号同学学习的课程完全相同的其他同学学号和姓名。

SELECT s_no,sname

FROM student

WHERE s_no IN(SELECT s_no

FROM sc

WHERE c_no IN (SELECT c_no FROM sc WHERE s_no=1002)

AND s_no !=1002

GROUP BY s_no

HAVING COUNT(1)=(SELECT COUNT(1) FROM sc WHERE s_no=1002));

 

13、删除学习“叶平”老师课的sc表记录。

delete from sc where c_no in (select c_no from course where c_no=(select t_no from teacher where tname='叶平'));

 

14、向sc表中插入一些记录，这些记录要求符合一下条件：没有上过编号“003”课程的同学学号

INSERT INTO sc(s_no) SELECT s_no FROM student

WHERE s_no NOT IN(SELECT s_no FROM sc WHERE c_no=3);

 

 

15、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。

SELECT c_no '课程ID',MAX(score) '最高分',MIN(score) '最低分'

FROM sc

GROUP BY c_no;

 

16、查询不同老师所教不同课程平均分从高到低显示。

SELECT c.t_no,c.cname,AVG(score)

FROM sc

JOIN course c

ON sc.c_no=c.c_no

GROUP BY c.t_no,c.cname

ORDER BY AVG(score) DESC;

 

17、统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】

SELECT sc.c_no,c.cname,COUNT(1),CASE

WHEN score>=85 THEN '[85-100]'

WHEN score>=70 THEN '[70-85]'

WHEN score>=60 THEN '[60-70]'

ELSE '[<60]'

END 成绩

FROM sc

JOIN course c

ON c.c_no=sc.c_no

GROUP BY c_no,成绩,c.cname

ORDER BY c_no,成绩;

18、查询每门课程被选修的学生数

select c_no,count(1) from sc group by c_no;

 

19、查询出只选修了一门课程的全部学生的学号和姓名

select s.s_no,s.sname

from student s,sc m

where s.s_no=m.s_no

group by m.s_no,s.s_no,s.sname

having count(1)=1;

 

20、查询男生、女生人数

select count(1),sex from student group by sex;