实验三 SQL 复杂查询

目的：

掌握复杂数据查询操作。

内容：

SQL语言各种连接查询、嵌套查询的使用

步骤：

1、 以实验一的XSGL数据库为基础，对各表中的数据进行不同条件的连接查询和嵌套查询：

(1)  查询每个学生及其选课情况；

  select student.*,sc.cno from student,sc where student.sno=sc.sno;

(2)  查询既选修了2号课程又选修了3号课程的学生姓名、学号；

select student.sname,student.sno from student,sc where student.sno=sc.sno and (sc.cno=2 or sc.cno=3);

(3)  选修了课程名为“数据库”的学生姓名和年龄；

select student.sname,student.sage from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname='数据库';

(4)  查询其他系比IS系任一学生年龄小的学生名单；

select sname,sage from student where sage<any(select sage from student where sdept='IS') and sdept!='IS';

(5)  查询其他系中比IS系所有学生年龄都小的学生名单；

select sname,sage from student where sage<all(select sage from student where sdept='IS') and sdept<>'IS';

(6)  查询选修了全部课程的学生姓名；

SELECT Sname FROM student

             WHERE NOT EXISTS (

                                   SELECT * FROM course WHERE NOT EXISTS (

                                   SELECT * FROM SC WHERE Sno = student.Sno AND Cno = course.Cno ) );

(7)  查询CS系学生及所有男学生；

 select * from student where sdept='CS' union select * from student where ssex='男';

(8)  查询选修课程1的学生集合和选修2号课程学生集合的差集；

select student.* from student,sc where student.sno=sc.sno and sc.cno='1' except select student.* from student,sc where student.sno=sc.sno and sc.cno='2';

(9)  查询李丽同学不学的课程的课程号；

SELECT CNO FROM COURSE WHERE CNO NOT IN (

    SELECT CNO

    FROM SC,STUDENT

    WHERE STUDENT.SNO = SC.SNO

    AND STUDENT.SNAME ='李丽');

(10) 查询选修了3号课程的学生平均年龄；

select avg(student.sage) from student,sc where student.sno=sc.sno and sc.cno='3';

(11) 求每门课程学生的平均成绩；

select sc.cno,avg(sc.grade) from sc group by cno;

(12) 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列；

SELECT sc.cno,COUNT(sc.cno) as num FROM sc

GROUP BY sc.cno HAVING num>3

ORDER BY num DESC,sc.cno ASC;

(13) 查询学号比刘晨大，而年龄比他小的学生姓名；

select sname from student where sno>(select sno from student where sname='刘晨') and sage < (select sage from student where sname='刘晨');

(14) 求年龄大于所有女同学年龄的男同学姓名和年龄。

select sname,sage from student where sage>all(select sage from student where ssex='女');

2、以实验一的MySPJ数据库为基础，用SQL语句完成如下查询：

（1）找出至少用了供应商S1所供应的全部零件的工程号JNO；

 SELECT DISTINCT JNO FROM J WHERE NOT EXISTS(SELECT * FROM SPJ SPJ WHERE SPJZ.SNO = 'S1' AND NOT EXISTS( SELECT * FROM SPJ SPJZ WHERE J.JNO = SPJZ.JNO AND SPJZ.PNO = SPJY.PNO AND SPJZ.SNO = 'S1') );

（2）找出所有供应商的名称和所在城市；

SELECT sname,city from s;

（3）找出所有零件的名称、颜色、重量；

select pname,color,weight from p;

（4）找出使用供应商S1所供应零件的工程号码；

select spj.jno from spj,s where spj.sno=s.sno and spj.sno='S1';

（5）找出工程项目J2使用的各种零件的名称及其数量；

select pno,count(pno) from spj where jno='J2' group by pno;

（6）找出上海厂商供应的所有零件号码

select spj.pno from spj,s where spj.sno=s.sno and s.city='上海';

（7）找出使用上海产的零件的工程名称；

select jname

from spj,s,j

WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO;

（8）找出没有使用天津产的零件的工程号码。

select jno from spj where jno not in(select DISTINCT jno

from spj,s where s.sno=spj.sno and s.city='天津');