河北经贸数据库实验3 嵌套查询和集合查询

河北鸡毛大学

__________数据库原理___实验报告

1．在教学管理 JXGL 数据库中进行如下操作：

(1) 求选修了数学的学生的学号和姓名。

SELECT STUDENT.Sno,Sname

FROM STUDENT

WHERE Sno IN 

 (SELECT Sno

  FROM SC,COURSE

  WHERE Cname='数学' AND

     SC.Cno=COURSE.Cno);

(2) 求数学课程成绩高于李勇的学生学号和成绩。

SELECT Sno,Grade

FROM SC

WHERE Cno IN(

SELECT SC.Cno

FROM COURSE,SC

WHERE Cname='数学' AND

COURSE.Cno=SC.Cno) AND

Grade IN(

SELECT Grade

FROM STUDENT,COURSE,SC

WHERE COURSE.Cno=SC.Cno AND

Cname='数学' AND

Grade>(SELECT Grade

FROM STUDENT,SC,COURSE

WHERE Cname='数学' AND

Sname='李勇'AND

STUDENT.Sno=SC.Sno AND

COURSE.Cno=SC.Cno));

(3) 求其他系中年龄小于计算机系年龄最大者的学生。

SELECT *
FROM STUDENT
WHERE Sage<(SELECT MAX(Sage)
			FROM STUDENT
			WHERE Sdept='CS')
		AND	Sdept<>'CS';

(4) 求其他系中比计算机系学生年龄都小的学生。

SELECT *
FROM STUDENT
WHERE Sage<ALL
			(SELECT (Sage)
			 FROM STUDENT
			 WHERE Sdept='CS')
		AND Sdept<>'CS';

(5) 求选修了数学课的学生姓名。

SELECT Sname
FROM STUDENT
WHERE EXISTS 
		(SELECT *
		 FROM SC,COURSE
		 WHERE STUDENT.Sno=SC.Sno
			AND Cname='数学' 
			AND SC.Cno=COURSE.Cno);

(6) 求没有选修数学课的学生姓名。

SELECT Sname
FROM STUDENT
WHERE NOT EXISTS 
		(SELECT *
		 FROM SC,COURSE
		 WHERE STUDENT.Sno=SC.Sno
			AND Cname='数学' 
			AND SC.Cno=COURSE.Cno);

(7) 查询选修了全部课程的学生的姓名。

SELECT Sname
FROM STUDENT
WHERE NOT EXISTS 
		(SELECT *
		 FROM SC,COURSE
		 WHERE NOT EXISTS
				(SELECT *
				 FROM SC
				 WHERE Sno=STUDENT.Sno
					AND Cno=COURSE.Cno));

(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。

SELECT  DISTINCT STUDENT.Sno,Sname
FROM SC SCX,STUDENT
WHERE STUDENT.Sno=SCX.Sno AND
		NOT EXISTS
		(SELECT *
		 FROM SC SCY
		 WHERE SCY.Sno='95002' AND
			NOT EXISTS
			(SELECT *
			 FROM SC SCZ
			 WHERE SCZ.Sno=SCX.Sno AND
					SCZ.Cno=SCY.Cno)); 

(9) 求选修各门课的人数及平均成绩。

SELECT Cno,COUNT(Sno) AS NUM,AVG(Grade) AS AGrade
FROM SC
GROUP BY Cno;

(10)求选修课程在 2 门以上且都及格的学生号及总平均分。

SELECT Sno,AVG(Grade) AS AVG
FROM SC
WHERE Grade>=60
GROUP BY Sno
HAVING COUNT(*)>2;

(11)求 95 级学生中选修课程在 2 门以上且都及格的学生号及总平均分，按平均成绩排序。

SELECT Sno,AVG(Grade) AS AVG
FROM SC
WHERE Sno LIKE '95%' AND Grade>=60
GROUP BY Sno
HAVING COUNT(*)>2
ORDER BY AVG(Grade);

(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno,AVG(Grade) AS GAVG,COUNT(*) AS CSUM
FROM SC
WHERE Grade>=60
GROUP BY Sno
ORDER BY GAVG DESC,CSUM DESC;

(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno,AVG(Grade) AS GAVG,COUNT(*) AS CSUM
FROM SC SCX
WHERE NOT EXISTS(SELECT *
			 FROM SC SCY
			 WHERE NOT EXISTS (
					SELECT *
					FROM SC SCZ
					WHERE Sno=SCY.Sno AND Grade>=60))
GROUP BY Sno
ORDER BY GAVG DESC,CSUM DESC;

2．在供应系统 GYXT 数据库中进行如下操作：

(1) 求供应工程 J1 红色零件的供应商号 SNO。

SELECT SNO
FROM SPJ,P
WHERE SPJ.PNO=P.PNO 
	  AND COLOR='红'
	  AND JNO='J1';

(2) 求零件供应总量在 1000 种以上的供应商名字。

SELECT Sname
FROM S
WHERE SNO IN (
		SELECT SNO
		FROM SPJ
		GROUP BY SNO
		HAVING SUM(QTY)>=1000);