数据库 习题2(关系代数,SQL).ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,关系代数及,SQL,查询实例练习,SNO,SNAME,AGE,SEX,1,李强,23,男,2,刘丽,22,女,5,张友,22,男,CNO(,课号,),CNAME,（,课名）,TEACHER,（,教师）,k1,C,语言,王华,k5,数据库原理,程军,k8,编译原理,程军,SNO,CNO,GRADE,1,K1,83,2,K1,85,5,K1,92,2,K5,90,5,K5,84,5,K8,80,SC,：,学生选课成绩表,S,：,学生信息表,C,：,课程信息表,(1),检索“程军”老师所授课程的课程号,CNO,和课程名,CNAME,。,（,2,）检索年龄大于,21,的男学生学号,SNO,和姓名,SNAME,。,（,3,）检索至少选修“程军”老师所授全部课程的学生姓名,SNAME,。,（,4,）检索“李强”同学不学课程的课程号。,(5),检索至少选修两门课程的学生学号。,(6),检索全部学生都选修的课程的课程号和课程名。,(7),检索选修课程包含“程军”老师所授课程之一的学生学号。,(8),检索选修课程号为,k1,和,k5,的学生学号。,(10),检索选修课程包含学号为,2,的学生所修课程的学生学号。,(9),检索选修全部课程的学生姓名；,(11),检索选修课程名为“,C,语言”的学生学号和姓名。,SQL,查询实例练习,(1),检索“程军”老师所授课程的课程号,CNO,和课程名,CNAME,。,SELECT CNO,CNAME,FROM C,WHERE TEACHER=,程军,;,（,2,）检索年龄大于,21,的男学生学号,SNO,和姓名,SNAME,。,SELECT SNO,SNAME,FROM S,WHERE AGE21 AND SEX=,男,;,（,3,）检索至少选修“程军”老师所授全部课程的学生姓名,SNAME,。,SELECT SNAME,FROM S,WHERE NOT EXISTS,(SELECT*,FROM C,WHERE TEACHER=,程军,AND NOT EXISTSSELECT*,FROM SC,WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO);,（,4,）检索“李强”同学不学课程的课程号。,SELECT CNO,FROM C,WHERE CNO NOT IN,(SELECT CNO,FROM SC,S,WHERE SC.SNO=S.SNO AND S.SNAME=,李强,);,SELECT CNO,FROM C,EXCEPT,SELECT CNO,FROM SC,S,WHERE SC.SNO=S.SNO AND S.SNAME=,李强,;,或,(5),检索至少选修两门课程的学生学号。,SELECT SNO,FROM SC ASC B,WHERE A.SNO=B.SNO AND A.CNOB.CNO;,(6),检索全部学生都选修的课程的课程号和课程名。,SELECT CNO,CNAME,FROM C,WHERE NOT EXISTS,(SELECT*,FROM S,WHERE NOT EXISTS,(SELECT*,FROM SC,WHERE SC.CNO=C.CNO AND SC.SNO=S.SNO);,(7),检索选修课程包含“程军”老师所授课程之一的学生学号。,SELECT SNO,FROM C,SC,WHERE C.CNO=SC.CNO AND C.TEACHER=,程军,;,(8),检索选修课程号为,k1,和,k5,的学生学号。,SELECT SNO,FROM SC,WHERE SC.CNO=k1,INTERSECT,SELECT SNO,FROM SC,WHERE SC.CNO=k5;,或：,SELECT SNO,FROM SC A,SC B,WHERE A.SNO=B.SNO AND A.CNO=k1 AND B.CNO=k5;,(9),检索选修全部课程的学生姓名；,SELECT SNAME,FROM S,WHERE NOT EXISTS,(SELECT*,FROM C,WHERE NOT EXISTS,(SELECT*,FROM SC,WHERE SC.SNO=S.SNO AND SCCNO=C.CNO);,SELECT SNO,FROM S,WHERE NOT EXISTS,(SELECT*,FROM SC A,WHERE SC.SNO=2 AND NOT EXISTS,(SELECT*,FROM SC B,B.SNO=S.SNO AND B.CNO=A.CNO);,(10),检索选修课程包含学号为,2,的学生所修课程的学生学号。,(11)SELECT SNO,SNAME,FROM S,SC,C,WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CNAME=C,语言,;,(11),检索选修课程名为“,C,语言”的学生学号和姓名。,SQL,练习二,NO,NAME,SEX,BIRTHDAY,CLASS,108,曾华,男,09/01/77,95033,105,匡明,男,10/02/75,95031,107,王丽,女,01/23/76,95033,101,李军,男,02/20/76,95033,109,王芳,女,02/10/75,95031,103,陆君,男,06/03/74,95031,NO,NAME,SEX,BIRTHDAY,PROF,DEPART,804,李诚,男,12/02/58,副教授,计算机系,856,张旭,男,03/12/69,讲师,电子工程系,825,王萍,女,05/05/72,助教,计算机系,831,刘冰,女,08/15/77,助教,电子工程系,Student:,学生信息表,Teacher:,教师信息表,CNO,CNAME,TNO,3-105,计算机导论,825,3-245,操作系统,804,6-166,数据字电路,856,9-888,高等数据学,100,6-187,数据库系统,804,NO,CNO,DEGREE,103,3-245,86,105,3-245,75,109,3-245,68,103,3-105,92,105,3-105,88,109,3-105,76,101,3-105,64,107,3-105,91,108,3-105,78,101,6-166,85,107,6-166,79,108,6-166,81,Course:,课程信息表,Score:,成绩信息表,（,1,）、在屏幕上列出,student,表中所有记录的,name,sex,和,class,列；,（,2,）、屏幕显示教师所有的单位（即不重复的,depart,列）；,（,3,）、屏幕显示,score,表中成绩在,60,到,80,分之间的所有记录；,（,4,）、在屏幕上显示,student,中”,950311”,班或性别为”女”的同学记录；,（,5,）、以,class,降序在屏幕上显示,student,表的所有记录；,（,6,）、在屏幕上显示“,95031”,班的学生人数；,（,7,）、显示所有学生的,name,cno,和,degree,列；,（,8,）、显示所有学生的,name,cname,和,degree,列；,（,9,）、列出所有未讲课的教师的,name,和,depart;,（,10,）、,删除,student,中学号为”,999”,的学生记录；,（,11,）、将,student,中学号为”,990”,的学生的班号改为“,95031”,；,用,SQL,完成以下操作：,(1)select name,sex,class,from student,；,(2)select distinct depart,from teacher,；,(3)select*,from score,where degree between 60 and 80,；,(4)select*,from student,where class=95031 or sex=,女；,(5)select*,from student,order by class,desc,；,(6)select count(*)as,学生人数,from student,where class=95031,(7)select,name,cno,degree,from student,course,score,where student.no=score.no,(8)select,name,cname,degree,from student,course,score,where student.no=score.no and,o=o,(9)select name,depart,from teacher,where no not in,(select distinct,tno,from course),(10)delete from student where no=999,(11)update student set class=95031 where no=990,