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

关系代数和SQL

查询按照

课

本

7

9

页

创

建

学生

-

课

程

数

据

库use

S

TCREATE

TABLEStudent(SnoCHAR(9)PRIMARY

KEY,/

*

列

级

完

整

性

约

束

条

件

，Sno

是主码*/Sname

CHAR(20)UNIQUE,Sname

取唯一值*/Ssex

CHAR(2),Sage

SMALLINT,Sdept

CHAR(20)CREATE

DATABASE

S_TGOuse

master);水学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept201215121李勇男20CS201215122刘晨久19CS201215123王敏女18MA201215125张立男19ISStudnet表CREATE

TABLE

Course/*创建课程表Course*/(Cno

CHAR(4)PRIMARYKEY,/*列级完整性约束条件，Cno是主码*/Cname

CHAR(40),Cpno

CHAR(4),Ccredit

float,FOREIGN

KEY

(Cpno)REFERENCES/*表级完整性约束条件，Cpno

是外码，

被参照表是Course,被参照的字段是Cno*/Course(Cno));课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27P

A

S

C

A

L

语

言64Course表CREATE

TABLE

SC(SnoCHAR(9),Cno

CHAR(4),Grade

float,PRIMARY

KEY(Sno,Cno),/*主码由两个属性

(Sno,Cno)

构成，

必

须作为

表级

完

整

性

进行

定

义

*

/FOREIGNKEY(Sno)REFERENCES

Student(Sno),/*表

级

完

整

性

约

束

条

件，Sno是外码，

被

参照

表

是Student

*/FOREIGN

KEY(Cno)REFERENCES

Course(Cno)/*表

级

完

整

性

约

束

条

件，Cno

是外

码，

被

参

照

表

是Course*);学

号Sno课程号Cno成绩Grade201215121192201215121285201215121388201215122290201215122380SC表·

查询学习1号课程的学生最高分数；select

MAX(Grade)maxgradefromSCgroup

byCnohavingCno='1'·

查询各个课程号与相应的选课人数；selectcno,COUNT(*)fromSCgroup

bycno·查询每个学生及其选修课程的情况，包括学生姓名和课程名，

成绩；Selectsname,cname,gradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno·查询选修2号课程且成绩在90分以上的所有学生；selectStudent.*fromStudent,SCwhereStudent.Sno=SC.SnoandSC.Cno='2'andGrade>90selectX.*fromStudentXwhere

Existsselect*fromStudentYwhereX.Sdept=Y.SdeptandY.Sname='刘晨')selectStudent.*fromStudentwhereSdeptIn

(selectSdeptfromStudentwhereSname='刘晨')·

查询与“刘晨”在同

一

个系学习的学生；selectSname,SagefromStudentwhereSage<(Select

Max(Sage)fromStudentwhereSdept='IS')andSdept<>'IS'selectSname,SagefromStudentwhereSage<ANY(SelectSagefromStudentwhereSdept='IS)andSdept<>'IS'·查询其他系中比信息系某一学生年龄小的学生姓名和年龄

；按照

课

本

7

1

页

创

建

供

应

数

据

库use

masterCREATE

DATABASE

DB_SPJGOuseDB_SPJCREATETABLE

S(/*创建供

应

商

表S*/SNO

char(3)PRIMARYKEY,/*列级完整性约束条件，Sno

是主码*/SNAME

char

(10)NULL,STATUS

char(2)NULL,CITY

char(10)

NULL)50SNAESTBTIISCITY51精益20天津52盛锡10北京53东方红30北京54丰泰盛20天津55为民30上海几

C

立

[儿C立上几C立上C立上S

(供应商代码，供应商名称，

状态，所在城市)供应商表SCREATETABLE

P

(/*

创建零件表

*

/PNO

char(3)

PRIMARY

KEY,/*列级完整性约束条件，Pno

是主码*/

PNAME

nchar(10)NULL,COLOR

char(4)NULL,WEIGHT

int

NULL)

PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺

丝

刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30…

…

·P

(零件代码，零件名，颜色，

重量)零件

表

PCREATE

TABLE

J(/*创建工程项目表*/JNO

char

(3)

PRIMARY

KEY,/*列级完整性约束条件，Jno

是主码*/JNAMEchar(10)NULL,CITY

char

(10)NULL)工程项目表JJNOJNAMECITY11三建北京]2—汽长春]3弹簧厂天津]4造船厂天津]5机车厂唐山]6无线电厂常州]7半导体厂南京J(工程项目代码，工程项目名，

项目所在城市)CREATE

TABLE

SPJ(/*

创建供应情况表*/SNO

char

(3)

,JNOchar

(3)

,PNOchar

(3),

QTY

intNULL,PRIMARY

KEY(SNO,JNO,PNO),/

*

主

码由

个

属

性(SNO,JNO,PNO)

构成，必须作为表级完整性进行定义*/FOREIGN

KEY(SNO)REFERENCES

S(SNO),/*表级完整性约束条件，

SNO是外码，参照表S的SNO*/FOREIGNKEY(JNO)REFERENCES

J(JNO),/*表级完整性约束条件，

JNO是外码，参照表J的JNO*/FOREIGN

KEY(PNO)REFERENCES

P(PNO)/*表级完整性约束条件，

PNO是外码，参照表P的PNO*/SNOJNOPNOQTY51J1P1200S1J2PZ100S1J3P1100S1]4P1了0052J1P3400S2]1P540052]2P320052]2P5100S2]4P350052]5P340053]1P1200S3]1P3100S4]1P510054]3P630054]4P620055]1P320055]2P620055]4PZ10055]4P6500SPJ

(供应商代码，零件代码，工程项目代码，供应数量)供

应

情

况

表SPJselectJNOfromJwhere

notexists(select*fromSPJ,S,PwhereSPJ.JNO=J.JNOandSPJ.SNO=S.SNOandSPJ.PNO=P.PNOand

S.CITY='

天

津'and

P.COLOR='红')selectJNOfromJExceptselectSPJ.JNOfrom

SPJ,S,PwhereSPJ.SNO=S.SNOandSPJ.PNO=P.PNOand

S.CITY='

天

津'and

P.COLOR='红'求

没

有

使

用

天

津

供

应

商生

产

的

红

色

零

件

的

工

程

号JNOselect

distinct

JNOfrom

SPJ

SPJZwhere

not

existsand

SPJY.SNO=SPJX.SNO));(select

*from

SPJ

SPJXwhere

SNO='S1'and

notexists(select

*from

SPJSPJY求至少使用了供应商S1所供应的全部零件的工程号JNOwhereSPJY.PNO=SPJX.PNOandSPJY.JNO=SPJZ.JNOselect

MAX(QTY)maxqty,MIN(QTY)minqty,AVG(QTY)avgqtyfromSPJgroup

byJNohavingJNO='J1selectSUM(QTY)fromSPJwhereSNO='S1'andJNO='J1'求各供应给工程项目J