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

1、第一次习题课关系代数和SQL 查询第一次习题课关系代数和SQL 查询按照课本79页创建学生-课程数据库按照课本79页创建学生-课程数据库use masterCREATE DATABASE S_TGOuse S_TCREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); use master学号Sno姓名Sname性别 Ssex年龄 Sage所在系

2、Sdept201215121李勇男20CS201215122刘晨女19CS201215123王敏女18MA201215125张立男19ISStudnet表学号姓名性别年龄所在系201215121李勇男20CS201CREATE TABLE Course/*创建课程表Course*/ (Cno CHAR(4) PRIMARY KEY, /* 列级完整性约束条件,Cno是主码*/ Cname CHAR(40), Cpno CHAR(4), Ccredit float, FOREIGN KEY (Cpno) REFERENCES Course(Cno) /* 表级完整性约束条件，Cpno是外码，被参

3、照表是Course,被参照的字段是Cno */ ); CREATE TABLE Course/*创建课程表CouCourse表课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course表课程号课程名先行课学分1数据库542数学23信CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade float, PRIMARY KEY (Sno,Cno), /* 主码由两个属性（Sno,Cno）构成，必须作为表级完整性进行定义*/ FOREIGN KEY

4、(Sno) REFERENCES Student(Sno), /* 表级完整性约束条件，Sno是外码，被参照表是Student */ FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件，Cno是外码，被参照表是Course*/ ); CREATE TABLE SCSC表学 号Sno 课程号 Cno 成绩 Grade2012151211 92201215121 285201215121 388201215122290201215122380SC表学 号 课程号 成绩2012151211 92201查询学习1号课程的学生最高分数；select

5、MAX(Grade) maxgradefrom SCgroup by Cnohaving Cno=1查询各个课程号与相应的选课人数；select cno,COUNT(*)from SCgroup by cno查询学习1号课程的学生最高分数；select MAX(Gra查询每个学生及其选修课程的情况，包括学生姓名和课程名，成绩；Select sname,cname,gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=SC.Cno查询选修2号课程且成绩在90分以上的所有学生；select Student.*from

6、Student,SCwhere Student.Sno=SC.Sno and SC.Cno=2 and Grade90查询每个学生及其选修课程的情况，包括学生姓名和课程名，成绩；查询与“刘晨”在同一个系学习的学生；select Student.*from Student where Sdept In (select Sdept from Student where Sname=刘晨 )select X.*from Student Xwhere Exists(select * from Student Y where X.Sdept=Y.Sdept and Y.Sname=刘晨 )查询与“刘晨”

7、在同一个系学习的学生；select Stude查询其他系中比信息系某一学生年龄小的学生姓名和年龄；select Sname,Sagefrom Student where SageANY ( Select Sage from Student where Sdept=IS) and SdeptISselect Sname,Sagefrom Student where Sage ( Select Max(Sage) from Student where Sdept=IS) and SdeptIS查询其他系中比信息系某一学生年龄小的学生姓名和年龄；sele按照课本71页创建供应数据库按照课本71页创建

8、供应数据库use masterCREATE DATABASE DB_SPJGOuse DB_SPJCREATE TABLE S ( /*创建供应商表S*/ SNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/ SNAME char (10) NULL, STATUS char (2) NULL, CITY char (10) NULL)use master供应商表SS（供应商代码,供应商名称，状态，所在城市）供应商表SS（供应商代码,供应商名称，状态，所在城市）CREATE TABLE P (/*创建零件表*/ PNO char (3) PRIMARY

9、 KEY, /* 列级完整性约束条件,Pno是主码*/ PNAME nchar (10) NULL, COLOR char (4) NULL, WEIGHT int NULL)CREATE TABLE P (/*创建零件表*/零件表 PP（零件代码，零件名，颜色，重量）零件表 PP（零件代码，零件名，颜色，重量）CREATE TABLE J (/*创建工程项目表*/ JNO char (3) PRIMARY KEY, /* 列级完整性约束条件,Jno是主码*/ JNAME char (10) NULL, CITY char (10) NULL)CREATE TABLE J (/*创建工程项目表

10、*/工程项目表 JJ（工程项目代码，工程项目名，项目所在城市）工程项目表 JJ（工程项目代码，工程项目名，项目所在城市）CREATE TABLE SPJ (/*创建供应情况表*/ SNO char (3) ,JNO char (3) , PNO char (3), QTY int NULL, PRIMARY KEY (SNO,JNO,PNO), /* 主码由个属性(SNO,JNO,PNO)构成，必须作为表级完整性进行定义*/FOREIGN KEY (SNO) REFERENCES S(SNO), /* 表级完整性约束条件，SNO是外码，参照表S的SNO*/ FOREIGN KEY (JNO)R

11、EFERENCES J(JNO), /* 表级完整性约束条件，JNO是外码，参照表J的JNO*/FOREIGN KEY (PNO)REFERENCES P(PNO) /* 表级完整性约束条件，PNO是外码，参照表P的PNO*/ )CREATE TABLE SPJ (/*创建供应情况表*供应情况表SPJSPJ（供应商代码，零件代码，工程项目代码，供应数量）供应情况表SPJSPJ（供应商代码，零件代码，工程项目代码，求没有使用天津供应商生产的红色零件的工程号JNOselect JNOfrom Jwhere not exists(select * from SPJ,S,P where SPJ.JNO

12、=J.JNO and SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and S.CITY=天津 and P.COLOR=红)select JNOfrom JExceptselect SPJ.JNOfrom SPJ,S,P where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and S.CITY=天津 and P.COLOR=红求没有使用天津供应商生产的红色零件的工程号JNOselect求至少使用了供应商S1所供应的全部零件的工程号JNOselect distinct JNOfrom SPJ SPJZwhere not exists (select *

13、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所供应的全部零件的工程号JNOsele求供应商S1提供给工程J1的零件供应数量的总和 select SUM(QTY)from SPJwhere SNO=S1 and JNO=J1求各供应给工程项目J1零件供应量的最大值，最小值和平均值select MAX(QTY) maxqty,MIN(QTY) mi

14、nqty,AVG(QTY) avgqtyfrom SPJgroup by JNO having JNO=J1求供应商S1提供给工程J1的零件供应数量的总和 select求各供应商供应零件供应量的最大值，最小值和平均值及供应商号select SNO, MAX(QTY) maxqty,MIN(QTY) minqty,AVG(QTY) avgqtyfrom SPJgroup by SNO 求供应给各个工程项目的工程号，供应量及供应商的个数select JNO,SUM(QTY),COUNT(distinct sno)from SPJgroup by JNO 求各供应商供应零件供应量的最大值，最小值和平均值及供应商号s求被供应4种以上零件的工程项目的工程号及工程名称select JNO,JNAMEfrom Jwhere JNO IN( select JNO from SPJ group by JNO having COUNT(PNO)4)求被供应4种以上零件的工程项目的工程号及工程名称sele