资源描述
/* 实验四 SQL 数据查询 */- 1. 列出不及格记录的学生名单SELECT sname as 姓名FROM STUDENT s JOIN SC ON s.snum=sc.snum AND score60- 2. 列出选修了计算机系课程的学生名单SELECT DISTINCT sname AS 姓名FROM STUDENT s JOIN SC ON s.snum=sc.snum JOIN SECTIONS ON sections.secnum=SC.secnum JOIN COURSE ON COURSE.cnum=SECTIONS.cnum WHERE COURSE.dept=计算机系- 3. 检索选修了数据库技术课程的学生姓名和系别SELECT sname 姓名,s.dept 系别FROM STUDENT s JOIN SC ON s.snum=sc.snum JOIN SECTIONS ON sections.secnum=SC.secnum JOIN COURSE ON COURSE.cnum=SECTIONS.cnum AND cname=数据库技术-4. 选修了所有课程的学生名单SELECT sname 姓名FROM student sWHERE not exists(SELECT * FROM course where cnum not in(SELECT cnum FROM sc JOIN sections ON sc.secnum=sections.secnum WHERE sc.snum=s.snum)- 5. 检索每门课程都在80分以上的学生名单-答案一：SELECT sname 姓名FROM student WHERE snum not in (SELECT snum FROM sc WHERE score 80-6.检索获奖学金的学生名单（每门课程在80分以上，平均成绩在90分以上）。SELECT sname 姓名FROM student JOIN sc ON student.snum=sc.snumGROUP BY snameHAVING min(score)80 AND AVG(cast(score as real)90-7. 检索选修了“大学英语”的学生名单和成绩，并按成绩从高到低排列SELECT sname 姓名, score 成绩FROM student JOIN sc ON student.snum=sc.snum JOIN sections ON sc.secnum = sections.secnum JOIN course ON um=um AND cname=大学英语ORDER BY score DESC-8. 统计每门课程的选修人数，输出列名为课程号、人数-答案一：SELECT um 课程号, count(snum) 人数FROM sc JOIN sections ON sc.secnum =sections.secnum right JOIN course ON um=um GROUP BY all um-答案二：SELECT um 课程号, count(snum) 人数FROM sc,sections,courseWHERE sc.secnum =sections.secnum AND um=um GROUP BY all um-9. 查询选修了数据库技术、没有选修高等数学的学生姓名和系别SELECT sname 姓名, dept 系别FROM student WHERE snum in (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname=数据库技术) AND snum not in (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname=高等数学) -10. 检索使用高等教育出版社出版的教材的课程名SELECT cname 课程名FROM courseWHERE textbook like %高等教育出版社% -11. 统计所有课程的最高成绩、最低成绩和平均成绩。-答案一：SELECT cname 课程名, MAX(score) 最高成绩, MIN(score) 最低成绩, AVG(score) 平均成绩FROM sc JOIN sections ON sc.secnum=sections.secnum RIGHT JOIN course ON um=umGROUP BY ALL cname-答案二：SELECT cname 课程名, MAX(score) 最高成绩, MIN(score) 最低成绩, AVG(score) 平均成绩FROM sc , sections,courseWHERE sc.secnum=sections.secnum AND um=umGROUP BY ALL cname-12. 统计每门课程的选修人数及不及格人数-答案一：select ame as 课程名,选课数,不及格人数from(select cname ,count(st.snum) as 选课数 from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um group by ame) as co1 JOIN(select cname , count(*) as 不及格人数 from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um and score=60 then 0else 1 end) as 不及格人数 from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um group by ame-13. 查询土木工程系、交通工程系和城市规划系的学生学号和姓名 -答案一： SELECT dept 系别,snum 学号, sname 姓名 FROM student WHERE dept like 土木工程% or dept like 交通工程% or dept like 城市规划%-答案二： SELECT dept 系别,snum 学号, sname 姓名 FROM student WHERE dept in ( 土木工程,交通工程,城市规划)-以下三题用集合运算符完成查询操作： -14. 查询选修了数据库技术或选修了多媒体的学生学号 (SELECT snum 学号 FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um AND cname=数据库技术)UNION(SELECT snum 学号 FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um AND cname=多媒体技术)-15. 查询计算机系且年龄不大于19岁的学生信息SELECT * FROM student WHERE dept=计算机INTERSECTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()19-答案二：SELECT * FROM student WHERE dept=计算机EXCEPTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()=19
展开阅读全文