设数据库 DB 有三个关系:
学生表 student (Sid ,Sname ,Sage ,Ssex)
INSERT INTO student VALUES ('01' ,'赵雷' ,'1990-01-01' ,'男') ;
科目表 course (Cid ,Cname ,Tid)
INSERT INTO course VALUES ('01' ,'语文' ,'02') ;
教师表 teacher (Tid ,Tname)
INSERT INTO teacher VALUES ('01' ,'张三') , ('02' ,'李四') , ('03' ,'王五 ') ;
成绩表 SC (Sid ,Cid ,Score)
INSERT INTO SC VALUES ('01' ,'01' ,80) ;
查询问题:
( 1) 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数;
( 2) 查询同时存在" 01 "课程和" 02 "课程的情况;
( 3) 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) ;
( 4) 查询不存在" 01 "课程但存在" 02 "课程的情况;
( 5) 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩;
( 6) 查询在 SC 表存在成绩的学生信息;
7) 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成 绩的显示为 null ) ;
( 8) 查询「李」姓老师的数量。
( 1) SELECT a .Sid ,a .score ,student.*
FROM ( SELECT * FROM sc WHERE sc .`Cid`=01 ) a LEFT JOIN ( SELECT
* FROM sc WHERE sc.cid=02)b ON a .Sid=b .Sid LEFT JOIN student ON a .Sid=student.Sid WHERE a .score>b .score;
( 2) SELECT * FROM ( SELECT * FROM sc WHERE sc .`Cid`=01) a INNER JOIN ( SELECT * FROM sc WHERE sc .`Cid`=02) b ON a .Sid=b .Sid;
( 3) SELECT * FROM ( SELECT * FROM sc WHERE sc.`Cid`=01) a
LEFT JOIN ( SELECT * FROM sc WHERE sc .`Cid`=02) b ON a .Sid=b.Sid;
( 4) SELECT b .* FROM ( SELECT * FROM sc WHERE sc.`Cid`=02) b
LEFT JOIN ( SELECT * FROM sc WHERE sc .`Cid`=01) a ON b .Sid=a.Sid
WHERE a .Sid IS NULL;
( 5) SELECT * FROM student stu INNER JOIN
( SELECT sc .Sid,AVG ( score ) A S avgscore FROM sc GROUP BY Sid) a ON stu.`Sid`=a .Sid WHERE a .avgscore > 60;
( 6) SELECT * FROM sc LEFT JOIN student stu ON sc.`Sid`=stu.`Sid` WHERE sc.`score` IS NOT NULL;
( 7) SELECT * FROM student stu LEFT JOIN
( SELECT Sid ,COUNT ( Cid) ,SUM ( score ) FROM sc GROUP BY Sid) b
ON stu.`Sid`=b.Sid;
( 8) SELECT COUNT ( *)
FROM teacher
WHERE Tname LIKE '李四%';