2014实验报告1.docVIP

实 验 报 告 实验目的：SQL查询练习，掌握SQL查询语句的语法结构和各子句的使用方法。 实验要求：根据给定的14个查询命题给出相应的SQL语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。 实验内容： 找出所有姓中以S.开头的学生。 SQL语句：SELECT * FROM students s where s.LAST_NAME LIKE S*; 返回结果： 找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。 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; 返回结果： 找出所有教室的座位数，最大教室的座位数、最小座位数。 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 返回结果： 找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。 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 返回结果： 找出各课程尚可选修的人数。 SQL语句：SELECT c.DESCRIPTIO as 课程名称,c.MAX_STUDEN-c.CURRENT_ST as 可选修人数 FROM classes c 返回结果： 找出开课最多的系。 SQL语句：SELECT top 1 rs.DEPARTMENT ,count(1) as count FROM registered_students rs group by rs.DEPARTMENT order by 2 desc 返回结果： 找出开课最少的系。 SQL语句：SELECT top 1 rs.DEPARTMENT ,count(1) as count FROM registered_students rs group by rs.DEPARTMENT order by 2 asc 返回结果： 找出选课最多的学生。 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 返回结果： 找出选课最少的学生。 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 返回结果： 找出不及格的学生。 SQL语句：SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM registered_students rs WHERE rs.STUDENT_ID=s.ID AND rs.GRADE=59) 返回结果： 找出各课程平均分以下的学生。 SQL语句：select z.*,temp.avg_grade from registered_students z left join( select rs.department,rs.course ,avg(rs.grade) as avg_grade from registered_students rs group by rs.department,rs.course) temp on z.department=temp.depa