1 / 19

对于教学数据库的 3 个基本表 S(S#,SNAME,AGE,SEX) SC(S#,C#,GRADE) C(C#,CNAME,TEACHER) 试用 SQL 的查询语句表达下列查询

对于教学数据库的 3 个基本表 S(S#,SNAME,AGE,SEX) SC(S#,C#,GRADE) C(C#,CNAME,TEACHER) 试用 SQL 的查询语句表达下列查询 1 检索 LIU 老师所授课程的课程号和课程名 2 检索年龄大于 23 岁的男生的学号和姓名 3 检索学号为 S3 学生所学课程的课程名与任课教师名 4 检索至少选修 LIU 老师所授课程中一门课程的女学生姓名. 5 检索 WANG 同学不学的课程的课程号 6 检索至少选修两门课程的学生学号 7 检索全部学生都选修的课程的课程号与课程名. 8 统计有学生选修的课程门数

donald
Télécharger la présentation

对于教学数据库的 3 个基本表 S(S#,SNAME,AGE,SEX) SC(S#,C#,GRADE) C(C#,CNAME,TEACHER) 试用 SQL 的查询语句表达下列查询

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 对于教学数据库的3个基本表 • S(S#,SNAME,AGE,SEX) • SC(S#,C#,GRADE) • C(C#,CNAME,TEACHER) • 试用SQL的查询语句表达下列查询 • 1检索LIU老师所授课程的课程号和课程名 • 2检索年龄大于23岁的男生的学号和姓名 • 3检索学号为S3学生所学课程的课程名与任课教师名 • 4检索至少选修LIU老师所授课程中一门课程的女学生姓名

  2. 5检索WANG同学不学的课程的课程号 • 6检索至少选修两门课程的学生学号 • 7检索全部学生都选修的课程的课程号与课程名

  3. 8统计有学生选修的课程门数 • 9求选修C4课程的女学生的平均年龄 • 10求LIU老师所授课程的每门课程的平均成绩 • 11统计每门课程的学生选修人数（超过10人的课程才统计）。显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 • 12检索学号比wang同学大而年龄比他小的学生姓名 • 13在表SC中检索成绩为空值的学生学号和课程号 • 14检索姓名以L打头的所有学生的姓名和年龄 • 15求年龄大于女学生平均年龄的男学生姓名和年龄 • 16求年龄大于所有女学生年龄的男学生姓名和年龄

  4. 17. 往关系c中插入一个课程元组（’c8’,’VC++’,’BAO’) • 18. 检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表Faculty（Tname） • 19. 在SC中删除尚无成绩的选课元组 • 20。把选修LIU老师课程的女同学选课元组全部删除 • 21。把MATHS课不及格的成绩全改为60分

  5. 22。把低于所有课程总平均成绩的男同学成绩提高5%22。把低于所有课程总平均成绩的男同学成绩提高5% • 23.在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70分时提高4％

  6. 解答 • 1. select C#, CName, from c where teacher =‘liu’ • 2. select S#, Sname from s where age>23 and sex=‘M’ • 3. select cname, teacher from sc,c where sc.c#=c.c# and s#=‘s3’

  7. 4. Select sname from s, sc, c where s.s#=sc.s# and sc.c#=c.c# and sex=‘F’ and teacher=‘LIU’ • Or: select Sname from s where sex=‘F’ and s# in (select s# from sc where c# in (select c# from c where teacher =‘LIU’)) • Or : select sname form s where sex=‘F’ and exists (select * from sc where sc.s#=s.s# and exists (select * from c where c.c#=sc.c# and teacher =‘LIU’))

  8. 5.Select c# from c where not exists (select * from s, sc where s.s#=sc.s# and sc.c#=c.c# and sname=‘WANG’) • 6.Select distinct x.s# from sc as x, sc as y where x.s#=y.s# and x.c#<>y.c# • 7.Select c#, cname from c where not exists(select * from s where not exists (select * from sc where s#=s.s# and c#=c.c#))

  9. 8.select count(distinct c#) from sc • 9. select avg(age) from s, sc where s.s#=sc.s# and c#=‘c4’ and sex=‘F’ • 10. select c.c#, avg(grade) from sc, c where sc.c#=c.c# and teacher=‘LIU’ group by c.c# • 11.select c#, count(s#) from sc group by c# having count(*) >10 order by count(s#) DESC, c#

  10. 12.select sname from s where s#>all (select s# from s where sname=’WANG’) and age<all(select age from s where sname=‘WANG’) • 13.select s#, c# from sc where grade IS NULL • 14. select sname, age from s where sname like ‘L%’

  11. 15. select sname, age from s where sex=‘M’ and age>(select avg(age) from s where sex=‘F’) • 16.Select sname, age from s where sex=‘M’ and age>all (select age from s where sex=‘F’)

  12. 17.insert into c values(‘c8’,’vc++’,’BAO’) • 18.insert into faculty (tname) select distinct teacher from (select teacher,c.c#,avg(grade) from s,sc where sc.c#=c.c# group by teacher,c.c#) as result (teacher, c#,avg_grade) as x where 80<=all(select avg_grade from result as y where y.teacher=x.eacher)

  13. 19.delete from sc where grade is null • 20. delete from sc where s# in (select s#from s where sex=‘F’) and c# in(select c# from c where teacher=‘LIU’) • 21.update sc set grade=60 where grade<60 and c# in(select c# from c where cname=‘MATHS’) • 22.update sc set grade=grade*1.05 wehre s# in(select s# from s where sex=‘F’) and grade<(select avg(grade) from sc)

  14. 23. update sc set grade=grade*1.04 where c#=‘c4’ and grade>70; update sc sc set grade=grade*1.05 where c#=‘c4’ and grade<=70;

  15. 针对教学库基本表SC，建立一个视图： • Create view s_grade (s#, c_num,avg_grade) as select s#, count (c#), avg (grade) from sc group by s#; • 试判断下列查询和更新操作是否允许执行。允许的话写出转换到基本表sc上的相应操作。 • 1. select * from s_grade;

  16. 2. select s#,c_num from s_grade where avg_grade>80; • 3. select s#,avg_grade from s_grade where c_num>(select c_num from s_grade where s#=‘s4’); • 4.Update s_grade set c_num=c_num+2 where s#=‘s4’ • 5.Update delete from s_grade where c_num>4;

  17. 解答 • 1.允许。相应操作： Select s#, count(c#) as c_num, avg(grade) as avg_grade from sc group by s# • 2. 允许。 select s#,count(c#) as c_num from sc group by s# having avg(grade) >80

  18. 3. 允许 select s#,avg(grade) as avg_grade from sc group by s# having count(c#) >(select count (c#) from sc group by s# having s#=‘s4’) • 4.不允许 • 5.不允许

  19. 对于下面的关系R和S，求出下列各种连接操作的执行结果对于下面的关系R和S，求出下列各种连接操作的执行结果 • 1. 自然连接 • 2. 右外连接 • 3. 全外连接

More Related