数据库第七次实验 嵌套查询

2021011212

针对stu数据库中的student、course和sc三个表实现以下查询：
1、查询’CS’系的学生的成绩信息，包括学号，课程号，成绩。

SELECT sno,cno,grade
FROM sclyx 
WHERE sno in
(SELECT sno
FROM studentlyx
WHERE sdept='cs')

 

2、查询邓军所选修的课程的课程名。

SELECT cname
FROM courselyu 
WHERE cno in
(SELECT cno
FROM sclyx
WHERE sno in
(SELECT sno
FROM studentlyx
WHERE sname='邓军'))

3、查询既选了1号课程又选了2号课程的学生学号。

SELECT sno
FROM sclyx 
WHERE cno='1' and sno in
(SELECT sno
FROM sclyx
WHERE cno='2')

4、查询没有选修课程的学生的名单。
   （提示：使用谓词NOT IN引导)

SELECT sname
FROM studentlyx 
WHERE sno not in
(SELECT sno
FROM sclyx)

5、查询选修“数据库”课的全部学生的总成绩。

SELECT SUM(grade) AS 课程总成绩
FROM sclyx 
WHERE cno IN
   (
     SELECT cno
     FROM courselyu
     WHERE cname='课程{10}'
   )

 

6、统计学生选修表，显示学号为“201215121"的学生在其各科成绩中，最高分成绩所对应的课程号和成绩。

SELECT cno ,grade 
FROM sclyx 
WHERE sno='2021000977'
AND grade =
   (
    SELECT MAX(grade) 
    FROM sclyx
    WHERE sno ='2021000977'
       )

思考：如果该学号学生有两个课程分数都为最高的100分，查询会有什么结果？
7、查询数学系选修"课程{10}"课的所有学生的成绩之和。

SELECT SUM(grade) AS 课程总成绩
FROM sclyx 
WHERE cno IN
  (
   SELECT cno
   FROM courselyu
   WHERE cname='课程{10}') 
AND sno IN 
  (
    SELECT sno
    FROM studentlyx
    WHERE sdept='MA')
   

8、查询数学系"张三"同学的"数据库"成绩。

SELECT grade
FROM sclyx
WHERE cno IN
  (
    SELECT cno 
    FROM courselyu
    WHERE cname='课程{10}'
  ) 
AND sno IN
  (
    SELECT sno 
    FROM studentlyx 
    WHERE sdept='IS'
    AND sname='邓军'
  )

9、查询学号为’201215121’的学生没选的课程的课程号和课程名。

SELECT cno,cname
FROM courselyu
WHERE cno not in 
(SELECT cno
FROM sclyx                  
WHERE sno='2021000977')

10、检索至少选修了两门课程的学生的信息，包括学号，姓名和所在系。      (提示：having)

SELECT sno,sname,sdept
FROM studentlyx
WHERE sno in 
  (SELECT sno
   FROM  sclyx
   Group by sno
   Having count(cno)>=2  )

 

使用EXISTS实现以下查询：

1、查询选修了课程的学生的名单。

SELECT sname
FROM studentlyx
WHERE EXISTS 
( SELECT *
FROM sclyx                   
WHERE studentlyx.sno=sno )

 

2、查询学号为’201215121’的学生没选的课程的课程号和课程名。

SELECT cno,cname
FROM courselyu
WHERE  not EXISTS 
(SELECT *
FROM sclyx                   
WHERE cno=courselyu.cno and sno='2021000977')