数据库系统概论sql查询习题课.pptVIP

求没有使用天津供应商生产的红色零件的工程号JNO select JNO from J where not exists (select * from SPJ,S,P where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and S.CITY=天津 and P.COLOR=红) select JNO from J Except select SPJ.JNO from SPJ,S,P where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and S.CITY=天津 and P.COLOR=红 求至少使用了供应商S1所供应的全部零件的工程号JNO select distinct JNO from SPJ SPJZ where not exists (select * from SPJ SPJX where SNO=S1 and not exists (select * from SPJ SPJY where SPJY.PNO=SPJX.PNO and SPJY.JNO=SPJZ.JNO and SPJY.SNO=SPJX.SNO ) ); 求供应商S1提供给工程J1的零件供应数量的总和 select SUM(QTY) from SPJ where SNO=S1 and JNO=J1 求各供应给工程项目J1零件供应量的最大值，最小值和平均值 select MAX(QTY) maxqty,MIN(QTY) minqty,AVG(QTY) avgqty from SPJ group by JNO having JNO=J1 求各供应商供应零件供应量的最大值，最小值和平均值及供应商号 select SNO, MAX(QTY) maxqty,MIN(QTY) minqty,AVG(QTY) avgqty from SPJ group by SNO 求供应给各个工程项目的工程号，供应量及供应商的个数 select JNO,SUM(QTY),COUNT(distinct sno) from SPJ group by JNO 求被供应4种以上零件的工程项目的工程号及工程名称 select JNO,JNAME from J where JNO IN( select JNO from SPJ group by JNO having COUNT(PNO)4) 求各个供应商供应量总数大于200的零件号码、颜色、重量和供应商个数 select countsno,P.PNO,Pname,color,weight from P,(select COUNT(distinct SNO) countsno,PNO from SPJ group by PNO having SUM(QTY)200) as table1 where P.PNO=table1.PNO 第一次习题课 关系代数和SQL 查询 按照课本79页创建学生-课程数据库 use master CREATE DATABASE S_T GO use S_T CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); 学号 Sno 姓名 Sname 性别 Ssex 年龄 Sage 所在系 Sdept 201215121 李勇 男 20 CS 201215122 刘晨 女 19 CS 201215123 王敏 女 18 MA 201215125 张立 男 19 IS Studnet表 CREATE TABLE Course/*创建课程表Course*/ (Cno CHAR(4) PRIMARY KEY, /* 列级完整性约束条件,Cno是主码*/