(SELECTSage
FROMStudents
WHEREClno='00312');
12、求没有选修‘数据库’课程的学生姓名;
SELECTSno,Sname
FROMStudents
WHERESnoNOTIN
(SELECTSnoFROMGrade,CoursesWHEREGrade.Cno=Courses.Cno
ANDCourses.Cname='数据库');
13、求至少选修了姓名为“李勇”的学生所选修全部课程的学生学号和姓名;
SELECTSno,SnameFROMStudents
WHERESnoIN
(SELECTDISTINCTSno
FROMGradeSCX
WHERENOTEXISTS
(SELECT*
FROMGradeSCY
WHERESCY.Sno=(SELECTSnoFROMStudents
WHERESname='李勇')ANDNOTEXISTS
(SELECT*
FROMGradeSCZ
WHERESCZ.Sno=SCX.Sno
ANDSCZ.Cno=SCY.Cno)));
14、求1号课程的成绩高于李勇的学生学号和成绩;
SELECTSno,GmarkFROMGrade
WHERESnoIN
(SELECTSnoFROMGrade
WHERECno='1'ANDGmark>
(SELECTGmarkFROMGrade,StudentsWHEREGrade.Sno=Students.Sno
ANDStudents.Sname='李勇'ANDCno='1'));
15、求选修了高等数学的学号和姓名;
SELECTSno,Sname
FROMStudents
WHERESnoIN
(SELECTSno
FROMGrade,Courses
WHEREGrade.Cno=Courses.Cno
ANDCourses.Cname=’高等数学’);
16、查询选修了全部课程的学生姓名;
SELECTSnameFROMStudents
WHERENOTEXISTS
(SELECT*FROMCourses
WHERENOTEXISTS
(SELECT*FROMGrade
WHEREGrade.Cno=Courses.Cno
ANDGrade.Sno=Students.Sno));
17、检索“张雨”老师讲授的课程名及选修课程的学生人数;
SELECTCname,Snum
FROMCourses,Teaching,Teachers
WHERETeaching.Tno=Teachers.Tno
ANDTeaching.Cno=Courses.CnoANDTname=’张雨’;
18、统计“讲师”职称的教师人数及平均年龄;
SELECTCOUNT(*),AVG(Sage)
FROMTeachers
WHEREPs=’讲师’;
19、查出课程成绩在80分以上的女学生的姓名、课程名和成绩;
SELECTSname,Cname,Gmark
FROMStudents,Courses,Grade
WHEREStudents.Sno=Grade.SnoANDGrade.Cno=Courses.Cno
ANDGrade.Gmark>=80ANDSsex=’女’;
20、找出学生的平均成绩和所学课程门数;
SELECTSno,AVG(Gmark),COUNT(*)
FROMGrade
GROUPBYSno;
21、找出年龄超过平均年龄的学生姓名;
SELECTSnameFROMStudents
GROUPBYSname
HAVINGSage>AVG(Sage);
22、查询哪些课程只有男生选读。
SELECTDISTINCTCnoFROMCourses
WHERE‘男’=ALL
(SELECTSsexFROMGrade,Students
WHEREGrade.Sno=Students.Sno
ANDGrade.Cno=Courses.Cno);
习题三答案
1、请使用Transact-SQL建立如下表:
表名:
雇员基本信息表;
字段名数据类型宽度精度可空性要求
NO_IDCHAR7NOTNULL设为主键
NAMECHAR10NOTNULL
SEXCHAR2NOTNULL默认值为”男”
AGEINTNOTNULL
DEPTVARCHAR50NULL
PHONEVARCHAR20NULL
NOTESVARCHAR50NULL
1、createtable雇员基本信息表
(no_idchar(7)notnull,
namechar(10)notnull,
sexchar
(2)notnull,
ageintnotnulldefault‘男’,
deptvarchar(50),
phonevarchar(20),
notesvarchar(50),
primarykey(no_id))
2、下面是一个电子商务网站购物模式,包括了三个表(主码用下划线标出)
客户表:
Client(Cno,Cname,Csex,Cage,Cclass),Cclass是客户类别
商品表:
Goods(Gno,Gname,Gpno,Gprice),Gpno是赠品号
购物表:
CG(Cno,Gno,Time,Value),Value是客户对这次购物的评分(10分制)
备注:
有的客户只登记,但尚未购买商品。
(1)查询购买了商品的客户号及姓名。
(2)查询1980年出生的客户号及姓名。
(3)购买了三种以上商品的客户号及客户姓名。
2、
(1)selectCno,Cname
fromClient
whereCnoin
(selectdistinctCno
fromCG)
(2)selectCno,Cname
fromClient
whereCage=26
(3)selectCno,Cname
fromClient
whereCnoin
(selectCno
fromCG
groupbyCno
havingcount(*)>3)
3、现有一销售表,它们结构如下:
idint(标识号)
codnochar(7)(商品编码)
codnamevarchar(30)(商品名称)
specvarchar(20)(商品规格)
pricenumeric(10,2)(价格)
sellnumint(销售数量)
deptnochar(3)(售出分店编码)
selldatedatetime(销售时间)
要求:
写出查询销售时间段在2002-2-15日到2002-4-29之间,分店编码是01的所有记录。
3、select*from销售表
whereselldatebetween‘2002-2-15’and’2002-4-29’anddeptno=01
4、设有三个关系:
学生关系S(SNO,SNAME,AGE,SEX),主码为SNO
学习关系SC(SNO,CNO,GRADE),主码为(SNO,CNO)
课程关系C(CNO,CNAME,TEACHER),主码为CNO
试用SQL语句表示下列查询语句:
检索选修课程包括ZHA老师所授课程的学生学号。
4、selectdistinctSNO
fromSC
whereCNOin
(selectCNO
fromC
whereTEACHER=‘ZHA’)
5、已知某教学管理数据库包含三个基本表:
学生基本信息表S(S#,SNAME,AGE,SEX)
选修课程成绩信息表SC(S#,C#,GRADE)
选修课程基本信息表C(C#,CNAME,TEACHER)
试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询:
(1)统计有学生选修的课程门数;
(2)求选修C4课程的学生的平均年龄;
(3)求ZHANG老师所授课程的每门课程的学生平均成绩;
(4)在基本表S中插入一个学生元组(‘S9’,‘WU’,18)。
(5)把选修MATHS课不及格的成绩全改为空值。
5.
(1)SELECTCOUNT(DISTINCTC#)
FROMSC
(2)SELECTAVG(AGE)
FROMS,SC
WHEREC#=’C4’ANDS.S#=SC.S#
(3)SELECTSC.C#,CNAME,AVG(GRADE)
FROMSC,C
WHERESC.C#=C.C#ANDTEACHER=’’
GROUPBYSC.C#
(4)INSERTINTOS(S#,SNAME,AGE)
VALUES(‘S9’,’WU’,18)
(5)UPDATESCSETGRADE=NULL
WHEREGRADE<60ANDC#IN
(SELECTC#FROMCWHERECNAME=’MATHS’)
6、已知某教学管理数据库中包含四个基本表:
学生情况基本表Students(Sno,Sname,Age,Sex,Bplace)
课程情况基本表Courses(Cno,Cname,Credit)
选课情况基本表Enrolls(Sno,Cno,Grade)
教师任课情况基本表Teaching(Cno,Class,Tno,Snum)
教师情况基本表Teachers(Tno,Tname,Age,PS)
试用SQL查询语句完成下列对数据的查询要求:
(1)检索至少选修课程号为001和010的学生学号;
(2)找出各课程的平均成绩,按课程号分组,且只选择学生超过10人的课程的成绩;
(3)检索“王一平”老师讲授的课程名及选修课程的学生人数;
(4)统计“教授”职称的教师人数及平均年令。
6.
(1)SELECTSNO
FROMENROLLSX,ENROLLSY
WHEREX.SNO=Y.SNOANDX.CNO=’0001’ANDY.CNO=’0010’
(2)SELECTCNO,AVG(GRADE),STUDENTS=COUNT(*)
FROMENROLLS
GROUPBYCNO
HAVINGCOUNT(*)>=10
(3)SELECTCNAME,SNUM
FROMCOURSES,TEACHERS,TEACHING
WHERECOURSES.CNO=TEACHING.CNO
ANDTEACHERS.TNO=TEACHING.TNO
ANDTEACHERS.TNAME=’王一平’
(4)SELECTCOUNT(*),AVG(AGE)
FROMTEACHERS
WHEREPS=’教授’
7、供应商-零件-工程项目数据库由以下四个关系模式构成:
S(SNO,SNAME,STATUS,CITY)
P(PNO,PNAME,COLOR,WEIGHT,CITY)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
供应商S,零件P和工程项目J分别由供应商号(SNO),零件号(PNO)和工程项目号(JNO)唯一标识。
供货SPJ是指由某个供应商向某个工程项目供应某些数量的某种零件。
请用SQL语言完成如下的操作:
(1)找出给北京的工程项目提供不同的零件号;
(2)将没有供货的所有工程项目从J中删除;
(3)查询提供全部零件的供应商名;
(4)查询这样的工程项目号:
供给该工程项目的零件P1的平均供应量大于供给工程J1的任何一种零件的最大供应量;
(5)一个视图,它由所有这样的工程项目(工程项目号与所在城市名称)组成:
它们由供应商S1供货且使用零件P1。
7.
(1)SELECTDISTINCTSPJ.PNO
FROMSPJ,J
WHERESPJ.JNO=J.JNOANDJ.CITY=’北京’
(2)DELETEFROMJ
WHEREJNONOTIN(SELECTJNOFROMSPJ)
(3)SELECTSNAMEFORMS
WHERENOTEXISTS(SELECT*FROMP
WHERENOTEXISTS(SELECT*FROMSPJ
WHERESNO=S.SNOANDPNO=P.PNO))
(4)SELECTDISTINCTJNOFROMSPJ
WHEREPNO=’P1’
GROUPBYJNO
HAVINGAVG(QTY)>(SELECTMAX(QTY)FROMSPJWHEREJNO=’J1’)
(5)CREATEVIEWJ_S1_P1
ASSELECTJ.JNO,J.CITYFROMSPJ,J
WHERESPJ.JNO=J.JNOANDSPJ.SNO=’S1’ANDSPJ.PNO=’P1’
8、设有学生表S(SNO,SN)(SNO为学生号,SN为姓名)和学生选修课程表SC(SNO,CNO,CN,G)(CNO为课程号,CN为课程名,G为成绩),试用SQL语言完成以下各题:
(1)建立一个视图V_SSC(SNO,SN,CNO,CN,G),并按CNO升序排序;
(2)从视图V_SSC上查询平均成线在90分以上的SN、CN和G。
8.
(1)CREATEVIEWV_SSC(SNO,SN,CNO,CN,G)
ASSELECTS.SNO,S.SN,CNO,SC.CN,SC.G
FROMS,SC
WHERES.SNO=SC.SNO
ORDERBYCNO
(2)SELECTSN,CN,G
FROMV_SSC
GROUPBYSNO
HAVINGAVG(G)>90
9、今有如下关系数据库:
S(SNO,SNAME,STATUS,CITY)
P(PNO,PN,COLOR,WEIGHT)
J(JNO,JN,CITY)
SPJ(SNO,PNO,JNO,QTY)
其中,S为供应单位,P为零件,J为工程,SPJ为工程订购零件的订单,其语义为:
某供应单位供应某种零件给某个工程,请用SQL完成下列操作。
(1)求为工程J1提供红色零件的供应商代号。
(2)求使用S1供应的零件的工程名称。
(3)求供应商与工程所在城市相同的供应商提供的零件代号。
(4)求至少有一个和工程不在同一城市的供应商提供零件的工程代号。
9.
(1)SELECTDISTINCTSPJ.SNO
FROMSPJ,P
WHEREP.PNO=SPJ.PNOANDSPJ.JNO=’J1’ANDP.COLOR=’红’
(2)SELECTJ.JN
FROMJ,SPJ
WHEREJ.JNO=SPJ.JNOANDSPJ.SNO=’S1’
(3)SELECTDISTINCTSPJ.PNO
FROMS,J,SPJ
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=J.CITY