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

实验二:数据查询

  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

 

 

  • 6
    点赞
  • 56
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值