【实验10-11 第4章综合实验】

实验10-11 第4章综合实验

提示：以上题目中如果答案运行结果出现了空白，则说明被查询表中没有该“人名“或“系名”，可以更改为表中已经存在的信息。如：没有张力，可以改成于田田。没有CS系可以改成软件系。

在Grademanager数据库中完成下面操作：

(1) 查询以‘DB_’开头，且倒数第3个字符为‘s’的课程的详细情况；

SELECT *
FROM course
WHERE cname LIKE 'DB/_%'  ESCAPE  '/' ;

(2) 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；

SELECT sname 姓名,a.sno 学号,b.cno 课程号,cname 课程名
FROM student a,course b,sc c
WHERE a.sno=c.sno AND b.cno=c.cno
AND sname LIKE '_高%';

思考能否用嵌套查询？

不能，外层查询涉及多个表

(3)列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

SELECT b.sno 学号,sname 姓名,sdept 院系,a.cno 课程号,degree 成绩
FROM course a,student b,sc c
WHERE a.cno=c.cno AND b.sno=c.sno
AND cname ='高等数学' OR cname='大学英语';

(4) 查询缺少成绩的所有学生的详细情况；

SELECT  a.*
FROM student a,sc b
WHERE a.sno=b.sno 
AND degree IS NULL;

(5) 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；（如表中没有张力，按照实际情况修改即可）

SELECT *
FROM student
WHERE  YEAR(CURDATE())- YEAR(sbirthday)<>
(SELECT YEAR(CURDATE())- YEAR(sbirthday)
	FROM student
	WHERE sname='王洪利'
);

(6) 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；（如表中没有张力，按照实际情况修改即可）

SELECT a.sno,sname,avg(degree)
FROM student a,sc b
WHERE a.sno=b.sno
group by a.sno,sname
HAVING avg(degree)>
(SELECT avg(degree)
FROM sc
WHERE cno in (
	SELECT cno
	FROM sc
	WHERE sno=(
		SELECT sno
		FROM student
		WHERE sname='张力'
)));

(7) 列出只选修一门课程的学生的学号、姓名、院系及成绩；

SELECT sno 学号,sname 姓名,sdept 院系
FROM student
WHERE sno IN
(SELECT sno
 FROM sc
 GROUP BY sno
 HAVING COUNT(*)=3
);

(8) 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；（如表中没有张力，按照实际情况修改即可）

SELECT a.sno,sname,cno
FROM student a,sc b
WHERE a.sno=b.sno
AND a.sno in 
(SELECT sno
 FROM sc
 WHERE cno IN
(SELECT cno
 FROM student a,sc b
 WHERE a.sno=b.sno AND sname='张力' 
))
AND sname != '张力'; 

(9) 只选修“数据库”和“数据结构”两门课程的学生的基本信息；（如表中没有“数据库”和“数据结构”，按照实际情况修改即可）

SELECT a.*
FROM student a,sc b,course c
WHERE a.sno=b.sno 
AND b.cno=c.cno
AND cname IN('数据库' AND'数据结构')
AND b.sno IN(SELECT sno
    FROM sc  
		GROUP BY sno
		HAVING COUNT(*)=2
); 

(10) 至少选修“数据库”或“数据结构”课程的学生的基本信息；（如表中没有“数据库”和“数据结构”，按照实际情况修改即可）

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

(11) 列出所有课程被选修的详细情况，包括课程号、课程名、学号及成绩；

SELECT a.cno,cname,sno,degree 
FROM course a
LEFT JOIN sc b
ON a.cno=b.cno;

(12) 查询只被一名学生选修的课程的课程号、课程名；

SELECT a.cno,cname
FROM course 
WHERE  cno in 
(SELECT cno
FROM sc
GROUP BY cno
HAVING COUNT(sno)=122); 

(13) 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；（如表中没有“数据结构”，按照实际情况修改即可）

SELECT sname 姓名,sno 学号
FROM student
WHERE sno IN
	(SELECT sno
	FROM sc
	WHERE cno=
		(SELECT cno
		FROM course
		WHERE cname='数据结构'));

(14) 使用嵌套查询查询其它系中年龄小于计算机系的某个学生的学生姓名、年龄和系别；

SELECT sname,sbirthday,sdept
FROM student
WHERE YEAR(CURDATE())-YEAR(sbirthday)< 
(SELECT MAX(YEAR(CURDATE())-YEAR(sbirthday))
 FROM student
 WHERE sdept='计算机系'
)
AND sdept<>'计算机系';

(15) 使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生；
提示：（如表中没有，按照实际情况修改即可）。

SELECT * 
from student 
where YEAR(CURDATE())-YEAR(sbirthday)<
all(SELECT YEAR(CURDATE())-YEAR(sbirthday)
from student 
where sdept='CS') 
and sdept<>'CS'

(16) 分别使用连接查询和嵌套查询，列出与‘张力’在一个系的学生的信息；（如表中没有张力，按照实际情况修改即可）
提示：如果没有张力，可以改成于田田。

SELECT b.*
FROM student a,student b
where a.sdept=b.sdept
AND a.sname='张力'
AND b.sname<>'张力';
SELECT *
FROM student
WHERE sdept=
(SELECT sdept
 FROM student
 WHERE sname='张力'
);

(17) 使用集合查询列出CS系系的学生以及性别为女的学生名单；（如表中没有CS系，按照实际情况修改即可）

SELECT sname
FROM student
WHERE sdept='CS'
UNION
SELECT sname
FROM student
WHERE ssex='女';