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

第一次习题课,关系代数和SQL查询,按照课本79页创建学生-课程数据库,usemasterCREATEDATABASES_TGOuseS_TCREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*列级完整性约束条件,Sno是主码*/SnameCHAR(20)UNIQUE,/*Sname取唯一值*/SsexCHAR(2),SageSMALLINT,SdeptCHAR(20);,Studnet表,CREATETABLECourse/*创建课程表Course*/(CnoCHAR(4)PRIMARYKEY,/*列级完整性约束条件,Cno是主码*/CnameCHAR(40),CpnoCHAR(4),Ccreditfloat,FOREIGNKEY(Cpno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course,被参照的字段是Cno*/);,Course表,CREATETABLESC(SnoCHAR(9),CnoCHAR(4),Gradefloat,PRIMARYKEY(Sno,Cno),/*主码由两个属性（Sno,Cno）构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);,SC表,查询学习1号课程的学生最高分数；,selectMAX(Grade)maxgradefromSCgroupbyCnohavingCno=1,查询各个课程号与相应的选课人数；,selectcno,COUNT(*)fromSCgroupbycno,查询每个学生及其选修课程的情况，包括学生姓名和课程名，成绩；,Selectsname,cname,gradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno,查询选修2号课程且成绩在90分以上的所有学生；,selectStudent.*fromStudent,SCwhereStudent.Sno=SC.SnoandSC.Cno=2andGrade90,查询与“刘晨”在同一个系学习的学生；,selectStudent.*fromStudentwhereSdeptIn(selectSdeptfromStudentwhereSname=刘晨),selectX.*fromStudentXwhereExists(select*fromStudentYwhereX.Sdept=Y.SdeptandY.Sname=刘晨),查询其他系中比信息系某一学生年龄小的学生姓名和年龄；,selectSname,SagefromStudentwhereSageIS,selectSname,SagefromStudentwhereSageIS,按照课本71页创建供应数据库,usemasterCREATEDATABASEDB_SPJGOuseDB_SPJCREATETABLES(/*创建供应商表S*/SNOchar(3)PRIMARYKEY,/*列级完整性约束条件,Sno是主码*/SNAMEchar(10)NULL,STATUSchar(2)NULL,CITYchar(10)NULL),供应商表S,S（供应商代码,供应商名称，状态，所在城市）,CREATETABLEP(/*创建零件表*/PNOchar(3)PRIMARYKEY,/*列级完整性约束条件,Pno是主码*/PNAMEnchar(10)NULL,COLORchar(4)NULL,WEIGHTintNULL),零件表P,P（零件代码，零件名，颜色，重量）,CREATETABLEJ(/*创建工程项目表*/JNOchar(3)PRIMARYKEY,/*列级完整性约束条件,Jno是主码*/JNAMEchar(10)NULL,CITYchar(10)NULL),工程项目表J,J（工程项目代码，工程项目名，项目所在城市）,CREATETABLESPJ(/*创建供应情况表*/SNOchar(3),JNOchar(3),PNOchar(3),QTYintNULL,PRIMARYKEY(SNO,JNO,PNO),/*主码由个属性(SNO,JNO,PNO)构成，必须作为表级完整性进行定义*/FOREIGNKEY(SNO)REFERENCESS(SNO),/*表级完整性约束条件，SNO是外码，参照表S的SNO*/FOREIGNKEY(JNO)REFERENCESJ(JNO),/*表级完整性约束条件，JNO是外码，参照表J的JNO*/FOREIGNKEY(PNO)REFERENCESP(PNO)/*表级完整性约束条件，PNO是外码，参照表P的PNO*/),供应情况表SPJ,SPJ（供应商代码，零件代码，工程项目代码，供应数量）,求没有使用天津供应商生产的红色零件的工程号JNO,selectJNOfromJwherenotexists(select*fromSPJ,S,PwhereSPJ.JNO=J.JNOandSPJ.SNO=S.SNOandSPJ.PNO=P.PNOandS.CITY=天津andP.COLOR=红),selectJNOfromJExceptselectSPJ.JNOfromSPJ,S,PwhereSPJ.SNO=S.SNOandSPJ.PNO=P.PNOandS.CITY=天津andP.COLOR=红,求至少使用了供应商S1所供应的全部零件的工程号JNO,selectdistinctJNOfromSPJSPJZwherenotexists(select*fromSPJSPJXwhereSNO=S1andnotexists(select*fromSPJSPJYwhereSPJY.PNO=SPJX.PNOandSPJY.JNO=SPJZ.JNOandSPJY.SNO=SPJX.SNO);,求供应商S1提供给工程J1的零件供应数量的总和,selectSUM(QTY)fromSPJwhereSNO=S1andJNO=J1,求各供应给工程项目J1零件供应量的最大值，最小值和平均值,selectMAX(QTY)maxqty,MIN(QTY)minqty,AVG(QTY)avgqtyfromSPJgroupbyJNOhavingJNO=J1,求各供应商供应零件供应量的最大值，最小值和平均值及供应商号,selectSNO,MAX(QTY)maxqty,MIN(QTY)minqty,AVG(QTY)avgqtyfromSPJgroupbySNO,求供应给各个工程项目的工程号，供应量及供应商的个数,selectJNO,SUM(QTY),COUNT(distinctsno)fromSPJgroupbyJNO,求被供应4种以上零件的工程项目的工程号及工程名称,selectJNO,JNAMEfromJwhereJNOIN(selectJNOfromSPJgroupbyJNOhavingCOUNT(PNO)4),求各个供应商供应量总数大于200的零件号码