Sql语句综合练习.ppt

2010-10-11,SQL语句综合练习,信息工程学院计算机专业教研室,2010-10-10,2010-10-11,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,select count(distinct Sno)from SCourse;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,1、求选了课程的学生人数,2010-10-11,select Sno,avg(score)from SCoursegroup by Sno having avg(score)60;,2、查询平均成绩大于60分的同学的学号和平均成绩,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,select Sno,count(Cno),sum(score)from SCourse group by Sno;,3、查询选课同学的学号、选课数、总成绩,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,select count(ssex)男生人数 from St group by ssex having ssex=男;select count(ssex)女生人数 from St group by ssex having ssex=女;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,4、查询男生、女生人数,select count(sno)from Stwhere Ssex=男;,2010-10-11,select Sname,count(*)from St group by Sname having count(*)1;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,5、查询同名学生名单，并统计同名人数,2010-10-11,select Cno,avg(score)from SCourse group by Cno order by avg(score),Cno desc;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列,2010-10-11,select Sno,avg(score)from SCourse where Sno in(select Sno from SCourse where score 2)group by Sno;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,7、查询两门以上不及格课程的同学的学号及其平均成绩,2010-10-11,select Sno,avg(score)from SCourse where score 2;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,7+、查询两门以上不及格课程的同学的学号及其平均成绩,2010-10-11,select count(Tname)from Teacherwhere Tname like 李%;,8、查询姓“李”的老师的个数,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,select distinct A.Sno,A.Cno,A.score from SCourse A,SCourse B where A.Score=B.Score and A.Cno!=B.Cno and A.Sno=B.Sno;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,9、查询同一个学生成绩相同、课程不同的课程号、学生成绩和该学生的学号,2010-10-11,select St.Sname,score from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and o=o and C.Tno=Teacher.Tnoand Teacher.Tname=张山 and score=(select max(score)from SCourse,C,Teacher where o=o and C.Tno=Teacher.Tnoand Teacher.Tname=张山);,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,10、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩,2010-10-11,select St.Sname,score from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and o=o and C.Tno=Teacher.Tnoand Teacher.Tname=张山 and score=(select max(score)from SCourse where cno in(select cno from C where Tno in(select Tno from Teacher where Tname=张山);,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,10、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩,2010-10-11,select St.Sname,max(score)from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and o=o and C.Tno=Teacher.Tnoand Teacher.Tname=张山;,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,10+、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩,2010-10-11,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,select St.Sno,St.Sname from St where Sno not in(select distinct(SCourse.Sno)from SCourse,C,TeacherWhere SCourse.Cno=C.Cno and Teacher.Tno=C.Tno and Teacher.Tname=张山);（解二、解三在备注）,11、查询没选修“张山”老师课的同学的学号、姓名,2010-10-11,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,select Sno from stwhere Sno not in(select Snofrom SCoursegroup by Sno having count(Cno)=(select count(Cno)from C);,12、查询没有选全所有课的同学的学号,2010-10-11,select Snofrom SCoursegroup by Sno having count(Cno)(select count(Cno)from C);,12+、查询没有学全所有课的同学的学号,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,update SCourse set score=(select avg(score)from SCoursewhere cno in(select cno from c where tno in(select tno from teacher where Tname=张山)Where cno in(select cno from c where tno in(select tno from teacher where Tname=张山);,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,13、把SCourse表中“张山”老师教的课的成绩都更改为“张山”老师教的课程的平均成绩,2010-10-11,Insert into SCourse(select Sno,0002,(Select avg(score)from SCourse where Cno=0002)from St where Sno not in(Select Sno from SCourse where Cno=0002);,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,14、向SCourse表中插入一些记录，这些记录要求符合以下条件：没有上过编号0002课程的同学学号、0002号课、0002号课的平均成绩；,2010-10-11,delete from Scourse where Cno in(select Cno from C where Tno in(select Tno from Teacher where Tname=张山);,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,16、删除学习“张山”老师课的SCourse表记录；,2010-10-11,17、查询和“0405102303”号的同学学习的课程完全相同的学生学号,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,2010-10-11,(SELECT DISTINCT Sno FROM SCourse SCX WHERE NOT EXISTS(SELECT*FROM SCourse SCY WHERE SCY.Sno=0405102303 AND NOT EXISTS(SELECT*FROM SCourse SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)INTERSECT(SELECT SNOFROM SCourseGROUP BY SNOHAVING COUNT(*)=(SELECT COUNT(*)FROM SCourse WHERE SNO=0405102303);,2010-10-11,St(Sno,Sname,Sage,Ssex)学生表 C(Cno,Cname,Tno)课程表 SCourse(Sno,Cno,score)成绩表 Teacher(Tno,Tname)教师表,select Sno from SCourse where Cno in(select Cno from SCourse where Sno=0405102303)intersect(select snofrom scoursegroup by Snohaving count(*)=(select count(*)from SCourse where Sno=0405102303);错:该查询包括课数相同但种类不同的学生.,17+、查询和“0405102303”号的同学学习的课程完全相同的学生学号,2010-10-11,休息一会儿。,2010-10-11,供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；要求定义实体完整性；供应商姓名不能为空，城市只能取大连、沈阳、西安。零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEI）组成；工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。要求定义实体完整性；参照完整性。,2010-10-11,查出各不同供应商代号及他们分别提供的所有零件的数量。查出大连供应商不提供任何零件的工程代号。查出提供零件的数量大于400的供应商代号及他们所提供的零件具体数量。查出供给关系不在同一个城市的供、需双方地址。求至少用了供应商 Sl 所供应的全部零件的工程号 JNO;,2010-10-11,授权用户刘星对供应情况表SPJ有修改表的结构的权力、有SELECT权力、对供应数量（QTY）字段具有更新权力。并具有给其他用户授权的权力。把全部红色零件的颜色改为蓝色。从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录。用户周军具有查询 供应商号、供货总数量的权力，他不能查看每个供应商对每个工程的供货数量。,2010-10-11,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)1供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；要求定义实体完整性；供应商姓名不能为空，城市只能取大连、沈阳、西安。,CREATE TABLE S(SNO CHAR(4)PRIMARY KEY,SNAME CHAR(10)NOT NULL,STATUS CHAR(2),CITY CHAR(10),CONSTRAINT C1 CHECK(CITY IN(大连,沈阳,西安);,2010-10-11,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)2零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEI）组成；要求定义实体完整性；零件名不能为空。,CREATE TABLE P(PNO CHAR(4)PRIMARY KEY,PNAME CHAR(10)NOT NULL,COLOR CHAR(4),WEIGHT INT);,2010-10-11,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)3工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；要求定义实体完整性；工程项目名不能为空。,CREATE TABLE J(JNO CHAR(4)PRIMARY KEY,JNAME CHAR(10)NOT NULL,CITY CHAR(10);,2010-10-11,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)4.供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。要求定义实体完整性；参照完整性。,CREATE TABLE SPJ(SNO CHAR(4),PNO CHAR(4),JNO CHAR(4),QTY CHAR(10),PRIMARY KEY(SNO,PNO,JNO),FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(PNO)REFERENCES P(PNO),FOREIGN KEY(JNO)REFERENCES J(JNO);,2010-10-11,5.查出各不同供应商代号及他们分别提供的所有零件的数量。,select SNO,sum(QTY)from SPJgroup by SNO;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,6.查出大连供应商不提供任何零件的工程代号。,select JNO from J where JNO not in(select JNO from SPJ where sno in(select sno from S where CITY=大连);,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,7.查出提供零件的数量大于400的供应商代号及他们所提供的零件具体数量。,select SNO,sum(QTY)from SPJgroup by SNOhaving sum(QTY)400;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,8.查出供给关系不在同一个城市的供、需双方地址。,select distinct S.CITY,J.CITYfrom S,J,SPJwhere S.SNO=SPJ.SNO and J.JNO=SPJ.JNO and S.CITY!=J.CITY;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,9.求至少用了供应商 000l 所供应的全部零件的工程号,SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS(SELECT*FROM SPJ SPJY WHERE SNO=0001 AND NOT EXISTS(SELECT*FROM SPJ SPJZ WHERE SPJZ.PNO=SPJY.PNO AND SPJZ.JNO=SPJX.JNO);,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,10.授权用户刘星对供应情况表SPJ有修改表的结构的权力、有SELECT权力、对供应数量（QTY）字段具有更新权力。并具有给其他用户授权的权力,GRANT ALTER,SELECT,UPDATE(QTY)ON SPJ TO刘星WITH GRANT OPTION;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,11.把全部红色零件的颜色改为蓝色。,UPDATE P SET COLOR=蓝 WHERE COLOR=红;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,12.从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录。,DELETE FROM SPJ WHERE SNO=S2;DELETE FROM S WHERE SNO=S2;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,13.用户周军具有查询 供应商号、供货最大数量的权力，他不能查看每个供应商对每个工程的供货数量。,CREATE VIEW SPJ_M(SNO,M_QTY)AS SELECT SNO,MAX(QTY)FROM SPJ GROUP BY SNO;GRANT SELECT ON SPJ_M TO 周军;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,休息一会儿。,