一、下面进行一些比较典型的查询例子：
1．在教学管理JXGL数据库中进行如下操作：
(1) 求选修了数学的学生的学号和姓名。
SELECT DISTINCT SC.Sno,Sname
FROM STUDENT,SC
WHERE Cno='2'AND SC.Sno=STUDENT.Sno;
(2) 求数学课程成绩高于李勇的学生学号和成绩。
SELECT DISTINCT SC.Sno,Grade
FROM STUDENT,SC
WHERE Cno='2'AND Grade>=92 AND SC.Sno=STUDENT.Sno;
(3) 求其他系中年龄小于计算机系年龄最大者的学生。
SELECT *
FROM STUDENT
WHERE Sage>ALL(SELECT Sage
FROM STUDENT
WHERE Sdept='CS'
)AND Sdept!='CS';
(4) 求其他系中比计算机系学生年龄都小的学生。
SELECT *
FROM STUDENT
WHERE Sage<ANY(SELECT Sage
FROM STUDENT
WHERE Sdept='CS'
)AND Sdept!='CS';
(5) 求选修了数学课的学生姓名。
SELECT Sname
FROM STUDENT
WHERE EXISTS(SELECT *
FROM SC
WHERE Cno='2' AND SC.Sno=STUDENT.Sno);
(6) 求没有选修数学课的学生姓名。
SELECT Sname
FROM STUDENT
WHERE NOT EXISTS
( SELECT *
FROM SC
WHERE Cno='2' AND SC.Sno=STUDENT.Sno);
(7) 查询选修了全部课程的学生的姓名。
SELECT Sname
FROM STUDENT
WHERE NOT EXISTS( SELECT *
FROM COURSE
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE STUDENT.Sno=SC.Sno AND COURSE.Cno=SC.Cno));
(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。
SELECT Sno,Sname
FROM STUDENT
WHERE EXISTS(SELECT *
FROM SC
WHERE Sno=STUDENT.Sno AND Cno IN(SELECT Cno
FROM SC
WHERE Sno='95002'));
(9) 求选修各门课的人数及平均成绩。
SELECT Cno,COUNT(*)AS 人数,AVG(Grade)AS 平均成绩
FROM SC
GROUP BY Cno;
(10)求选修课程在2门以上且都及格的学生号及总平均分。
SELECT Sno,AVG(Grade)AS 总平均分
FROM SC
GROUP BY Sno
HAVING COUNT(Cno)>=2 AND MIN(Grade)>='60';
(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。
SELECT Sno,AVG(Grade)AS 总平均分
FROM SC
WHERE EXISTS(SELECT *
FROM STUDENT
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno=SC.Sno AND Cno>='60'))
GROUP BY Sno
HAVING COUNT(Cno)>='2';
(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。
SELECT AVG(Grade)AS 平均值,COUNT(*)AS 及格的门数
FROM SC
WHERE Grade>='60'
GROUP BY Sno
ORDER BY 平均值DESC, 及格的门数DESC;
(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。
SELECT Sno,AVG(Grade)AS 平均成绩,COUNT(*)AS 及格的门数
FROM SC
WHERE EXISTS(SELECT *
FROM STUDENT
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno=STUDENT.Sno))
AND Grade>='60'
GROUP BY Sno
ORDER BY 平均成绩DESC, 及格的门数DESC;
2．在供应系统GYXT数据库中进行如下操作：
(1) 求供应工程J1红色零件的供应商号SNO。
SELECT DISTINCT SPJ.SNO
FROM SPJ
WHERE PNO IN(
SELECT PNO
FROM P
WHERE COLOR='红')AND JNO='J1'
(2) 求零件供应总量在1000种以上的供应商名字。
SELECT SNAME
FROM S
WHERE SNO IN(
SELECT SNO
FROM SPJ
GROUP BY SNO
HAVING SUM(QTY)>='1000')
