1.查询年龄比“刘晨”大的学生姓名
select sname
from student
where sage>
(select sage
from student
where sname = '刘晨'
)
2.参与选课的男生的姓名
select sname
from student
where sno in
(select student.sno
from sc,student
where sc.sno=student.sno and student.ssex='男'
);
3.没有参与选课的男生的学生姓名
select sname
from student
where sno not in
(select student.sno
from sc,student
where sc.sno=student.sno and student.ssex='男'
);
4.没有选修“1”号课程的学生姓名
select sname
from student
where sno not in
(select student.sno
from sc,student
where sc.sno=student.sno and sc.cno='1');
5.没有使用“P1”零件的工程名称
select jno,jname
from j
where jno not in
(select j.jno
from j,spj
where j.jno=spj.jno and pno='p1');
6.没有使用“S1”供应商零件的工程名称
select jno,jname
from j
where jno not in
(select j.jno
from spj,j
where j.jno=spj.jno and spj.sno='s1')
7.没有人选修的课程名称
select cname
from Course
where cname not in
(select cname
from Course,sc
where sc.cno=Course.cno)
使用EXISTS嵌套查询实现下列查询:
与“刘晨”不是一个系的学生
select *
from student
where sdept not in
(select sdept
from student
where sname='刘晨');
没有选择“1”号课的学生
select *
from student
where not exists
(select *
from sc
where sc.sno=student.sno and cno='1'
);
所有学生都没有选修的课程
select *
from course
where not exists
(select *
from sc
where course.cno=sc.cno );
查询‘1’号学生都没有选修的课程
select *
from course
where not exists
(select *
from sc
where course.cno=sc.cno and sno='200215121');
查询没有使用p1零件的工程名称
select *
from j
where not exists
(select *
from spj
where j.jno=spj.jno and pno='p1');
查询J1工程没有使用的零件名称
select *
from s
where not exists
(select *
from spj
where spj.sno=s.sno and jno='j1');
查询使用了红色零件的工程编号和名称
select *
from j
where exists
(select *
from spj,p
where spj.jno=j.jno and p.pno=spj.pno and p.color='红');
查询使用了天津供应商供应零件的工程编号和名称
select *
from j
where exists
(select *
from s,spj
where j.jno=spj.jno and s.sno=spj.sno and s.city='天津');
查询选修“数据库”课程学生的学号
select *
from sc
where exists
(select *
from course
where course.cno=sc.cno and course.cname='数据库');
查询选修“数据库”课程学生的姓名
select *
from student
where exists
(select *
from course,sc
where course.cno=sc.cno and student.sno=sc.sno and course.cname='数据库');
查询各学生学号、姓名和选课门数
查询各课程编号、名称和选课学生数