实验3：简单查询和连接查询

实验三：数据库的简单查询和连接查询

实验目的：

掌握简单表的数据查询、数据排序和数据联结查询的操作方法。

实验内容：

简单查询操作和连接查询操作。

实验步骤：

一. 单表查询：

1. 查询全体学生的姓名和学号.

select sname, sno

from student

2. 查询全体学生的所有信息(2种方法).

第一种：

select * from student

第二种：

select sno, sname, ssex, sage, sdept from student

3. 查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名.

select sname, year(getdate()) - sage, lower(sdept)

from student

4. 给上例的结果集指定列名.

select sname, year(getdate()) - sage as [Year of birth], lower(sdept) as sdept

from student

5. 查询选修了课程的学生的学号.

select sno

from sc

where cno != 'null'

6. 查询年龄在20岁以下的学生的姓名及其年龄.

select sname, sage

from student

where sage < 20

7. 查询考试成绩有不及格的学生的学号.

select sno

from sc

where grade < 60

8. 查询年龄在20-30岁之间的学生的姓名, 姓名, 所在系(between).

select sname, sdept

from student

where sage between 20 and 30

9. 查询 IS,CS,MA系的所有学生的姓名和性别(in).

select sname, ssex

from student

where sdept in ('IS', 'CS', 'MA')

10. 查找所有姓’李’的学生的姓名, 学号和性别.

select sname, sno, ssex

from student

where sname like '李%'

比较: 将学生表中的’95001’号学生的姓名’李勇’改为’李勇勇’select sname, sno, ssex from student where sname like '李_'

update student set sname = '李勇勇' where sname = '李勇'

SQL中”_”代表任意单个字符，所以查询不到’李勇勇’

11. 查询没有先行课的课程的课程号cno和课程名cname:

select cno, cname

from course

, 再执行:

where pcno is NULL

12.查询学生表中前20%的数据记录。(select top 20 percent * from student)

13、查询全体学生的姓名、出生年份，并在姓名前加 “姓名”，出生年份前加 “出生年份”。

select '姓名',sname, '出生年份' ,year(getdate()) - sage

from student

二. 查询结果排序

14. 查询选修了3号课程的学生的学号和成绩, 并按分数降序排列:

select sno, grade

from sc

where cno = '3'

order by grade DESC

15. 查询全体学生的情况,查询结果按所在系号升序排列, 同一系中的学生按年龄降序排列:

select * from student

order by sdept ASC, sage DESC.

三. 连接查询:

16. 查询每个学生及其选修课程的情况.

select student.*, sc.* from student, sc where student.sno=sc.sno

比较: 笛卡尔集: select student.*, sc.* from student, sc

自然连接: select student.sno, sname, ssex, sdept, cno, grade from student, sc where student.sno=sc.sno

笛卡尔集的查询结果有45行，student表中的每一行依次与sc表中的每一行对应；而自然连接的查询结果只有9行，只是将两表中sno相同的列查询出来。

17. 查询每一门课程的间接先行课(只求两层即先行课的先行课).

select First.cno, Second.pcno 间接先行课from course First, course Second where First.pcno=Second.cno

比较:

select First.cno, Second.pcno 间接先行课 from course First, course Second

where First.pcno=Second.cno and Second.pcno is not null

第一种是直接查询先行课的先行课；第二种考虑了先行课的先行课是否为空的情况，如果为空，则不进行输出。

18. 列出所有学生的基本情况和选课情况, 若没有选课,则只列出基本情况信息.

select student.*, sc.*

from student left

join sc on student.sno = sc.sno

19. 查询每个学生的学号, 姓名, 选修的课程名和成绩.

select S.sno, sname, cname, grade from student S, course C, sc SC where S.sno=SC.sno and C.cno=SC.cno

20.求出不及格学生的学号, 姓名, 不及格的课程名以及成绩。

Select student.sno, sname, cname, grade

from student

join sc on student.sno = sc.sno

join course on sc.cno = course.cno

where grade < 60

实验小结：

通过本次实验，掌握了SQL Server查询分析器的使用方法，加深对SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。