1、5 找出各课程尚可选修的人数。SELECT Course AS 开课课程,DESCRIPTION AS 课程名称, （MAX_STUDENTS - CURRENT_STUDENTS） AS 可选修人数 FROM classes6 找出开课最多的系。SELECT DEPARTMENT AS 系别,COUNT（COURSE） AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 17 找出开课最少的系。SELECT DEPARTMENT AS 系别,COUNT（COURSE） AS 开课数 FROM re
2、gistered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 18 找出选课最多的学生。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）9 找出选课最少的学生。SELECT STUDENT_ID AS 学生号,COU
3、NT（STUDENT_ID） AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT（COURSE） 10 找出不及格的学生。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_stu
4、dents WHERE FRACTION 6011 找出各课程平均分以下的学生。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 = c
5、.COURSE AND b.FRACTION c.平均分12 找出各系所占教室的座位数。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.DEPARTMENT13 分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。创建学生历史表：增加入学日期（REGDATE）、学习状态字段（STATUS）CREATE TABLE demo.st
6、udents_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 TABLE demo.registered_student
7、s_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（ID,FIRST_NAME,LAST_NAME,M
8、AJOR,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 ENRDATE = 20170915删除毕业学生:del
9、ete from students_his where STATUS = 毕业delete from registered_students_his where STUDENT_ID not in （select ID from students_his）14 找出选修了HIS系开的所有课程的学生。SELECT s.ID AS 学号,s.FIRST_NAME AS 名,s.LAST_NAME AS 姓,s.MAJOR AS 专业,s.CURRENT_CREDITS AS 已得学分 FROM students s,registered_students rs WHERE s.ID = rs.ST
10、UDENT_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 temp=89 WHERE temp=66; -B5 UPDATE regi
11、stered_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 SET GRADE = temp -修改grade的值为temp列的值
12、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 结果：11 找出各课程平均分以下的学生SELECT a.ID AS 学号,a.FIRST_NAME AS 名,a.LAST_NAME AS 姓,b.COU
13、RSE 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 THEN 1 ELSE 0 END ） AS 年长教师数,SUM（ WHEN c.AGE ） AS 年轻教师数FROM teacher c统计各系年轻教师中职称为
14、“教授”的教师数量。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.DEPARTMENT5. 统计每个教师带的学生个数。SELECT t.ID,t.NAME,SUM（c.CURRENT_STUDENTS） FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_ID6. 统计每个教师的工作量，教师的工作量按
15、照学生人数*所授课程的学分数进行统计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_ID7. 根据指定的教师名字或教师工号确定教师现在的授课课程及课程的上课地点（教室号）t.ID = 和 t.NAME 都是可以替换的SELECT r.*, temp.DEPARTMENT, temp.COURSEFROM rooms rRIGHT JOIN （ SELECT c.DEPARTMENT, c.TEACHER_ID, c.COURSE, c.ROOM_ID FROM classes c WHERE EXISTS （ SELECT 1 FROM teacher t WHERE c.TEACHER_ID = t.ID AND （t.ID = 1 OR t. NAME = 赵清） ） temp ON r.ROOM_ID = temp.ROOM_ID
