数据库系统概论-实验二：数据查询

实验二：数据查询

1. 查询选修1号课程的学生学号与姓名。
2. 查询选修课程名为数据结构的学生学号与姓名。
3. 查询不选1号课程的学生学号与姓名。
4. 查询学习全部课程学生姓名。
5. 查询所有学生除了选修1号课程外所有成绩均及格的学生的学号和平均成绩，其结果按平均成绩的降序排列。
6. 查询选修数据库原理成绩第2名的学生姓名。
7. 查询所有3个学分课程中有3门以上（含3门）课程获80分以上（含80分）的学生的姓名。
8. 查询选课门数唯一的学生的学号。
9. SELECT语句中各种查询条件的实验。

 

/*1．查询选修1号课程的学生学号与姓名。*/

SELECT DISTINCT S.Sno,Sname

FROM SC,S

WHERE SC.Cno='1' AND SC.Sno=S.Sno

 

 

/*2．查询选修课程名为数据结构的学生学号与姓名。*/

SELECT S.Sno,Sname

FROM SC,S,C

WHERE SC.Sno=S.Sno AND SC.Cno=C.Cno AND C.Cname='数据结构'

 

 

/*3．查询不选1号课程的学生学号与姓名。*/

SELECT S.Sno,Sname

FROM S

WHERE NOT EXISTS

    (SELECT *

    FROM SC

WHERE SC.Sno=S.Sno  AND Cno='1')

 

 

/*4.查询学习全部课程学生姓名。*/

SELECT DISTINCT Sname

FROM S

WHERE NOT EXISTS(

    SELECT *

FROM C

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE SC.Sno=S.Sno AND SC.Cno=C.Cno))

 

 

/*5．查询所有学生除了选修1号课程外所有成绩均及格的学生的学号和平均成绩，其结果按平均成绩的降序排列。*/

SELECT  Sno,AVG(Grade)平均成绩

FROM SC

WHERE NOT EXISTS(

SELECT *

FROM SC

WHERE SC.Cno='1' AND SC.Grade<60)

GROUP BY Sno

ORDER BY AVG(Grade) DESC

 

 

/*6.查询选修数据库原理成绩第2名的学生姓名。*/



SELECT *

FROM (

SELECT S.Sno,S.Sname,SC.Grade,ROW_NUMBER() OVER (PARTITION BY SC.Cno ORDER BY Grade DESC) M

FROM SC

INNER JOIN S ON  SC.Sno=S.Sno

INNER JOIN C ON SC.Cno=C.Cno

WHERE C.Cname='数据库')A

WHERE M  IN (2)

为符合题目要求，改为

SELECT *

FROM (

SELECT S.Sname,ROW_NUMBER() OVER (PARTITION BY SC.Cno ORDER BY Grade DESC) M

FROM SC

INNER JOIN S ON  SC.Sno=S.Sno

INNER JOIN C ON SC.Cno=C.Cno

WHERE C.Cname='数据库')A

WHERE M  IN (2)

 

 

 

/*7.查询所有3个学分课程中有3门以上（含3门）课程获80分以上（含80分）的学生的姓名。*/

/*利用视图查询*/

CREATE VIEW CREDIT_GRADE(Sno,Cno,Grade,Credit)

AS

SELECT Sno,SC.Cno,Grade,Credit

FROM SC

INNER JOIN C ON SC.Cno=C.Cno

WHERE  Credit>=3 AND Grade>=80



SELECT Sname

FROM S

WHERE Sno IN(

SELECT Sno

FROM  CREDIT_GRADE

GROUP BY Sno HAVING COUNT(*)>=3)

 

 

/*8. 查询选课门数唯一的学生的学号。*/

SELECT Sno,COUNT(Cno)课程数

FROM SC

GROUP BY Sno HAVING COUNT(Cno)=1

 

 

第9题没有具体说明，以下是一些cha'xu

/*9．SELECT语句中各种查询条件的实验。*/

/*查询选修了课程号为2且分数大于90的学生学号和姓名*/

SELECT S.Sno,Sname

FROM S,SC

WHERE S.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90

 

 

/*查询选修了1课程或2课程的学生*/

SELECT Sno

FROM SC

WHERE Cno='1'

UNION

SELECT Sno

FROM SC

WHERE Cno='2'

 

 

/*查询计算机科学系中年龄不大于19岁的学生*/

SELECT *

FROM S

WHERE Sdept='CS'

INTERSECT

SELECT *

FROM S

WHERE Sage<=19

 

 

/*查询计算机科学系的学生与年龄不大于19岁的学生的差集*/

/*实际上就是查询计算机科学系中年龄大于19岁的学生*/

SELECT *

FROM S

WHERE Sdept='CS'

EXCEPT

SELECT *

FROM S

WHERE Sage<=19

 

 

/*9.(1)查询课程号为1的成绩大于课程号为2的所有学生学号*/

/*使用到了内连接inner join*/

/*得到的表为 学号| 课程号1的成绩|课程号2的成绩*/

SELECT A.Sno,STU.Sname,A.Grade,B.Grade

FROM(

SELECT Sno,Cno,Grade

FROM SC

WHERE SC.Cno='1'

)AS A

INNER JOIN (

SELECT Sno,Cno,Grade

FROM SC

WHERE Cno='2')AS B ON A.Sno=B.Sno

INNER JOIN S AS STU ON STU.Sno=A.Sno

WHERE A.Grade>B.Grade

 

 

/*9.(2).查询至少两门课不及格的学生学号和姓名*/

/*此处COUNT计算中只计算了Grade<60的情况，成绩为NULL表示缺考，无成绩*/

/*首先是子查询，通过SC表查出至少两门课不及格的学生学号*/

SELECT Sno

FROM SC

WHERE Grade<60

GROUP BY Sno HAVING COUNT(DISTINCT Cno)>=2

更改后，代码如下：

/*嵌套查询,将S表与SC表做内连接*/

SELECT  A.Sno,A.Sname

FROM S AS A

INNER JOIN SC AS B ON A.Sno=B.Sno

WHERE A.Sno IN(

SELECT Sno

FROM SC

WHERE Grade<60

GROUP BY Sno HAVING COUNT(DISTINCT Cno)>=2)

GROUP BY A.Sno,A.Sname

 

/*9.（3）.按平均成绩从高到低显示所有学生的所有课程成绩及平均成绩*/

/*利用聚集函数*/

SELECT Sno,

MAX(CASE WHEN Cno='1' THEN Grade ELSE NULL END)数据库,

MAX(CASE WHEN Cno='2' THEN Grade ELSE NULL END)数学,

MAX(CASE WHEN Cno='3' THEN Grade ELSE NULL END)信息系统,

MAX(CASE WHEN Cno='4' THEN Grade ELSE NULL END)操作系统,

MAX(CASE WHEN Cno='5' THEN Grade ELSE NULL END)数据结构,

MAX(CASE WHEN Cno='6' THEN Grade ELSE NULL END)数据处理,

MAX(CASE WHEN Cno='7' THEN Grade ELSE NULL END)PASCAL语言,

MAX(CASE WHEN Cno='8' THEN Grade ELSE NULL END)Java语言,

MAX(CASE WHEN Cno='9' THEN Grade ELSE NULL END)上机实验,

AVG(Grade)平均成绩

FROM SC

GROUP BY Sno

ORDER BY AVG(Grade) DESC

 

 

/*9.(4)查询每门课的学生名单*/

SELECT A.Sno,Cname

FROM SC AS A

INNER JOIN C ON A.Cno=C.Cno

GROUP BY A.Sno,Cname HAVING  SUM(CASE WHEN A.Cno NOT LIKE 'NULL' THEN 1 ELSE 0 END)=1

 

 

/*9.(5)查询每个学生选了哪些课*/

SELECT A.Sno,Cname

FROM SC AS A

INNER JOIN C ON A.Cno=C.Cno

GROUP BY A.Sno,Cname

 

 

 

/*9.（6）查各科的最高分、最低分和平均分，并按以下格式显示：*/

/*课程号 课程名 最高分 最低分 平均分 成绩及格人数 成绩中等人数 成绩优秀人数*/

/*及格：>=60，中等：[70，80)，优良：[80,90),优秀>=90 */

SELECT SC.Cno,C.Cname,MAX(SC.Grade)最高分,MIN(SC.Grade)最低分,AVG(SC.Grade)平均分,

SUM(CASE WHEN SC.Grade>=60 THEN 1 ELSE 0 END)成绩及格人数,

SUM(CASE WHEN SC.Grade>=70 AND SC.Grade<80 THEN 1 ELSE 0 END)成绩中等人数,

SUM(CASE WHEN SC.Grade>=80 AND SC.Grade<90THEN 1 ELSE 0 END)成绩优良人数,

SUM(CASE WHEN SC.Grade>=90 THEN 1 ELSE 0 END)成绩优秀人数

FROM SC

INNER JOIN C ON SC.Cno=C.Cno

GROUP BY SC.Cno,C.Cname