201421314431-张和杰-数据库实验三报告.doc

仲恺农业工程学院实验报告纸 计算科学（院、系） 信息与计算科学 专业 144 班 数据库原理与应用 课学号 201421314431 姓名 张和杰 实验日期 2016-3-21 教师评定 实验三实验项目：数据库多表查询与视图的创建和使用。实验目的：通过实验，要求学生掌握多表查询和使用视图。实验要求：成功使用多表查询和视图。实验内容：在SM数据库中进行以下操作：1. 使用EXISTS查询所有选修了001号课程的学生的姓名。2. 使用NOT EXISTS查询未选修了001号课程的学生的姓名。3. 使用NOT EXISTS查询选修了课程学分为的全部课程的学生的姓名。4. 统计教职工的总人数、最高工资、最少工资、平均工资。5. 查询选修两门课程以上的学生学号。6. 查询选修了三门课程的学生学号。7. 使用分组查询选修了所有课程的学生学号、姓名。8. 在选修课001中，使用ALL查询比学号00001和学号00004的成绩都低的学生学号；9. 在选修课001中，使用ANY查询比学号00001或学号00004的成绩低的学生学号；10. 在选修课001中，查询比平均成绩低的学生的学号；11. 按总平均值降序给出所有课程都及格但不包括课程001的所有学生的总平均成绩；12. 更新表student中的年龄，年龄为当前年份减去出生年份（提示，使用year()）；13. 更新表SC中的成绩，把选修了“数据库”的成绩加1；14. 在表student中删除学号为00001的学生信息；15. 在表course中删除“数据库”课程的信息；16. 在表SC中删除男同学的信息。17. 建立一个系号为003的学生表（学号，姓名），要求把student的所有学号和姓名插入该表。18. 使用SQL语句写出课本P70页第6题的查询。(1) 求供应工程J1零件的供应商号码SNO:SELECT DIST SNO FROM SPJ WHERE JNO=J1(2) 求供应工程J1零件P1的供应商号码SNO:SELECT DIST SNO FROM SPJ WHERE JNO=J1 AND PNO=P1(3) 求供应工程J1零件为红色的供应商号码SNO:SELECT SNO FROM SPJ,P WHERE JNO=J1 AND SPJ.PNO=P.PNO AND COLOR=红(4) 求没有使用天津供应商生产的红色零件的工程号JNO:SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SPJ,P,S WHERE S.CITY=天津 AND COLOR=红 AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)。 (5) 求至少用了供应商S1所供应的全部零件的工程号JNO。将查询分为两步1、查询S1供应商供应的零件号SELECT DIST PNO FROM SPJ WHERE SNO=S1结果是（P1，P2）2、查询哪一个工程既使用P1零件又使用P2零件。SELECT JNO FROM SPJ WHERE PNO=P1 AND JNO IN (SELECT JNO FROM SPJ WHERE PNO=P2)源代码：select snamefrom studentwhere exists (select *from scwhere sc.SNo = student.sno and sc.CNo = 001)select snamefrom studentwhere not exists (select *from scwhere sc.SNo = student.sno and sc.CNo = 001)select snamefrom studentwhere not exists(select *from coursewhere CCredits = 4and not exists(select *from scwhere sc.SNo = student.SNo and sc.CNo = course.CNo)select count(*)总人数,max(distinct sal)最高工资,min(distinct sal)最少工资,avg(sal)平均工资from teacherselect snofrom sc group by sno having count(*)=2select snofrom sc group by sno having count(*)=3select sno,snamefrom studentwhere sno in(select snofrom sc group by sno having count(*) = (select count(*) from course) ) select sno from sc where CNo = 001 and Score all(select Score from sc where (sno = 00001 or sno = 00004) and CNo = 001 ) select sno from sc where CNo = 001 and Score any(select Score from sc where (sno = 00001 or sno = 00004) and CNo = 001 ) select sno from sc where CNo = 001 and Score (select AVG(Score) from sc) group by SNo select sno,AVG(score) from sc where CNo 001 group by SNo having MIN(score) =60 order by AVG(score) desc update Student set SAge = 2016 - YEAR(SBir) update sc set Score = Score + 1 where cno = (select cno from course where CName = 数据库) EXEC SP_HELPCONSTRAINT sc alter table sc drop constraint FK_sc_Student alter table sc add foreign key(sno) references student (sno) on delete cascade delete from Student where student.SNo = 00001 EXEC SP_HELPCONSTRAINT sc alter table sc drop constraint FK_sc_CNo_22AA2996 alter table sc add foreign key(CNo) references course(CNo) on delete cascade delete from course where CName = 数据库 delete from sc where SNo in(select SNo from Student where SSex =男) create table 学生表( sdept char(15) default 003, 学号 char(5) not null, 姓名 varchar(8) not null ) insert into 学生表(学号,姓名 )select sno, SName from Student; 实验结果：图见上面，由于后面一些更新删除操作使得表内容有很多变化且更新删除插入等操作不能直观看出来结果所以没截图，只截图代码部分，全都是自己做的实验体会： 这次实验题目比较多且有些有难度，用no exists表示全称量词，还有分组那部分尚且还用不太熟练，其中最麻烦的