实验2 数据查询

一、实验目的
熟悉SQL语句的数据查询语言，能够使用SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。通过实验理解在数据库表中对数据的NULL值的处理。
二、实验内容1

1. 问题描述
   （1）单表查询
   查询的目标表达式为所有列、指定列或指定列的运算
   使用DISTINCT保留字消除重复行
   对查询结果排序和结果
   集合分组使用集函数进行各项统计
   （２）连接查询
   笛卡尔链接和等值连接
   自连接
   外连接
   复合条件连接
   多表连接
   （３）嵌套查询
   通过实验验证对子查询的两个限制条件
   体会相关子查询和不相关自查询的不同
   考察四类谓词的用法
   （４）集合运算
   使用保留字ＵＮＩＯＮ进行集合或运算
   采用逻辑运算符ＡＮＤ或ＯＲ来实现集合交和减运算

2. 数据库设计
   1)单表查询
   •查询的目标表达式为所有列、指定列或指定列的运算。
   •使用 DISTINCT保留字消除重复行。
   •对查询结果排序和分组。
   •集合分组使用集函数进行各项统计。
   一般格式：
   SELECT [ALL|DISTINCT] <目标列表达式>
   [，<目标列表达式>] …
   FROM <表名或视图名>[， <表名或视图名> ] …
   [ WHERE <条件表达式> ]
   [ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
   [ ORDER BY <列名2> [ ASC|DESC ] ]；
   2) 连接查询
   •笛卡儿连接和等值连接。
   自连接。
   •外连接。
   •复合条件连接。
   •多表连接。
   一般格式：
   [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
   [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
   连接字段：连接谓词中的列名称
   连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

3. 程序实现
查询年级为2001的所有学生的名称，按编号升序排列：
SELECT SNAME
FROM STUDENTS
WHERE GRADE=‘2001’
ORDER BY SID
查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）：
SELECT TID,CID,SCORE,‘POINT OF SCORE’,(SCORE-50)/10
FROM CHOICES
WHERE SCORE>60
查询课时是48或64的课程的名称：
SELECT CNAME
FROM COURSES
WHERE HOUR IN(‘48’,‘64’)
查询所有课程名称中含有data的课程编号：
SELECT CNAME
FROM COURSES
WHERE CNAME like ‘%data%’
查询所有选课记录的课程号（不重复显示）：
SELECT CID FROM CHOICES
–去重
SELECT DISTINCT CID FROM CHOICES
统计所有老师的平均工资：
SELECT AVG(SALARY) FROM TEACHERS
查询所有学生的编号，姓名和平均成绩，按总平均成绩降序排列：
SELECT TID,AVG(SCORE)
FROM CHOICES
GROUP BY TID
ORDER BY AVG(SCORE) DESC
统计各个课程的选课人数和平均成绩：
SELECT CID, COUNT(NO),AVG(SCORE)
FROM CHOICES
GROUP BY CID

查询至少选修了三门课程的学生编号：
SELECT SID
FROM CHOICES
GROUP BY SID
HAVING COUNT(*)>3

查询编号800009026的学生所选的全部课程的课程名和成绩：
SELECT COURSES.CNAME,CHOICES.SCORE
FROM COURSES,CHOICES
WHERE CHOICES.SID=‘800009026’ AND COURSES.CID=CHOICES.CID

查询所有选了ｄａｔａｂａｓｅ的学生的编号
SELECT CHOICES.SID
FROM CHOICES,COURSES
WHERE CHOICES.CID=COURSES.CID AND COURSES.CNAME=‘database’

SELECT SID
FROM CHOICES
WHERE ‘database’ IN
(
SELECT CNAME
FROM COURSES
WHERE COUSES.CID=CHOICES.CID
)
–子查询(11)

求出选择了同一个课程的学生对
SELECT CID,COUNT(*)
FROM CHOICES
GROUP BY CID;

求出至少被两名学生选修的课程编号
SELECT X.CID
FROM CHOICES X
GROUP BY X.TID
HAVING COUNT(*)>2

查询选修了编号80009026的学生所选的某个课程的学生编号
SELECT Y.SID
FROM CHOICES AS X,CHOICES AS Y
WHERE X.CID=Y.CID AND X.SID=‘850955252’

查询学生的基本信息及选修课编号和成绩
SELECT STUDENTS.SID,STUDENTS.SNAME,STUDENTS.GRADE,CHOICES.CID,CHOICES.SCORE
FROM STUDENTS JOIN CHOICES ON STUDENTS.SID=CHOICES.SID

查询学号850955252的学生的姓名和选修的课程名
SELECT STUDENTS.SNAME,COURSES.CNAME,CHOICES.SCORE
FROM STUDENTS,COURSES,CHOICES
WHERE STUDENTS.SID=CHOICES.SID AND COURSES.CID=CHOICES.CID AND STUDENTS.SID=‘850955252’

查询学号850955252的学生同年级的所有学生资料
SELECT *
FROM STUDENTS
WHERE GRADE=(
SELECT GRADE
FROM STUDENTS
WHERE SID=‘850955252’
)

查询所有的有选课的学生的详细信息
SELECT *
FROM STUDENTS
WHERE SID IN
(
SELECT SID
FROM CHOICES
)

查询没有学生选的课程编号
SELECT CNAME
FROM COURSES
WHERE CID NOT IN
(
SELECT CID
FROM CHOICES
)

查询选修了课程名为ｃ＋＋的学生学号和姓名
SELECT SID,SNAME
FROM STUDENTS
WHERE SID IN
(
SELECT SID
FROM CHOICES
WHERE CID IN
(
SELECT CID
FROM COURSES
WHERE CNAME=‘C++’
)
)

找出选修课程成绩最差的选课记录
SELECT *
FROM CHOICES
WHERE CHOICES.SCORE>=ALL
(SELECT SCORE
FROM CHOICES
WHERE SCORE IS NOT NULL
)

找出和课程UML或ｃ＋＋的课时一样的课程名称
SELECT CNAME
FROM COURSES
WHERE HOUR=SOME
(
SELECT HOUR
FROM COURSES
WHERE CNAME='UML’OR CNAME=‘C++’
)

查询所有选修编号10001的课程的学生的姓名
SELECT SNAME
FROM STUDENTS
WHERE EXISTS
(
SELECT*
FROM CHOICES X
WHERE X.CID=‘10001’ AND X.SID=STUDENTS.SID
)

查询所有选修了所有课程的学生姓名
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT *
FROM COURSES AS X
WHERE NOT EXISTS
(
SELECT *
FROM CHOICES AS Y
WHERE Y.SID=STUDENTS.SID AND Y.CID=X.CID
)
)

利用集合运算，查询选修课程ｃ＋＋或课程Java的学生的编号
SELECT TID
FROM CHOICES
WHERE CHOICES.CID=
(
SELECT COURSES.CID
FROM COURSES
WHERE COURSES.CNAME=‘C++’
)
UNION
SELECT TID
FROM CHOICES
WHERE CHOICES.CID=
(
SELECT CID
FROM COURSES
WHERE COURSES.CNAME=‘Java’
)

实现集合交运算，查询既选修课程ｃ＋＋又选修课程Java的学生的编号
SELECT SID
FROM CHOICES
WHERE SID IN(
SELECT SID
FROM COURSES
WHERE CNAME=‘C++’
)
INTERSECT
SELECT SID
FROM CHOICES
WHERE SID IN(
SELECT SID
FROM COURSES
WHERE CNAME=‘JAVA’
)
实现集合减运算，查询选修课程ｃ＋＋而没有选修课程Java的学生编号
SELECT SID
FROM CHOICES
WHERE SID IN(
SELECT SID
FROM COURSES
WHERE CNAME=‘C++’
)
EXCEPT
SELECT SID
FROM CHOICES
WHERE SID IN(
SELECT SID
FROM COURSES
WHERE CNAME=‘JAVA’
)