河北大学数学与信息科学学院
1.实验内容
用SQL语句和企业管理器建立如下的表结构并输入数据
给定表结构如下：
学生表：student（主键Sno）
| 学号 Sno | 姓名 Sname | 性别 Ssex | 年龄 Sage | 所在系 Sdept | | 95001 | 李勇? | 男? | 20 | CS | | 95002 | 刘晨? | 女? | 21 | IS | | 95003 | 王敏? | 女? | 18 | MA | | 95004 | 张力? | 男? | 19 | IS |
课程表：Course（主键Cno）
| 课程号 Cno | 课程名 Cname | 先行课 Cpno | 学分 Ccredit | | 1 | 数据库? | 5 | 4 | | 2 | 数学? | | 2 | | 3 | 信息系统? | 1 | 4 | | 4 | 操作系统? | 6 | 3 | | 5 | 数据结构? | 7 | 4 | | 6 | 数据处理? | | 2 | | 7 | PASCAL语言? | 6 | 4 |
选课表：SC（主键Sno,Cno，外部键Sno，Cno）
| 学号 Sno | 课程表 Cno | 成绩 Grade | | 95001 | 1 | 92 | | 95001 | 2 | 85 | | 95001 | 3 | 88 | | 95002 | 2 | 90 | | 95002 | 3 | 85 | | 95003 | 3 | 59 |
2.实验步骤
(一)查询信息系（IS）的所有学生信息
SELECT
*
FROM
student
WHERE
Sdept = "IS"
（二）查询选修了“数学”课的所有学生名单
SELECT
s.Sname
FROM
student s
LEFT JOIN sc c ON s.Sno = c.Sno
WHERE
c.Cno = ( SELECT Cno FROM course WHERE Cname = "数学" )
（三）查询至少选修了一门其直接先行课为5号课程的学生的姓名。
SELECT
s.Sname
FROM
student s,
sc c,
course o
WHERE
o.Cpno = '5'
AND c.Cno = o.Cno
AND c.Sno = s.Sno
（四）查询全体学生的姓名和出生年份。
SELECT
Sname AS NAME,
2021- Sage AS birthday
FROM
student
（五）查询所有姓王的学生。
SELECT
Sname
FROM
student
WHERE
Sname LIKE '王%'
（六）查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。
SELECT
s.Sname,
c.Grade
FROM
student s,
sc c
WHERE
c.Cno = '3'
AND c.Sno = s.Sno
ORDER BY
Grade DESC
（七）查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT
*
FROM
student
ORDER BY
Sdept ASC,
Sage DESC
（八）计算2号课程的平均成绩。
SELECT
AVG( Grade ) AS 平均成绩
FROM
sc
WHERE
Cno = '2'
（九）查询选修了2号课程的学生的最高成绩。
SELECT
MAX( Grade ) AS 最大成绩
FROM
sc c
WHERE
c.Cno = '2'
（十）求各个课程号及相应的选课人数。
SELECT
Cno AS 课程号,
count( Sno ) AS 选课人数
FROM
sc c
GROUP BY
c.Cno
（十一）查询至少选修了3门课程以上的学生学号。
SELECT
Sno
FROM
sc
GROUP BY
Sno
HAVING
count( Cno ) >=3
（十二）查询“数据库”的间接先行课。
SELECT
Cname
FROM
course
WHERE
Cno = ( SELECT Cpno FROM course WHERE Cname = '数据库' )
（十三）查询平均成绩最高的学生的学号和姓名。
SELECT
c.Sno AS 学号,
s.Sname AS 姓名
FROM
sc c,
student s
WHERE
c.Sno = s.Sno
GROUP BY
c.Sno
ORDER BY
AVG( Grade ) DESC
LIMIT 1
（十四）查询数学成绩最高的学生的学号和姓名。
SELECT
s.Sno AS 学号,
s.Sname AS 姓名
FROM
student s,
sc c
WHERE
s.Sno = c.Sno
AND c.Cno = ( SELECT Cno FROM course WHERE Cname = "数学" )
ORDER BY
Grade DESC
LIMIT 1
（十五）查询出成绩最低学号最大的学生学号。
SELECT
(
SELECT
c.Sno AS 学号
FROM
sc c,
student s
WHERE
c.Sno = s.Sno
GROUP BY
c.Sno
ORDER BY
AVG( Grade ) ASC
LIMIT 1
) 成绩最低的学号,
( SELECT s.Sno FROM student s ORDER BY Sno DESC LIMIT 1 ) 学号最大的学生的学号
（十六）查询成绩高于学生平均成绩的记录。
SELECT
*
FROM
student s,
sc c,
course o
WHERE
c.Grade > ( SELECT AVG( Grade ) FROM sc)
AND c.Sno=s.Sno
AND c.Cno=o.Cno
（十七）查询至少选修了1号课程和3号课程的学生学号。
SELECT
c.Sno
FROM
sc c
WHERE
c.Cno = 3
AND EXISTS ( SELECT * FROM sc a WHERE a.Sno = c.Sno AND a.Cno = 1 )
（十八）查询只选修了1号课程和3号课程的学生学号。
SELECT
c.Sno
FROM
sc c
WHERE
c.Cno = 3
AND EXISTS ( SELECT * FROM sc a WHERE a.Sno = c.Sno AND a.Cno = 1 )
AND c.Sno IN ( SELECT Sno FROM sc GROUP BY Sno HAVING COUNT( Cno ) = 2 );
（十九）查询没有选修1号课程的学生姓名。
SELECT
s.Sname
FROM
student s
WHERE
s.Sno not in(SELECT Sno FROM sc WHERE cno=1);
（二十）查询选修了全部课程的学生姓名。
SELECT
s.Sname
FROM
student s
WHERE
NOT EXISTS (
SELECT
*
FROM
course
WHERE
NOT EXISTS ( SELECT * FROM sc WHERE sc.Sno = s.Sno AND sc.Cno = course.Cno )
);
（二十一）查询至少选修了95002所选修的全部课程的学生学号。
SELECT DISTINCT
x.Sno
FROM
sc x
WHERE
NOT EXISTS (
SELECT
*
FROM
sc y
WHERE
y.Sno = 95002
AND NOT EXISTS ( SELECT * FROM sc z WHERE z.Cno = y.Cno AND z.Sno = x.Sno )
);
（二十二）查询没有不及格课程的学生的学号和姓名。
SELECT DISTINCT
s.Sno,
s.Sname
FROM
student s
LEFT JOIN sc c ON s.Sno = c.Sno
WHERE
c.Grade >= 60
（二十三）查询没有不及格学生的课程的课程号和课程名。
SELECT DISTINCT
o.Cno,
o.Cname
FROM
course o
LEFT JOIN sc c ON c.Cno = o.Cno
WHERE
c.Cno NOT IN ( SELECT Cno FROM sc WHERE Grade <= 60 )
（二十四）建立信息系学生视图，并从视图中查询年龄最大的学生记录。
CREATE VIEW is_student AS SELECT
*
FROM
student
WHERE
Sdept = 'IS' WITH CHECK OPTION;
SELECT
*
FROM
is_student
WHERE
Sage IN ( SELECT max( Sage ) FROM is_student );
|