实验6 多表连接查询

1、查询“张天”同学所有已学课程的课程号、课程名、成绩及相应学分。

方法一：SELECT sc.cno,cname,credit,grade FROM student,course,sc WHERE course.cno=sc.cno AND student.sno=sc.sno AND sname='张天';
方法二：SELECT sc.cno,cname,credit,grade FROM student INNER JOIN sc USING(sno) INNER JOIN course USING(cno) WHERE sname='张天';

2、查询与“张天”同系的学生的学号，姓名和性别。

SELECT sno,sname,sex FROM student WHERE deptno=(SELECT deptno FROM student WHERE sname='张天');

3、查询年龄最大的学生信息。（提示四种方法：小于等于所有的出生日期、小于等于最小的出生日期、大于等于所有的年龄、大于等于最大的年龄）。

方法一：SELECT * FROM student WHERE birth<=ALL(SELECT birth FROM student);
方法二：SELECT * FROM student WHERE birth<=(SELECT MIN(birth) FROM student);
方法三：SELECT * FROM student WHERE YEAR(CURDATE())-YEAR(birth)>=ALL(SELECT YEAR(CURDATE())-YEAR(birth) FROM student);
方法四：SELECT * FROM student WHERE YEAR(CURDATE())-YEAR(birth)>=(SELECT MAX(YEAR(CURDATE())-YEAR(birth)) FROM student);

4、查询成绩均在90分以上课程的课程名。(提示：最小的成绩大于90分)

SELECT cname FROM course INNER JOIN sc USING(cno) GROUP BY cname HAVING MIN(grade)>90; 

5、查询列出成绩低于本课程平均分的学生的姓名，课程号及成绩。(采用相关子查询)。

SELECT sname,cno,grade FROM sc X INNER JOIN student USING (sno) WHERE grade<(SELECT AVG(grade) FROM sc Y WHERE sno=x.sno);

6、查询还没有选修相关课程的学生学号，姓名和专业。

INSERT INTO student VALUES(2011006,'陈星','2001-5-12','女',30),(2011007,'欧阳敏','2009-7-29','女',30);
方法一：SELECT student.sno,sname,deptno FROM student LEFT OUTER JOIN sc ON student.sno=sc.sno WHERE cno IS NULL;
方法二：SELECT sno,sname,deptno FROM student WHERE sno  NOT IN(SELECT sno FROM sc);
方法三：SELECT sno,sname,deptno FROM student WHERE NOT EXISTS (SELECT * FROM sc WHERE sno=student.sno);

7、查询各门课程的最高成绩的学生的课程号，姓名及其成绩。(采用相关子查询)。

SELECT cno,sname,grade FROM student,sc X WHERE student.sno=x.sno AND x.grade=(SELECT MAX(grade) FROM sc Y WHERE cno=x.cno);

8、查询选修了“1”号课的学生中的成绩比选修“2”号课的最低成绩高的学生的学号，课程号和成绩。

SELECT sno,cno,grade FROM sc WHERE cno=1 AND grade >ANY(SELECT grade FROM sc WHERE cno=2);

9、查询选修了全部课程的学生信息。（提示：用exists命令、学生所选课程数目与course中开设的总课程数目相等）。

方法一：SELECT sname FROM student WHERE NOT EXISTS(SELECT * FROM course WHERE NOT EXISTS(SELECT * FROM sc WHERE sno=student.sno AND cno=course.cno);
方法二：SELECT sno,sname from student where sno in(SELECT sno FROM sc GROUP BY sno HAVING COUNT(cno)=(SELECT COUNT(*) FROM course) );

10、查询选修了“数据结构”和“数据库”两门课的学生的学号、课程号和成绩。（提示：选修了数据结构课程的学生学号与选修了数据库课程的学生学号要相同）。

SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname='数据结构')) AND sno IN(SELECT sno FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname='数据库'));

11、查询1号课程和2号课程的最高成绩和平均成绩。

SELECT cno,AVG(grade) avg_grade,MAX(grade) max_grade FROM sc WHERE cno=1 UNION SELECT cno,AVG(grade) avg_grade,MAX(grade) max_grade FROM sc WHERE cno=2;