数据库系统概论SQL查询习题课.ppt

1、1,第一次习题课,关系代数和SQL 查询,2,按照课本79页创建学生-课程数据库,3,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) );,4,Studnet表,5,CREATE TABLE Course/*创建课程表Course*/ (Cno CHAR(4)

2、PRIMARY KEY, /* 列级完整性约束条件,Cno是主码*/ Cname CHAR(40), Cpno CHAR(4), Ccredit float, FOREIGN KEY (Cpno) REFERENCES Course(Cno) /* 表级完整性约束条件，Cpno是外码，被参照表是Course,被参照的字段是Cno */ );,6,Course表,7,CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade float, PRIMARY KEY (Sno,Cno), /* 主码由两个属性（Sno,Cno）构成，必须作为表级完整性进行定义*/

3、 FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件，Sno是外码，被参照表是Student */ FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件，Cno是外码，被参照表是Course*/ );,8,SC表,9,查询学习1号课程的学生最高分数；,select MAX(Grade) maxgrade from SC group by Cno having Cno=1,查询各个课程号与相应的选课人数；,select cno,COUNT(*) from SC group by cno

4、,10,查询每个学生及其选修课程的情况，包括学生姓名和课程名，成绩；,Select sname,cname,grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno,查询选修2号课程且成绩在90分以上的所有学生；,select Student.* from Student,SC where Student.Sno=SC.Sno and SC.Cno=2 and Grade90,11,查询与“刘晨”在同一个系学习的学生；,select Student.* from Student where Sdept

5、 In (select Sdept from Student where Sname=刘晨 ),select X.* from Student X where Exists (select * from Student Y where X.Sdept=Y.Sdept and Y.Sname=刘晨 ),12,查询其他系中比信息系某一学生年龄小的学生姓名和年龄；,select Sname,Sage from Student where SageIS,select Sname,Sage from Student where SageIS,13,按照课本71页创建供应数据库,14,use master

6、 CREATE DATABASE DB_SPJ GO use DB_SPJ CREATE TABLE S ( /*创建供应商表S*/ SNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/ SNAME char (10) NULL, STATUS char (2) NULL, CITY char (10) NULL),15,供应商表S,S（供应商代码,供应商名称，状态，所在城市）,16,CREATE TABLE P (/*创建零件表*/ PNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Pno是主码*/ PNAME nchar

7、 (10) NULL, COLOR char (4) NULL, WEIGHT int NULL),17,零件表 P,P（零件代码，零件名，颜色，重量）,18,CREATE TABLE J (/*创建工程项目表*/ JNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Jno是主码*/ JNAME char (10) NULL, CITY char (10) NULL),19,工程项目表 J,J（工程项目代码，工程项目名，项目所在城市）,20,CREATE TABLE SPJ (/*创建供应情况表*/ SNO char (3) ,JNO char (3) , PNO c

8、har (3), QTY int NULL, PRIMARY KEY (SNO,JNO,PNO), /* 主码由个属性(SNO,JNO,PNO)构成，必须作为表级完整性进行定义*/ FOREIGN KEY (SNO) REFERENCES S(SNO), /* 表级完整性约束条件，SNO是外码，参照表S的SNO*/ FOREIGN KEY (JNO)REFERENCES J(JNO), /* 表级完整性约束条件，JNO是外码，参照表J的JNO*/ FOREIGN KEY (PNO)REFERENCES P(PNO) /* 表级完整性约束条件，PNO是外码，参照表P的PNO*/ ),21,供应情

9、况表SPJ,SPJ（供应商代码，零件代码，工程项目代码，供应数量）,22,求没有使用天津供应商生产的红色零件的工程号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

10、S.CITY=天津 and P.COLOR=红,23,求至少使用了供应商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 ) );,24,求供应商S1提供给工程J1的零件供应数量的总和,select SUM(QTY) fro

11、m 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,25,求各供应商供应零件供应量的最大值，最小值和平均值及供应商号,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,26,求被供应4种以上零件的工程项目的工程号及工程名称,select JNO,JNAME from J where JNO IN( select JNO from SPJ group by JNO having COUNT(PNO)4),27,求各个供应商供应量总数大于20