数据库SQL Server实验报告 之 SQL数据库综合查询(5/8)

数据库综合查询

注意:原版word在下载资源里面(免费下载)

你走过的才是你的世界

实验目的及要求:

  1. 掌握SELECT语句的基本语法和查询条件表示方法;
  2. 掌握查询条件种类和表示方法;
  3. 掌握连接查询的表示及使用;
  4. 掌握嵌套查询的表示及使用;
  5. 了解集合查询的表示及使用。
  6. 了解SELECT语句的基本语法格式和执行方法;

实验内容及步骤:

  1. 查询以‘数据_’开头,且倒数第3个字符为‘结’的课程的详细情况;

select *

from course

where cname like '数据\_%结__'escape'\';--在_前添加\以及结尾添加escape’\’是为了规定_为查询符号,而不是一个字节

  1. 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名;

select student.sno ,student.sname ,sc.cno,cname--学生姓名和学号在学生表,课程号和课程名又需要连接其他两个表

from student,course,sc

where student.sname like'_阳%'

and student.sno=sc.sno

and sc.cno=course.cno

  1. 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩;

方法一:(推荐,符合个人记忆)

select sname,student.sno,sdept,cno,grade--所求属性只需要了两个表

from student,sc

where student.sno=sc.sno

and cno in(select cno from course where cname='数学'or cname='大学英语')--由于选取元素的条件需要用到第三个表,直接连接嵌套筛选条件即可

方法二:

select sc.sno,sname,sdept,sc.cno,grade

from course,sc,student--筛选条件需要用到course表,

where sc.sno=student.sno

and sc.cno=course.cno

and(cname='数学'or cname='大学英语');

  1. 查询缺少成绩的所有学生的详细情况;

方法一(推荐)只查询学生表中的信息,所以只使用学生表

select *--学生表详细情况

from student

where sno in(select sno from sc where  grade is null )

方法二:以下题目同理,都有类似两种方法,为了方便,只使用方法一。

select *--学生表和选课表详细情况

from student,sc--成绩在sc表

where Grade is null and student .sno=sc.sno

  1. 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息;

select sname,sno--学生的信息默认为姓名和学号

from student

where sage<>(select sage from student where sname='张力');--直接嵌套连接自身

  1. 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩;

select sc.sno,sname,avg(grade) 平均成绩--需要成绩属性,所以要连接表

from sc,student

where student.sno=sc.sno

group by sc.sno,sname--计算的是个人平均成绩,所以需要分组将学生分类

having avg(grade)>(select  avg(grade)--需要表示张力的平均成绩,所以需要嵌套

from sc--成绩仅仅需要选课表

where sno in(select sno from student where sname='张力')); --需要限制名字为张力,所以嵌套一个学生表

  1. 按照“学号,姓名,所在院系,已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和;

select student.sno, sname, sdept,

sum(case when grade>=60 then credit*1 else 0 end) 学分--只能用sum,如果用count则只能计算选课门数,并不呢个计算学分总和

from student,sc,course

where student.sno=sc.sno

and sc.cno=course.cno--此处不能限制grade>=60,否则结果不会出现学分为0的同学

group by student.sno,sname,sdept

  1. 列出只选修一门课程的学生的学号、姓名、院系及成绩;                                                                                                                   

select  sc.sno,sname,sdept,cname,grade--需要知道是哪一门课,所以加入cname

from student,sc,course

where sc.sno=student.sno

and sc.cno=course.cno

and sc.sno in(select sc.sno from sc--如果不进行嵌套分组,则在查询成绩时,必须对成绩进行分组,则结果不正确

group by sno having COUNT(*)=1)--对每个学生选课进行查询,需要对学生分组再进行统计

  1. 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号;

(难)

select student.sno,sname,cno

from student,sc

where student.sno=sc.sno

and sname<>'张力'--不包含张力自己

and sc.sno in(select sc.sno from sc,student where

cno in(select cno from sc,student where sname='张力'--张力的选课的课程号

and student .sno=sc.sno))

  1. 只选修“数据库”和“数据结构”两门课程的学生的基本信息;

(难)

select student .sno ,sname

from student , sc , course

where student.sno=sc.sno and sc.Cno=course.cno and

sc.sno in (select sc.sno from sc, course

where sc.cno=course.cno

and (cname='数据库'or cname='数据结构' )--通过or来限制只能选的课程范围

group by sc.sno

having count(*)=2)--通过count()=2 来限制选了数据库和数学的

group by student .sno , sname

having count(*)=2--通过count()=2 来限制只选了数据库和数学

  1. 至少选修“数据库”或“数据结构”课程的学生的基本信息;

select *

from student

where sno in(select sno from sc--需要通过学号嵌套选课表从而嵌套限制选课名称

where cno in(select cno from course

where cname='数据库'or cname='数据结构'))--只要求至少选修其中一门课程

  1. 列出所有课程被选修的详细情况,包括课程号、课程名、学号、姓名及成绩;

select sc.cno,cname,sc.sno,sname,grade

from student,sc,course

where student.sno=sc.sno

and course.cno=sc.cno

order by cname desc--列出课程的情况所以对课程进行排序以便观察

  1. 查询只被一名学生选修的课程的课程号、课程名;

select sc.cno,cname

from sc,course

where course.cno=sc.cno

group by sc.cno,cname

having count(sno)=1;

  1. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名;

select sno,sname

from student

where sno in (select sno from sc    --通过学生表查询课程名字必须通过选课表过度

where cno in(select cno from course

where  cname='数据结构'))

  1. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系;

select sname,sage,sdept

from student

where sage <any(select sage

from student where sdept='cs')--年龄小于cs系中任何一个人的年龄

and sdept<>'cs'--不包含cs系别

  1. 使用ANY、ALL 查询,列出其他院系中比CS系所有学生年龄小的学生;

方法一:(all)

select sname,sage,sdept

from student

where sage <all(select sage

from student where sdept='cs')--年龄小于cs系中所有人的年龄

and sdept<>'cs'--不包含cs系别

方法二:(不用all)

select sname, sage ,sdept

from student

where sage<(select min(sage) from student where sdept='cs')

  1. 分别使用连接查询和嵌套查询,列出与‘张力’在一个院系的学生的信息;

方法一:(嵌套查询)

select *

from student

where sdept in (select sdept from student where sname='张力')--嵌套表示张力的院系

and sname<>'张力';

方法二:(自身连接)

select s1.*

from student s1,student s2--建立表s1和表s2都是自身

where s1.sno=s2.sno --建立自身连接

and s1.sdept in (select student.sdept--自身连接的嵌套必须是基本表

from student where sname='张力')

and s1.sname<>'张力';

  1. 使用集合查询列出CS系的学生以及性别为女的学生名单;

select sno,sname --第一个查询

from student

where sdept='cs'

intersect--集合运算符,表示求交集

select Sno,Sname --第二个查询

from student

where ssex='女';

  1. 使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集;

一:(交集)

select *from student where Sdept='CS'

intersect

select *from student where Sage<=19

二:(差集)

select * from student where sdept='CS'

except --集合运算符,表示求差集

select * from student where sage<=19

  1. 使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集;

select sno from sc where cno=1 --此处不能select * 因为课程号为1的所有信息不可能与课程号为2的完全相同

intersect

select sno from sc where cno=2 --例如select sno,grade 表示选秀了1和2课程 并且成绩都是45的学生

  • 5
    点赞
  • 26
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

君无戏言。

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值