《数据库原理及应用》上机实验
实验五数据查询
一、学时
４学时
二、实验类型
综合应用
三、实验目的
了解查询的概念和方法；
掌握查询分析器的使用方法；
掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式；
加深理解各种关系运算（尤其是关系的选择，投影，连接和除运算）
四、实验准备
1．熟悉SQL Server2012工作环境；
2．复习对表中数据进行查询操作的SQL语言命令；
五、实验方法及步骤
1．在表student，course，sc上进行简单查询；
2．在表student，course，sc上进行连接查询；
3．在表student，course，sc上进行嵌套查询；
4．使用聚合函数的查询；
5．对数据的分组查询；
6．对数据的排序查询；
7．综合查询。
六、实验内容
在表student，course，sc上完成以下查询：
1．查询学生的基本信息；
SELECT*FROM STUDENT
2．查询“CS”系学生的基本信息；
SELECT*FROM STUDENT WHERE SDEPT ='cs'
3．查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
SELECT*FROM STUDENT WHERE SDEPT ='cs'AND AGE<19 OR AGE>21
无匹配结果
4．找出最大年龄；
SELECT MAX(AGE)AS'MAX AGE'FROM STUDENT
5．找出“CS”系年龄最大的学生，显示其学号、姓名；
SELECT SNO,SNAME FROM STUDENT WHERE AGE =(SELECT MAX(AGE)FROM STUDENT WHERE SDEPT='CS')
6．查询所有姓王的学生的学号、姓名、所在系；
SELECT SNO,SNAME,SDEPT FROM STUDENT WHERE SNAME LIKE'王%'
7．统计“CS”系学生的人数；
SELECT COUNT(*)AS CS FROM STUDENT WHERE SDEPT ='CS'
8．统计各系学生的人数，结果按升序排列；
SELECT SDEPT,COUNT(*)AS'各系人数'FROM STUDENT GROUP BY SDEPT ORDER BY ASC
9．按系统计各系学生的平均年龄，结果按降序排列；
SELECT SDEPT,AVG(AGE) AVGAGE FROM STUDENT GROUP BY SDEPT ORDER BY AVGAGE DESC
10.查询每门课程的课程名；
SELECT CNAME FROM COURSE
11．统计无先修课的课程的学分总数；
SELECT SUM(CREDIT)AS无先修课总学分FROM COURSE WHERE CPNO IS NULL
12．查询选修了“1”或“2”号课程的学生学号和姓名；
SELECT DISTINCT STUDENT.SNO,SNAME FROM SC,STUDENT WHERE STUDENT.SNO=SC.SNO AND CNO IN('1','2')
13．查询选修了“1”和“2”号课程的学生学号和姓名；
SELECT STUDENT.SNO,SNAME FROM SC,STUDENT WHERE SC.SNO=STUDENT.SNO AND CNO='1'AND STUDENT.SNO IN(SELECT STUDENT.SNO FROM SC,STUDENT WHERE SC.SNO=STUDENT.SNO AND CNO='2')
14．查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；
SELECT STUDENT.SNO,SNAME,GRADE FROM SC,STUDENT,COURSE WHERE SC.SNO=STUDENT.SNO AND CNAME='数据库系统'AND GRADE <60
15．查询每位选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；
SELECT STUDENT.SNO,SNAME,https://www.doczj.com/doc/6010911140.html,O,CNAME,GRADE FROM SC,STUDENT,COURSE WHERE STUDENT.SNO=SC.SNO AND https://www.doczj.com/doc/6010911140.html,O = https://www.doczj.com/doc/6010911140.html,O
16．查询每位学生的信息（显示：学号，姓名，所在系，课程号，课程名，成绩。包括没选课的学生）；
SELECT STUDENT.SNO,SNAME,S DEPT,https://www.doczj.com/doc/6010911140.html,O,CNAME,GRADE FROM SC,STUDENT,COURSE WHERE SC.SNO = STUDENT.SNO AND https://www.doczj.com/doc/6010911140.html,O = https://www.doczj.com/doc/6010911140.html,O UNION
SELECT STUDENT.SNO,SNAME,SDEPT,https://www.doczj.com/doc/6010911140.html,O, CNAME,GRADE FROM STUDENT LEFT OUTER
JOIN SC ON(STUDENT.SNO =SC.SNO)LEFT OUTER JOIN COURSE ON(https://www.doczj.com/doc/6010911140.html,O =https://www.doczj.com/doc/6010911140.html,O)WHERE
https://www.doczj.com/doc/6010911140.html,o IS NULL
17．查询没有选修课程的学生的基本信息；
SELECT*FROM STUDENT WHERE SNO !=ALL(SELECT SNO FROM SC)
18．查询各系年龄最大的学生，显示其学号、姓名；
SELECT SNO, SNAME ,SDEPT FROM STUDENT WHERE AGE =(SELECT MAX(AGE)FROM STUDENT WHERE A.SDEPT = B.SDEPT)
19．查询选修了3门以上课程的学生学号；
SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)>3
20．查询选修课程成绩至少有一门在80分以上的学生学号；
SELECT SNO,MAX(GRADE) MAXGRADE FROM SC GROUP BY SNO HAVING MAX(GRADE)>80
21．查询选修课程成绩均在80分以上的学生学号；
SELECT SNO,MIN(GRADE) MINGRADE FROM SC GROUP BY SNO HAVING MIN(GRADE)>80
22．查询选修课程平均成绩在80分以上的学生学号；
SELECT SNO,AVG(GRADE) AVGGRADE FROM SC GROUP BY SNO HAVING AVG(GRADE)> 80
23．统计每位学生选修课程的门数、学分及其平均成绩；
SELECT SNO,COUNT(https://www.doczj.com/doc/6010911140.html,O)'课程总数',AVG(GRADE)'平均成绩',SUM(CREDIT)'总分数' FROM COURSE,SC WHERE https://www.doczj.com/doc/6010911140.html,O=https://www.doczj.com/doc/6010911140.html,O GROUP BY SNO
24．统计选修每门课程的学生人数及各门课程的平均成绩；
SELECT CNO,COUNT(SNO)课程人数,AVG(GRADE)'平均成绩'FROM SC GROUP BY CNO
25．找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。
SELECT SC.SNO,SDEPT,AVG(GRADE)'平均成绩'FROM SC,STUDENT WHERE
STUDENT.SNO=SC.SNO GROUP BY SDEPT,SC.SNO HAVING AVG(GRADE)>85 ORDER BY AVG(GRADE) ASC