SQL练习.docx
《SQL练习.docx》由会员分享，可在线阅读，更多相关《SQL练习.docx（10页珍藏版）》请在冰豆网上搜索。
SQL练习
STUDENT
栏名类型栏宽内容
id数字4学生编号
name字符10学生名字
dob日期8出生日期
sex字符1性别：
M/F
class字符2班别
hcode字符1社名：
R,Y,B,G
dcode字符3地区码
remission逻辑1学费减免
mtest数字2数学测验分数
1.[一般语法]
（a）列出2A班学生。
（b）求出绿社女社员的班别及姓名。
（c）求出1B班男生的名字及数学科测验成绩。
（d）求出蓝社社员名字及居住地区。
（e）求出所有居住于旺角（MKK）的红社男社员名字。
（f）求出于星期一出生的2B班男生名字。
（g）求出2B班女生的名字及出生月份。
2.[比较]
（a）求出以下学生的班别及名字：
（i）其名字是以‘M’为起首，
（ii）其名字是以‘a’为结尾，
（iii）其名字的第三个字母为‘e’，
（iv）其名字是以‘S’为起首并以‘e’为结尾，
（v）其名字是以‘T’为起首但不包含‘y’。
（b）求出1A班学生名字及其数学科测验成绩：
（i）其分数界乎于60至70之间，
（ii）其分数不是一个平方数（即不是：
1,4,9,16,...,81），
（iii）其分数不是51,61,71,81,或91。
（c）求出所有并不居住于红磡（HHM）的中一女生的名字。
（d）求出所有并不在86年3月22日至86年4月21日出生的学生名字。
3.[群组]
（a）（i）求出居住于油麻地（YMT）的学生人数。
（ii）求出居每区的女学生人数。
（b）求出每班数学测验合格的人数（合格分=50）。
（c）求出女生的数目：
（i）全校，（ii）依照class来群组，
（iii）依照年级来群组，（iv）依照出生年份来群组。
（d）求出每区享有学费减免的学生人数。
（e）查看有没有两个学生的名字是相同的。
[使用群组并函数COUNT（）]
（f）求出中一学生的平均岁数。
以下题目与显示次序有关：
（g）在一组数据中，差距（range）定义为：
差距=最大值–最小值
（i）求出每班数学科测验成绩的差距，并按差距的升幂排列。
（ii）求出每班女生数学科测验成绩的差距，并按班别序排列。
（h）在一组数据中，控制平均值（CAVG）的定义是，撇除最高和最低的各一个数据后，剩下的数据的平均值。
（即n个数据中只计算n–2个）
（i）求出每班数学测验分数的CAVG，并按班别序排列。
（ii）求出每社中一男生数学测验分数的CAVG，并按CAVG排列。
4.[显示次序]
（a）列出所有中二男生的名单，并按班别及姓名序排列。
（b）求出每社的男社员数目，并按社的次序排列。
（c）求出享有学费减免的学生名单，并按班别及姓名序排列。
（d）列出红社社员的名单，并按性别（男生先排），班别及姓名序排列。
（e）求出每班男生和女生的数学测验平均分并按班别序排列。
5.[输出]
（a）按社员的班别及名字的次序，把蓝社社员的数据打印出来。
（b）把居住于尖沙咀（TST）的学生名字打印出来。
（c）把中一数学测验不合格的学生名字打印出来。
（d）把所有中二学生的编号、姓名和班别，按班别及姓名序，贮存成一数据库文件NAME.DBF。
（e）把所有学生的姓名和出生日期，按出生日期序，贮存成一数据库文件DOB.DBF。
6.[二重查询]*较难
（a）求出1A班数学科测验取得最高分数的学生（可多于一位）的名字、编号及他所得的分数。
（b）求出所有学生的名字及班别，其数学科测验所得的分数比他的班的平均值高出至少10。
（c）求出中二年级每一班女生数目占该班的人数百分比。
7.[数据库联合、相交及差分]
数据库文件PHY.DBF、CHEM.DBF和BIO.DBF分别贮存物理学会、化学学会及生物学会的数据。
这三文件有以下的共同结构：
PHY/CHEM/BIO
栏名类型栏宽内容
id数字4学生编号
name字符10学生名字
sex字符1性别：
M/F
class字符2班别
（a）求出化学学会和生物学会的联合。
（b）求出物理学会和化学学会的男生的联合。
（c）求出物理学会和化学学会的共同会员。
（d）求出三会的共同会员。
（e）在化学学会和生物学会的共同会员中，找出不属于物理学会的会员。
（f）求出只属于生物学会的会员。
以下题目与自然接合和外接合有关：
使用这三个学会的会员数据，并使用数据库文件STUDENT.DBF回答：
（g）列出化学学会中1A班的会员名字。
（h）列出物理学会中女会员名字。
（i）求出这三个学会都参加了的2A班学生的名字。
（j）求出这三个学会都没有参加的男生的名字。
8.[自然接合和外接合]
数据库文件SWIM.DBF贮存参加学校水运会的中一学生资料。
（须与STUDENT.DBF一合并使用）
SWIM
栏名类型栏宽内容
id数字4学生编号
event字符10比赛项目
（a）把参加水运会的学生名单，按比赛项目、班别及学生名字序打印。
（b）把参加水运会的1A班学生名字及比赛项目，按名字序打印。
（c）按班别及名字序，求出参加水运会的女生名单。
（d）按班别及名字序，求出参加自由式项目（FreeStyle）的男生名单。
（e）求出参加自由式项目（FreeStyle）的蓝社男社员名单。
（f）求出每项比赛项目的人数。
（g）求出每社参加比赛的人数。
（h）按班别及名字序，求出没有参加水运会比赛的中一学生名单。
（i）按班别及名字序，打印一份水运会比赛名单。
名单须包括社名及所参加的项目，没有参加比赛的学生则以‘******’表示。
（j）求出所有参加两项或以上比赛项目的学生的班别及名字。
[使用群组]
（k）求出背泳项目（BackStroke）及自由式项目（FreeStyle）两式都有参加的学生的班别及名字。
（l）按社及学生名字序，求出参加水运会其它项目但没有参加50m背泳（50mBackStroke）的男生名单。
答案：
1.[一般语法]
（a）SELECT*FROMstudentWHEREclass=‘2A’
（b）SELECTclass,nameFROMstudentWHEREhcode=‘G’ANDsex=‘F’
（c）SELECTclass,mtestFROMstudentWHEREclass=‘1B’ANDsex=‘M’
（d）SELECTclass,name,dcodeFROMstudentWHEREhcode=‘B’
（e）SELECTclass,nameFROMstudentWHEREhcode=‘R’ANDsex=‘M’ANDdcode=‘MKK’
（f）SELECTnameFROMstudentWHEREclass=‘2B’ANDsex=‘M’ANDDOW（dob）=2
（g）SELECTname,MONTH（dob）FROMstudentWHEREclass=‘2B’ANDsex=‘F’
2.[比较]
（a）（i）SELECTclass,nameFROMstudentWHEREnameLIKE‘M%’
（ii）SELECTclass,nameFROMstudentWHEREnameLIKE‘%a’
（iii）SELECTclass,nameFROMstudentWHEREnameLIKE‘__e%’
（iv）SELECTclass,nameFROMstudentWHEREnameLIKE‘S%e’
（v）SELECTclass,nameFROMstudentWHEREnameLIKE‘T%’ANDnameNOTLIKE‘%y%’
（b）（i）SELECTclass,name,mtestFROMstudentWHEREclass=‘1A’ANDmtestBETWEEN60AND70
（ii）SELECTclass,name,mtestFROMstudentWHEREclass=‘1A’ANDmtestIN（1,4,9,16,25,36,49,64,81）
（iii）SELECTclass,name,mtestFROMstudentWHEREclass=‘1A’ANDmtestNOTIN（51,61,71,81,91）
（c）SELECTclass,nameFROMstudentWHEREclass=‘1B’ANDmtestBETWEEN50AND55
（d）SELECTclass,nameFROMstudentWHEREclassLIKE‘1_’ANDsex=‘F’ANDNOTdcode=‘HHM’
（e）SELECTclass,name,dobFROMSTUDENTWHEREdobBETWEEN{03/22/86}AND{04/21/86}
3.[群组]
（a）（i）SELECTCOUNT（*）FROMstudentWHEREdcode=‘YMT’
（ii）SELECThcode,COUNT（*）FROMstudentWHEREsex=‘F’GROUPBYhcode
（b）SELECTCOUNT（*）FROMstudentWHEREmtest>=50GROUPBYclass
（c）（i）SELECTCOUNT（*）FROMstudentWHEREsex=‘F’
（ii）SELECTclass,COUNT（*）FROMstudentWHEREsex=‘F’GROUPBYclass
（iii）SELECTLEFT（class,1）asform,COUNT（*）FROMstudentWHEREsex=‘F’GROUPBYform
（iv）SELECTYEAR（dob）asydob,COUNT（*）FROMstudentWHEREsex=‘F’GROUPBYydob
（d）SELECTdcode,COUNT（*）FROMstudentWHEREremissionGROUPBYdcode
（e）SELECTname,COUNT（*）FROMstudentGROUPBYnameHAVINGCOUNT（*）>=2
（f）SELECTAVG（（DATE（）-dob）/365）FROMstudentWHEREclassLIKE‘1_’ANDsex=‘M’
（g）（i）SELECTclass,MAX（mtest）–MIN（mtest）ASrangeFROMstudentGROUPBYclassORDERBYrange
（ii）SELECTclass,MAX（mtest）–MIN（mtest）FROMstudentWHEREsex=‘F’GROUPBYclassORDERBYclass
（h）（i）SELECTclass,（SUM（mtest）–MAX（mtest）–MIN（mtest））/（COUNT（*）–2）AScavgFROMstudentGROUPBYclassORDERBYclass
（ii）SELECThcode,（SUM（mtest）–MAX（mtest）–MIN（mtest））/（COUNT（*）–2）AScavgFROMstudentWHEREsex=‘M’andclassLIKE‘1_’GROUPBYhcodeORDERBYcavg
4.[显示次序]
（a）SELECT*FROMstudentWHEREclassLIKE‘2_’ORDERBYclass,name
（b）SELECThcode,COUNT（*）FROMstudentGROUPBYhcode,sexORDERBYhcode
（c）SELECTclass,nameFROMstudentWHEREremissionORDERBYclass,name
（d）SELECTsex,class,nameFROMstudentORDERBYsexDESC,class,name
（e）SELECTclass,sex,AVG（mtest）FROMstudentGROUPBYclass,sex
5.[输出]
（a）SELECTclass,nameFROMstudentWHEREhcode=‘B’ORDERBYclass,nameTOPRINTER
（b）SELECTclass,nameFROMstudentWHEREdcode=‘TST’TOPRINTER
（c）SELECTclass,nameFROMstudentWHEREclassLIKE‘1_’ANDmtest<50TOPRINTER
（d）SELECTid,class,nameFROMstudentWHEREclassLIKE‘2_’INTOTABLEname.dbf
（e）SELECTname,dobFROMstudentORDERBYdobINTOTABLEdob.dbf
6.[二重查询]
（a）
（1）SELECTMAX（mtest）asmaxFROMstudentWHEREclass=‘1A’INTOCURSORx
（2）SELECTs.nameFROMstudents,xWHEREs.mtest=x.maxANDclass=‘1A’
OR:
SELECTnameFROMstudentWHEREclass=‘1A’ANDmtest=（SELECTMAX（mtest）FROMstudentWHEREclass=‘1A’）
（b）
（1）SELECTAVG（mtest）ASavg,classFROMstudentGROUPBYclassINTOCURSORtemp
（2）SELECTs.name,s.id,s.mtest,s.classFROMstudents,temptWHEREs.class=t.classANDs.mtest>t.avg+10ORDERBYs.class
（c）
（1）SELECTclass,COUNT（*）ASgirlnoFROMstudentWHEREclassLIKE‘2_’ANDsex=‘F’GROUPBYclassINTOCURSORq
（2）SELECTs.class,q.girlno/COUNT（*）FROMstudents,qWHEREs.class=q.classGROUPBYs.class
7.[数据库联合、相交及差分]
（a）SELECT*FROMchemUNIONSELECT*FROMbio
（b）SELECT*FROMphyWHEREsex=‘M’UNIONSELECT*FROMchemWHEREsex=‘M’
（c）SELECT*FROMphyWHEREidIN（SELECTidFROMchem）
（d）SELECT*FROMphyWHEREidIN（SELECTidFROMchem）ANDidIN（SELECTidFROMbio）
（e）SELECT*FROMchemWHEREidIN（SELECTidFROMbio）ANDidNOTIN（SELECTidFROMphy）
（f）SELECT*FROMbioWHEREidNOTIN（SELECTidFROMphy）ANDidNOTIN（SELECTidFROMchem）
7.
（g）SELECTs.nameFROMchemc,studentsWHEREc.id=s.idANDs.class=‘1A’
（h）SELECTs.nameFROMphyp,studentsWHEREp.id=s.idANDs.sex=‘F’
（i）SELECTs.nameFROMstudents,phyp,chemc,biobWHEREs.class=‘2A’ANDs.id=p.idANDs.id=c.idANDs.id=b.id
（j）SELECTs.nameFROMstudentsWHEREs.sex=‘M’ANDs.idNOTIN（SELECTidFROMphy）ANDs.idNOTIN（SELECTidFROMchem）ANDs.idNOTIN（SELECTidFROMbio）
8.[自然接合和外接合]
（a）SELECTw.event,s.class,s.nameFROMstudents,swimwWHEREs.id=w.idORDERBY1,2,3
（b）SELECTs.name,w.eventFROMstudents,swimwWHEREs.id=w.idANDs.class=‘1A’ORDERBY1TOPRINTER
（c）SELECTDISTINCTs.class,s.nameFROMstudents,swimwWHEREs.id=w.idANDs.sex=‘F’ORDERBY1,2
（d）SELECTs.class,s.nameFROMstudents,swimwWHEREs.id=w.idANDs.sex=‘M’ANDw.eventLIKE‘%FreeStyle’ORDERBY1,2
（e）SELECTDISTINCTs.class,s.nameFROMstudents,swimwWHEREs.id=w.idANDs.hcode=‘B’ANDw.eventLIKE‘%FreeStyle’
（f）SELECTevent,COUNT（*）FROMswimwGROUPBYevent
（g）SELECTs.hcode,COUNT（DISTINCTs.id）FROMstudents,swimwWHEREs.id=w.idGROUPBY1ORDERBY1
（h）SELECTclass,nameFROMstudentWHEREidNOTIN（SELECTidFROMswim）ANDclassLIKE‘1_’
（i）SELECTs.class,s.name,hcode,w.eventFROMstudents,swimwWHEREs.id=w.idUNIONSELECTclass,name,hcode,‘******’FROMstudentWHEREidNOTIN（SELECTidFROMswim）ANDclassLIKE‘1_’ORDERBY1,2
（j）SELECTs.name,s.classFROMstudents,swimwWHEREs.id=w.idGROUPBYw.idHAVINGCOUNT（*）>=2
（k）SELECTDISTINCTs.class,s.nameFROMstudents,swimw1,swimw2WHEREs.id=w1.idANDs.id=w2.idANDw1.eventLIKE‘%BackStroke’ANDw2.eventLIKE‘%FreeStyle’[或用二重查询]
（l）SELECThcode,nameFROMstudentWHEREsex=‘M’ANDidIN（SELECTidFROMswim）ANDidNOTIN（SELECTidFROMswimWHEREevent=‘50mBackStroke’）ORDERBY1,2
升级会员 