实验报告1 (800字.doc
文本预览下载声明
实验报告1 (800字)
实 验 报 告
实验目的：SQL查询练习，掌握SQL查询语句的语法结构和各子句的使用方法。
实验要求：根据给定的14个查询命题给出相应的SQL语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。 实验内容：
a) 找出所有姓中以S.开头的学生。
SQL语句：SELECT * FROM students s where s.LAST_NAME LIKE #39;S*#39;; 返回结果：
b) 找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。
SQL语句：SELECT s.MAJOR as 专业,count(1) as 人数,max(s.CURRENT_CR) as 最高
分,min(s.CURRENT_CR) as 最低分,avg(s.CURRENT_CR) as 平均分,sum(CURRENT_CR) as 总学分 FROM students s group by s.MAJOR;
返回结果：
c) 找出所有教室的座位数，最大教室的座位数、最小座位数。
SQL语句：SELECT r.NUMBER_SEA as number_sea,max(r.NUMBER_SEA) as
max_number_sea,min(r.NUMBER_SEA) as min_number_sea FROM rooms r group by r.NUMBER_SEA
返回结果：
d) 找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。
SQL语句：SELECT r.BUILDING as 大楼,max(r.NUMBER_SEA) as 最大座位
数,min(r.NUMBER_SEA) as 最小座位数,avg(r.NUMBER_SEA) as 平均座位数,sum(r.NUMBER_SEA) as 总座位数 FROM rooms r group by r.BUILDING
返回结果：
e) 找出各课程尚可选修的人数。
SQL语句：SELECT c.DESCRIPTIO as 课程名称,c.MAX_STUDEN-c.CURRENT_ST as
可选修人数 FROM classes c
返回结果：
f)
找出开课最多的系。 SQL语句：SELECT top 1 rs.DEPARTMENT ,count(1) as count FROM registered_students
rs group by rs.DEPARTMENT order by 2 desc
返回结果：
g) 找出开课最少的系。
SQL语句：SELECT top 1 rs.DEPARTMENT ,count(1) as count FROM registered_students
rs group by rs.DEPARTMENT order by 2 asc
返回结果：
h) 找出选课最多的学生。
SQL语句：SELECT s.*,temp.选课数 FROM students s right join (SELECT top 1
rs.STUDENT_ID,count(1) as 选课数 FROM registered_students rs group by rs.STUDENT_ID order by 2 desc ) temp on temp.STUDENT_ID=s.ID
返回结果：
i)
找出选课最少的学生。
SQL语句：SELECT s.*,temp.选课数 FROM students s right join (SELECT top 1
rs.STUDENT_ID,count(1) as 选课数 FROM registered_students rs group by rs.STUDENT_ID order by 2 asc ) temp on temp.STUDENT_ID=s.ID
返回结果：
j)
找出不及格的学生。
SQL语句：SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM
registered_stude
显示全部