数据库原理与应用实验作业参考答案.doc

数据库原理与应用实验报告参考答案实验1 数据库的建立修改与删除实验2 表结构的建立修改删除及完整性约束条件定义实验3 数据查询与更新 (一) 规定内容1. 单表查询USE ST-3_1 查询全体学生的详细记录。select * from student-3_2 查询选修了课程的学生学号。SELECT DISTINCT sno FROM sc-3_3 将“学生”表中的sno、sname这2列合并为1列snosname输出(不改变表中存储的内容)，其余列不变。select sno+sname snosname,ssex,sage,sdeptfrom student-3_4 查询年龄不在2023岁之间的学生姓名、系别和年龄。方法1：SELECT sname,sdept,sageFROM studentWHERE sage NOT BETWEEN 20 AND 23; 方法2： SELECT sname,sdept,sage FROM student WHERE sage23;-3_5 查询计算机科学系(cs)、数学系(ma)和信息系(is)学生的姓名和性别。SELECT sname,ssexFROM studentWHERE sdept IN ( cs,ma,is );-3_6 查询所有姓“刘”学生的姓名、学号和性别。SELECT sname,sno,ssexFROM studentWHERE sname LIKE 刘%;-3_7 查询名字中第2个字为阳字的学生的姓名和学号。SELECT sname,snoFROM studentWHERE sname LIKE _阳%;-3_8 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。 -查询缺少成绩的学生的学号和相应的课程号。SELECT sno,cno FROM scWHERE grade IS NULL;-3_9 查询计算机系年龄在20岁以下的学生姓名。SELECT snameFROM studentWHERE sdept=cs AND sage=3 -也可为:COUNT(cno)=3-3_19 查询有2门以上课程是80分以上的学生的学号及(80分以上的)课程数SELECT sno 学号,COUNT(*) 80分以上的课程数FROM scWHERE grade=80GROUP BY sno HAVING COUNT(*)=2 2. 连接查询-自然连接-3_20 查询每个学生的学号、姓名、课号及成绩。SELECT student.sno,sname,cno,grade FROM student,sc WHERE student.sno = sc.sno-左外连接-3_21 查询每个学生的学号、姓名、课号及成绩(包括没有选修课程的学生)。方法1：SELECT student.sno,sname,cno,gradeFROM student,scwhere student.sno *= sc.sno方法2：SELECT student.sno,sname,cno,gradeFROM student LEFT JOIN sc ON student.sno = sc.sno运行结果：sno sname cno grade - - - - 200215121 李勇 1 97.0200215121 李勇 2 40.0200215121 李勇 3 93.0200215122 刘晨 2 95.0200215122 刘晨 3 55.0200215123 王敏 NULL NULL200215124 张立 NULL NULL-3_22 查询所有学生选修课程的成绩。包括没有选课的学生。列出学号、姓名、课号、课名、成绩。SELECT student.sno,sname,o,cname,gradeFROM sc JOIN course ON o=o RIGHT JOIN student ON student.sno = sc.sno运行结果：sno sname cno cname grade - - - - - 200215121 李勇 1 数据库 97.0200215121 李勇 2 数学 40.0200215121 李勇 3 信息系统 93.0200215122 刘晨 2 数学 95.0200215122 刘晨 3 信息系统 55.0200215123 王敏 NULL NULL NULL200215124 张立 NULL NULL NULL-自身连接: 一个表与其自己进行连接，称为表的自身连接-3_23 查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.cno 课号,SECOND.cpno 间接先修课FROM course FIRST,course SECONDWHERE FIRST.cpno = SECOND.cno-复合条件连接-3_24 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECT student.sno, sname FROM student,sc WHERE student.sno = sc.sno /* 连接谓词*/ AND o=2 AND sc.grade 90 /* 其他限定条件*/-多表连接-3_25 查询每个学生的学号、姓名、课名及成绩。SELECT student.sno,sname,cname,gradeFROM student,sc,courseWHERE student.sno = sc.sno and o = o运行结果：sno sname cname grade - - - - 200215121 李勇 数据库 97.0200215121 李勇 数学 40.0200215121 李勇 信息系统 93.0200215122 刘晨 数学 95.0200215122 刘晨 信息系统 55.0 3 嵌套查询-3_26 查询与“刘晨”在一个系学习的学生。-方法1: 不相关子查询(子查询的查询条件不依赖于父查询)select sno,sname,sdeptfrom student where sdept in -当内查询结果最多只有一个值时可用=代替in (select sdept from student where sname=刘晨) -3_27 查询所有姓名相同的学生。-方法1: 不相关子查询(子查询的查询条件不依赖于父查询)select *from studentwhere sname in (select sname from student group by sname having count(*)1)order by sname,sno -3_28 查询选修了课程名为“信息系统”的学生学号和姓名。-方法1：用嵌套查询(不相关子查询)SELECT sno,sname -最后在sudent关系中取出Sno和SnameFROM student WHERE sno IN (SELECT sno -然后在sc中找出选修了“信息系统”所在课号的学生学号 FROM sc WHERE cno IN (SELECT cno -首先在curse关系中找出“信息系统”的课程号 FROM course WHERE cname=信息系统)-3_29 找出每个学生超过他选修课程平均成绩的学号、课号及成绩。SELECT x.* FROM sc x WHERE grade=(SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno)运行结果：sno cno grade - - - 200215121 1 97.0200215121 3 93.0200215122 2 95.0-3_30 查询其他系中比计算机科学系某一学生年龄小的学生学号、姓名和年龄。-方法1：用any谓词 select sname,sage from student where sageany (select sage from student where sdept=cs) and sdeptcs /* 注意这是父查询块中的条件*/-3_31 查询其他系中比计算机科学系所有学生年龄都小的学生学号、姓名和年龄。select sname,sage from student where sageall (select sage from student where sdept=cs) and sdeptcs-3_32 查询所有选修了1号课程的学生学号、姓名。SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=1); -相关子查询-3_33 查询没有选修1号课程的学生学号、姓名。-方法1：相关子查询 SELECT sno,sname FROM student WHERE NOT EXISTS (SELECT * -此处*可换为sno FROM sc WHERE sno=student.sno AND cno=1); -3_34 查询选修了全部课程的学生姓名。SELECT sname -查询这样的学生y FROM student WHERE NOT EXISTS -不存在课程x (SELECT * FROM course WHERE NOT EXISTS -学生y不选修x (SELECT * FROM sc WHERE sno=student.sno -表SC与Student自然连接AND cno=o) -表SC与course自然连接-3_35 查询至少选修了学生200215122选修的全部课程的学生号码。SELECT DISTINCT sno -查询这样的学生的xFROM sc SCXWHERE NOT EXISTS -不存在这样的课程y (SELECT * FROM sc SCY WHERE SCY.sno=200215122 AND -学生200215122选修了yNOT EXISTS -学生x没有选修y (SELECT * FROM sc SCZ WHERE SCZ.sno=SCX.sno AND SCZ.cno=SCY.cno)-3_36 将表sc复制到sc1中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc1 AND type = U) DROP TABLE sc1 GOSelect * Into sc1 from sc -复制表-3_37 将表sc的结构复制到sc2中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc2 AND type = U) DROP TABLE sc2 GOselect * into sc2 from sc -复制表结构where sno is null-或Select * into sc2 from sc -复制表结构Where not exists (select * from sc) 4 集合查询-3_38 查询计算机系的学生及年龄不大于19岁的学生，并按年龄降序排列。-方法1：并操作(UNION)SELECT *FROM studentWHERE sdept=csUNION SELECT *FROM student WHERE sage=19Order by sage desc-3_39 查询选修了课程1或者选修了课程2的学生学号。SELECT snoFROM scWHERE cno=1UNION SELECT snoFROM scWHERE cno=2-3_40 查询既选修了1号又选修了2号课程的学生学号。本查询可表述为：查询至少选修了1、2号课程的学生学号。-方法1：集合交操作，查询选修课程1的学生集合与选修课程2的学生集合的交集SELECT snoFROM scWHERE cno=1 INTERSECTSELECT snoFROM scWHERE cno=2-方法2：SELECT snoFROM scWHERE cno=1 AND sno IN (SELECT sno FROM sc WHERE cno=2)-3_41 查询计算机系的学生与年龄不大于19岁的学生的交集。-方法1：交操作SELECT *FROM studentWHERE sdept=cs INTERSECTSELECT *FROM studentWHERE sage=19-方法2：标准SQL中没有提供集合交操作，但可用其他方法间接实现。-本例实际上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept=cs AND sage19-方法2：标准SQL中没有提供集合差操作，但可用其他方法间接实现。-本例实际上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept=cs AND sage=60Group by student.sno,sname-3_44 学士学位授予条件为：至少选修了1、3、4号3门学位课程，每门学位课必须及格且学位课平均成绩在75分以上。查询cs系可授予学士学位的学生名单。select *from studentwhere sdept=cs and sno in (select sno from sc where cno in(1,3,4) and grade=60 group by sno having count(sno)=3 and avg(grade)=75)-3_45 学士学位授予条件为：至少选修了数据结构,数据库,操作系统3门学位课程，每门学位课必须及格且学位课平均成绩在75分以上。查询cs系可授予学士学位的学生名单。select *from studentwhere sdept=cs and sno in (select sno from sc where grade=60 and cno in (select cno from course where cname in(数据结构,数据库,操作系统) group by sno having count(sno)=3 and avg(grade)=75)5 插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的使用-3_46 插入一条选课记录( 200215123,1)。 if not exists(select * from sc where sno=200215123 and cno=1) INSERT INTO sc(sno,cno) VALUES (200215123,1) -新插入的记录在Grade列上取空值-3_47 对每一个系，求学生的平均年龄，并把结果存入数据库。 -第1步：建表，第2步：插入子查询结果 -第一步：建表 use ST IF EXISTS(SELECT name FROM sysobjects WHERE name = DeptAge AND type = U) DROP TABLE DeptAge -若表Deptage已存在,则先删除再重建 GO CREATE TABLE DeptAge (sdept CHAR(2), -系名 avgage SMALLINT) -学生平均年龄 -第二步：插入子查询结果 INSERT INTO DeptAge(sdept,avgage) SELECT sdept,AVG(sage) FROM student GROUP BY sdept select * from DeptAge-3_48 将所有学生的年龄增加1岁。 UPDATE student SET sage=sage+1-3_49 对计算机科学系(cs)全体学生选修2号课程分数=36的按10*sqrt(grade)计算。 update sc set grade=10*sqrt(grade) where cno=2 and grade=36 and cs=(select sdept from student where student.sno=sc.sno) -相关子查询-3_50 删除is系所有学生的选课记录。DELETE FROM sc WHERE is=(SELECT sdept FROM student WHERE student.sno=sc.sno);-3_51 删除只选1门课且成绩不及格学生的选课记录。 DELETE FROM sc WHERE sno in (SELECT sno FROM sc WHERE grade60 GROUP BY sno HAVING COUNT(*)=1)-3_52 删除计算机系只选1门课且成绩不及格学生的选课记录。DELETE FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept=cs AND sno IN (SELECT sno FROM sc WHERE grade45 or 月工资45; UNION Select 姓名,年龄,月工资 from 职工 Where 月工资=90-4_10 先建立高考(考号，姓名，语文，数学，英语，综合)基本表, 在此基础上建立含总分列的视图。IF EXISTS(SELECT name FROM sysobjects WHERE name = GK) drop table GK -若GK已存在,则删除 go create table GK (考号 char(5) primary key, 姓名 char(8), 语文 decimal(5,1) check(语文 between 0 and 150), 数学 decimal(5,1) check(数学 between 0 and 150), 英语 decimal(5,1) check(英语 between 0 and 150), 综合 decimal(5,1) check(综合 between 0 and 300)IF EXISTS(SELECT name FROM sysobjects WHERE name = V_gk) drop view V_gk -若GH_view已存在,则删除 go create view V_gk (考号,姓名,语文,数学,英语,综合,总分) -总分为虚拟列 as select 考号,姓名,语文,数学,英语,综合,语文+数学+英语+综合 from GK运行以上程序建立高考表“GK”及视图“V_gk”。-4_11 将学生的学号及他的平均成绩定义为一个视图。IF EXISTS (SELECT name FROM sysobjects WHERE name=V_avg) drop view V_avg -若V_avg已存在,则删除 GO CREATE VIEW V_avg(sno,gavg) AS SELECT sno,AVG(grade) FROM sc GROUP BY sno -分组视图-4_12 将student表中所有女生记录定义为一个视图。方法1：IF EXISTS(SELECT name FROM sysobjects WHERE name =V_fs) drop view V_fs -若V_fs已存在,则删除 GO CREATE VIEW V_fs (sno,sname,sex,age,dept) AS SELECT sno,sname,ssex,sage,sdept FROM student WHERE ssex=女 -当基表Student增加属性列时，不会破坏Student表与V_fs视图的映象关系。-4_13 建立视图V_good (修课成绩在平均成绩之上的元组)。IF EXISTS(SELECT name FROM sysobjects WHERE name = V_good) drop view V_good -若V_good已存在,则删除 GO CREATE VIEW V_good AS SELECT sno,cno,grade FROM sc WHERE grade (SELECT AVG(grade) -该视图应属行列子集视图 FROM sc)-4_14 建立1号课程的选课视图，并要求透过该视图进行的更新操作只涉及1号课程， -同时对该视图的任何操作只能在工作时间进行。IF EXISTS(SELECT name FROM sysobjects WHERE name = V_sc) drop view V_sc -若V_sc已存在,则删除 go CREATE VIEW V_sc AS SELECT sno,cno,grade FROM sc WHERE cno=1 and datepart(weekday,GETDATE() BETWEEN 2 and 6 -星期一至星期五 and datepart(hour,GETDATE() BETWEEN 9 and 17 -9:00至17:00 WITH CHECK OPTION3视图的查询与更新-4_15 在信息系学生的视图中找出所有女学生。SELECT * FROM V_is1 WHERE ssex=女-4_16 查询信息系选修了1号课程的学生SELECT V_is1.* FROM V_is1,sc WHERE V_is1.sno=sc.sno AND o=1-4_17 在V_avg视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT * FROM V_avg WHERE gavg=90-4_18 将信息系学生视图V_is1中学号200215124的学生姓名改为“张三”。UPDATE V_is1 SET sname= 张三 WHERE sno= 200215124-4_19 向信息系学生视图V_is1中插入一个新的学生记录：200215129，赵新，女，18，isINSERT INTO V_is1 VALUES(200215129,赵新,女,18,is)-4_20 删除视图V_is1中学号为200215124的记录DELETE FROM V_is1 WHERE sno=200215124(二) 自定内容五、出现的问题及解决方法实验5 T_SQL基本程序设计完成以下实验报告：数据库原理与应用实验报告院名专业年级班级学号姓名教师成绩一、实验题目实验5 Transact-SQL基本程序设计二、实验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000 或MS SQL Server 2008。三、实验目的 1掌握Transact-SQL语言中顺序、分支、循环三种控制结构的语句。2掌握Transact-SQL语言中系统函数的使用方法。3学会使用Transact-SQL语句编写基本程序。四、实验内容 (一) 规定内容5_1 求方程ax2+bx+c=0的根。declare a real,b real,c realdeclare da real,p real,q realselect a=5,b=5,c=3set da=b*b-4*a*cif da=0 begin print 方程有如下2个实根： print x1=+ltrim(str(-b+sqrt(da)/(2*a),10,2) print x2=+ltrim(str(-b-sqrt(da)/(2*a),10,2) endelse begin select p=-b/(2*a),q=sqrt(-da)/(2*a) print 方程有如下2个共轭虚根： print x1=+ltrim(str(p,10,2)+ltrim(str(q,10,2)+i print x2=+ltrim(str(p,10,2)+-+ltrim(str(q,10,2)+i end方程有如下2个共轭虚根：x1=-0.50+0.59ix2=-0.50-0.59i 5_2 将某待查学生姓名赋给变量s，查询该学生记录，若不存在则输出“查无此人！”。declare s char(8)set s=张三if exists (select * from student where sname=s) select * from student where sname=selse print 查无此人！ 5_3 查询全体学生的sno、sname、ssex、sdept，对于ssex，若是“男” 则显示“男生”，若是“女”则显示“女生”。select sno,sname, ssex=case ssex when 男 then 男生 when 女 then 女生 end,sdeptfrom student5_4 对选修表按成绩的分数段显示A(90100)、B(80