「 数据查询（综合篇）」

--综合篇
--1.查询每个学生及其选课情况（等值连接 和 右连接）
SELECT STUDENT.*,cno,grade FROM STUDENT,SC WHERE STUDENT.sno = SC.sno;
SELECT STUDENT.*,cno,grade FROM STUDENT RIGHT OUTER JOIN SC ON STUDENT.sno = SC.sno;
--2.查询每门课的间接选修课
SELECT X.cno,Y.cno FROM SC X,SC Y WHERE X.cno = Y.cno;
--3.查询既选修了2号课程又选修了3号课程的学生姓名、学号
SELECT STUDENT.sname,STUDENT.sno FROM SC,STUDENT WHERE STUDENT.sno IN(SELECT STUDENT.sno FROM SC WHERE cno = '2') AND cno = '3'
AND STUDENT.sno = SC.sno;
--4.查询和刘晨同一年龄的学生
SELECT X.sno,Y.sname FROM STUDENT X,STUDENT Y WHERE X.sage IN(SELECT Y.sage FROM STUDENT Y WHERE Y.sname = '刘晨') AND X.sno = Y.sno;
--5.查询选修了课程名为“数据库”的学生姓名和年龄（相关子查询 和 不相关子查询）
--相关子查询
SELECT sname,sage FROM STUDENT WHERE EXISTS(
    SELECT * FROM SC WHERE EXISTS(
        SELECT * FROM COURSE WHERE cname = '数据库' 
            AND cno = SC.cno AND sno = STUDENT.sno
        )
);
--不相关子查询
SELECT sname,sage FROM STUDENT WHERE sno IN    (
        SELECT sno FROM SC WHERE cno IN    (
            SELECT cno FROM COURSE WHERE cname = '数据库')
        );
--6.查询其他系中比IS系任一学生年龄小的学生名单（两种解法）
SELECT sno,sname FROM STUDENT WHERE sdept <> 'IS' AND sage < ANY(SELECT sage FROM STUDENT WHERE sdept = 'IS');
SELECT sno,sname FROM STUDENT WHERE sdept <> 'IS' AND sage < (SELECT MAX(sage) FROM STUDENT WHERE sdept = 'IS');
--7.查询其他系中比IS系所有学生年龄都小的学生名单（两种解法）
SELECT sno,sname FROM STUDENT WHERE sdept <> 'IS' AND sage < ALL(SELECT sage FROM STUDENT WHERE sdept = 'IS');
SELECT sno,sname FROM STUDENT WHERE sdept <> 'IS' AND sage < (SELECT MIN(sage) FROM STUDENT WHERE sdept = 'IS');
--8.查询选修了全部课程的学生姓名（相关子查询 和 不相关子查询）
--相关子查询
SELECT sname FROM STUDENT WHERE NOT EXISTS (
    SELECT * FROM SC WHERE NOT EXISTS (
        SELECT * FROM COURSE WHERE 
            STUDENT.sno = SC.sno AND SC.cno = COURSE.cno
        )
    );
--不相关子查询
SELECT sname FROM STUDENT WHERE sno IN(
    SELECT sno FROM SC GROUP BY sno HAVING COUNT(*) = (
        SELECT COUNT(*) FROM COURSE
        )
    );
--9.查询计算机系学生及其性别是男的学生
SELECT sno,sname FROM STUDENT WHERE sdept = 'IS' AND ssex = '男';
--相关子查询
SELECT X.sno,X.sname FROM STUDENT X WHERE EXISTS(
    SELECT * FROM STUDENT Y WHERE 
        Y.ssex = '男' AND Y.sdept = 'IS' 
        AND X.sno = Y.sno
    );
--10.查询张立同学不学的课程的课程号
--相关子查询
SELECT cno FROM COURSE WHERE NOT EXISTS(
    SELECT * FROM SC WHERE EXISTS(
        SELECT * FROM STUDENT WHERE sname = '张立'
            AND STUDENT.sno = SC.sno AND SC.cno = COURSE.cno
        )
);
--不相关子查询
SELECT cno FROM COURSE WHERE cno NOT IN(
    SELECT cno FROM SC WHERE sno =(
        SELECT sno FROM STUDENT WHERE sname = '张立'
        )
);
--11.查询选修了3号课程的学生平均年龄
--相关子查询
SELECT AVG(sage) AS avgsage FROM STUDENT WHERE EXISTS(
    SELECT * FROM SC WHERE cno = '3' 
    AND STUDENT.sno = SC.sno
);
--不相关子查询
SELECT AVG(sage) AS avgsage FROM STUDENT WHERE sno IN(
    SELECT sno FROM SC WHERE cno = '3' 
);
--12.求每门课程学生的平均成绩
SELECT cno,AVG(grade) FROM SC GROUP BY cno;
--13.统计每门课程的学生选修人数（超过1人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cno,COUNT(*) AS number FROM SC GROUP BY cno HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC,cno ASC;