浙大远程数据库技术的实验报告.doc

Word格式 完美整理 实 验 报 告(使用MySql数据库) 实验目的：SQL查询练习，掌握SQL查询语句的语法结构和各子句的使用方法。 实验要求：根据给定的14个查询命题给出相应的SQL语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。 实验内容： 找出所有姓中以S.开头的学生。 SQL语句：SELECT * FROM students WHERE LAST_NAME LIKE S% 返回结果： 找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。 SQL语句： SELECT MAJOR AS 专业,count(MAJOR) AS 人数,MAX(CURRENT_CREDITS) AS 最高学分,MIN(CURRENT_CREDITS) AS 最低学分,AVG(CURRENT_CREDITS) as 平均学分,SUM(CURRENT_CREDITS) AS 学分总数 FROM students GROUP BY MAJOR 返回结果： 找出所有教室的座位数，最大教室的座位数、最小座位数。 SQL语句： SELECT SUM(NUMBER_SEATS) AS 座位数,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数 FROM rooms 返回结果： 找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。 SQL语句： SELECT BUILDING AS 大楼,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数,AVG(NUMBER_SEATS) AS 平均座位数,SUM(NUMBER_SEATS) AS 座位总数 FROM rooms GROUP BY BUILDING 返回结果： 找出各课程尚可选修的人数。 SQL语句： SELECT Course AS 开课课程,DESCRIPTION AS 课程名称, (MAX_STUDENTS - CURRENT_STUDENTS) AS 可选修人数 FROM classes 返回结果： 找出开课最多的系。 SQL语句： SELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 1 返回结果： 找出开课最少的系。 SQL语句： SELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 1 返回结果： 找出选课最多的学生。 SQL语句： SELECT STUDENT_ID AS 学生号,COUNT(STUDENT_ID) AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT(COURSE) = all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID) 返回结果： 找出选课最少的学生。 SQL语句： SELECT STUDENT_ID AS 学生号,COUNT(STUDENT_ID) AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT(COURSE) = all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID) 返回结果： 找出不及格的学生。 SQL语句： ALTER TABLE registered_students ADD FRACTION INT UPDATE registered_students SET FRACTION = CASE GRADE WHEN A THEN 90 WHEN B THEN 80 WHEN C THEN 70 WHEN D THEN 60 ELSE 50 END SELECT * FROM registered_students WHERE FRACTION 60 返回结果： 找出各课程平均分以下的学生。 SQL语句： SELECT a.ID AS 学号,a.FIRST_NAME AS 名,a.LAST_NAME AS 姓,b.COURSE AS 科目,b.FRACTION AS 分数,c.`平均分` FROM stu