数据库实验三

计算机与信息学院实验报告系：专业：年级：姓名：学号：22实验室号计算机号22实验时间：2013年10月29日指导教师签字：成绩：报告退发(订正、重做)实验名称：（三）数据库的定义实验一、实验目的和要求（1）掌握SQLServer查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。（2）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。（3）熟练掌握数据查询中的分组、统计、计算和组合的操作方法。二、实验内容和原理1.基本操作实验用Transact-SQL语句表示下列操作．在学生-课程库中实现其数据查询操作：（1）求数学系学生的学号和姓名。（2）求选修了高等数学的学生学号、姓名和成绩。（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。（4）获选修课程C1且成绩在80～90分之间的学生学号、姓名及成绩，并将成绩乘以系数0.8输出。（5）求数学系或计算机系姓张的学生的信息。（6）求缺少了成绩的学生的学号和课程号。（7）求C1课程的成绩高于张三的学生学号和成绩。（8）求其他系中比计算机系学生年龄都小的学生。（9）查询选修了全部课程的学生的姓名。（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。（11）查询每一门课的间接先行课（即先行课的先行课）。在图书-图书库中实现其查询操作。（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍。（2）求机械工业出版社出版的各类图书的平均定价，用GROUPBY表示。（3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。（4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。（5）查询计算机类和机械工业出版社出版的图书。（6）在图书-借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在1999年以前的借阅记录存入临时的超期借阅表。用Transact-SQL语句表示，并在学生选课库中实现下列数据连接查询操作：2.提高操作实验（1）按表1的格式，建立职工部门库和职工表、部门，并向表中输入数据。表1职工和部门表数据职工表部门表职工号姓名性别年龄部门1010李勇男20111011刘晨女191012王敏女22121013张立男2113部门号部门名称电话11生产科56612计划科57813一车间46714科研所（2）用Transact-SQL语句表示职工和部门之间的内连接、左外部连接和右外部连接，在职工部门库中实现其数据内连接和各种外查询操作。三、实验环境硬件：计算机软件：Windows2000和SQLServer2000四、算法描述及实验步骤五、调试过程六、实验结果七、总结Sql语句：/*用Transact-SQL语句表示下列操作．在学生-课程库中实现其数据查询操作：（1）求数学系学生的学号和姓名。（2）求选修了高等数学的学生学号、姓名和成绩。（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。（4）获选修课程C1且成绩在～分之间的学生学号、姓名及成绩，并将成绩乘以系数.8输出。（5）求数学系或计算机系姓张的学生的信息。（6）求缺少了成绩的学生的学号和课程号。（7）求C1课程的成绩高于张三的学生学号和成绩。（8）求其他系中比计算机系学生年龄都小的学生。（9）查询选修了全部课程的学生的姓名。（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。（11）查询每一门课的间接先行课（即先行课的先行课）。*/insertintostudentvalues('0007','abc7',21,'女','数学系');insertintostudentvalues('0008','abc8',24,'男','计算机系');insertintostudentvalues('0009','abc9',22,'男','数学系');insertintostudentvalues('0010','abc10',21,'女','计算机系');select*fromstudent;--（1）求数学系学生的学号和姓名。selectsno,snamefromstudentwheresdept='数学系';--（2）求选修了高等数学的学生学号、姓名和成绩。select*fromstudent;select*fromcourse;select*fromsc;updatecoursesetcname='高等数学'wherecno='c004';selectstudent.sno,sname,cradefromstudent,course,scwherecourse.cno=sc.cnoandstudent.sno=sc.snoandcourse.cname='高等数学';--（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。insertintocoursevalues('c1','C++','c001',4,78);insertintoscvalues('0008','c001',92);insertintoscvalues('0008','c1',95);insertintoscvalues('0010','c001',90);insertintoscvalues('0010','c1',83);selectsno,cradefromscwherecno='c1'orderbycradedesc,snoasc;--（4）获选修课程C1且成绩在～分之间的学生学号、姓名及成绩，并将成绩乘以系数.8输出。selectstudent.sno,sname,crade*0.8as成绩fromstudent,scwherestudent.sno=sc.snoandcno='c1'and(cradebetween80and90);--（5）求数学系或计算机系姓张的学生的信息。insertintostudentvalues('1000','张三',22,'男','数学系');insertintostudentvalues('1001','张小小',21,'女','计算机系');insertintostudentvalues('1002','张敏',21,'女','计算机系');--select*fromstudentwheresnamelike'张%'andsdept='数学系'orsdept='计算机系';select*fromstudentwheresnamelike'张%'andsdeptin('数学系','计算机系');--（6）求缺少了成绩的学生的学号和课程号。insertintoscvalues('1002','c001',null);selectsno,cnofromscwherecradeisnull;--（7）求C1课程的成绩高于张三的学生学号和成绩。insertintoscvalues('1000','c1',85);selectsno,cradefromscwherecno='c1'andcrade(selectcradefromsc,studentwheresc.sno=student.snoandsname='张三'andcno='c1');--（8）求其他系中比计算机系学生年龄都小的学生。select*fromsc;select*fromstudent;select*fromcourse;insertintostudentvalues('2001','王玲',19,'女','生物技术系');insertintostudentvalues('2002','李莉',20,'女','城市规划系');insertintostudentvalues('5001','黄成',20,'男','土木工程系');select*fromstudentwheresage(selectmin(sage)fromstudentwheresdept='计算机系')andsdeptnotin('计算机系');--（9）查询选修了全部课程的学生的姓名。insertintoscvalues('1001','c001',83);insertintoscvalues('1001','c004',93);insertintoscvalues('1001','c002',97);insertintoscvalues('1001','c005',80);insertintoscvalues('1001','c006',77);insertintoscvalues('1001','c007',81);insertintoscvalues('1001','c1',89);selectsnamefromstudentwherenotexists(select*fromcoursewherenotexists(select*fromscwheresc.sno=student.snoandsc.cno=course.cno));--（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。selectstudent.sno,snamefromstudentwheresnoin(selectdistinctsnofromscs1wherenotexists(select*fromscs2wheresno=(selectsnofromstudentwheresname='张三')andnotexists(select*fromscs3wheres3.sno=s1.snoands3.cno=s2.cno)));--（11）查询每一门课的间接先行课（即先行课的先行课）。select*fromcourse;selectfirst.cno,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=second.cno;/*在图书-图书库中实现其查询操作。（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的倍。（2）求机械工业出版社出版的各类图书的平均定价，用GROUPBY表示。（3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。（4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。（5）查询计算机类和机械工业出版社出版的图书。（6）在图书-借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在年以前的借阅记录存入临时的超期借阅表。*/useDB_BookAndReadergo--（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的倍。select*frombook;insertintobookvalues('1001','语言','清华大学','HF','汉语的由来',77);insertintobookvalues('1002','科技','华中科技大学','李四','未来的移动终端形式',89);insertintobookvalues('1003','计算机','清华大学','FZX','C++程序设计',57);insertintobookvalues('1004','数学','清华大学','FS','线性代数',45);insertintobookvalues('1005','经济','清华大学','GM','香港地产',119);insertintobookvalues('2003','计算机','清华大学','FZX','oracle',300);--selectmax(pricing),typefrombookgroupbytype;--selectavg(pricing)*2,typefrombookgroupbytype;selecttype