《数据库系统概论》实验四 DML的数据查询

                   《数据库系统概论》实验报告

题目：实验四 DML的数据查询 姓名 XXX 日期 XXXX.XX.XX
实验内容与完成情况：
一、实验内容：
1．启动数据库服务软件SQL Server 2008的管理器，用SELECT语句对表进行简单查询操作，整个查询过程只涉及到一个表，是最基本的查询语句。
语法格式如下：
Select <目标列组> From <数据源> [Where <元组选择条件>]
[Group by <分组列> [Having <组选择条件>]]
[Order by <排序列1> <排序要求> [, …n]]
2．用SELECT语句对表进行连接查询操作，连接查询涉及被连接和连接两个表，所以数据源一般为多个表。用来连接两个表的条件称为连接条件，一般格式为：
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
其中比较运算符主要有：=、>、<、>=、<=、!=。
3．用SELECT语句对表进行嵌套查询操作，一个Select…From…Where语句称为一个查询块，将一个查询块嵌套在另一个查询块的Where子句或Having短语的条件中的查询，就是嵌套查询。主要有使用In操作符、使用比较运算符的嵌套查询方式。
4．用SELECT语句对表进行组合查询操作，将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。组合查询操作符有Union(并操作)、Intersect(交操作)和Minus(差操作)

二、实验任务
1．打开数据库SQL Server 2008的管理查询分析器。
2、用INSERT语句向表SC中插入记录,记录见P79。

利用以前建立的Student,Course,SC进行查询。
3．简单查询
1）查询全体学生的详细记录；
Select * From student

2）查询所有选修过课的学生的学号；
Select distinct sno from sc

3）查询考试成绩不及格的学生的学号；
Select sno from sc where score<60

4）查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别；
Select sname,ssex from student where sdept in (‘IS’,‘MA’,‘CS’)

5）查所有姓刘的学生的姓名、学号和性别；
Select sname,sno,ssex from student where sname like ‘刘%’

6）查 DB_Design 课程的课程号和学分；
Select cname,credit from course where cname like ‘DB_Design’ ESCAPE’\’

因为没有课程名为 DB_Design的课程，故搜索到的结果为空。故此处
以‘数据库’为例：
Select cname,credit from course where cname like ‘数据库’

7）查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列；
Select sno,Grade from sc where cno=‘3’ order by Grade DESC

8）查询学习1号课程的学生最高分数；
Select max(Grade) from sc where cno=‘1’

4．在查询分析器窗口下用SELECT语句完成连接（嵌套）查询：
1)查询各个课程号与相应的选课人数；
Select cno,count(sno) from sc group by cno

2)查询每个学生及其选修课程的情况；
Select student.,sc. from student,sc where student.sno=sc.sno

3)查询每个学生及其选修的课程名其及成绩；
Select student.*,course.cname,sc.Grade from student,sc,course where student .sno=sc.sno and sc.cno=course.cno

4）查询每一门课的间接先行课（即先行课的先行课）。
先要为Course表取两个别名，一个是first，另一个是second。
Select first.Cname,first.Cno,first.Cpno,first.Credit from Course as first
Select second.Cname,second.Cno,second.Cpno,second.Credit
from Course as second

Select first.Cno,second.cpno from Course first,course second where first.Cpno=second.Cno

5）查询同时选修了“程序设计”和“微机原理”的学生姓名及所在系。
SELECT Sname,Sdept FROM Course as c1,Course as c2,Sc as sc1,Sc as sc2,Student
where c1.Cno=sc1.Cno And c2.Cno=sc2.Cno And sc1.Sno=sc2.Sno and sc1.Sno=Student.Sno
AND c1.Cname=‘程序设计’ AND c2.Cname=‘微机原理’

因为没有课程名为‘程序设计’和‘微机原理’的课程，故搜索到的结果为空，此处以‘数据库’和‘数学’为例，如下：
SELECT Sname,Sdept FROM Course as c1,Course as c2,Sc as sc1,Sc as sc2,Student
where c1.Cno=sc1.Cno And c2.Cno=sc2.Cno And sc1.Sno=sc2.Sno and sc1.Sno=Student.Sno
AND c1.Cname=‘数学’ AND c2.Cname=‘数据库’

6）查询所有学生的学号、姓名、选修的课程名及成绩（没有选课的学生其选课信息显示为空）。（用外连接做）
Select Student.Sno,Sname,Cname,Grade FROM Student
LEFT OUTER JOIN SC ON(Student.Sno=SC.SNO)
LEFT JOIN Course ON (SC.Cno=Course.Cno)

7）查询仅仅选修了课程号为5号课程同学的学号。
因为原定数据库中没有仅仅选修一门课程的同学，故在进行此步骤时，添加了仅仅选修5号课程的学生信息。

添加后进行数据查询操作：
SELECT student.sno FROM student,sc WHERE sc.sno IN
(SELECT sno FROM sc WHERE cno=‘5’)AND student.sno=sc.sno
group by student.sno having count(*)=1

8）查询仅仅选修了1号课程和2号课程指定这两门课程学生的学号。
SELECT student.sno FROM student,sc WHERE sc.sno IN
(SELECT sno FROM sc WHERE cno=‘2’ AND sno IN (SELECT sno FROM sc
WHERE cno=‘3’))AND student.sno=sc.sno group by student.sno
having count(*)=2

5. 在查询分析器窗口下用SELECT语句完成嵌套查询：
   1）求选修了信息系统的学生学号和姓名。
   SELECT Sno,Sname FROM Student WHERE Sno IN
   (SELECT Sno FROM Sc WHERE Cno IN
   (SELECT Cno FROM Course WHERE Cname=‘信息系统’))
   

2）求选修2号课程的成绩高于李勇的学生学号和成绩。
SELECT Sno,Grade FROM SC WHERE Cno=‘2’ AND Grade>ANY(SELECT Grade
FROM SC WHERE Sno IN(SELECT Sno FROM Student WHERE Sname=‘李勇’))

3）求其他系中年龄小于计算机系年龄最大者的学生。
SELECT Sname,Sage FROM Student WHERE Sage <
(SELECT MAX(Sage) FROM Student WHERE Sdept =‘CS’)
AND Sdept <>‘CS’

4）求其他系中比计算机系学生年龄都小的学生。
SELECT Sname,Sage FROM Student WHERE Sage <
(SELECT MIN(Sage) FROM Student WHERE Sdept =‘CS’)
AND Sdept <>‘CS’

5）求选修了2号课程的学生姓名。
SELECT Sname FROM Student WHERE Sno IN
(SELECT DISTINCT Sno FROM SC WHERE Cno =‘2’)

6）求没有选修2课程的学生姓名。
SELECT Sname FROM Student WHERE Sno NOT IN
(SELECT DISTINCT Sno FROM SC WHERE Cno =‘2’)

7)查询选修了全部课程的学生的姓名。
SELECT Sname FROM Student WHERE NOT EXISTS
(SELECT * FROM Course WHERE NOT EXISTS
(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno))

因为在该数据库里面并未有选修全部课程的学生，故在此步骤添加一位选修全部课程学生的信息：

查询结果如下：

7）求选修了学号为“201215121”的学生所选修的全部课程的学生学
号和姓名。
SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS
(SELECT * FROM SC SCY WHERE SCY.Sno =‘201215121’ AND NOT EXISTS
(SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)))

6、在查询分析器在窗口下用SELECT语句完成集合查询：
1）查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT * FROM Student WHERE Sdept =‘CS’
UNION SELECT * FROM Student WHERE Sage<=‘19’

2）查询选修了课程1或者选修了课程2的学生。
SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno=‘1’
UNION SELECT Sno FROM SC WHERE Cno=‘2’)

3）查询计算机科学系的学生与年龄不大于19岁的学生的交集。
SELECT * FROM Student WHERE Sdept =‘CS’
INTERSECT SELECT * FROM Student WHERE Sage<=‘19’

4）查询选修了课程1的学生的集合与选修了课程2的学生的集合的交集。
SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno=‘1’
INTERSECT SELECT Sno FROM SC WHERE Cno=‘2’)

5）查询计算机科学系的学生与年龄大于19 的学生的差集。
SELECT * FROM Student WHERE Sdept =‘CS’
EXCEPT SELECT * FROM Student WHERE Sage<=‘19’

7、将以上的SQL语言存盘，以备老师检查。