SQL学习——经典50道习题练习

SQL经典练习题50题

1：查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

mysql> select a.* ,b.Score as score_01,c.Score as socre_02 from Student a           
	-> join SC b on b.Sid=a.Sid and b.CId='01'                                 
    -> join SC c on c.Sid=a.Sid and c.CId='02'
	-> where b.score>c.score;

2:查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

mysql> select b.sid,b.sname,AVG(a.score) as avg_score from student b
    -> join sc a on b.sid=a.sid
	-> group by b.sid,b.sname Having avg_score>=60;

3:查询在 SC 表存在成绩的学生信息

mysql> select distinct a.* from SC b
    -> join Student a
	-> on a.sid=b.sid;

4:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

mysql> select s.sid,s.sname,count(c.cid) as course_number,SUM(sc.score) as total_score from student s
    -> join sc on s.sid=sc.sid
    -> left join course c on sc.cid=c.cid
    -> group by s.sid,s.sname;

4.1 查有成绩的学生信息

  mysql> select distinct s.* from student s
    -> join sc on sc.sid=s.sid
    -> where sc.score is not null;

5:查询「李」姓老师的数量

mysql> select count(Tname) from Teacher
	-> where Tname like 'li%';

6:查询学过「张三」老师授课的同学的信息

mysql> select distinct a.* from student a
    -> join sc on sc.sid=a.sid
    -> join course c on c.cid=sc.cid
	-> join teacher t on t.tid=c.tid;

or

mysql> select a.* from student a
    -> join sc on sc.sid=a.sid
    -> join course c on c.cid=sc.cid
    -> join teacher t on t.tid=c.tid
	-> where t.tname='zhangsan';

7:查询没有学全所有课程的同学的信息

mysql> select a.* from student a
    -> join sc on sc.sid=a.sid
    -> join course c on c.cid=sc.cid
    -> group by a.sid,a.sname,a.sage,a.ssex
	-> having count(c.cid)<3; //数据量小，作弊做法
or  ->having