实验报告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