SQL笔试题

《SQL笔试题》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1.⍝SQL☢⚪ᐵgroupby⊤ᑁ2005-05-092005-05-092005-05-09!2005-05-09!2005-05-102005-05-10!2005-05-10!᝞#⌕%ᡂ'.ᑡ)#*᝞+ᑏsql0!2005-05-09222005-05-1012createtable#tmp(rqvarchar(10),shengfunchar(1))insertinto#tmpvalues('2005-05-09')insertinto#tmpvalues(,2005-05-09*,'ft')insertinto#tmpvalues('2005-05-09!')insertinto#tmpvalues(,2005-05-09*,'')insertinto#tmpvalues('2005-05-10')insertinto#tmpvalues(,2005-05-10*,'ft')insertinto#tmpvalues('2005-05-10!')1)selectrq,sum(casewhenshengfu=then1else0end)Isum(casewhenshengfu=**then1else0end),*from#tmpgroupbyrq2)selectN.rq,N.M.fifrom(selectrq,IB=count(*)from#tmpwhereshengfu='ᨴ%'groupbyrq)Ninnerjoin(selectrq,Account(*)from#tmpwhereshengfu=**groupbyrq)MonN.rq=M.rq3)selecta.colOOl,a.alb.bl!from(selectcolOOl,count(colOOl)alfromtempiwherecol002='ft*groupbycolOOl)a,(selectcolOOl,count(colOOl)blfromtempiwherecol002=,*groupbycolOOl)bwherea.co1001=b.colOOl2.MᦟO☢P⍗ᑮḄSQLḄTUV⚪⊤PᨵABCYᑡᵨSQL[\]AᑡᜧBᑡ_⌱aAᑡᔲᑣ⌱aBdU]BᑡᜧCᑡ_⌱aBᑡᔲᑣ⌱aCᑡ஺select(casewhena>bthenaelsebend),(casewhenb>cthenbeslecend)fromtable_name3.☢⚪OijᑨlḄsql0Mmntb_send⊤PijoSendTimeqrst]IᜩḄᡠᨵwx0oSendTimeqrtdatetimeyᒹ{

1ij|_}sselect*fromtbwheredatediffddSendTime,getdate

=0z4.ᨵ~⊤☢ᨵ3Oqrᦻᦪ஺ᐸPᨵ3ᩩwxᑖ⊤ᦻ70ᑖᦪ80ᑖ,58ᑖMᵨᩩsql᩵UnYᩩwxᢥ'ᩩnᩭoᑏnḄsᜧᡈ80⊤ᜧᡈ60⊤60ᑖ⊤஺ᦻᦪselectcasewhenᦻ=80then'whenᦻ=60then''elsesasᦻcasewhenᦪ=80thenwhenᦪ=60thenelsesasᦪcasewhen=80then''when=60then''elsesasfromtable5.ᙠsqlserver2000Pᵨsql~ᵨᡝ¢_⊤£¤¥¢_⊤☢ᒹ{¦OqrID£IDValues,§y¨©intyª«'.¦ὅḄ0ᵨᡝ¢_⊤createtable#xxIDint,IDValuesint
¤¥¢_⊤createtable##xxIDint,IDValuesint
ᵨᡝ¢_⊤®¯O⊤ḄᵨᡝḄSession°±¯ᐸ²³´©°±Ḅ.]µḄ³´¶ᜫ_O¢_⊤¸¹ºᑤ◀.ᐰ¾¢_⊤¯᦮OSQLServer[À¨°±Á©ᡠᨵÂVµḄSession¨¶ᜫḄ_᎛µÄ¹ºᑤ◀.6.sqlserver2000©ÅᜧyᦪÆDzḄÈÉÊ®ËÈÉÌÍḄ▲ᑴMVµ©ÐÑÒÓÔ[\ÅÕ▲ÊÖᑴḄ஺µḄᡠᨵᦪƨÈÉᙠᦪÆᦻPo*.dbf
,ᡠ®⌕ᦻ×ᜧSQLServerḄÈÉÊ©°᡽ᜧḄ.SQLServer2000ᦪÆÇᨵYŧyḄᦻÙ⌕ᦪÆᦻÙ⌕ᦪÆᦻ©ᦪÆÇḄÚÛᢣᔣᦪÆÇPᦻḄᐸµÞᑖ஺ßOᦪÆǨᨵOÙ⌕ᦪÆᦻ஺Ù⌕ᦪÆᦻḄà₞ᦻ᡽âã©.mdfo

2ä⌕ᦪÆᦻä⌕ᦪÆᦻᒹ{◀Ù⌕ᦪÆᦻ᜜ḄᡠᨵᦪÆᦻ஺ᨵæᦪÆÇ°çèᨵä⌕ᦪÆᦻéᨵæᦪÆÇᑣᨵêOä⌕ᦪÆᦻ஺ä⌕ᦪÆᦻḄà₞ᦻ᡽âã©.ndfoiëᦻiëᦻᒹ{ឮíᦪÆÇᡠ◤Ḅᡠᨵiëïð஺ßOᦪÆÇñòóôᨵOiëᦻÁ°õO஺iëᦻḄà₞ᦻ᡽âã©.ldf«7.MᵨOsqlön)#÷tablel,table2Pmn᝞table3ᡠᑡᦪÆøùúûḄᦪÆ)#üý®©þtOᔣᜧÿᦟ஺᝞ᵨ஺tablelᨴmondepyjᨴ0110ᨴ0210ᨴ035:ᨴ028ᨴ049ᨴ038table2depdname01ᑁ02ᑁ03ᑁ04▭table3(result)depᨴᨴᨴ0110nullnull02108null03null5804nullnull91)selecta.dname,b.yjas'ᨴlc.yjasᨴd.yjas'ᨴ

3fromtablela,table2btable2c,table2dzwherea.dep=b.depandb.ᨴmon=*ᨴanda.depc.depandc.ᨴmon='ᨴanda.dep=d.depandd.ᨴmon='ᨴ'and2)selecta.dep,sum(casewhenb.mon=lthenb.yjelse0end)asᨴ'sum(casewhenb.mon=2thenb.yjelse0end)asᨴ'sum(casewhenb.mon=3thenb.yjelse0end)asᨴIsum(casewhenb.mon=4thenb.yjelse0end)as!ᨴIsum(casewhenb.mon=5thenb.yjelse0end)as"ᨴ'sum(casewhenb.mon=6thenb.yjelse0end)as#ᨴ'sum(casewhenb.mon=7thenb.yjelse0end)as$ᨴIsum(casewhenb.mon=8thenb.yjelse0end)as%ᨴIsum(casewhenb.mon=9thenb.yjelse0end)as&ᨴIsum(casewhenb.mon=10thenb.yjelse0end)asᓝᨴIsum(casewhenb.mon=llthenb.yjelse0end)asᓝᨴ'sum(casewhenb.mon=12thenb.yjelse0end)asᓝᨴ'fromtable2aleftjointablelbona.dep=b.dep8.()⍝☢,⚪.⊤0ḄIdᨵ3.456ᡠᨵ8.idḄ459:ᩭ<=>ᐳᨵ3@ᩩ45ᦪ஺selectid,Count(*)fromtbgroupbyidhavingcount(*)>1select*from(selectcount(ID)ascountfromtablegroupbyID)TwhereT.count>lC⚪DᎷFᨵGHIᡂ⊤Ktb
᝞L.:MNᑖᦪGPᦻ74GᦪH83Gᱥᳮ93T!Pᦻ74T!ᦪH84T!ᱥᳮ94UVᡂKWᑮ᝞L.YZ[DMPᦻᦪHᱥᳮT!748494G748393createtabletb(Mvarchar(10),Nvarchar(10),ᑖᦪint)insertintotbvalues^G'Pᦻ74)insertintotbvaluesK'G'ᦪH83)insertintotbvaluesKGᱥᳮ,93)

4insertintotbvalues^T!Pᦻ',74)insertintotbvaluesKT!ᦪH,84)insertintotbvaluesKT!,ᱥᳮ,94)goSQLSERVER2000☟᝱SQL,ᢣNbᨵPᦻஹᦪHஹᱥᳮ8N஺(de)selectMasM,max(caseNwhen'Pᦻthenᑖᦪelse0end)Pᦻmax(caseNwhenᦪHthenᑖᦪelse0end)ᦪHmax(caseNwhenᱥᳮthenᑖᦪelse0end)ᱥᳮfromtbgroupbyMSQLSERVER2000g᝱SQL,ᢣNhiPᦻஹᦪHஹᱥᳮ8N஺(de)declare@sqlvarchar(8000)set@sql=1selectM'select@sql=@sql+*,max(caseNwhen**'+N+**'thenᑖᦪelse0end)[•+N+»]'from(selectdistinctNfromtb)asaset@sql=0sql+'fromtbgroupbyM’exec(@sql)--SQLSERVER2005☟᝱SQL஺select*from(select*fromtb)apivot(max(ᑖᦪ)forNin(PᦻᦪHᱥᳮ))b--SQLSERVER2005g᝱SQL஺declare@sqlvarchar(8000)select@sql=isnull(@sql+'*)+NfromtbgroupbyNset@sql='[1+0sql+*]*exec(*select*from(select*fromtb)apivot(max(ᑖᦪ)forNin('+@sql+1))bf)/*C⚪DᙠstYZḄuẠswxᙳᑖzᑖWᑮ᝞dYZDMPᦻᦪHᱥᳮxᙳᑖzᑖT!74849484.00252G74839383.33250**/--SQLSERVER2000☟᝱SQL஺selectMMmax(caseNwhen'Pᦻthenᑖᦪelse0end)Pᦻ,max(caseNwhen'ᦪHthenᑖᦪelse0end)ᦪH,

5max(caseNwhenᱥᳮthenᑖᦪelse0end)ᱥᳮcast(aug(ᑖᦪ*1.0)asdecimal(18,2))xᙳᑖsum(ᑖᦪ)zᑖfromtbgroupbyM--SQLSERVER2000g᝱SQL஺declare@sqlvarchar(8000)set@sql=1selectM'select@sql=@sql+*,max(caseNwhen**'+N+**1thenᑖᦪelse0end)[,+N+f]'from(selectdistinctNfromtb)asaset@sql=@sql+',cast(avg(ᑖᦪ*1.0)asdecimal(18,2))xᙳᑖ,sum(ᑖᦪ)zᑖfromtbgroupbyM'exec(@sql)—SQLSERVER2005☟᝱SQL஺selectm.*,n.xᙳᑖ,n.zᑖfrom(select*from(select*fromtb)apivot(max(ᑖᦪ)forNin(PᦻᦪHᱥᳮ))b)m,(selectM,cast(avg(ᑖᦪ*1.0)asdecimal(182))xᙳᑖ,sum(ᑖᦪ)zᑖfromztbgroupbyM)nwherem.M=n.M--SQLSERVER2005g᝱SQL஺declare@sqlvarchar(8000)select@sql=isnull(@sql+*,*,**)+NfromtbgroupbyNexec(*selectm.*,n.xᙳᑖ,n.zᑖfrom(select*from(select*fromtb)apivot(max(ᑖᦪ)forNin(1+@sql+1))b)m,(selectM,cast(avg(ᑖᦪ*1.0)asdecimal(18,2))xᙳᑖ,sum(ᑖᦪ)zᑖfromtbgroupbyM)nwherem.M=n.MDdroptabletb00/**//*C⚪D᝞Zst~⊤ᣚdDᓽ⊤Y᪀ᦪ)DMPᦻᦪHᱥᳮG748393T!748494UVᡂ(Wᑮ᝞dYZ)DMNᑖᦪ

6T!Pᦻ74T!ᦪH84T!ᱥᳮ94GPᦻ74GᦪH83Gᱥᳮ93createtabletb(Mvarchar(10),Pᦻint,ᦪHint,ᱥᳮint)insertintotbvalues('G'74,83,93)insertintotbvalues('T!'74,84,94)go—SQLSERVER2000☟᝱SQL஺select*fromKselectMN=Pᦻᑖᦪ=PᦻfromtbunionallselectM,N=ᦪH'ᑖᦪ=ᦪHfromtbunionallselectMN=ᱥᳮᑖᦪ=ᱥᳮfromtb[torderbyMcaseNwhen'Pᦻthen1when'ᦪHthen2when'ᱥᳮthen3end--SQLSERVER2000g᝱SQL஺—¨ᵨ©ª⊤g᝱I᝱஺declare@sqlvarchar(8000)select@sql=isnull(0sql+1unionall',*')4-'selectM,N®='+quotename(Name,'11*)+',ᑖᦪ®=*+quotename(Name)+1fromtb,fromsyscolumnswherename!=NMandID=object_id(*tb')⊤tb,hᒹ¶ᑡ)MḄᐸ¹ᑡorderbycolidascexec(@sql+1orderbyM,)—SQLSERVER2005g᝱SQLoselectM,NᑖᦪfromtbunpivotKᑖᦪforNinKPᦻ®,ᦪH®ᱥᳮ®[[t--SQLSERVER2005g᝱SQL,eSQLSERVER2000g᝱SQL஺00/**//*IC⚪DᙠstḄYZsw.xᙳᑖzᑖWᑮ᝞dYZ:

7MNᑖᦪT!Pᦻ74.00T!ᦪH84.00T!ᱥᳮ94.00T!xᙳᑖ84.00T!zᑖ252.00GPᦻ74.00GᦪH83.00Gᱥᳮ93.00Gxᙳᑖ83.33Gzᑖ250.00select*fromselectMasM,N-Pᦻ,,ᑖᦪPᦻfromtbunionallselectMasM,N-ᦪH,ᑖᦪ-ᦪHfromtbunionallselectMasM,N-ᱥᳮ,ᑖᦪ-ᱥᳮfromtbunionallselectMasMN=xᙳᑖ,ᑖᦪ=cast((Pᦻ+ᦪH+ᱥᳮ)ᜧ1.0/3asdecimal(18,2))fromtbunionallselectMasM,NzᑖᑖᦪPᦻ+ᦪH+ᱥᳮfromtb)torderbyM,caseNwhen'Pᦻthen1when'ᦪH'then2when'ᱥᳮ'then3when'xᙳᑖthen4when'zᑖ'then5enddroptabletbᎷFbᨵ.table,)pages,ᨵ!.¾¿id,urltitle,bodyÀ☢ÁÂ3Ã⚓Ã⚓zoḄurlᙢᙬtitleÃ⚓ḄᑁÇᯠÉÊᵨ•.sql9ËÌurlᓛÎḄ᣸ᙠᨬÑtitleᓛÎḄᐸÒ,bodyᓛÎᨬÉÓᨵÔÕ¾¿ᓛÎḄhÖ×஺selecta.[id],a.markfrom(select[page].[id],100asmarkfrom[page]where[page].[url]like,%baidu%,unionselect[page].[id],50asmarkfrom[page]where[page].[title]like'%baidu%'unionselect[page].[id],10asmarkfrom[page]where[page].[body]like'%baidu%')asaorderbymark.desc

8⊤Dᡂ⊤MNᑖᦪGPᦻ81GᦪH75T!Pᦻ56T!ᦪH90Ø"Pᦻ81Ø"ᦪH100Ø"ÙP49KᐸÚᵨᡝÜÝḄ45ᜧÞßàáᐭ[ã:ᡂᐰᔠæḄHIçèKᒹ¶MஹNஹᑖᦪ[éDᑖᦪᙠ60sê)ᔠæselect*fromscorewheres_namenotinselects_namefromscorewherescore<60
ᡈὅDselect*fromscorewheres_nameinselects_namefromscoregroupbys_namehavingmin(score)>=60)(3)⊤Dᖪî⊤ïᙢðñòZóô2.5òZõᓭ1.9òZ!÷3øùúø1.5øùᓅü2.4KᐸÚᵨᡝÜÝḄ45ᜧÞßàáᐭ[ã:xᙳðñᙠ2ᐗdḄᖪîselectfromᖪî⊤groupbyhavingavgKðñ[v24
⊤Dþὃ
⊤ὃᡂ2006001ᦻ119

92006001ᦪ1082006002ᱥᳮ1422006001ᓄ1362006001ᱥᳮ1272006002ᦪ14920060021102006002ᦻ1052006001982006002ᓄ129ᐸᵨᡝḄᜧ!"#ᐭ%&'(ὃ)ᑖᙠ600,-Ḅ.ὃselectὃfrom(ὃ
⊤groupbyὃhavingsumᡂ%/6005
⊤01(ὃ
⊤ὃᦪᦻᱥᳮᓄ2006001108119981271362006002149105110142129ᐸᵨᡝḄᜧ!"#ᐭ%&'(ὃ)ᑖᙠ600,-Ḅ.ὃselectὃfrom(ὃ
⊤whereᦪ+ᦻ++ᱥᳮ+ᓄ%ு60045ᑖ%6%⊤01clubidgenderage67M1968F3069F2770F1671M32……ᐸ89:ᦪ;'?@A5BᵱឋEᕒG᝕ឋEᕒḄ)ᦪselectgender,countid
fromclubgroupbygenderI%⊤01teamIDnumberJ%Namevarchar2J%1a2b3b

104a5c6c⌕L:᡻"6Oᑤ◀RSTNameᑡ-ᨵWXYSZ[ᶇID]ᑡ-^_Ḅ`᝞1ᑤ◀bḄcde᝞f1ID(numberJ)Name(varchar2J)1a2b5c<ᑏ'SQLR஺deletefromteamwhereidnotin(selectmin(al.id)fromteama1wherea1.name=team.name)deletefromteamwhereidnotin(selectmin(id)fromteamgroupbyname)(k)⊤01studentnamecoursescorel☘ᦻ72noᦪ72lo81l☘ᱥᳮ67pqᓄ98lrᱥᳮ70l☘ᓄ76=>'“l”u.vwᙳᡂᜧy75ᑖḄ.
select*fromstudentwherenamein(selectnamefromstudentwherenamelike'l{'groupbynamehavingavg(score)>75)SQLᐺ☢:⚪(6)6⚪1ᳮ
Sq3O⊤1S(S#,SN,SD,SA)S#,SN,SD,SAᑖ⊤Sᕒu0Sᡠᓫ,ᕒ

11C(C#,CN)C#,CNᑖ⊤S0SC(S#,C#,G)S#,C#,Gᑖ⊤Sᡠ⌱Ḅ,¡ᡂ(1)£ᵨ᪗SQL¥ᝅR=>⌱§0‘©ᦈ«Ạ’ḄᕒGu0®(2)£ᵨ᪗SQL¥ᝅR᩵>⌱§‘C2'Ḅᕒu0Gᡠᓫ®(3)£ᵨ᪗SQL¥ᝅR=>±⌱§‘C5"Ḅᕒu0Gᡠᓫ®(4)=>⌱§µḄᕒ¶ᦪ®(5)=>⌱§·¸5¹ḄᕒGᡠᓫ®droptableS;droptableC;droptableSC;createtableS(S#varchar(10),SNvarchar(25),SDvarchar(25),SAint)createtableC(C#varchar(10),CNvarchar(25))createtableSC(S#varchar(10),C#varchar(10),GintPrimaryKey(Stt,C#))

12insertintoSvalues('10001','Students!*,*departmentT,23)insertintoSvalues(,10002*,1Students2*,*departmentT,24)insertintoSvalues('10003'Students3'department?’,25)insertintoSvalues('10004','Students4','department2,,26)insertintoSvalues('10005'Students5','department3,,23)insertintoSvalues('10006'Students6'departments',24)insertintoSvalues('10007'Students7','departments*,25)insertintoSvalues('10008','Students8,,*department],25)insertintoCvaluesØ1','ᦪ’%insertintoCvaluesSC2S,SᱥᳮS%insertintoCvaluesÙ3','ᓄ’%insertintoCvaluesSC4S,SS%insertintoCvalues'C5','vᦻS%insertintoCvalues'C6','©ᦈ«Ạ'%insertintoCvaluesSC7S,SÚÛS%insertintoCvalues('C8','Ü')insertintoSCvalues('10001','Cl',67)insertintoSCvaluesC10001S,SC2S,77)insertintoSCvaluesC1000r/C3\87)insertintoSCvaluesC10001,/C4\97)insertintoSCvalues(T0001','C5',57)insertintoSCvalues('1000147)insertintoSCvalues(T0002','Cl'62)insertintoSCvaluesC10002,/C2\72)insertintoSCvaluesC10002S,SC3S,82)insertintoSCvaluesC10002,/C4,,92)insertintoSCvalues(T0002','C5',52)insertintoSCvalues('10002'C6',42)

13insertintoSCvalues('10004'C2',74)insertintoSCvalues('10004'C5',54)insertintoSCvalues('10004'C6',44)-(1)£ᵨ᪗SQL¥ᝅR=>⌱§0‘©ᦈ«Ạ’ḄᕒGu0®6áâ61selectS#,SNfromSwhereS#in(selectS#fromC,SCwhereC.C#=SC.C#andC.CN='©ᦈ«Ạ')6áâI1selectS.S#>S.SNfromSinnerjoin(selectS#fromCleftjoinSConC.C#=SC.C#whereC.CN=>©ᦈ«Ạ')TonT.S#=S.S#~(2)£ᵨ᪗SQL¥ᝅR=>⌱§'C2"Ḅᕒu0Gᡠᓫ®6áê1selectS.SN,S.SDfromS,SCwhereS.S#=SC.S#andSC.C#='C2'6(3)£ᵨ᪗SQL¥ᝅR=>±⌱§‘C5,Ḅᕒu0Gᡠᓫ®6áê1selectdistinctS.SN,S.SDfromSwhereS.S#notin(selectS.S#fromS,SCwhereS.S#=SC.S#andSC.C#='C5')-(4)=>⌱§µḄᕒ¶ᦪ®6áâ61selectᕒ¶ᦪIcount(distincts#)fromsc6áâI1selectcount(*)asᕒ¶ᦪfrom(selectdistinctSC.S#fromSC)t-(5)=>⌱§·¸5¹ḄᕒGᡠᓫ®6áâ,1selectS#,SDfromSwhereS.S#in(selectSC.S#fromSCgroupbySC.S#havingcount(*)>5)

146áâI1selectS#,SDfromSwhereS#inselectS#fromSCgroupbyS#havingcountdistinctC#%ு5%I⚪1createtabletesttableiidintIDENTITY,departmentvarchar12%%insertintotesttablelvalues'ïð'%insertintotesttablelvalues'ñò'%insertintotesttablelvalues'ób'%cd1iddepartment1ïð2ñò3óbcreatetabletesttable2idintIDENTITY,dptIDint,namevarchar12%%insertintotesttable2values1,'lk'%insertintotesttable2values1,'p4'%insertintotesttable2values2,'nô'%insertintotesttabie2values3,‘õö’%

15insertintotesttable2values4,'▰Ù'%insertintotesttable2values5,'▰Ù'%selectt2.id,t2.dptID,tl.department,t2.namefromtesttable2t2leftjointesttableitlon11.id=t2.dptIDselect*fromtesttable2ᵨ6ᩩSQLRSùúûü᝞fcdiddptIDdepartmentname11ïðlk21ïðp432ñònô43óbõö54ý¶▰Ù6áê16áâ61selectt2.id,t2.dptII%,tl.department,t2.namefromtesttable2t2leftjointesttabiel11on11.id=12.dptID6áâI1SELECTt2.id,t2.dptID,ISNULLtl.department,1ý¶'%dptName,t2.nameFROMtesttableltlrightjointesttable2t2ont2.dptID=tl.ID☢!"#$%&☢'ᫀḄ*+selectt2.id,t2.dptID,tl.department,t2.namefromtesttableltl,testtable2t2where11.id=t2.dptIDselectt2.id,t2.dptID,tl.department,t2.namefromtesttable2t2innerjointesttableltlontl.id=t2.dptID23⚪5ᨵ⊤A,#᪀᝞:

16A:p_IDp_Nums_id11001112022801311013803ᐸ=5p>D?@AID,p_Num?@ABCD,s_id?EBID஺GᵨSQLIJK&⊤=ḄᦪMᔠO,ᔠOPḄᦪM?5p_IDsl_ids2_ids3_id110120280031108ᐸ=5sl_id?EB1ḄBCDQs2id?EB2ḄBCDQs3id?EB3ḄBCD஺᝞$R@AᙠTEB=UBCDQVWXY0Z[஺createtableA(p_IDint,p_Numint,sidint)insertintoAvaluesd,10,01)insertintoAvalues(1,12,02)insertintoAvalues(2,8,01)insertintoAvalues(3,11,01)insertintoAvalues(3,8,03)\'5selectp_id,sum(casewhens_id=lthenp_numelse0end)assl_id,sum(casewhens_id=2thenp_numelse0end)ass2_id,

17sum(casewhens_id=3thenp_numelse0end)ass3_idfromAgroupbypid2]⚪5—1.!"A(ID,Name)⊤=231^40ᩩ`aQIDb?cdefYgYhijkḄᑡmcreatetableA(idintIDENTITY,Namevarchar(25))-1.!"A(ID,Name)⊤=231^40ᩩ`aQIDb?cdefYgYhijkḄᑡm\'5selecttop10*fromAwhereID>(selectmax(ID)from(selecttop30IDfromAorderbyid)T)orderbyid2o⚪5!"A(ID,Name)⊤=CᙠIDpq3rs&Ḅ`droptableAcreatetableAidint,Namevarchar(25))insertintoAvalues(1,'a')insertintoAvalues(2,*a*)insertintoAvalues(3,'a')insertintoAvalues(1,*a')insertintoAvalues(2,Ja,)insertintoAvalues(3,1a*)

18insertintoAvalues(4,'a')insertintoAvalues(1,*a*)\'5selectid,namefromAwhereidin(selectidfromAgroupbyidhavingcount(id)>3)orderbyid2t⚪5u⊤Course:courseidcoursenamescore1java702oracle903xml404jsp305servlet80?vwx▅zQ!"{⊤PḄ#$|}᝞~ᑖᦪ?60:courseidcoursenamescoremark1java70pass2oracle90pass3xml40fai14jsp30fail5servlet80passᑏ{!"஺createtableCourse(courseidintIDENTITY,coursenamevarchar(25),scoreint)

19insertintoCoursevalues('java',70)insertintoCoursevalues('oracle',90)insertintoCourse(xml',40)insertintoCoursevalues(jsp',30)insertintoCoursevalues('servlet',80)\':——oracle5selectcourseid,coursename,score,decode(sign(score-60),-1,*fail*,'pass*)asmarkfromcourseSQLServer5select*,(casewhenscore<60then'failed'else'pass'end)asmarkfromCourse2⚪:ᨵ⊤:emp(id,name,age)⌕:ᑡᡠᨵpqḄḄ`amcreatetableemp(idintIDENTITY,namevarchar(25),ageint)insertintoempvalues('Zhang11,26)insertintoempvalues('Zhang2*,27)insertintoempvalues('Zhang3J,28)insertintoempvalues('Zhang11,26)insertintoempvalues('Zhang2,,27)insertintoempvalues(,Zhang3,,29)insertintoempvalues(,ZhangI*,26)insertintoempvaluesCZhang2*,27)

20insertintoempvalues(,Zhang3,,28)insertintoempvalues('Zhangl',26)insertintoempvaluesZhang41,22)insertintoempvalues(,WangT,27)insertintoempvalues('wang21,28)insertintoempvalues('Wang2',26)insertintoempvalues(*WangT,22)ᑡᡠᨵpqḄḄ`am\§•5⌕©⍝ᡠᨵᨵpq«ᧇQ✌ᐜ¯°©⍝±pqv5selectid,name,agefromempwherenamein(selectnamefromempgroupbynamehavingcount(*)>1)\§²5᝞$³´µ¶u⊤·¸¹ºQᜧx2%¼ᩩ`a½¾ḄXYᔠḄQXᨵ5select*fromempwhere(selectcount(*)fromempewheree.name=emp.name)>1\§35᝞$ᨵ¿᜜½¾ḄÁÂg%᝛Ľ¾VW¼ᩩ`aÅᔠ⌕5select*fromempwhereexists(select*fromempewheree.name=emp.nameande.idOemp.id)ᡈ5selectdistinctemp.*fromempinnerjoinempeonemp.name=e.nameandemp.idOe.id2Ç⚪5ᨵÈ⊤5emp(name,age)Tom16Sun14Tom16Tom16⌕5ÉÊᣵᡠᨵÌÍḄpq`acreatetableemp(namevarchar(20),

21ageint)insertintoempvaluesCTom,,16)insertintoempvaluesCSun*,14)insertintoempvalues('Tom',16)insertintoempvalues(fTomQ,16)\§•5ÎÉdistinctஹgroupbyÉÊpq5selectdistinct*fromempᡈselectname,agefromempgroupbyname,ageÑÒ◤⌕ḄᦪMQ᝞$esÔᵨÕÖ⊤Xᨵ\§5selectdistinct*into#tmpfromempdeletefromempinsertintoempselect*from#tmpØY᝞$gesÔᵨÕÖ⊤QVRÙWÚmaltertableempaddchkintidentity(1,1)pq`aes⊤Û?5select*fromempwhere(selectcount(*)fromempewheree.name=emp.name)>1⌕ᑤ◀ḄY5deletefromempwhere(selectcount(*)fromempewheree.name=emp.nameande.chk>=emp.chk)>1ßàáâḄᑡᑤᣵQJ#$஺altertableempdropcolumnchk--)¿•ãä5åæselectmin(chk)fromempgroupbynamehavingcount(*)>1ÑÒᨵpqḄ`achkᨬèḄéQxYesdeletefromempwherechknotin(selectmin(chk)fromempgroupbyname)2ê⚪:

22ᨵëU⊤5emp(emp_no,name,age)001Tom17002Sun14003Tom15004Tom16⌕îᡂðᑡÂcreatetableemp(empnoint,namevarchar(20),ageint)insertintoempvalues(001,*Tom*,17)insertintoempvalues(002,1Sun*,14)insertintoempvalues(003,*Tom>,15)insertintoempvalues(004,'Tom',16)~1ñᨬòᓫḄô§:altertableempaddchkintidentity(1,1)ᡈselect*,identity(int,1,1)chkinto#tmpfrom(select*fromempaltertableempdropcolumnchk—᝞$◤⌕õᑴ÷ðÙWÚmselect*,identity(int,1,1)chkinto#tmpfromorderbyagedeletefromempaltertableempaddchkintinsertintoempselect*fromtttmpselect*from#tmpdroptabletttmp

23ফᎷ᝞gesúᦋ⊤#᪀QÙWÚm᝞$gesü*ᑖ´ᩩ`aYýᨵÚ§ḄQselectemp.*,(selectcount(*)fromempewheree.emp_no<=emp.emp_no)fromemporderby(selectcount(*)fromempewheree.emp_no<=emp.emp_no)2ᓝ⚪5ÿ⊤⌱ᦪᱥᳮᦻᓄᦪᓄᦻᦪᱥᳮᦻᦪᱥᳮᦻᕜᦪᕜᱥᳮ⚪⌱ᦪᱥᳮᦻḄ!"᝞$ᑏ&'(SQL)⌱

24ᦪᱥᳮᦻᦪᱥᳮᦻ⚪*+,⌱-ᦪᱥᳮᦻḄ!"᝞$ᑏ&'(SQL)⌱ᦪᱥᳮᦻᦪᱥᳮᦻᦪᱥᳮᦻcreatetablecourse(Namevarchar(25),CNamevarchar(25))insertintocoursevalues('''ᦪ’)insertintocoursevalues('''ᱥᳮ')insertintocoursevalues('''ᦻ')insertintocoursevaluesJ'‘'ᓄ'L

25insertintocoursevalues('ᦪ')insertintocoursevalues(''ᦻ')insertintocourse('ᓄ')insertintocoursevalues('’,ᦪ’)insertintocoursevalues('’ᱥᳮ')insertintocoursevalues(''ᦻ')insertintocourse('’ᦪ’)insertintocoursevalues('ᱥᳮ')insertintocoursevalues('ᦻ')insertintocoursevalues('ᕜ’,ᦪ')insertintocoursevalues('ᕜ'ᱥᳮ')select*fromcourse⚪⌱ᦪᱥᳮ,ᦻḄ᩵!"᝞P.ᑏ&'(SQL)RS•,selectA.Name,B.CNamefrom(selectT.Namefrom(selectName,CNamefromCoursewhereCNamein('ᦪ'‘ᱥᳮ‘'ᦻ'))TgroupbyNamehavingcount(*)=3)A,(selectName,CNamefromCoursewhereCNamein('ᦪ''ᱥᳮ''ᦻ'))BwhereA.Name=B.NameandA.Namenotin(selectNamefromCoursegroupbyNamehavingcount(*)>3)RSselect*fromcoursewherenamein(selectnamefromcoursewhereCNamein(,ᦪ''ᱥᳮ''ᦻ')groupbynamehavingcount(*)=3)andnamenotin(selectnamefromcoursegroupbynamehavingcount(*)>3)⚪+,⌱-ᦪᱥᳮᦻḄ᩵!"᝞Pᑏ&'(SQL)RS:

26selectA.Name,B.CNamefrom(selectT.Namefrom(selectName,CNamefromCoursewhereCNamein('ᦪ‘'ᱥᳮ''ᦻ'))TgroupbyNamehavingcount(*)=3)A,(selectName,CNamefromCoursewhereCNamein('ᦪ?Jᱥᳮ''ᦻ'))BwhereA.Name=B.NameRS*select*fromcoursewherenamein(selectnamefromcoursewhereCNamein('ᦪ''ᱥᳮ‘'ᦻ')groupbynamehavingcount(*)=3)SQLcᐺ☢f⚪g(*)hᓝ⚪ᨵ⊤students(name,class,grade),kᵨ᪗nsql)pᡂnameclassgradeᦪ81ᦻ70ᦪ90ᦻ60ᦪ100ᦻ90x81⌕zᵨsql){&ᔜ}~ᜧ80ᑖḄ+createtablestudents(namevarchar(25),classvarchar(25),gradeint)

27insertintostudentsvalues('''ᦻ'20)insertintostudentsvalues('‘ᦪ'90)insertintostudentsvalues('‘x'50)insertintostudentsvalues(''ᦻ'8Dinsertintostudentsvalues('‘ᦪ'60)insertintostudentsvalues('‘x',90)insertintostudentsvalues('*‘ᦪ'81)insertintostudentsvalues('*‘x',90)insertintostudentsvalues(''ᦪ'83)insertintostudentsvalues('‘x'90)insertintostudentsvalues(''ᓄ'90)--⌱&ᡠᨵᡂᜧ80ᑖḄ——-----RS------selectnamefromstudentsgroupbynamehavingmin(grade)>80-----RS*------selectdistinctNamefromstudentswheregrade>80andNamenotin(selectNamefromstudentswheregrade<80)-----RS------selectdistinctnamefromstudentswherenamenotin(selectnamefromstudentswheregrade<=80groupbyname)——RS------selectnamefromstudentsgroupbynamehavingnamenotin(selectnamefromstudentswheregradeீ*80)hᓝ*⚪⊤Ḅ!᪀:ᡂᦻ20

28ᦪ30x50ᦻ70ᦪ60x90᪵select)ᡂ$!᪀:ᦻᦪx203050706090createtablestudents(namevarchar(25),classvarchar(25),gradeintinsertintostudentsvaluesJ‘''ᦻ’20insertintostudentsvaluesJ'''ᦪ'90insertintostudentsvalues
'''x'50insertintostudentsvaluesJ'''ᦻ'81insertintostudentsvaluesJ'''ᦪ'60insertintostudentsvaluesJ'''x'90RselectA.Name,A.gradeasᦻB.gradeasᦪ,C.gradeasxfromstudentsA,studentsB,studentsCwhereA.Name=B.NameandB.Name=C.NameandA.class='ᦻ'andB.class='ᦪ'andC.class='x'hᓝ⚪:

29ᡃᙠᨵ⊤⊤createtableuserinfo(idint,usernamevarchar(32),uidint)createtablecheckinfo(idint,checktypevarchar(32)&Ḅ(¡¢£Ꮇ¥Ꮇ)u_idint)⊤u_idᐵὶḄ¨᩵&©ᵨᡝḄ«ᨴ&Ḅ®¯᝞°1ᨴ±¡¢&²³ᜩ¥Ꮇ²³ᜩ£Ꮇ²³ᜩµ᝞username£Ꮇ(ᜩᦪ)¥Ꮇ(ᜩᦪ)£Ꮇ(ᜩᦪ)1552createtableuserinfo(idint,usernamevarchar(32),u_idint)createtablecheckinfo(idint,checktypevarchar(32),&Ḅ(¡¢£Ꮇ¥Ꮇ)

30u_idintLdeletefromuserinfoinsertintouserinfovaluesJ1,*userf,1LinsertintouserinfovaluesJ2,,user2,,2LinsertintouserinfovaluesJ3,*user3,,3LinsertintouserinfovaluesJ4,*user4,,4Linsertintocheckinfovalues஺'¡¢'1LinsertintocheckinfovaluesJ2,'¡¢'1LinsertintocheckinfovaluesJ3,'£Ꮇ'1LinsertintocheckinfovaluesJ4,'¡¢'2LinsertintocheckinfovaluesJ5,'¥Ꮇ'2LinsertintocheckinfovaluesJ6,'£Ꮇ'2LinsertintocheckinfovaluesJ7,'ºE¢'2LinsertintocheckinfovaluesJ8,'£Ꮇ'2LinsertintocheckinfovaluesJ9,'ºE¢'3LinsertintocheckinfovaluesJ10,'¥Ꮇ'3LinsertintocheckinfovaluesJ11,'£Ꮇ'3LinsertintocheckinfovaluesJ12,'¡¢'3LinsertintocheckinfovaluesJ13,'¡¢3LinsertintocheckinfovaluesJ14,'¡¢'3LinsertintocheckinfovaluesJ15,'¡¢'3LinsertintocheckinfovaluesJ16,'£Ꮇ'3LinsertintocheckinfovaluesJ17,'¡¢'4LinsertintocheckinfovaluesJ18,'¥Ꮇ4LinsertintocheckinfovaluesJ19,'£Ꮇ'4LinsertintocheckinfovaluesJ20,'¡¢'4LinsertintocheckinfovaluesJ21,'¥Ꮇ'4LinsertintocheckinfovaluesJ22,'£Ꮇ'4L

31insertintocheckinfovalues(23,'¥Ꮇ'4)insertintocheckinfovalues(24,'£Ꮇ'4)RS:selectb.*,m.¡¢,m.¥Ꮇ,m.£Ꮇfromuserinfobjoin(selecta.u_id,count(casewhena.checktype='£Ꮇ'then'1'end)£Ꮇcount(casewhena.checktype='¡¢'then'1'end)¡¢count(casewhena.checklype*'¥Ꮇ'then'rend)¥Ꮇfromcheckinfoagroupbya.u_id)monm.uid=b.uidRS*:selectb.*,ml.¡¢,m2.£Ꮇ,m3.¥Ꮇfromuserinfobleftjoin(selecta.u_id,count(a.checktype)¡¢fromcheckinfoawherea.checktype='¡¢'groupbya.uid)mlonb.uid=ml.uidleftjoin(selecta.u_id,count(a.checktype)£Ꮇfromcheckinfoawherea.checktype=‘£Ꮇ'groupbya.u_id)m2onb.u_id=m2.u_idleftjoin(selecta.u_id,count(a.checktype)¥Ꮇfromcheckinfoawherea.checktype='¥Ꮇ'groupbya.u_id)m3onb.u_id¼3.u_idhᓝ⚪½¾¿⁐ᦪÁ½¾1Â⁐100½¾1Ã⁐80½¾2Ã⁐103½¾2Â⁐NULL½¾2Â⁐89

32½¾1Â⁐1001:ᢥ½¾ᑖÅᑡ&ᔜᖪ¾ÈÂ⁐²Ã⁐Ḅᖪ¾ÉÊËÌᦪÁcreatetableproducts(namevarchar(20),colorchar(20),quantitiesintinsertintoproductsvalues('½¾1',‘Â⁐'100)insertintoproductsvalues('½¾1','Ã⁐'80)insertintoproductsvalues('½¾2'Â⁐'null)insertintoproductsvalues('½¾2'Ã⁐'103)insertintoproductsvalues(,½¾2',‘Â⁐'89)insertintoproductsvalues(,½¾1','Â⁐'100)——RߟhÎ&©Ïᖪ¾Èᐲ⁐ÑÂ⁐ᦪÁʽ¾ÉÂ⁐selectname,sum(quantities)fromproductswherecolor='Â⁐'groupbynameÃ⁐selectname,sum(quantities)fromproductswherecolor=‘Ã⁐'groupbyname--h*Î&⌕zḄ!"selecttl.name,tl.x-t2.xasbalancefrom(selectname,sum(quantities)asxfromproductswherecolor'Â⁐'groupbyname)tl,(selectname,sum(quantities)asxfromproductswherecolor='Ã⁐'groupbyname)t2where11.x>t2.xand11.name=:t2.name

33hᓝ⚪:⊤È⌱ÓÔ5}ḄÕ!createtablestudents(idintIDENTITY,namevarchar(20),electivecoursevarchar(20))insertintostudentsvaluesstudentTcourseT)insertintostudentsvalues(*student1course2,)insertintostudentsvalues(,studentTcourseB*)insertintostudentsvalues(,studentTcourse41)insertintostudentsvalues(*studentTcourse6,)insertintostudentsvalues(fstudentTcourseG,)insertintostudentsvalues(*student2,coursel)insertintostudentsvalues(,student2,course2,)insertintostudentsvalues(,student2,courses*)insertintostudentsvalues(*student2lcourse4,)insertintostudentsvalues(fstudent?,courses*)insertintostudentsvaluesCstudent3,coursel,)insertintostudentsvalues(,studentB,course2*)insertintostudentsvalues(*students,courses*)insertintostudentsvaluesstudentB,course4,)insertintostudentsvalues(*student4,coursel*)insertintostudentsvalues(fstudent4,course2,)insertintostudentsvalues('studentd)course3,)insertintostudentsvalues(,student4,course4*)insertintostudentsvalues(*student4*,Jcourses*)

34insertintostudentsvalues(Jstudent4,course6))insertintostudentsvalues('student]course7*)insertintostudentsvalues(,student5,course2,)insertintostudentsvalues(*students*courseB*)insertintostudentsvalues(*students)course4,)insertintostudentsvalues(*student5,courseS*)insertintostudentsvalues(,student5,course6')insertintostudentsvalues(,student5,course7,)insertintostudentsvalues(*students,course8,)insertintostudentsvalues(,students)course9))insertintostudentsvalues(fstudentG,course7*)insertintostudentsvalues(*studentG*courses1)insertintostudentsvalues(*student61course9,)Rselectnamefromstudentsgroupbynamehavingcount(elective_course)>=5hᓝ⚪DbTable⊤ᨵᑡid,name,data,ᐸÈnameᑡÛ©ÜÝᨵ'{data}᝞hÜÛ'aa{data}bb',h*Ü’abc{data}cd’⌕zᵨÞ(dataᑡḄᦪßàᣚᣵ’{data}',sql¨ᑏcreatetableDbTable(IDintIDENTITY,namevarchar(20),datavarchar(lO))insertintoDbTablevalues(,al{data}bb,,*T)insertintoDbTablevalues('a2{data}bb','2')

35insertintoDbTablevalues('a3{data}bb''3')insertintoDbTablevalues('a4{data}bb','4')insertintoDbTablevalues('a5{data}bb','5')RupdateDbTab1esetname=replace(name,?(data),,data)select*fromDbTablehᓝ⚪åᙠ⊤table(FID,FCLASS,FSSCORE),Õéᑖêë⊤ஹíîஹᡂ஺ᵨᨬðᦔஹᨬòᓫḄSQL)ᑡ&ôᦪᜧ30ḄᔜíᨬðᡂḄᑡ⊤õöíîஹᡂÕé஺createtableF3(FIDvarchar(20),FLASSvarchar(20),FSSCOREint)insertintoF3values('S_Namel,,Classr,67)insertintoF3values('S_Name2','ClassT,57)insertintoF3values(,S_Name3,,,Class1\27)insertintoF3valuesCS_Name4/ClassT,37)insertintoF3valuesCS.NameS*/Class97)insertintoF3values('S_Name6','Class2,,67)insertintoF3valuesCS_Name7,Class2,,57)insertintoF3values('S_Name8,,'Class2,,27)insertintoF3values('S_Name9,,yClass2,,37)insertintoF3values('SNamelO','Class2,,97)

36insertintoF3values(*S_NamelT,*Class2,,37)insertintoF3values('S_Namell2,Class2,97)insertintoF3values('S_Namel7,,Class3,,57)insertintoF3valuesCS_Namel8,Class3,27)insertintoF3values('S_Namel9','Class3',37)insertintoF3values('S_NamellO,,*Class3,,88)insertintoF3valuesCS.Namelir/Classi,37)insertintoF3valuesCS_Namelll2/Class3,67)insertintoF3values('S_Namell7','Class4',57)insertintoF3values('S_Namell8,/Classi,27)insertintoF3values('S_Namell9,,Class4,37)insertintoF3valuesCS_NamelllO,Class4,82)insertintoF3values('S_NamellU','Class4',37)insertintoF3valuesCS_NamellU2,,*Classi,67)insertintoF3valuesCS_Namelllll,Class5,,37)insertintoF3valuesCS_Namelllll2,Class5,,67)-R-úûüýfᦪßþÛ-5ôᦪRSselectF3.FLASS,Max(FSSCORE)fromF3groupbyFLASShavingcount(*)>=5!"#$%!&!'%᩵)*+ᦪᜧ.5Ḅ01!selectFLASS,count(*)asTotalfromF3groupbyFLASShavingcount(*)>=5!&$'%7)*ᡠᨵ+ᦪᜧ.5Ḅ01Ḅᡠᨵ:;<=!select*fromF3whereFLASSin(selectFLASSfromF3groupbyFLASShavingcount(*)>=5)!&?'%@AB&$'Ḅ<=᪷DFCLASSᑖF7)!selectFLASS,Max(FSSCORE)fromF3whereFLASSin(selectFLASSfromF3groupbyFLASShavingcount(*)>=5)groupbyFLASS

37!"#?%selectFTASS,max(fsscore)from(select*fromF3whereFLASSin(selectFLASSfromF3groupbyFLASShavingcount(*)>=5))TgroupbyFLASS&ᓝI⚪%ᨵ!KὁM⊤Teachers,OPQTRD,T_NAMEVᨵ!K:;⊤Students,OPQSRD,S_NAMEVXᨵ!K01⊤Classes,OPQT_ID,SRD,CNAME,ᐸ[C_NAMEḄ\]^ᨵ‘ᜧ0‘`ab0’ad7)*eᔠᩩhḄὁMḄiOaᩩhQὁMᙠᜧ0[kḄ:;ᦪᜧ.lὁMᙠb0[kḄ:;ᦪ஺createtableTeachers(T_IDint,T_NAMEvarchar(20))createtableStudents(S_IDint,S_NAMEvarchar(20))createtableClasses(T_IDint,S_JDint,C_NAMEvarchar(20))insertintoTeachersvalues(1,*TT)insertintoTeachersvalues(2,'T2')insertintoTeachersvalues(3,*T3,)

38insertintoTeachersvalues(4,1T4,)insertintoTeachersvalues(5,*T5,)insertintoStudentsvalues஺a'SI')insertintoStudentsvalues(2,'SI')insertintoStudentsvalues(3,JST)insertintoStudentsvalues(4,*SI*)insertintoStudentsvalues(5,*Sf)insertintoStudentsvalues(6,*ST)insertintoStudentsvalues(7,1SI,)insertintoStudentsvalues(8,'SI')insertintoStudentsvalues(9,*Sf)insertintoStudentsvalues(10,1ST)insertintoStudentsvalues(ll,1ST)insertintoStudentsvalues(12,'Si')insertintoStudentsvalues(13,1ST)insertintoStudentsvalues(14,'Si')insertintoStudentsvalues(15,'SI')insertintoStudentsvalues(16,'SI')insertintoClassesvalues(1,1,'ᜧ0')insertintoClassesvalues(1,2,'ᜧ0')insertintoClassesvalues(1,3,'b0')insertintoClassesvalues(1,4,'ᜧ0')insertintoClassesvalues(1,13,‘ᜧ0')insertintoClassesvalues(1,14,'ᜧ0')insertintoClassesvalues(1,15,'b0')insertintoClassesvalues(1,16,'ᜧ0')insertintoClassesvalues(2,1,‘ᜧ0')insertintoClassesvalues(2,2,'b0')

39insertintoClassesvaluess2,3,'ᜧ0'tinsertintoClassesvaluess2,4,'ᜧ0'tinsertintoClassesvaluess2,16,'b0'tinsertintoClassesvaluess2,15,'b0'tinsertintoClassesvaluess2,14,*b0'tinsertintoClassesvaluess3,5,'ᜧ0’tinsertintoClassesvaluess3,6,'b0'tinsertintoClassesvaluess3,7,'ᜧ0'tinsertintoClassesvaluess4,4,'ᜧ0'tinsertintoClassesvaluess4,5,'ᜧ0'tinsertintoClassesvaluess4,6,'b0'tinsertintoClassesvaluess4,7,'b0'tinsertintoClassesvaluess4,8,'b0'tinsertintoClassesvaluess5,9,'ᜧ0'tinsertintoClassesvaluess5,10,'b0'tinsertintoClassesvaluess5,11,'b0'tinsertintoClassesvaluess5,12,'b0't!&!'%7)*z{ὁMᡠkḄb0Ḅ+ᦪ-------selectT_ID,count(*)asxfromClasseswhereC_Name='b0'groupbyT_ID!&$'%7)*z{ὁMᡠkḄᜧ0Ḅ+ᦪ!!selectTID,count(*)asxfromClasseswhereC_Name='ᜧ0'groupbyT_ID!&?'%ᙠ}☢!$'ḄẠ$7)*ᜧ0+ᦪᜧ.b0+ᦪḄὁM!selectT_NAMEfromTeacherst,(selectT_ID,count(*)asxfromClasseswhereC_Name='b0'groupbyT_IDtTl,sselectT_ID,counts*tasxfromClasseswhereC_Name=,ᜧ0'groupbyT_IDtT2

40whereTl.x

41insertintoemployeesvalues(1,'Zhang3')insertintoemployeesvalues(1,'Zhang4,)insertintoemployeesvalues(1,'Zhang5*)insertintoemployeesvalues(2,aLi3a)insertintoemployeesvalues(2,'Li4')insertintoemployeesvalues(2,'145')insertintoemployeesvalues(2,'Li6')insertintoemployeesvalues(3,'Zhao3')insertintoemployeesvalues(3/Zhao4')insertintoemployeesvalues(3,'Zhao51)insertintoemployeesvalues(4,'Chen41)insertintoemployeesvalues(4,'Chen5')insertintoemployeesvalues(5,'Zhu4,)!"#!----selectb.id,a.Name,count(b.id)asemployeecountfromdepartmentsaleftjoinemployeesbona.id=b.idgroupbyb.id,a.Name——ᡈselectb.id,a.Name,count(*)asemployeecountfromdepartmentsaleftjoinemployeesbona.id=b.idgroupbyb.id,a.Name!!"#$!!selectt.idas'Id',t.nameas'Name',count(*)as'EmployeeCount,from(selectd.id,d.namefromdepartmentsasdinnerjoinemployeesaseond.id=e.id)tgroupbyt.id,t.name!"#?——selecta.id,a.Name,count(b.id)asemployeecountfromdepartmentsaleftjoinemployeesbona.id=b.idgroupbya.id,a.Name

42&$ᓝ⚪%ᙠOracleᦪD°[ᨵ!K⊤Ai1a2b3c4d᝞¡ᑏ!ᩩSQL£¤a³´µ¶.ab,ac,ad,be,cddroptableBselectb.id,b.namefromBbcreatetableB(idintIDENTITY,namevarchar(20)insertintoBvalues('a')insertintoBvalues('b')insertintoBvaluesinsertintoBvalues('d')!SQLServer:selecta.name+b.name,a.name+c.name,a.name+d.name,b.name+c.name,c.name+d.namefromBa,Bb,Bc,Bdwherea.id=landb.id=2andc.id=3andd.id=4-Oracle:selectdistincta.name|Ib.nameI%','IIa.nameIIc.name|i','|Ia.name|Id.nameII','.|b.nameI|c.nameII','%ic.nameIId.namefromBa,Bb,Bc,Bdwherea.numberஹ1andb.number=2andc.number=3andd.number=4

43!ᐸº»ὃ%᝞⌕¼½ᙠ¾!ᑡ³´ᕖ¨abacadbebdcd!»ὃÁᫀ%selecta.name+b.namefromBa,Bbwherea.id

44᩵*ᡠᨵᨵËÌ<=ḄᦪDaᵨ!¤sqlᩭÎÏcreatetableD(idvarchar(20),namevarchar(20))insertintoDvalues(*I,,'test!)insertintoDvalues(*2','test2)insertintoI)values('3','test3)insertintoDvalues('4','test4)insertintoDvalues(*6','test6)insertintoDvalues(*5','test5)insertintoDvalues('2','test2)insertintoDvalues3','test3)insertintoDvalues(*4*,1test4)insertintoI)values('7','test?)insertintoDvalues(*4','test4)insertintoDvalues(*6','test6)insertintoDvalues(*5','test5)insertintoDvalues('2',*test2)insertintoDvalues('3','lesl3)insertintoDvalues(*4','test4)insertintoDvalues(*8','test8)insertintoDvalues('10','test4')select*fromDwhere!"#!:!7)*ËÌḄ<=selectid,namefromDgroupbyid,namehavingcount(*)>1!7)*ËÌḄ<=ÐËÌÑᦪ

45selecta.id,a.namefromDa,(selectid,namefromDgroupbyid,namehavingcount(*)>1)bwherea.id=b.idanda.name=b.name!"#$%selecttl.*fromDtl,(selectsum(1)asSig,id,namefromDgroupbyid,name)ast2where11.name=t2.nameandtl.id=t2.idandt2.Sig>l&$ᓝ!⚪%ÒÓÔ⊤(t_salary)yearsalary20001000200120002002300020034000ᐜ⌕Îϳ´(salaryµḄÖ`)yearsalary2000100020013000dSQL£¤ÇÈᑏ¨createtablet_salary(yearint,salaryint)select*fromt_salaryinsertintot_salaryvalues(2000,1000)insertintot_salaryvalues(2001,2000)

46insertintot_salaryvalues(2002,3000)insertintot_salaryvalues(2003,4000)!"Á%selectyear,(selectsum(salary)fromt_salarybwhereb.year<=a.year)salaryfromt_salaryagroupbyyear&$ᓝ$⚪%yearmonthtotal199613000199634000199675000199734000199763000⌕¼ᢥ᝞ØÙÚÛ*%yearml,m2,m3,m4yearÜamlÝÞßa⌕¼ᢥ}àÛ*createtableOutputs(yearchar(4),monthint,totalint)insertintoOutputsvalues('1996',1,3000)insertintoOutputsvalues('1996',3,4000)insertintoOutputsvalues('1996',7,5000)insertintoOutputsvalues('1997',3,4000)insertintoOutputsvalues('1997',6,3000)insertintoOutputsvalues('1997',2,3000)

47insertintoOutputsvalues(*1998*,1,3000)insertintoOutputsvalues('1998',4,3500)select*fromOutputs!"Á!!"#!%selectyear,sum(casewhenmonth<=3thentotalelse0end)asMl,sum(casewhenmonth>3andmonthீ$6thentotalelse0end)asM2,sum(casewhenmonth>6andmonth<=9thentotalelse0end)asM3,sum(casewhenmonth>9andmonth<=12thentotalelse0end)asM2fromOutputsgroupbyyear!"#$:selectyear,sum(casewhenmonthin(1,2,3)thentotalelse0end)asMl,sum(casewhenmonthin(4,5,6)thentotalelse0end)asM2,sum(casewhenmonthin(7,8,9)thentotalelse0end)asM3,sum(casewhenmonthin(10,11,12)thentotalelse0end)asM2fromOutputsgroupbyyear&$ᓝ?⚪%â@àᑡãᣚ⚪%ᎷæçK:;ᡂé⊤(lb)᝞Ø:êiëìᑖᦪK?£ᦻ74K-£ᦪ:83K?ᱥᳮ93ñò£ᦻ74

48ñòᦪ:84ñòᱥᳮ94óôᡂsõᑮ᝞Øt:êi£ᦻᦪ:ᱥᳮñò748494K?748393createtabletb(êivarchar(lO),ëìvarchar(lO),ᑖᦪint)insertintotbvalues('K?',a£ᦻa74)insertintotbvalues(,K?',‘ᦪ:’83)insertintotbvalues('K?'aaᱥᳮa93)insertintotbvalues(,ñò'aa£ᦻa74)insertintotbvalues(,ñò'aaᦪ:a84)insertintotbvaluesCñò'a‘ᱥᳮ’94)insertintotbvalues('ñò'aaᔊøa94)go!"#!%SQLSERVER2000☟᝱SQL,ᢣëì^ᨵ£ᦻஹᦪ:ஹᱥᳮü?ëì஺sµØ¾tselectêiamax
casewhenëì='£ᦻ'thenᑖᦪendas£ᦻamax
casewhenëì='ᦪ:'thenᑖᦪendasᦪ:amax
casewhenëì='ᱥᳮ'thenᑖᦪendasᱥᳮfromtbgroupbyêi"#$%!SQLSERVER2000ý᝱SQL,ᢣëìþÿᦻஹᦪஹᱥᳮ஺

49declare©sqlvarchar(8000)set©sql=*select'select@sql=@sql+,max(casewhen'''++'''thenᑖᦪelse0end)['+']from(selectdistinctfromtb)asaset©sql=©sql+'fromtbgroupby'exec(@sql)-----SQLSERVER2005☟᝱SQL஺select*from(select*fromtb)apivot(max(ᑖᦪ)forin(ᦻᦪᱥᳮ))b-----":SQLSERVER2005☟᝱SQL஺declare@sqlvarchar(8000)select@sql=isnull(@sql+fromtbgroupbyexec(*select*from(select*fromtb)apivot(max(ᑖᦪ)forin('+©sql+'))b')$⚪ᙠ'()*Ḅ,Ạ'./ᙳᑖ1ᑖ2ᑮ᝞)*:ᦻᦪᱥᳮ/ᙳᑖ1ᑖ5"74849484.00252674839383.33250—SQLSERVER2000☟᝱SQL஺selectmax(casewhen'ᦻ'thenᑖᦪelse0end)ᦻ,max(casewhen'ᦪ'thenᑖᦪelse0end)ᦪ,max(casewhen'ᱥᳮ'thenᑖᦪelse0end)ᱥᳮ,cast(avg(ᑖᦪ*1.0)asdecimal(18,2))/ᙳᑖsumᑖᦪ1ᑖ

50fromtbgroupby—SQLSERVER2000K᝱SQL஺declare@sqlvarchar(8000)set@sql=*select'select@sql=©sql+',max(casewhen'''++'''thenᑖᦪelse0end)['++']from(selectdistinctfromtb)asaset@sql=©sql+',cast(avg(ᑖᦪ*1.0)asdecimal(18,2))/ᙳᑖ,sum(ᑖᦪ)1ᑖfromtbgroupby’exec(@sql)—SQLSERVER2005☟᝱SQL஺selectm.*,n./ᙳᑖn.1ᑖfrom(select*from(select*fromtb)apivot(max(ᑖᦪ)forin(ᦻᦪᱥᳮ))b)m,(selectcast(avg(ᑖᦪ*1.0)asdecimal(18,2))/ᙳᑖsum(ᑖᦪ)1ᑖfromtbgroupby)nwherem.=n.—SQLSERVER2005K᝱SQL஺declare©sqlvaixhar(8000)select@sql=isnull(@sql++fromtbgroupbyexecCselectm.*,n./ᙳᑖn.1ᑖfrom(select*from(select*fromtb)apivot(max(ᑖᦪ)forin('+©sql+'))b)m,(select,cast(avg(ᑖᦪ*1.0)asdecimal(18,2))/ᙳᑖ,sum(ᑖᦪ)1ᑖfromtbgroupby)nwherem.dn.')droptabletb

51$⚪᝞*'(e⊤ghᣚjᓽ⊤)᪀mᦪno:ᦻᦪᱥᳮ67483935"748494tuᡂ2ᑮ᝞)*:ᑖᦪ5"ᦻ745"ᦪ845"ᱥᳮ946ᦻ746ᦪ836ᱥᳮ93createtabletbvarcharlO,ᦻint,ᦪint,ᱥᳮintinsertintotbvalues'6'74,83,93insertintotbvalues'5"74,84,94go—SQLSERVER2000☟᝱SQL஺select*fromselect='ᦻ'ᑖᦪdᦻfromtbunionallselect=ᦪ'ᑖᦪ=ᦪfromtbunional1select='ᱥᳮ'ᑖᦪdᱥᳮfromtbtorderbycasewhen'ᦻ'then1when'ᦪ'then2when'ᱥᳮ'then3end

52—SQLSERVER2000K᝱SQL஺jxᵨz{⊤K᝱|᝱஺declare©sqlvarchar(8000)select@sql=isnull(@sql+'unionall','')+select,}~='+quotename(Name,'''')+',}ᑖᦪ~='+quotename(Name)+'fromtb'fromsyscolumnswherename!=N''andID=object_id(tb,)j⊤tb,ᒹᑡoḄᐸᑡorderbycolidascexec(@sql+'orderby')—SQLSERVER2005K᝱SQL஺selectᑖᦪfromtbunpivot(ᑖᦪforin(}ᦻ~,}ᦪ~,}ᱥᳮ~))t—SQLSERVER2005K᝱SQL,SQLSERVER2000K᝱SQL஺$⚪ᙠ'(Ḅ)*'./ᙳᑖ1ᑖ2ᑮ᝞)*:ᑖᦪ5"ᦻ74.005"ᦪ84.005"ᱥᳮ94.005"/ᙳᑖ84.005"1ᑖ252.006ᦻ74.006ᦪ83.006ᱥᳮ93.006/ᙳᑖ83.3361ᑖ250.00

53select*fromselectas:'ᦻ'ᑖᦪdᦻfromtbunionallselectas‘ᦪ'ᑖᦪ=ᦪfromtbunionallselectas='ᱥᳮ'ᑖᦪdᱥᳮfromtbunionallselectas='/ᙳᑖ'ᑖᦪ=cast

ᦻ+ᦪ+ᱥᳮ*1.0/3asdecimal(18,2))fromtbunional1selectas='1ᑖ'ᑖᦪdᦻ+ᦪ+ᱥᳮfromtb)torderbycasewhen'ᦻ'then1when'ᦪ'then2when'ᱥᳮ'then3when'/ᙳᑖ'then4when'1ᑖ'then5enddᓝ"⚪ᵨjᩩSQL,⌕⊤⊤!⊤⊤,⊤⌕Ḅ)*,e⊤ὶᔠ.⊤⊤IDNAMEIDNAME1A51A5,A8,AF....2A82B5,B3,BD....3AF3C3,CK,CL...4B55B3

546BD7C38CK9CIcreatetableleftTable(idintindentity,namevarchar(20)createtablerightTable(idintindentity,namevarchar(20))insertintoleftTablevalues(*A5*)insertintoleftTablevalues(*A8*)insertintoleftTablevaluesCAF')insertintoleftTablevalues('B5')insertintoleftTablevalues('B3')insertintoleftTablevalues(*BD')insertintoleftTablevalues('C3')insertintoleftTablevalues(*CK')insertintoleftTablevalues(JCT)j⚪ᫀ¡¢£dᓝ¤⚪j᝞¥ᑤ◀SQL⊤£¨©Ḅª«,◀ID¬•᪵᜜ᐸ°±²᪵,³e´ª«¨©createtableduplicateTable(idintIDENTITY,namevarchar(10),

55classvarchar(lO),addressvarchar(20),nationalityvarchar(30))insertintoduplicateTablevalues('name1,,classl,‘addressT,*nationalityT)insertintoduplicateTablevalues('name2,,class2,'address2,,1nationalily2')insertintoduplicateTablevalues(*nameB),class3,'address3",*nationality3J)insertintoduplicateTablevalues(*name4,,class4,'address4,,*nationality4,)insertintoduplicateTablevalues('name5',class5,'address5,,,nationalityS1)insertintoduplicateTablevalues('name6',class6,'address6','nationalily6')insertintoduplicateTablevalues('name2,,class2,‘address2,,,nationality2,)insertintoduplicateTablevalues(,nameS,,class3,'address3','nationalityS*)insertintoduplicateTablevalues('name4,,class4,‘address4,,*nationa1ity4*)insertintoduplicateTablevalues('nameS),class5,'address5','nationalily5')insertintoduplicateTablevalues('name6',class6,‘address6','nationa1ity6')insertintoduplicateTablevalues('name3,,class3,‘address3','nationality3*)insertintoduplicateTablevaluesname4,,class4,'address4,,,nationality4*)insertintoduplicateTablevaluesnameS,,class5,'address5','nationalityS,)insertintoduplicateTablevalues(,name6,,class6,'address6','nationality6,)insertintoduplicateTablevalues(*name4,,class4,'address4,,*nationality4,)insertintoduplicateTablevalues('name5',class5,'address5,,,nationalityS1)insertintoduplicateTablevalues('name6',class6,‘address6','nationality6,)insertintoduplicateTablevalues('name5,,class5,‘address5','nationality5,)insertintoduplicateTablevalues('name6,,class6,'address6','nationalily6')insertintoduplicateTablevalues('name7*,*class7,'address7*,,nationality7))j:deletetfromduplicateTabletwhereexists(select1fromduplicateTablewherename=t.nameandclass=t.classandaddress=t.addressandnationality=t.nationalityandid>t.id)

56