实验三、数据库查询

目录

一、实验名称

二、实验目的

三、实验内容

四、实验任务及结果

1、简单查询

2、连接查询

3、嵌套查询

4、组合查询和统计查询

---

一、实验名称

       数据库的查询

二、实验目的

       熟悉 SQL 的数据查询语言，能够使用 SQL 语句对数据库进行单表查询、连接查询、嵌套 查询、组合查询，加深对 T-SQL 语言的查询语句的理解。

三、实验内容

     1）简单查询操作。包括投影、选择条件表达、数据排序等。

     2）连接查询操作。包括笛卡儿积、等值连接、自然连接、复合条件连接、多表连接、外连 接和自连接等。

     3）嵌套查询操作。包括使用 IN、比较符、ANY 或 ALL 和 EXISTS 操作符的嵌套查询操作， 体会相关子查询和非相关子查的不同。

     4）组合和统计查询。包括使用 UNION、INTERSECT、EXCEPT 进行的集合运算，采用 AND 或 OR 实现的集合交和减运算，以及使用统计函数和分组统计函数的查询。

四、实验任务及结果

1、简单查询

1)查询数计学院学生的学号和姓名。

go

select sno,sname

from student

where sdept = '数计'

2)查询选修了课程的学生学号。

go

select sno

from SC

group by sno

3)查询选修 C1 课程的学生学号和成绩,结果按成绩降序排列,如果成绩相同按学号升序排序。

go

select sno,grade

from SC

where cno = 'C1'

order by grade desc,sno

4)查询选修 C1 课程，成绩在 80~90 之间的学生学号和成绩,并将成绩乘以 0.8 输出。

go

select sno,grade*0.8 as grade

from SC

where cno = 'C1' and grade between 80 and 90

5)查询生工学院或数计学院系姓张的学生的信息。

go

select *

from student

where (sdept = '数计' or sdept = '生工') and sname like '张%'

6)查询缺少了成绩的学生的学号和课程号。

go

select sno,cno

from SC

where grade is null

2、连接查询

在学生选课库中实现其数据连接查询操作。

1)查询学生的学号、姓名、选修的课程名称及成绩。

go

select student.sno,sname,cname,grade

from (student inner join SC on SC.sno = student.sno)

                      inner join Course on Course.cno = SC.cno

2)查询数计学院学生选修的课程学分大于 2 的课程详细信息。

go

select Course.*

from (student inner join SC on SC.sno = student.sno)

                       inner join Course on Course.cno = SC.cno

where ccredit > 2 and sdept = '数计'

3)查询所有学生的信息以及他(她)所选课的课程号和成绩（要求查询结果也显示出没有 选修课程的学生信息）。

go

select student.*,SC.cno,grade

from student left join SC on SC.sno = student.sno

4)查询选修课程号为 C1 且成绩在 90 分以上的学生学号、姓名及成绩。

go

select student.sno,sname,grade

from (student inner join SC on SC.sno = student.sno)

                      inner join Course on Course.cno = SC.cno

where Course.cno = 'C1' and grade > 90

5)查询每一门课的间接先行课（即先行课的先行课）。

go

select distinct x.cno,y.cpno

from Course x,Course y

where x.cpno = y.cno

3、嵌套查询

在学生选课库中实现其数据嵌套查询操作。

1) 查询选修了 UML 课程的学生学号和姓名。

go

select sno,sname

from student

where sno in (select sno

                       from SC

                       where cno in(select cno

                                            from Course

                                            where cname = 'UML'))

2) 查询比王华年龄大的学生学号和姓名。

go

select sno,sname

from student

where sage > all(select sage

                           from student 

                           where sname = '王华')

3) 查询 C1 课程的成绩低于张三的学生学号和成绩。

go

select SC.sno,SC.grade

from SC

where cno = 'C1' and SC.grade <( select grade

                                                       from SC

                                                        where cno = 'C1' and sno = (select sno

                                                                                                      from student

                                                                                                      where sname = '张三'))

4) 查询其他学院中比数计学院学生年龄都小的学生。

go

select sno,sname

from student

where sdept != '数计' and student.sage <( select min(sage)

                                                                   from student

                                                                   where sdept = '数计')

5) 查询选修了 C2 课程的学生姓名。

go

select sname

from student

where sno in ( select sno

                       from SC

                       where cno = 'C2')

6) 查询没有选修 C2 课程的学生姓名。

方法一：

go

select sname

from student

where sno in ( select sno

                       from SC

                       where cno != 'C2')

方法二：

go

select sname

from student

where sno not in ( select sname

                              from SC

                             where cno = 'C2')

方法三：

go

select sname

from student

where not exists (select *

                            from SC

                            where SC.sno = student.sno and cno = 'C2')

7) 查询选修了全部课程的学生姓名。

go

select sname

from student

where not exists (select *

                            from Course

                            where not exists( select *

                                                        from SC

                                                        where SC.sno = student.sno and SC.cno = Course.cno))

8) 查询至少选修了学生为“S2”的学生所选修的全部课程的学生学号的姓名。

go

select sname

from student

where not exists(select *

                           from SC

                           where SC.sno = 'S2' and not exists(select *

                                                                                    from Course

                                                              where SC.sno = student.sno and SC.cno = Course.cno))

9）查询既选修了数据结构课程又选修了数据库原理及应用课程的学生姓名。

go

select sname

from student

where sno in (select sno

                       from SC

                       where cno in (select cno

                                             from Course where cname = '数据结构'))

           and sno in (select sno

                              from SC

                              where cno in ( select cno

                                                     from Course

                                                     where cname = '数据库原理及应用'))

10）查询选修了数据结构课程或选修了数据库原理及应用课程的学生学号。

go

select sname

from student

where sno in (select sno

                      from SC

                      where cno in (select cno

                                             from Course

                                             where cname = '数据结构'))

           or sno in (select sno

                           from SC

                           where cno in ( select cno

                                                   from Course

                                                   where cname = '数据库原理及应用'))

11）查询选修了数据结构课程而没有选修数据库原理及应用课程的学生学号。

go

select sname

from student

where sno in (select sno

                       from SC

                       where cno in (select cno

                                              from Course

                                              where cname = '数据结构'))

             and sno not in (select sno

                                      from SC

                                      where cno in ( select cno

                                                             from Course

                                                             where cname = '数据库原理及应用'))

12）查询全是男生选修的课程号。

方法一：

go

select distinct cno

from SC

where cno not in(select cno

                           from student,SC

                           where student.sno = SC.sno and ssex = '女')

方法二：

go

select distinct cno

from SC x

where not exists(select cno

                           from student

                           where ssex = '女' and exists(select *

                                                                         from SC y

                                                                         where y.cno = x.cno and y.sno = student.sno))

4、组合查询和统计查询

在学生选课库中实现组合和统计查询操作。

1）使用集合运算查询既选修了数据结构课程又选修了数据库原理及应用课程的学生姓名。

go 

select sname

from student

where sno in( select sno

                       from SC

                       where cno in (select cno

                                              from Course

                                              where cname = '数据结构'))

intersect

select sname

from student

where sno in( select sno

                       from SC

                       where cno in (select cno

                                              from Course

                                              where cname = '数据库原理及应用'))

2）使用集合运算查询选修了数据结构课程或选修了数据库原理及应用课程的学生学号。

go

select sno

from SC

where cno in (select cno

                       from Course

                       where cname = '数据结构')

union

select sno

from SC

where cno in (select cno

                       from Course

                       where cname = '数据库原理及应用')

3）使用集合运算查询选修了数据结构课程而没有选修了数据库原理及应用课程的学生学号。

go

select sno

from SC

where cno in (select cno

                       from Course

                       where cname = '数据结构')

except

select sno

from SC

where cno in (select cno

                       from Course

                       where cname = '数据库原理及应用')

4）统计选修了课程的学生人数。

go

select count(distinct sno) as stusum

from SC

5）查询选修成绩合格的课程超过 4 门以上学生的学生学号、总成绩。

go

select sno,sum(grade) as '总成绩'

from SC where grade >= 60

group by sno

having count(cno) > 4

6）统计各院系的学生人数。

go

select sdept,count( sdept) as sdepsum

from student

group by sdept

7）统计各年龄的学生人数。

go

select sage,count( sage) as sagesum

from student

group by sage

8）统计每个学生的选修课程数目和平均成绩。

go

select sno,count(cno) as '课程数目',AVG(grade) as '平均成绩'

from SC

group by sno

9）查询至少被 3 位学生选修的课程编号。

go

select cno

from SC

group by cno

having count(cno) >= 3

10）查询每门课程的详细信息及选课人数。

方法一：（不是最优的方案）

go

select Course.*,count(SC.cno) as '选课人数'

from Course,SC

where SC.cno = Course.cno

group by Course.cno,ccredit,cname,cpno,ctech 

方法二：

go

select Course.*,选课人数

from Course left join (select cno,count(*) 选课人数

                                   from SC

                                   group by cno) as a on a.cno = Course.cno

（11）查询选修操作系统的课程的学生的平均成绩与选课门数

方法一：

go

select sno ,count (*) as 选课门数, avg(grade) as 平均成绩

from SC

group by sno

having sno in(select sno

                      from SC

                      where cno =( select cno from Course where cname='操作系统'))

方法二：

go

select sno,count(SC.cno) as '选课门数',AVG(grade) as '选课门数'

from SC

where cno = cno and sno in( select sno

                                               from SC

                                               where SC.cno in (select cno

                                                                           from Course

                                                                           where cname = '操作系统'))

group by sno

（12）统计数计学院和管理学院选课的男女生人数和总人数

go

select sdept,sum(case when ssex = '男' then 1 else 0 end) as '男',

                     sum(case when ssex = '女'then 1 else 0 end) as '女',count(ssex) as '总计'

from student

where sdept = '数计' or sdept = '管理' group by sdept