2.2实验二 SQL数据查询
一、实验目的和要求
1．掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。 2．熟练掌握查询语句的一般格式。
3．熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。
二、实验内容及步骤 1 无条件查询
【题2-01】 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为： SELECT * /*这里的“*”等价于ALL*/ FROM Student;
其结果为Student表中的全部数据。
【题2-02】 查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。这是一个无条件的投影查询，其命令为：
SELECT Sname, Sno, Sdept FROM Student;
【题2-03】 查询全体学生的学号(Sno)、姓名(Sname)及出生年份。由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：
SELECT Sno, Sname, 2012-Sage as ‘2012-Sage’ FROM Student;
【题2-04】 查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名。其命令为：
SELECT Sno, Sname, 2012-Sage 'Year of Birth', LOWER(Sdept) Sdept FROM Student;
【题2-05】 查询选修了课程的学生学号。其命令为：
SELECT DISTINCT Sno FROM SC;
2 条件查询
【题2-06】 查询数学系（MA）全体学生的学号(Sno)和姓名 (Sname)。其命令为： SELECT Sno, Sname FROM Student WHERE Sdept='MA';
【题2-07】查询考试成绩有不及格的学生的学号。 SELECT DISTINCT Sno FROM SC
WHERE Grade<60;
【题2-08】查询所有年龄在20岁以下的学生姓名(Sname)及年龄(Sage)。其命令为：
6
SELECT Sname, Sage FROM Student WHERE Sage<20;
【题2-09】查询所有年龄在18~20岁(包括18岁和20岁)之间的学生姓名(Sname)及年龄(Sage)。其命令为：
SELECT Sname, Sage FROM Student
WHERE Sage?=18 AND Sage<=22; 或
SELECT Sname, Sage FROM Student
WHERE Sage BETWEEN 18 AND 22;
【题2-10】 查询年龄不在18-20岁之间的学生姓名(Sname)及年龄(Sage)。其命令为： SELECT Sname, Sage FROM Student
WHERE Sage NOT BETWEEN 18 AND 20;
【例11】 查询计算机系、数学系和信息系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。其命令为： SELECT Sno, Sname, Ssex FROM Student
WHERE Sdept IN ('CS', 'MA', 'IS'); 等价于：SELECT Sno, Sname, Ssex
FROM Student
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';
【例12】 查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名(Sname)和性别(Ssex)。其命令为：
SELECT Sname, Ssex FROM Student
WHERE Sdept NOT IN ('IS', 'MA', 'CS');
【例13】 查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。其命令为： SELECT Sname, Sno, Ssex FROM Student
WHERE Sname LIKE '刘%';
【例14】 查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。其命令为： SELECT Sname, Sdept FROM Students
WHERE Sname LIKE '刘____';
【例15】 查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。 SELECT Sname, Sage
7
FROM Students
WHERE Sname NOT LIKE '刘%';
【例16】 查询课程名为“DB_设计”的课程号(Cno)和学分(Ccredit)。其命令为： SELECT Cno, Ccredit FROM Course
WHERE Cname LIKE 'DB\\_设计' ESCAPE '\\';
【例17】 查询以\开头，且倒数第2个汉字字符为“设”的课程的详细情况。其命令为： SELECT * FROM Course
WHERE Cname LIKE 'DB\\_%设__'ESCAPE'\\';
【例18】 假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。其命令为：
SELECT Sno, Cno FROM Reports
WHERE Grade IS NULL;
【例19】 查询所有有成绩的学生学号(Sno)和课程号(Cno)。其命令为： SELECT Sno, Cno FROM SC
WHERE Grade IS NOT NULL;
【题20】查询计算机系年龄在20岁以下的学生姓名。
3 查询结果排序
例24 查询选修了3号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。其命令为： SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
【例25】 查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。其命令为：
SELECT * FROM Student
ORDER BY Sdept, Sage DESC;
4 集函数的使用
例26 查询学生总人数。其命令为：
SELECT COUNT(*)
FROM Student;
例27 查询选修了课程的学生人数。其命令为：
SELECT COUNT(DISTINCT Sno)
8
FROM SC;
例28 计算选修2号课程的学生平均成绩。其命令为：
SELECT AVG(Grade)
FROM SC WHERE Cno='2';
例29 查询选修2号课程的学生最高分数。其命令为：
SELECT MAX(Grade)
FROM SC WHERE Cno='2';
例30 查询学生200215122选修课程的总学分数。其命令为：
SELECT SUM(Ccredit) FROM SC,Course
WHERE Sno='200215122' AND SC.Cno=Course.Cno;
5 查询结果分组
例31 求各个课程号(Cno)及相应的选课人数。其命令为： SELECT Cno , COUNT(Sno) CntSno FROM SC GROUP BY Cno;
例32 查询选修了3门或3门以上课程的学生学号(Sno)。其命令为：SELECT Sno FROM Reports GROUP BY Sno
HAVING COUNT(Cno)>3
9
2.3实验三 连接、嵌套和集合查询
一、实验目的和要求
1．掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。 2．熟练掌握查询语句的一般格式。 3．熟练掌握连接、嵌套和集合查询的使用。
二、实验内容及步骤 （一）连接查询
1 不同表之间的连接查询
【题3-01】 查询每个学生及其选修课程的情况。
本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：
SELECT Student.*, SC.* FROM Student, SC
WHERE Student.Sno = SC.Sno；
说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接错误！未找到引用源。，其命令为
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC
WHERE Student.Sno= SC.Sno; 2 自身连接
【例35】 查`询每一门课的间接先修课(即先修课的先修课)。
在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Course表与其自身连接。为方便连接运算，这里为Course表取两个别名分别为A，B。则完成该查询的SQL语句为：
SELECT A.Cno, A.Cname, B.Cpno FROM Course A, Course B WHERE A.Cpno =B.Cno; 3 外连接
【例36】把例33中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为： SELECT Student.Sno, Sname, Ssex, Sdept, Cno, Grade FROM Student LEFT JOIN SC ON Student.Sno= SC.Sno;
说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。 SELECT Student.Sno, Sname, Ssex, Sdept, Cno, Grade FROM SC
RIGHT JOIN Student ON
10