23. 查询“张旭“教师任课的学生成绩
SELECT degree FROM scores AS sc
INNER JOIN
(SELECT co.cno FROM courses AS co
INNER JOIN
(SELECT t.tno FROM teachers AS t WHERE tname = '张旭') AS s1
ON co.tno = s1.tno) AS s2
ON sc.cno = s2.cno
24. 查询选修某课程的同学人数多于5人的教师姓名
# 先查找选修课程人数大于5人的课程
SELECT tname FROM teachers
INNER JOIN
(SELECT co.tno FROM courses AS co
INNER JOIN
(SELECT sc.cno FROM scores AS sc
GROUP BY sc.cno
HAVING COUNT(sc.sno) > 5) AS s1
ON co.cno = s1.cno) AS s2
ON teachers.tno = s2.tno
25. 查询95033班和95031班全体学生的记录
SELECT * FROM students
WHERE class IN (95033,95031)
ORDER BY class,sno
26. 查询存在有85分以上成绩的课程Cno
# 法一
SELECT distinct cno FROM scores WHERE degree > 85
# 法二
SELECT cno FROM scores
GROUP BY cno HAVING MAX(degree) > 85
27. 查询出“计算机系“教师所教课程的成绩表
SELECT depart,cname,degree FROM teachers
INNER JOIN courses ON (teachers.tno = courses.tno)
INNER JOIN scores ON (courses.cno = scores.cno)
WHERE depart = '计算机系'
ORDER BY degree DESC
28. 查询“计算机系”中与“电子工程系“不同职称的教师的Tname和Prof
SELECT tname,prof FROM teachers
WHERE depart = '计算机系'
AND prof NOT IN
(SELECT DISTINCT prof FROM teachers WHERE depart = '电子工程系')
29. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
# 法一
SELECT cno,sno,degree FROM scores
WHERE cno = '3-105' AND
degree > ANY(SELECT degree FROM scores WHERE cno = '3-245')
ORDER BY degree DESC
any是任意一个，all是所有
val>ANY(subnet):表示val比subnet中最小的元素大即可
val=ANY(subset):表示val等于subnet子集中的任何一个元素即可
# 法二
SELECT cno,sno,degree FROM scores
WHERE cno = '3-105' AND
degree > (SELECT MIN(degree) FROM scores WHERE cno = '3-245')
ORDER BY degree DESC
30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree。
# 法一
SELECT cno,sno,degree FROM scores
WHERE cno = '3-105' AND
degree > ALL(SELECT degree FROM scores WHERE cno = '3-245')
ORDER BY degree DESC
# 法二
SELECT cno,sno,degree FROM scores
WHERE cno = '3-105' AND
degree > (SELECT MAX(degree) FROM scores WHEREcno = '3-245')
ORDER BY degree DESC
31.查询所有教师和同学的name、sex和birthday。
SELECT sname,ssex,sbirthday FROM students
UNION
SELECT tname,tsex,tbirthday FROM teachers
查询所有女教师和女同学的name、sex和birthday。
SELECT sname,ssex,sbirthday FROM students
WHERE ssex = '女'
UNION
SELECT tname,tsex,tbirthday FROM teachers
WHERE tsex = '女'
33.查询成绩比该课程平均成绩低的同学的成绩表。
SELECT DISTINCT s1.* FROM scores AS s1 INNER JOIN
(SELECT cno,AVG(degree) AS avg1 FROM scores GROUP BY cno) AS s2
ON (s1.`cno` = s2.cno AND s1.`degree` < s2.avg1)
ORDER BY degree DESC
34.查询所有任课教师的Tname和Depart。
SELECT DISTINCT tname,depart FROM teachers AS t
WHERE t.`tno` NOT IN (SELECT c.tno FROM courses AS c)
35.查询所有未讲课的教师的Tname和Depart。
SELECT DISTINCT tname,depart FROM teachers AS t
WHERE t.`tno` NOT IN (SELECT c.tno FROM courses AS c)
36.查询至少有2名男生的班号。
SELECT class FROM students
WHERE ssex = '男' GROUP BY class
HAVING COUNT(ssex) >= 2
37. 查询Student表中不姓“王”的同学记录。
SELECT sname FROM students
WHERE sname NOT LIKE '王%'
模糊查询用like或not like
%匹配任意字符，_匹配单个字符
38. 查询Student表中每个学生的姓名和年龄。
SELECT Sname,YEAR(GETDATE())-YEAR(Sbirthday)AS age FROM Students
39.查询Student表中最大和最小的Sbirthday日期值
SELECT MAX(sbirthday),MIN(sbirthday) FROM students
40. 以班号和年龄从大到小的顺序查询Student表中的全部记录
SELECT * FROM students
ORDER BY class DESC,sbirthday ASC
41. 查询“男”教师及其所上的课程
SELECT t.tname,c.cname FROM courses AS c
INNER JOIN teachers AS t ON c.`tno`= t.`tno`
WHERE t.`tsex` = '男'
42. 查询最高分同学的Sno、Cno和Degree列
SELECT * FROM scores ORDER BY degree DESC LIMIT 1
43. 查询和“李军”同性别的所有同学的Sname
SELECT sname FROM students
WHERE ssex IN (SELECT ssex FROM students WHERE sname = '李军')
44. 查询和“李军”同性别并同班的同学Sname
SELECT sname FROM students WHERE
ssex IN (SELECT ssex FROM students WHERE sname = '李军')
AND class IN (SELECT class FROM students WHERE sname = '李军')
45. 查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT cname,sname,degree FROM courses
INNER JOIN scores ON (courses.cno = scores.cno)
INNER JOIN students ON (scores.sno = students.sno)
WHERE ssex = '男' AND cname = '计算机导论'
网友评论