SQL语句练习(进阶版

290 阅读3分钟

学生数据库中有三个基本表(关系)如下: 学生表S(Sno,Sname,Age,Sex,SD) 课程表C(Cno,Cname, Teacher) 选课表SC(Sno,Cno,Grade) 请用SQL语言完成以下操作:

(1)检索选修课程名为“数据库系统原理”的学生号和学生姓名。

(2)检索至少选修了课程号为“C1”和“C3”的学生号。

(3)检索选修了“操作系统”或“自动控制原理”课程的学号和成绩。

(4)检索年龄在18到20之间(含18和20)的女生的学号、姓名及年龄。

(5)检索选修了“高博”老师所讲课程的学生的学号、姓名。

(6)检索选修了全部课程的学生姓名。

(7)检索至少选修了学号“S002”的学生选修的全部课程的学生学号。

(8)检索所有姓“蒋”的学生姓名、年龄和所在系。

(9)检索选修了三门课以上的学生姓名、年龄和所在系。

解:以下都是参考代码,仅供参考

--(1)检索选修课程名为“数据库系统原理”的学生号和学生姓名--
--方法1:--
SELECT S.Sno,Sname FROM S,SC,C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Cname=‘数据库系统原理’

--方法2:--
SELECT Sno,Sname FROM S
WHERE Sno IN (SELECT Sno FROM SC
WHERE Cno IN (SELECT Cno FROM C
WHERE Cname=‘数据库系统原理’))

方法1与方法2就是连接查询和嵌套查询的区别。在方法2中 IN 作为 WHERE 的一个筛选条件,与 = 有一定的区别,两者都是等于的意思,但 IN 可以定义多个值的范围,我们查询的结果只要存在于 IN 的范围中就可被视为符合条件

--(2)检索至少选修了课程号为“C1”和“C3”的学生号--
SELECT Sno FROM SC SCX,SC SCY
WHERE SCX.Sno=SCY.Sno AND SCX.Cno=‘C1’ AND SCY.Cno=‘C3’

根据题意,我们需要在一张表里进行互相的查询,所以为了防止出现 Sno = Sno 这样容易混淆的情况,我们通过 SC SCX,SC SCY ,给选课表SC定义两个别名,利于区分。实际上还是 SC 数据表自己和自己的比较。

--(3)检索选修了“操作系统”或“自动控制原理”课程的学号和成绩--
--方法1:--
SELECT Sno,Grade FROM SC,C
WHERE SC.Cno=C.Cno AND (C.Cname=‘操作系统’OR C.Cname=‘自动控制原理’)

--方法2:--
SELECT Sno,Grade FROM SC
WHERE Cno IN (SELECT Cno FROM C
WHERE C.Cname=‘操作系统’OR C.Cname=‘自动控制原理’)

第 (3) 题的思路与第 (1) 题相似,只是把其中一个条件改变为 OR 运算符

--(4)检索年龄在18到20之间(含18和20)的女生的学号、姓名及年龄--
--方法1:--
SELECT Sno,Sname,Age FROM S
WHERE Age>=18 AND Age<=20 AND Sex=‘女’

--方法2:--
SELECT Sno,Sname,Age
FROM S WHERE Age BETWEEN 18 AND 20 AND Sex=‘女’

这道题主要是对于范围的练习,但更推荐使用方法1,因为这样比较清晰地查看查询地范围是否包含边界。BETWEEN …AND 在确定范围中是包含边界的,方法2比较巧妙地利用了这一性质,但如果记忆不清特性,在某些范围查询语句中滥用,就会获得错误的查询结果,因此笔者更推荐方法1的思路。

--(5)检索选修了“高博”老师所讲课程的学生的学号、姓名。--
--方法1:--
SELECT S.Sno,Sname FROM S,SC,C
WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Teacher=‘高博’

--方法2:--
SELECT Sno,Sname FROM S
WHERE Sno IN (SELECT Sno FROM SC
WHERE Cno IN (SELECT Cno FROM C
WHERE Teacher=‘高博’))

这道题的思路与第 (1) 题类似,便不赘述了。

--(6)检索选修了全部课程的学生姓名--
SELECT Sname FROM S
WHERE NOT EXISTS (SELECT * FROM C
WHERE NOT EXISTS (SELECT * FROM SC
WHERE Sno=S.Sno AND Cno=C.Cno))

这道题是运用了 EXIST 的嵌套查询,对于查询结果返回 True 或 False,同时在一条语句中使用两个 NOT EXIST 即表示程序的双重否定,第 (6) 题的语意,笔者大致理解为,查找某些学生,对于他们来说,不存在这样一门课,它对应的选课表里面的学号不存在这个学生的学号。

意思可能还是很难理解,但是我们已经可以理出双重否定的语句,双重肯定表否定,且根据逻辑, ┐∃ ≡ ∀ ┐

即:查找某些学生,对于他们来说,任意一门课,它对应的任意的一张选课表里面的学号都存在这个学生的学号

--(7)检索至少选修了学号“S002”的学生选修的全部课程的学生学号--
SELECT Sno FROM SC SCX
WHERE NOT EXISTS (SELECT * FROM SC SCY
WHERE SCY.Sno=‘S002’ AND NOT EXISTS (SELECT * FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno
AND SCZ.Cno=SCY.Cno))

第 (6) 题和第 (2) 题的思路

--(8)检索所有姓“蒋”的学生姓名、年龄和所在系--
SELECT Sname,Age,SD FROM S
WHERE Sname LIKE ‘蒋%

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

--(9)检索选修了三门课以上的学生姓名、年龄和所在系--
SELECT Sname,Age,SD FROM S
WHERE Sno IN (SELECT Sno FROM SC
GROUP BY Sno HAVING COUNT(*)>3)

WHERE 关键字无法与聚合函数一起使用,HAVING 子句可以让我们筛选分组后的各组数据

分享一下常见的聚合函数: :

以上这些就是此次SQL语句的进阶版,希望你看完能有所收获。