sql基礎(chǔ)入門(mén)經(jīng)典面試筆試題

1、觸發(fā)時(shí)間：有 insert,update,delete3) ?：selects#觸發(fā)時(shí)間：有 insert,update,delete3) ?：selects#,snfromswhereS#in(selectS#fromc,scwherec.c#=sc.c#and基(2) 使用標(biāo)準(zhǔn) SQL和所答：selectsn,sdfroms,scwheres.s#=sc.s#and(3) 使用標(biāo)準(zhǔn) SQL?答：selectsn,sdfromswheres#notin(selectsfromscwhere 答：select 學(xué)員人數(shù)=count(distinctsfrom(5) 查詢選修課程超過(guò) 5? 值。

2、不能把任何值與一個(gè) 個(gè)ISNULL檢查系統(tǒng)的I/O令A(yù)、MODIFY、MODIFYSTRUCTUREC、ALTERTABLED、ALTER 值。不能把任何值與一個(gè) 個(gè)ISNULL檢查系統(tǒng)的I/O令A(yù)、MODIFY、MODIFYSTRUCTUREC、ALTERTABLED、ALTER令A(yù)、DROP、ALTERTABLEC、DELETETABLED、60語(yǔ)句的功能DELETEFROMSAS60B、S表大于60歲C、刪除S 表 D、刪除S列 A、選擇工作區(qū)語(yǔ)句 B、數(shù)據(jù)查詢語(yǔ)句 C、選擇標(biāo)準(zhǔn)語(yǔ)句 DSQL語(yǔ)言語(yǔ)言A、層次數(shù)據(jù)庫(kù) B、網(wǎng)絡(luò)數(shù)據(jù)庫(kù) C、關(guān)系數(shù)據(jù)庫(kù) D在SQL中，刪除視圖 命令 命令 SQ

3、L80：selectdistinctnamefromnamenotin(selectdistinctnamefromwhere123學(xué)生表 如下:：deletetablenamewhere 自動(dòng)by 學(xué)號(hào),一個(gè)叫 teamname4a,b,c,d答： from team a, team b123學(xué)生表 如下:：deletetablenamewhere 自動(dòng)by 學(xué)號(hào),一個(gè)叫 teamname4a,b,c,d答： from team a, team b where 12341234：select(s

4、electamountfrom (selectamountfrom (selectamount(selectamountaaamwhere aaamwheremonth=2 aaamwheremonth=3 aaamwhere andm.year=aaa.year)asandm.year=aaa.year)asm2, andm.year=aaa.year)asm3, and m.year=aaa.year) as m4fromgroupbyABkeyvalue 兩個(gè)字段，如果BkeyABA 中對(duì)應(yīng)的valuebaSQL1，：Createtablestu,家庭地址 ,：Altertablestu

5、add 學(xué)歷 ：Altertablestudropcolumn 家庭地址1A221234562SQL1，：Createtablestu,家庭地址 ,：Altertablestuadd 學(xué)歷 ：Altertablestudropcolumn 家庭地址1A221234562B 211193C231104D18114 ostuvalues(2,B,21,男,119 5：Updatestuset 學(xué)歷=大專 like6) 刪除學(xué)生表的數(shù)據(jù)，C：Delectfromstulike7來(lái),學(xué)號(hào) fromstu22and 學(xué)歷=大專825：Selecttop25percent*from9) 查詢出所有學(xué)生的，

6、,fromstuorder10) 按照6) 刪除學(xué)生表的數(shù)據(jù)，C：Delectfromstulike7來(lái),學(xué)號(hào) fromstu22and 學(xué)歷=大專825：Selecttop25percent*from9) 查詢出所有學(xué)生的，,fromstuorder10) 按照：Select)fromstugroupcreatetable(deptnovarchar(10)primarykey, dname varchar(10)createtableemp empnovarchar(10)primarykey, ename varchar(10),job varchar(10), mgrvarchar(1

7、0), sal varchar(10),deptnovarchar(10)droptabledept; droptable odeptvalues1odeptvalues2odeptvalues3oempvalues oempvalues oempvalues oempvalues oempvalues oempvalues oempvalues select*fromselect*fromSQL（1oempvalues oempvalues oempvalues oempvalues oempvalues select*fromselect*fromSQL（1：selectdeptnoas

8、emp group by deptno;部門(mén)號(hào),max(salas 最高工資,min(sal)as 最低工資 （2 為：selectmax(sal)as 最高工資,min(sal)as 最低工資,deptnoas 部門(mén)號(hào) fromempjob=clerkgroupby（3：selectb.deptnoas 部門(mén)號(hào),max(sal)as 最高工資,min(sal)as 最低工資 fromempasb wherejob=clerkandselectmin(sal)fromempasawherea.deptno=b.deptno)(selectavg(a.sal)fromempasawherea.d

9、eptno=b.deptno)orderby（11、對(duì)于 ：selecta.deptnoas 部門(mén)號(hào),count(a.salas 員工數(shù) fromempaswhere a.sal(select avg(b.sal) from emp as bwhere a.deptno=b.deptno) group by a.deptno by（12：selectcount(a.empnoas 員工數(shù),a.deptnoas 部門(mén)號(hào),avg(sal)as 平均工資fromempasawhere(selectcount(c.empno)fromempascwherec.deptno=a.deptnoand c.

10、sal(select avg(sal) from emp as b where c.deptno=b.deptno)1groupbya.deptnoorderby（13：selecta.deptnoas 部門(mén)號(hào),a.enameempas whereb.sala.salas 人數(shù) fromempas,a.salas 工資,(selectcount(b.enamewhere(selectcount(b.ename)fromempasbwhereTableXCode、 Name、 Age、 其中 CodeTableYCode、 Class、Score, 其中 CodeClass 為主鍵。兩表Code

11、NameAgeCodeClass22212022數(shù)學(xué) 計(jì)算機(jī) 計(jì)算機(jī) 數(shù)學(xué) （1：select*fromTableXwherenamelikeorderby（2：select*fromtableXwherecodeinselectcodefromtableYWEHREclassand score 60)（3：,b.class,b.scorefromtableXa,tableYbwhere（4：,b.class,b.scorefromtableXfull（2：select*fromtableXwherecodeinselectcodefromtab

12、leYWEHREclassand score bthenaelsebend), (case when bc then b esle c end)from請(qǐng)取出 tb_send 表期(SendTime?(SendTime 字段為：select*fromtbwhere一個(gè)表中的Id1：selectid,Count（*)fromtbgroupbyidhaving2：select*from(selectcount(ID)ascountfromtablegroupbyID)Twhere：select*fromscore where s_name not in (selects_namefromscore

13、 where score=60)、課程、分?jǐn)?shù)60：select*fromscore where s_name not in (selects_namefromscore where score=60)、課程、分?jǐn)?shù)6032：select 名稱 from 商品表 group名稱 havingavg600：select 準(zhǔn)考證號(hào) from 高考信息表 group準(zhǔn)考證號(hào) havingsum600：select 準(zhǔn)考證號(hào) from 高考信息表 whereidgenderMFFFM里：selectgender,count(id)600：select 準(zhǔn)考證號(hào) from 高考信息表 whereidgend

14、erMFFFM里：selectgender,count(id)fromclubgroupbyID(numberName(varchar123456acID(numberName(varchar2125SQLa：deletefromteamwhereidnot(select min(a1.id) from team a1 =deletefromteamwhereidnotin selectmin(id)fromteamgroupbynamecourse張青 化學(xué)：select*fromstudentwherenamein (select name fro

15、m studentwherenamelikegroupbynamehavingavg(score)create張青 化學(xué)：select*fromstudentwherenamein (select name from studentwherenamelikegroupbynamehavingavg(score)createtable(department)otesttable1values(otesttable1values(otesttable1values(id123createtable(,name)otesttable2values(1otesttable2values(1otestt

16、able2values(2otesttable2values(3otesttable2values(4otesttable2values(5SQLiddptIDdepartment1234511234select t2.id,t2.dptID,t1.department, from testtable2 t2left jointesttable1t1on SELECT t2.id , t2.dptID, ) dptName, FROM t1rightesttable2t2ont2.dptID=A(ID,Name3140createtable(select t2.id

17、,t2.dptID,t1.department, from testtable2 t2left jointesttable1t1on SELECT t2.id , t2.dptID, ) dptName, FROM t1rightesttable2t2ont2.dptID=A(ID,Name3140createtable(varchar)SQL：selecttop10 * from Awhere ID (select max(ID) from (select top 30ID fromA byid)T)orderbyA(ID,NameID：createtable(,

18、varchar)AAAAAAAAby有表,name,createtablenamevarchar)oempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoempoemp編寫(xiě) ?：selectid,name,age from empwhere name in(select name from empgroupby havingcreatetablenamevarchar(20), )oempoempoempoemp1：selectdistinct*2：selectna

19、me,agefromempgroupbycreatetableName varchar(25), CNamevarchar(25)ocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesocoursevaluesSQL：select*fromwherenameocoursevaluesoco

20、ursevaluesocoursevaluesocoursevaluesSQL：select*fromwherenamein(selectnamefromcoursewhereCNameingroupbyname having count(*)=3)andnamenotin(selectnamefromcoursegroupbynamehaving問(wèn)題二：同時(shí)選了數(shù)學(xué)，物理，語(yǔ)文的學(xué)生, 查詢結(jié)果如下,寫(xiě)出相應(yīng)SQL：select*fromwherenamein(selectnamefromcoursewhereCNameingroupbyname having count(*)=3)F（FI

21、DFLASFSSEcreatetableF3FID varchar(20), FLASSvarchar(20), )F3valuesF3values F3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3values：selectF3.FLASS,Max(FSSCORE)fromF

22、3groupbyFLASShavingcount(*)F3valuesF3valuesF3valuesF3valuesF3valuesF3valuesF3values：selectF3.FLASS,Max(FSSCORE)fromF3groupbyFLASShavingcount(*)createtableTeachers,T_NAME)createtableStudents,S_NAME)createtableClasses,C_NAME)TeachersTeachersTeachersTeachersTeachersStudentsStudentsStudentsStudentsStude

23、ntsStudentsStudentsStudentsStudentsStudentsStudentsStudentsStudentsStudentsStudentsStudents StudentsStudentsStudentsStudents ：selectfromTeachersT_ID,count(*)asxfromClasseswhereC_Name=小班groupbyT_ID)T1, T_ID,count(*) as xfrom Classeswhere C_Name=大班 group by T_ID) T2whereandT1.T_ID=T2.T_IDand有兩張表：a 部門(mén)表

24、 b 員工表aid)b employee- 員工名稱)createtableNamevarchar)createtable,Namevarchar)odepartmentsvalueso)b employee- 員工名稱)createtableNamevarchar)createtable,Namevarchar)odepartmentsvaluesodepartmentsvaluesodepartmentsvaluesodepartmentsvaluesodepartmentsvaluesoemployeesvaluesoemployeesvaluesoemployeesvaluesoemp

25、loyeesvaluesoemployeesvalues oemployeesvaluesoemployeesvaluesoemployeesvaluesoemployeesvaluesoemployeesvaluesoemployeesvalues oemployeesvaluesoemployeesvalues：select b.id,a.Name,count(b.id)as employeesbona.id=b.idgroupbyfrom departments a left year2000200120022003yearsalary 2000200120022003SQLcreate

26、table)selectyearsalary 2000200120022003SQLcreatetable)select*fromot_salaryot_salaryot_salaryot_salary：select year, (select sum(salary) from t_salary b where b.year =2018-01-0100:00:00 and user_id = U002orderbyborrow_time（58：select*fromuserwherephonelike：selectcount(*)from：selectdistinctuser_idfrombo

27、rrow_log where borrow_bike_stop_id = R002orreturn_bike_stop_id=20171115（9：select*fromuserwherenotect1fromrecharge_logwherea.user_id=andb.recharge_time=erval -90（10：select*fromuserwherenotect1fromborrow_logwherea.user_id=andb.borrow_time=erval -90（11 borrow_bike_stop_id,count(distinctuser_id)fromwhere borrow_time = date_add(now(),erval-180day) group by borrow_bike_stop_id;（12 return_bike_stop_id,count(distinctuser_id)fromwhere borrow_time = date_add(now(),erval-180day) and return_bike_stop_id groupby（13a.borrow_bike_stop_id,max(b.stop_na