SQL查询练习题省公共课一等奖全国赛课获奖课件.pptx

SQL查询练习题第1页设教学数据库中有三个关系：设教学数据库中有三个关系：学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)下面用下面用SQLSQL语句实现下面每个查询：语句实现下面每个查询：（1 1）查询学习课程号为）查询学习课程号为C2C2学生学号与成绩。学生学号与成绩。SELECT S#,GRADE SELECT S#,GRADE FROM SC FROM SC WHERE C#=WHERE C#=C2C2（2 2）查询学习课程号为）查询学习课程号为C2C2学生学号与姓名。学生学号与姓名。SELECT S.S#,SNAMESELECT S.S#,SNAME FROM S,SC FROM S,SC WHERE S.S#=SC.S#AND C#=WHERE S.S#=SC.S#AND C#=C2C2或或 SELECT S#,SNAME FROM S WHERE S#INSELECT S#,SNAME FROM S WHERE S#IN (SELECT S#FROM SC (SELECT S#FROM SC WHERE C#=WHERE C#=C2C2)第2页（3 3）查询选修课程名为）查询选修课程名为MATHSMATHS学生学号与姓名。学生学号与姓名。SELECT S.S#,SNAMESELECT S.S#,SNAME FROM S,SC,C FROM S,SC,C WHERE S.S#=SC.S#AND SC.C#=C.C#WHERE S.S#=SC.S#AND SC.C#=C.C#AND CNAME=AND CNAME=MATHSMATHS 或或 SELECT S#,SNAME SELECT S#,SNAME FROM S FROM S WHERE S#IN WHERE S#IN (SELECT S#FROM SC (SELECT S#FROM SC WHERE C#IN WHERE C#IN (SELECT C#FROM C (SELECT C#FROM C WHERE CNMAE=WHERE CNMAE=MATHSMATHS)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第3页（4 4）查询选修课程号为）查询选修课程号为C2C2或或C4C4学生学号。学生学号。SELECT S#SELECT S#FROM SC FROM SC WHERE C#=WHERE C#=C2C2 OR C#=OR C#=C4C4设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第4页（5 5）查询选修了）查询选修了2 2号课程且成绩在号课程且成绩在9090分以上学生学号和性名。分以上学生学号和性名。SELECT S.S#,SNAMESELECT S.S#,SNAME FROM S,SC FROM S,SC WHERE S.S#=SC.S#AND SC.C#=2AND SC.GRADE90 WHERE S.S#=SC.S#AND SC.C#=2AND SC.GRADE90设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第5页（6 6）查询不学）查询不学C2C2课学生姓名与年纪。课学生姓名与年纪。SELECT SNAME,AGESELECT SNAME,AGE FROM S FROM S WHERE S#NOT IN(SELECT S#WHERE S#NOT IN(SELECT S#FROM SC FROM SC WHERE C#=WHERE C#=C2C2)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第6页（9 9）统计有学生选修课程门数。）统计有学生选修课程门数。SELECT COUNT(DISTINCT C#)SELECT COUNT(DISTINCT C#)FROM SC FROM SC设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第7页（1010）求选修）求选修C4C4课程女学生平均年纪。课程女学生平均年纪。SELECT AVG(AGE)SELECT AVG(AGE)FROM S FROM S WHERE SEX=WHERE SEX=女女 AND AND S#IN S#IN (SELECT S#(SELECT S#FROM SC FROM SC WHERE C#=WHERE C#=C4C4)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第8页（1111）求刘老师所讲课程每门课程平均成绩。）求刘老师所讲课程每门课程平均成绩。SELCT C#,AVG(GRADE)SELCT C#,AVG(GRADE)FROM SC FROM SC WHERE C#IN(SELECT C#FROM C WHERE WHERE C#IN(SELECT C#FROM C WHERE TEACHER=TEACHER=LIULIU)GROUP BY C#GROUP BY C#或或 SELECT C.C#,AVG(GRADE)SELECT C.C#,AVG(GRADE)FROM C,SC FROM C,SC WHERE C.C#=SC.C#AND WHERE C.C#=SC.C#AND TEACHER=TEACHER=LIULIU GROUP BY C.C#GROUP BY C.C#设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第9页（1212）统计每门课程学生选修人数（超出）统计每门课程学生选修人数（超出1010人课程才人课程才统计）。要求显示课程号和人数，查询结果按人数统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。降序排列，若人数相同，按课程号升序排列。SELECT C#SELECT C#，COUNT(S#)COUNT(S#)FROM SC FROM SC GROUP BY C#HAVING COUNT(*)10 GROUP BY C#HAVING COUNT(*)10 ORDER BY COUNT(S#)DESC,C#ORDER BY COUNT(S#)DESC,C#设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第10页(13)(13)在在SCSC表中检索成绩为空值学生学号和课程号。表中检索成绩为空值学生学号和课程号。SELECT S#,C#SELECT S#,C#FROM SC FROM SC WHERE GRADE IS NULL WHERE GRADE IS NULL设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第11页(14)(14)检索姓名以检索姓名以L L打头全部学生姓名和年纪。打头全部学生姓名和年纪。SELECT SNAME,AGESELECT SNAME,AGE FROM S FROM S WHERE SNAME LIKE WHERE SNAME LIKEL%L%设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第12页(15)(15)求年纪大于女同学平均年纪男学生姓名和年纪。求年纪大于女同学平均年纪男学生姓名和年纪。SELECT SNAME,AGESELECT SNAME,AGE FROM S FROM S WHERE SEX=WHERE SEX=男男 AND AGE(SELECT AVG(AGE)AND AGE(SELECT AVG(AGE)FROM S FROM S WHERE SEX=WHERE SEX=女女)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第13页（1717）求年纪大于全部女同年纪男学生姓名和年纪。）求年纪大于全部女同年纪男学生姓名和年纪。SELECT SNAME,AGESELECT SNAME,AGE FROM S FROM S WHERE SEX=WHERE SEX=男男 AND AGEALL(SELECT AGE AND AGEALL(SELECT AGE FROM S FROM S WHERE SEX=WHERE SEX=女女)或或SELECT SNAME,AGESELECT SNAME,AGE FROM S FROM S WHERE SEX=WHERE SEX=男男 AND AGEAND AGE (SELECT MAX(AGE)(SELECT MAX(AGE)FROM S FROM S WHERE SEX=WHERE SEX=女女)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第14页（1 1）往关系）往关系C C中插入一个课程元组（中插入一个课程元组（C8C8，VC+VC+，BAOBAO）INSERT INSERT INTO INTO C C VALUES(VALUES(C8C8,VC+VC+,BAOBAO)（2 2）在）在SCSC中删除尚无成绩选课元组。中删除尚无成绩选课元组。DELETEDELETE FROM SC FROM SC WHERE GRADE IS NULL WHERE GRADE IS NULL（3 3）把选修）把选修LIULIU老师课程女同学选课元组全部删去。老师课程女同学选课元组全部删去。DELETE DELETE FROM SC FROM SC WHERE S#IN(SELECT S#FROM S WHERE SEX=WHERE S#IN(SELECT S#FROM S WHERE SEX=F F)AND C#IN(SELECT C#FROM C WHERE AND C#IN(SELECT C#FROM C WHERE TEACHER=TEACHER=LIULIU)u设教学数据库中有三个关系：设教学数据库中有三个关系：学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第15页(4)(4)把把MATHSMATHS课不及格成绩全改为课不及格成绩全改为60.60.UPDATE SC UPDATE SC SET GRADE=60 SET GRADE=60 WHERE GRADE60 AND WHERE GRADE60 AND C#IN(SELECT C#C#IN(SELECT C#FROM C FROM C WHERE CNAME=WHERE CNAME=MATHSMATHS)(5)(5)把低于全部课程总平均成绩女同学成绩提升把低于全部课程总平均成绩女同学成绩提升5%5%。UPDATE SCUPDATE SC SET GRADE=GRADE*1.05 SET GRADE=GRADE*1.05 WHERE S#IN(SELECT S#FROM S WHERE SEX=WHERE S#IN(SELECT S#FROM S WHERE SEX=F F)AND GRADE(SELECT AVG(GRADE)FROM SC)AND GRADE(SELECT AVG(GRADE)FROM SC)设教学数据库中有三个关系：设教学数据库中有三个关系：u学生关系学生关系 S S（S#,SNAME,AGE,SEXS#,SNAME,AGE,SEX）u选课关系选课关系 SC(S#,C#,GRADE)SC(S#,C#,GRADE)u课程关系课程关系 C(C#,CNAME,TEACHER)C(C#,CNAME,TEACHER)第16页