《数据库系统概论SQL查询习题课.pptx》由会员分享，可在线阅读，更多相关《数据库系统概论SQL查询习题课.pptx（27页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、按照课本79页创建学生-课程数据库第1页/共27页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);第2页/共27页学号学号Sno姓名姓名Sname性别性别 Ssex年龄年龄 Sage所在系所在系 Sdept201215121李勇李勇男男20CS201215122刘晨刘晨女女19CS2012
2、15123王敏王敏女女18MA201215125张立张立男男19ISStudnet表第3页/共27页CREATE TABLE Course/*创建课程表Course*/(Cno CHAR(4)PRIMARY KEY,/*列级完整性约束条件,Cno是主码*/Cname CHAR(40),Cpno CHAR(4),Ccredit float,FOREIGN KEY(Cpno)REFERENCES Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course,被参照的字段是Cno*/);第4页/共27页Course表课程号课程号Cno课程名课程名Cname先行课先行课Cpno
3、学分学分Ccredit1数据库数据库542数学数学23信息系统信息系统144操作系统操作系统635数据结构数据结构746数据处理数据处理27PASCAL语言语言64第5页/共27页CREATE TABLE SC (Sno CHAR(9),Cno CHAR(4),Grade float,PRIMARY KEY(Sno,Cno),/*主码由两个属性（Sno,Cno）构成，必须作为表级完整性进行定义*/FOREIGN KEY(Sno)REFERENCES Student(Sno),/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGN KEY(Cno)REFERENCES
4、Course(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);第6页/共27页SC表学学 号号Sno 课程号课程号 Cno 成绩成绩 Grade2012151211 92201215121 285201215121 388201215122290201215122380第7页/共27页查询学习1号课程的学生最高分数；select MAX(Grade)maxgradefrom SCgroup by Cnohaving Cno=1查询各个课程号与相应的选课人数；select cno,COUNT(*)from SCgroup by cno第8页/共27页查询每个学生及其选
5、修课程的情况，包括学生姓名和课程名，成绩；Select sname,cname,gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=SC.Cno查询选修2号课程且成绩在90分以上的所有学生；select Student.*from Student,SCwhere Student.Sno=SC.Sno and SC.Cno=2 and Grade90第9页/共27页查询与“刘晨”在同一个系学习的学生；select Student.*from Student where Sdept In(select Sdept f
6、rom Student where Sname=刘晨刘晨)select X.*from Student Xwhere Exists(select*from Student Y where X.Sdept=Y.Sdept and Y.Sname=刘晨)第10页/共27页查询其他系中比信息系某一学生年龄小的学生姓名和年龄；select Sname,Sagefrom Student where SageANY(Select Sage from Student where Sdept=IS)and SdeptISselect Sname,Sagefrom Student where Sage(Sele
7、ct Max(Sage)from Student where Sdept=IS)and SdeptIS第11页/共27页按照课本71页创建供应数据库第12页/共27页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)第13页/共27页供应商表SS（供应商代码,供应商名称，状态，所在城市）第14页/共27页CRE
8、ATE TABLE P(/*创建零件表*/PNO char(3)PRIMARY KEY,/*列级完整性约束条件,Pno是主码*/PNAME nchar(10)NULL,COLOR char(4)NULL,WEIGHT int NULL)第15页/共27页零件表 PP（零件代码，零件名，颜色，重量）第16页/共27页CREATE TABLE J(/*创建工程项目表*/JNO char(3)PRIMARY KEY,/*列级完整性约束条件,Jno是主码*/JNAME char(10)NULL,CITY char(10)NULL)第17页/共27页工程项目表 JJ（工程项目代码，工程项目名，项目所在城
9、市）第18页/共27页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)REFERENCES J(JNO),/*表级完整性约束条件，JNO是外码，参照表J的JNO*/FOREIGN KEY(PNO)REFERE
10、NCES P(PNO)/*表级完整性约束条件，PNO是外码，参照表P的PNO*/)第19页/共27页供应情况表SPJSPJ（供应商代码，零件代码，工程项目代码，供应数量）第20页/共27页求没有使用天津供应商生产的红色零件的工程号JNOselect JNOfrom Jwhere 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 JNOfrom JExceptselect SPJ.JNOfrom S
11、PJ,S,P where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and S.CITY=天津天津 and P.COLOR=红红第21页/共27页求至少使用了供应商S1所供应的全部零件的工程号JNOselect distinct JNOfrom SPJ SPJZwhere 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);
12、第22页/共27页求供应商S1提供给工程J1的零件供应数量的总和 select SUM(QTY)from SPJwhere SNO=S1 and JNO=J1求各供应给工程项目J1零件供应量的最大值，最小值和平均值select MAX(QTY)maxqty,MIN(QTY)minqty,AVG(QTY)avgqtyfrom SPJgroup by JNO having JNO=J1第23页/共27页求各供应商供应零件供应量的最大值，最小值和平均值及供应商号select SNO,MAX(QTY)maxqty,MIN(QTY)minqty,AVG(QTY)avgqtyfrom SPJgroup b
13、y SNO 求供应给各个工程项目的工程号，供应量及供应商的个数select JNO,SUM(QTY),COUNT(distinct sno)from SPJgroup by JNO 第24页/共27页求被供应4种以上零件的工程项目的工程号及工程名称select JNO,JNAMEfrom Jwhere JNO IN(select JNO from SPJ group by JNO having COUNT(PNO)4)第25页/共27页求各个供应商供应量总数大于200的零件号码、颜色、重量和供应商个数select countsno,P.PNO,Pname,color,weightfrom P,(select COUNT(distinct SNO)countsno,PNO from SPJ group by PNO having SUM(QTY)200)as table1 where P.PNO=table1.PNO 第26页/共27页感谢您的观看！第27页/共27页