数据库实验七——嵌套查询

一、查询"cs"系的学生的成绩信息，包括学号，课程号，成绩。

SELECT sno,cno,grade
FROM sclj
WHERE sno IN
   (SELECT sno
    FROM studentlj
    WHERE sdept='cs')

 二、查询李1所选修的课程的课程名。

SELECT cname
FROM courselj
WHERE Cno IN
   (SELECT Cno
    FROM sclj
    WHERE Sno IN
     (SELECT Sno
      FROM studentlj
      WHERE sname='李1'))

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

SELECT sno
FROM sclj
WHERE Cno='1' AND sno IN
   (SELECT sno 
   FROM sclj
   WHERE Cno='2')

 

四、查询没有选修课程的学生的名单。 

SELECT sname
FROM studentlj
WHERE Sno NOT IN
   (SELECT Sno
    FROM sclj)

五、查询选修“ch”课程的全部学生的总成绩。

SELECT SUM(grade) AS zong
FROM sclj
WHERE cno IN
  (
  SELECT cno
  FROM courselj
  WHERE cname='ch' )

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

SELECT cno,Grade
FROM sclj
WHERE Sno='2021001976'
AND Grade=
  (
   SELECT MAX (Grade)
   FROM sclj
   WHERE Sno='2021001976')

 

 七、查询数学系选修“语文"课程的所有学生的成绩之和。

SELECT SUM(Grade) as 语文总成绩
FROM sclj
WHERE Cno IN
(SELECT Cno
 FROM courselj
 WHERE Cname='语文')
 AND Sno IN
 (
     SELECT Sno
     FROM studentlj
     WHERE Sdept='ma'
     )

 

 

八、查询数学系‘赵980”同学“sc”的成绩

SELECT grade
FROM sclj
WHERE cno IN
 (SELECT cno
  FROM courselj
  WHERE cname='sc')
AND sno IN
 (SELECT sno
  FROM studentlj
  WHERE sdept='ma' 
  AND sname='赵980')

九、查询学号为“2021001977”的学生没选的课程的课程号和课程名。

SELECT DISTINCT cno,cname 
FROM courselj 
WHERE cno NOT IN 
  (SELECT cno 
   FROM sclj 
   WHERE Sno='2021001977')

十、检索至少选修了两门课程的学生的信息，包括学号，姓名，所在系。 

SELECT sno,sname,sdept 
FROM studentlj 
WHERE sno IN 
  (SELECT sno 
   FROM sclj 
   GROUP BY sno 
   HAVING COUNT(Cno)>=2)