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

1、.实 验 报 告(使用MySql数据库)实验目的：SQL查询练习，掌握SQL查询语句的语法结构和各子句的使用方法。实验要求：根据给定的14个查询命题给出相应的SQL语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。实验内容：1 找出所有姓中以S.开头的学生。SQL语句：SELECT * FROM students WHERE LAST_NAME LIKE S%返回结果：2 找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。SQL语句：SELECT MAJOR AS 专业,count(MAJOR) AS 人数,MAX(CURRENT_CREDITS) AS 最高学分

2、,MIN(CURRENT_CREDITS) AS 最低学分,AVG(CURRENT_CREDITS) as 平均学分,SUM(CURRENT_CREDITS) AS 学分总数 FROM students GROUP BY MAJOR返回结果：3 找出所有教室的座位数，最大教室的座位数、最小座位数。SQL语句：SELECT SUM(NUMBER_SEATS) AS 座位数,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数 FROM rooms返回结果：4 找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。SQL语句：SEL

3、ECT BUILDING AS 大楼,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数,AVG(NUMBER_SEATS) AS 平均座位数,SUM(NUMBER_SEATS) AS 座位总数 FROM rooms GROUP BY BUILDING返回结果：5 找出各课程尚可选修的人数。SQL语句：SELECT Course AS 开课课程,DESCRIPTION AS 课程名称, (MAX_STUDENTS - CURRENT_STUDENTS) AS 可选修人数 FROM classes返回结果：6 找出开课最多的系。SQL语句：

4、SELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 1返回结果：7 找出开课最少的系。SQL语句：SELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 1返回结果：8 找出选课最多的学生。SQL语句：SELECT STUDENT_ID AS 学生号,COUNT

5、(STUDENT_ID) AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT(COURSE) = all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID)返回结果：9 找出选课最少的学生。SQL语句：SELECT STUDENT_ID AS 学生号,COUNT(STUDENT_ID) AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT(COURSE) = a

6、ll(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID)返回结果：10 找出不及格的学生。SQL语句：ALTER TABLE registered_students ADD FRACTION INTUPDATE registered_students SET FRACTION = CASE GRADE WHEN A THEN 90 WHEN B THEN 80 WHEN C THEN 70 WHEN D THEN 60 ELSE 50 ENDSELECT * FROM registered_students WH

7、ERE FRACTION 60返回结果：11 找出各课程平均分以下的学生。SQL语句：SELECT a.ID AS 学号,a.FIRST_NAME AS 名,a.LAST_NAME AS 姓,b.COURSE AS 科目,b.FRACTION AS 分数,c.平均分 FROM students as a,registered_students as b,(SELECT COURSE,AVG(FRACTION) as 平均分 FROM registered_students GROUP BY COURSE) as c WHERE a.ID = b.STUDENT_ID AND b.COURSE

8、= c.COURSE AND b.FRACTION c.平均分返回结果：12 找出各系所占教室的座位数。SQL语句：SELECT a.DEPARTMENT AS 系别,SUM(b.NUMBER_SEATS) FROM classes AS a, rooms AS b WHERE a.ROOM_ID = b.ROOM_ID GROUP BY a.DEPARTMENT返回结果：13 分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。SQL语句：创建学生历史表：增加入学日期（REGDATE）、学习状态字段(ST

9、ATUS)CREATE TABLE demo.students_his(ID SMALLINT NULL,FIRST_NAME varchar(20) NULL, LAST_NAME varchar(20) NULL, MAJOR varchar(30) NULL, CURRENT_CREDITS SMALLINT NULL, REGDATE DATETIME NULL, STATUS varchar(10) null,PRIMARY KEY(ID)ENGINE=MyISAM DEFAULT CHARSET=utf8;创建选修课程历史表：增加选课日期字段（ENRDATE）CREATE TABL

10、E demo.registered_students_his ( STUDENT_ID smallint(6) DEFAULT NULL, DEPARTMENT varchar(3) DEFAULT NULL, COURSE smallint(6) DEFAULT NULL, GRADE varchar(1) DEFAULT NULL, FRACTION int(11) DEFAULT NULL,ENRDATE datetime DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;导入当前学生信息insert into students_his(I

11、D,FIRST_NAME,LAST_NAME,MAJOR,CURRENT_CREDITS) SELECT * FROM students更新入学日期、学习状态update students_his set REGDATE = 20170901,STATUS = 在学导入当前选修信息insert into registered_students_his(STUDENT_ID,DEPARTMENT,COURSE,GRADE,FRACTION) select * from registered_students更新选课日期update registered_students_his set ENRD

12、ATE = 20170915删除毕业学生:delete from students_his where STATUS = 毕业 delete from registered_students_his where STUDENT_ID not in (select ID from students_his)14 找出选修了HIS系开的所有课程的学生。SQL语句：SELECT s.ID AS 学号,s.FIRST_NAME AS 名,s.LAST_NAME AS 姓,s.MAJOR AS 专业,s.CURRENT_CREDITS AS 已得学分 FROM students s,registered

13、_students rs WHERE s.ID = rs.STUDENT_ID AND rs.DEPARTMENT = HIS返回结果：数据库改造1 ALTER TABLE registered_students ADD COLUMN temp INTEGER -增加字段2 UPDATE registered_students SET temp = ASCII(GRADE) -将grade转换为数字插入temp3 UPDATE registered_students SET temp=99 WHERE temp=65; -A4 UPDATE registered_students SET te

14、mp=89 WHERE temp=66; -B5 UPDATE registered_students SET temp=79 WHERE temp=67; -C6 UPDATE registered_students SET temp=69 WHERE temp=68; -D7 UPDATE registered_students SET temp=59 WHERE temp=69; -E8 alter table registered_students modify column GRADE INTEGER; -修改grade字段类型9 UPDATE registered_students

15、 SET GRADE = temp -修改grade的值为temp列的值10 alter table registered_students drop column temp -删除temp字段-改造后，重写数据库说明.doc里的10、11。10找出不及格的学生SQL：SELECT ID AS 学号,FIRST_NAME AS 名,LAST_NAME AS 姓,GRADE AS 分数 FROM students, registered_students WHERE ID=STUDENT_ID AND GRADE 60结果：11 找出各课程平均分以下的学生SQL：SELECT a.ID AS 学

16、号,a.FIRST_NAME AS 名,a.LAST_NAME AS 姓,b.COURSE AS 科目,b.GRADE AS 分数,c.平均分 FROM students as a,registered_students as b,(SELECT COURSE,AVG(GRADE) as 平均分 FROM registered_students GROUP BY COURSE) as c WHERE a.ID = b.STUDENT_ID AND b.COURSE = c.COURSE AND b.GRADE 35 THEN1ELSE0END) AS 年长教师数,SUM(CASEWHEN c.

17、AGE 35 THEN1ELSE0END) AS 年轻教师数FROM teacher c结果：统计各系年轻教师中职称为“教授”的教师数量。SQL：SELECT c.DEPARTMENT AS 系别,COUNT(t.P_TITLE) AS 教授数量 FROM teacher t,classes c WHERE t.ID = c.TEACHER_ID AND t.P_TITLE=教授 GROUP BY c.DEPARTMENT结果：5. 统计每个教师带的学生个数。SQL：SELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS) FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_ID结果：6. 统计每个教师的工作量，教师的工作量按照学生人数*所授课程的学分数进行统计SQL：SELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS)*c.NUM_CREDITS FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_ID结果：7. 根据指定的教师名字或教师工号确定教师现在的授课课程及课程的上课地