数据库系统第8讲.pptVIP

An Introduction to Database System ⑵求供应工程J1零件的供应商号SNO 关系代数 ∏SNO(?JNO=‘J1’(SPJ)) SQL: SELECT distinct SNO FROM SPJ WHERE JNO=j1 ⑵求供应工程J1零件P1的供应商号SNO。 关系代数 ∏SNO(?JNO=‘J1’∧PNO=‘P1’(SPJ)) SQL: SELECT distinct sno from spj where jno=j1 and pno=p1 ⑶求供应工程J1红色零件的供应商号SNO。 关系代数 ∏SNO(∏SNO,PNO (?JNO=‘J1’(SPJ))??∏PNO(?COLOR=‘红’(P))) SQL SELECT distinct sno from spj,p where spj.jno=j1 and p.color=红 and spj.pno=p.pno ⑷求没有使用天津供应商生产的红色零件的工程号JNO。 关系代数∏JNO(J)－ ∏JNO(∏SNO(?CITY=‘天津’(S))??∏SNO,PNO,JNO(SPJ) ??∏PNO(?COLOR=‘红’(P))) SQL: select jno from j where not exists(select * from spj,s,p where spj.pno=p.pno and spj.sno=s.sno and p.color=红 and s.city=天津 and spj.jno=j.jno) 或者 select jno from j where jno not in(select jno from spj,s,p where spj.pno=p.pno and spj.sno=s.sno and p.color=红 and s.city=天津) select jno from spj where jno not in(select jno from spj,s,p where spj.pno=p.pno and spj.sno=s.sno and p.color=红 and s.city=天津) select distinct jno from s,p,spj where spj.pno=p.pno and spj.sno=s.sno and p.color‘红’ and s.city‘天津‘ 是否正确？ select * from s,p,spj where spj.pno=p.pno and spj.sno=s.sno ⑸求至少用了S1供应商所供应的全部零件的工程号JNO。 答：关系代数 ∏JNO,PNO(SPJ)÷∏PNO(?SNO=‘S1’(SPJ)) SQL： select distinct jno from spj spjx where not exists(select * from spj spjy where spjy.sno=s1 and not exists(select * from spj spjz where spjz.pno=spjy.pno and spjz.jno=spjx.jno)) 2、用sql完成以下各项操作： 7）找出没有使用天津产的零件的工程号码； select jno from j where not exists(select * from spj,s where spj.sno=s.sno and s.city=天津 and spj.jno=j.jno) select distinct jno from s,spj where s.sno=spj.sno and city ’天津 Student(SNO,Name,SEX,BIRTHDAY,CLASS); Teacher(Tno,Name,Sex,Birthday,Prof,DEPART); Course(CNO,CNAME,Tno); SCORE(SNO,CNO,DEGREE); （1）查询最低分大于70分、最高分小于90分的学号； Select sno from score group by sno having min(degree)70 and max(degree)90 Student(SNO,Name,SEX,BIRTHDAY,CLASS); Teacher(Tno,Name,Sex,Birthday,Prof,DEPART); Course(CNO,CNAME,Tno); SCORE(SNO,CNO,DEGREE); （2）查询学生的姓名、课程号和成绩 SELECT stud