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

实验二:数据查询

  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
    评论
实验报告( 第 1 周 ) 班级: 姓名: 学号: 实验名称:[实验名称] SQL Server 2005的安装及相关配置 报告内容:(根据各个实验具体要求填写,题目形式自定。) 实验内容: 一、写出实验内容步骤及完成情况以及出现问题及解决方案 如:安装SQL Server 2005企业版、注册服务器的管理等操作 SQL Sever 2005企业版的安装: 1. 安装Internet信息服务(IIS) Windows系统默认安装有IE浏览器。因此,首先安装Internet信息服务( IIS) 2. 安装SQL Sever 2005及其组件 因为SQL Server 2005软件安装包中有ASP.NET2.0、Microsoft Windows NET Framework2.0、Microsoft Windows Installer3.1、Microsoft 数据访问组件(MAC)2.8 SPI。 3、 设置账户 在"实例名"页上,请为安装的软件选择默认实例或已命名的实例。 在"服务帐户"页上,为 SQL Server 服务帐户指定用户名、密码和域名。您可以对所有服务使用一个帐户。 在"身份验证模式"页上,选择要用于 SQL Server 安装的身份验证模式。还必须输入并确认用于 sa 登录的强密码。若要继续安装,请单击"下一步"。 如果可能,请使用 Windows 身份验证。 在"排序规则设置"页上,指定 SQL Server 实例的排序规则。若要为 SQL Server 和 Analysis Services 设置单独的排序规则设置,请选中"为每个服务帐户进行自定义"复选框。 在"错误报告"页上,可以清除复选框以禁用错误报告。 在"安装进度"页上,可以在安装过程中监视安装进度。若要在安装期间查看组 件的日志文件,请在"安装进度"页上单击产品或状态名称。 如果得到重新启动计算机的指示,请立即进行此操作。完成安装后,阅读来自 安装程序的消息是很重要的。如果未能重新启动计算机,可能会导致以后运行 安装程序失败。 4、 配置TCP/IP协议的SQL Server 2005网络 (1)配置TCP/IP协议的SQL Server 2005服务器端 1) 启动【SQL Server 配置管理器】,启动SQL Server,单击MSSQLSERVER,只选择启用TCP/IP。 右击TCP/IP,选择属性,并对IP地址、TCP端口等属性进行设置。(SQL Server 2005服务器默认分配的端口为1433,FTP为21,HTTP为80等。可用netstat –n来查看端口使用情况。) (2)配置TCP/IP协议的SQL Server 2005客户机端 动【SQL Server 配置管理器】,启动SQL Server,单击客户端协议,只选择启用TCP/IP。 右击TCP/IP,选择属性,并对IP地址、默认端口(服务器端口)属性进行设置。 接下来建立一个使用TCP/IP的别名来访问SQL Server 2005服务器。操作为选择【别名】选项,在右边空白处右击,选择【新建别名】, 设置其名称、端口号及服务器名称即可。 (3)测试TCP/IP协议的SQL Server 2005网络 启动【 SQL Server management studio】,在服务器名称中直接输入别名,单击""连接"按钮测试连接是否成 功; 如:服务器注册失败、无法连接服务器等 常见的注册故障: 1. 验证方式设置错误的故障; 解决方法:在注册时和服务器一致,或更改服务器身份验证方式; 2. Sa密码错误的故障 解决方法:修改sa帐户密码,或询问Dba有权限的帐户及密码 3. SQL Server服务错误或者客户机参数错误的故障 解决方法:确保服务器正常启动,检查SQL Server 2005网络的配置参数是否一致; 二、实验思考题 1. SQL Server 2005服务器的两种身份验证方式有什么区别? Windows验证是集成于操作系统,利用判断系统帐号来判定是否有权访问。 而SQL Sever验证模式则是使用数据库自己的用户名进行访问,访问时需要密码,和系统账户不 相干。 2. SQL Server 2005的Management Studio有什么主要功能? 进行数据库的创建、修改、更新、删除,从而来操纵数据库。 3. SQL Server 2005的配置管理器有什么主要功能? 如何排除SQL Server 2005连接故障? 配置管理器用来管理SQL Server 2005的启动、暂停、停止和重新启动等操作。出现连接故障时:先检查SQL Server Configuration Manager中【配置工具】 【SQL Server Configuration Ma
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值