sql--习题.pptVIP

1、求供应工程J1零件的供应商号码 select sno from spj where jno=j1 2、求没有使用天津单位生产的红色零件的工程号 select jno from j where jno not in (select jno from spj,s,p where spj.sno=s.sno and spj.pno=p.pno and city=天津 and color=红) 3、求供应工程J1零件P1的供应商号码 select sno from spj where jno=j1 and pno=p1 4、求供应工程J1零件为红色的供应商号码 select sno from spj where jno=j1 and pno in (select pno from p where color=红) 5、求至少用了供应商S1所供应的全部零件的工程号 select jno from spj a where sno=‘s1’ and not exists (select * from spj b where sno=s1 and not exists (select * from spj c where a.jno=c.jno and b.pno=c.pno)) 6、求供应商与工程在同一城市能供应的零件数量 select quantity from spj,s,j where spj.sno=s.sno and spj.jno=j.jno and s.city=j.city P39课后习题2.13 学生S(s#，sn，sd，sa) 社团T(t#，cn) 学生加入社团St(s#，t#，c) 1、求计算机系CS学生的学号、姓名 select s#,sn from s where sd=cs‘ 2、求参加了某一社团的学生学号、姓名 select s#,sn from s where s# in (select s# from st) 3、求参加了社团名称为“田径队”的学号和姓名 select s.s#,sn from s,st,t where s.s#=st.s# and st.t#=t.t# and cn=田径队‘ 4、求未参加任何社团的学生姓名 select sn from s where s# not in (select s# from st) 5、求计算机系的学生以及年龄小于18岁的学生 select * from s where sd=cs union select * from s where sa18 P83课后习题3.10 学生Student(sno，sname，ssex，sage，sdept) 课程Course(cno，cname，cdept，ccredit，tname) 选课Sc(sno，cno，grade) 1、建立三个表 create table student(sno char(5) not null,sname varchar(10) not null,ssex char(2) not null,sage tinyint null,sdept varchar(30) null) create table course(cno char(2) not null,cname char(20) not null,cdept varchar(30) null,ccredit tinyint not null,tname char(10) not null) create table sc(sno char(5) not null,cno char(2) not null,grade decimal(4,1) null) 2、检索年龄大于23岁的男学生的学号和姓名 select sno,sname from student where sage23 and ssex=男 3、检索至少选修“刘力”教师所授课程中一门课程的女学生姓名 select sname from student where ssex=女 and sno in (select sno from sc where cno in (select cno from course where tname=刘力)) 4、检索至少选修两门课程的学生学号 select sno from sc group by sno having count(*)=2 5、检索选修课程包含“刘力”老师所授课程的学生学号 select sno from student s where not exists (select * from course c where tname=刘力 and