关于查询的作业题

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='数据库');


查询各学生学号、姓名和选课门数








查询各课程编号、名称和选课学生数