《数据库系统概论SQL查询习题课.ppt》由会员分享，可在线阅读，更多相关《数据库系统概论SQL查询习题课.ppt（27页珍藏版）》请在三一文库上搜索。
1、第一次习题课,关系代数和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) );,Studnet表,CREATE TABLE Course/*创建课程表Course*/ (Cno CHAR(4) PRIMARY KEY,
2、/* 列级完整性约束条件,Cno是主码*/ Cname CHAR(40), Cpno CHAR(4), Ccredit float, FOREIGN KEY (Cpno) REFERENCES Course(Cno) /* 表级完整性约束条件，Cpno是外码，被参照表是Course,被参照的字段是Cno */ );,Course表,CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade float, PRIMARY KEY (Sno,Cno), /* 主码由两个属性（Sno,Cno）构成，必须作为表级完整性进行定义*/ FOREIGN KEY (Sno
3、) REFERENCES Student(Sno), /* 表级完整性约束条件，Sno是外码，被参照表是Student */ FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件，Cno是外码，被参照表是Course*/ );,SC表,查询学习1号课程的学生最高分数；,select MAX(Grade) maxgrade from SC group by Cno having Cno=1,查询各个课程号与相应的选课人数；,select cno,COUNT(*) from SC group by cno,查询每个学生及其选修课程的情况，包括学生
4、姓名和课程名，成绩；,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,查询与“刘晨”在同一个系学习的学生；,select Student.* from Student where Sdept In (select Sdept from Stud
5、ent where Sname=刘晨 ),select X.* from Student X where Exists (select * from Student Y where X.Sdept=Y.Sdept and Y.Sname=刘晨 ),查询其他系中比信息系某一学生年龄小的学生姓名和年龄；,select Sname,Sage from Student where SageIS,select Sname,Sage from Student where SageIS,按照课本71页创建供应数据库,use master CREATE DATABASE DB_SPJ GO use DB_SP
6、J CREATE TABLE S ( /*创建供应商表S*/ SNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/ SNAME char (10) NULL, STATUS char (2) NULL, CITY char (10) NULL),供应商表S,S（供应商代码,供应商名称，状态，所在城市）,CREATE TABLE P (/*创建零件表*/ PNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Pno是主码*/ PNAME nchar (10) NULL, COLOR char (4) NULL, WEIGHT in
7、t NULL),零件表 P,P（零件代码，零件名，颜色，重量）,CREATE TABLE J (/*创建工程项目表*/ JNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Jno是主码*/ JNAME char (10) NULL, CITY char (10) NULL),工程项目表 J,J（工程项目代码，工程项目名，项目所在城市）,CREATE TABLE SPJ (/*创建供应情况表*/ SNO char (3) ,JNO char (3) , PNO char (3), QTY int NULL, PRIMARY KEY (SNO,JNO,PNO), /* 主
8、码由个属性(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*/ ),供应情况表SPJ,SPJ（供应商代码，零件代码，工程项目代码，供应数量）,求没有使用天津供应商生产的红色零件的工程号JN
9、O,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 di
10、stinct 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
11、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,