書面學(xué)習(xí)4幫助經(jīng)典sql面試題

)CREATETABLEstudentsidvarchar(10)NOTsNamevarchar(20)DEFAULTsAgedatetimeDEFAULT'1980-10-12svarchar(10)DEFAULTPRIMARYKEY)ENGINE=InnoDBDEFAULTame,tid)CREATETABLEcoursecidvarchar(10)NOTcNamevarchar(10)DEFAULTNULL,tidint(20)DEFAULTNULL,PRIMARYKEY(cid))ENGINE=InnoDBDEFAULTSC(sid,cid,score)CREATETABLEscsidvarchar(10)DEFAULTNULL,cidvarchar(10)DEFAULTNULL,scoreint(10)DEFAULTNULL)ENGINE=InnoDBDEFAULT)ENGINE=InnoDBDEFAULTTeacher(tid,Tname)CREATETABLEtaachertidint(10)DEFAULTtNamevarchar(10)DEFAULT)ENGINE=InnoDBDEFAULTinsertinsertintotaacher(tid,tName)values insertinto )values10-1223:12:36','男 世明','1981-10-1223:12:36','男 ','1986-10-1223:12:36','男 ','1981-10-1223:12:36','女insertintosc(sid,cid,score)values('1','001',80),('1','002',60),('1','003',75),('2','001',85),('2','002',70),('3','004',100),insertintoame,tid)values('001','企業(yè)管理CREATETABLEstudentsidvarchar2(10)NOTNULL,sNamevarchar2(20)DEFAULTNULL,sAgedate, varchar2(10)DEFAULTNULL,PRIMARYKEY(sid))CREATETABLEcoursecidvarchar2(10)NOTNULL,cNamevarchar2(10)DEFAULTNULL,tidnumber(20)DEFAULTNULL,PRIMARYKEY(cid))CREATETABLEscsidvarchar2(10)DEFAULTNULL,cidvarchar2(10)DEFAULTNULL,scorenumber(10)DEFAULTNULL)CREATETABLEteachertidnumber(10)DEFAULTNULL,tNamevarchar2(10)DEFAULTNULL)insertintocourse( ame,tid)values('001','企業(yè)管理',3);insertintocourse( ame,tid)values('002','',3);insertintocourse( ame,tid)values('004','數(shù)據(jù)庫',1);insertintocourse( ame,tid)values('005','英語',1);insertintosc(sid,cid,score)values('1001','001',80);insertintosc(sid,cid,score)values('1001','002',60);insertintosc(sid,cid,score)values('1001','003',70);insertintosc(sid,cid,score)values('1002','001',85);insertintosc(sid,cid,score)values('1002','002',70);insertintosc(sid,cid,score)values('1003','004',90);insertintosc(sid,cid,score)values('1003','001',90);insertintosc(sid,cid,score)values('1003','002',99);insertintosc(sid,cid,score)values('1004','002',65);insertintosc(sid,cid,score)values('1004','003',50);insertintosc(sid,cid,score)values('1005','005',80);insertintosc(sid,cid,score)values('1005','004',70);insertintosc(sid,cid,score)values('1003','003',10);insertintosc(sid,cid,score)values('1003','005',10);insertintostudent(sid,sName,sAge,s)values('1001','',to_date('1980-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'男insertintostudent(sid,sName,sAge,s)values('1002','張無極',to_date('1995-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'男insertintostudent(sid,sName,sAge,s)values',to_date('1992-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'女insertintostudent(sid,sName,sAge,s values('1004','李元寶',to_date('1980-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'女insertintostudent(sid,sName,sAge,s )values('1005','',to_date('1981-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'男insertintostudent(sid,sName,sAge,s )values('1006','',to_date('1986-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'男');insertintostudent(sid,sName,sAge,s )values('1007','',to_date('1981-10-1223:12:36','YYYY-MM-DDHH24:MI:SS'),'女');insertintoteacher(tid,tName)values(1,'');insertintoteacher(tid,tNamevalues(2,'何以琛');insertintoteacher(tid,tName)values(3,'');selecta.sidfrom(selectsid,scorefromSCwherecid='001')a,(selectsid,scorefromSCwherecid='002')bwherea.score>b.scoreand260分的同學(xué)的學(xué)號(hào)和平均成績(jī)selectsid,avg(score)fromscgroupbysidhavingavg(score)、選課數(shù)、總成績(jī)selectfromStudentleftOuterjoinSCongroupbyselectfromwhereTnamelike'李 selectfromwheresidnotin(selectdistinct(SC.sid)fromSC,Course,TeacherwhereSC.cid=Course.cidandse.tidand A:selectStudent.sid,Student.SnamefromStudent,SCwhereStudent.sid=SC.sidandSC.cid='001'andexists(*fromSCasSC_2whereSC_2.sid=SC.sidandB:SELECT students,(SELECTsid,COUNT(cid)FROMscWHEREcidIN('001','002')GROUPBYsidHAVINGUNT(cid)>=2)tWHEREs.sid= selectfromwheresidin(selectsidfromSC,Course,TeacherwhereSC.cid=Course.cidandTeacher.tid=Course.tidand 'groupbysidhavingcount(SC.cid)=(selectcount(cid)fromCourse,TeacherwhereCourse.tidand 1>Selectsid,Snamefrom(selectStudent.sid,Student.Sname,score,(selectscorefromSCSC_2wheretudent.sidandSC_2.cid='002')fromStudent,SCwhereStudent.sid=SC.sidandcid='001')S_2wherescore22>SELECTs.sid,s.sNameFROMstudent(SELECTsid,scoreFROMscWHEREcid='001')(SELECTsid,scoreFROMscWHEREcid='002')WHEREsc_1.sid=sc_2.sidANDs.sid=sc_2.sidANDsc_2.score<9、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào) selectfromwheresidnotin(selectStudent.sidfromStudent,SCwhereS.sid=SC.sidand selectfromwhereStudent.sid=SC.sidgroupbyStudent.sid,Student.Snamehavingcount(cid)<(selectcount(cid)fromSELECTs.sid,s.snameFROMstudent(SELECTsid,COUNT(cid)FROMscGROUPBYsidHAVINGCOUNT(cid)<(SELECTCOUNT(cid)FROMcourse)WHEREs.sid= selectsid,SnamefromStudent,SCwhereStudent.sid=SC.sidandcidin(selectcidfromSCwhere UPDATEsc,(SELECTc.cid,AVG(score)avgsFROMsc,coursec,teachertWHEREsc.cid=c.cidc.tid=t.tidANDt.tName= 'GROUPBYc.cid)sc_2SETsc.score=sc_2.avgsWHEREsc.cid= selectsidfromSCwherecidin(selectcidfromSCwheregroupbysidhavingcount(*)=(selectcount(*)fromSCwhere ”老師課的SC表記錄DELETEFROMscWHEREsc.cidIN(SELECTsc.cidFROMcoursec,teachertWHEREsc.cid=c.cidAND=t.tidANDt.tName= ID,,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),SELECTsidas,(SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='004')AS,(SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='001')AS,(SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='005')AS,COUNT(*)AS有效課程數(shù),AVG(t.score)ASFROMSCASGROUPBYORDERBY分selectselectcidID",max(score最高分",min(score最低分fromscgroupbycidSELECTt.cidAS課程號(hào) ame)AS課程名,nvl(AVG(score),0),100*SUM(CASEWHENnvl(score,0)>=60THEN1ELSE0END)/COUNT(*)FROMSCT,CourseWHEREt.cid=course.cidGROUPBYt.cidORDERBY100*SUM(CASEWHENnvl(score,0)>=60THEN1ELSE0END)/COUNT(*)DESCSELECTt.cidAS課程號(hào),MAX( ame)AS課程名,IFNULL(AVG(score),0)AS平均成績(jī),100*SUM(CASEWHENIFNULL(score,0)>=60THEN1ELSE0ASFROMSCT,CourseWHEREt.cid=course.cidGROUPBYt.cidORDERBY100*SUM(CASEWHENIFNULL(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC2020、查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行"顯示):企業(yè)管理（002），OO&UML（003），數(shù)據(jù)庫SELECTSUM(CASEWHENcid='001'THENscoreELSE0END)/SUM(CASEcidWHEN'001'THEN1ELSE0AS,100*SUM(CASEWHENcid='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='001'N1ELSE0END)AS,SUM(CASEWHENcid='002'THENscoreELSE0END)/SUM(CASEcidWHEN'002'THEN1ELSE0END)AS,100*SUM(CASEWHENcid='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='002'N1ELSE0END) ,SUM(CASEWHENcid='003'THENscoreELSE0END)/SUM(CASEcidWHEN'003'THEN1ELSE0END)AS,100*SUM(CASEWHENcid='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='003'N1ELSE0END)ASUML,SUM(CASEWHENcid='004'THENscoreELSE0END)/SUM(CASEcidWHEN'004'THEN1ELSE0END)AS,100*SUM(CASEWHENcid='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='004'N1ELSE0END)ASFROMSELECTMAX(t.tid)"教師ID",MAX(t.tName)"教 ",c.cid"課程ID", ame)"課程名稱",AVG(sc.score)FROMsc,coursec,teachertWHEREsc.cid=c.cidANDc.tid=t.tidGROUPBYORDERBYAVG(sc.score)課程[SELECTSC.cidasID,Cnameas,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-,SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS[70-,SUM(CASEWHENscore<60THEN1ELSE0END)AS[60-FROMwhereGROUP selectcid,count(sid)fromscgroupbyselectSC.sid,Student.Sname,count(cid)ASfromSCwhereSC.sid=Student.sidgroupbySC.sid,Student.Snamehaving 人Select )as男生人數(shù)fromStudentgroupby having Select ) 人數(shù)fromStudentgroupby having SELECTSnameFROMStudentWHERESnamelike'張 ，并統(tǒng)計(jì)同名人SELECT ,COUNT(*)FROMstudentGROUPBY HAVINGCOUNT(*)>31、1981年出生的學(xué) (注：Student表中Sage列的類型是selectSname,CONVERT(char(11),DATEPART(year,Sage))asfromwhereselect*fromstudentwheresubstr(to_char(sage,'yyyy-MM-dd'),1,4)=Selectcid,Avg(score)fromSCgroupbycidorderbyAvg(score),cidDESC33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào) 和平均成selectSname,SC.sidfromwhereStudent.sid=SC.sidgroupbySC.sid,Snamehaving34、查詢課程名稱為“數(shù)據(jù)庫”，且分?jǐn)?shù)低于60的學(xué) 和分SelectfromwhereSC.sid=Student.sidandSC.cid=Course.cid ame='數(shù)據(jù)庫'andscoreSELECT FROMwhereSC.sid=Student.sidandSC.cid=Course.cid36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上 、課程名稱和分?jǐn)?shù)SELECTdistinctFROMWHERESC.score>=70ANDselectcidfromscwherescore<60orderbycid38、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào) selectSC.sid,Student.SnamefromSC,StudentwhereSC.sid=Student.sidandScore>80andselectcount(*)from 及其成selectfromStudent,SC,Coursewher