ImageVerifierCode 换一换
格式:DOCX , 页数:14 ,大小:187.60KB ,
资源ID:5757876      下载积分:3 金币
快捷下载
登录下载
邮箱/手机:
温馨提示:
快捷下载时,用户名和密码都是您填写的邮箱或者手机号,方便查询和重复下载(系统自动生成)。 如填写123,账号就是123,密码也是123。
特别说明:
请自助下载,系统不会自动发送文件的哦; 如果您已付费,想二次下载,请登录后访问:我的下载记录
支付方式: 支付宝    微信支付   
验证码:   换一换

加入VIP,免费下载
 

温馨提示:由于个人手机设置不同,如果发现不能下载,请复制以下地址【https://www.bingdoc.com/d-5757876.html】到电脑端继续下载(重复下载不扣费)。

已注册用户请登录:
账号:
密码:
验证码:   换一换
  忘记密码?
三方登录: 微信登录   QQ登录  

下载须知

1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。
2: 试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓。
3: 文件的所有权益归上传用户所有。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 本站仅提供交流平台,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

版权提示 | 免责声明

本文(浙大远程数据库技术实验报告Word文档下载推荐.docx)为本站会员(b****1)主动上传,冰点文库仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知冰点文库(发送邮件至service@bingdoc.com或直接QQ联系客服),我们立即给予删除!

浙大远程数据库技术实验报告Word文档下载推荐.docx

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

copyright@ 2008-2023 冰点文库 网站版权所有

经营许可证编号:鄂ICP备19020893号-2