2022年sql面试题 .pdf

《2022年sql面试题 .pdf》由会员分享，可在线阅读，更多相关《2022年sql面试题 .pdf（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Sql 常见面试题（总结）1. 用一条 SQL语句 查询出每门课都大于80 分的学生姓名namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90（1）selectstu_namefromstu_grademinusselectstu_namefromstu_gradegwhereg.grade=80（2）selectdistinctstu_namefromstu_gradeouwherenotexists(select1fromstu_gradeinnwhereou.stu_name= inn.stu_nameandinn.gra

2、de=80)（3）selectdistinctt1.stu_namefromstu_gradet1wheret1.stu_namenotin(selectdistinctt2.stu_namefromstu_gradet2wheret2.grade=80)2.学生表如下 :自动编号学号姓名 课程编号课程名称分数12005001张三0001数学6922005002李四0001数学8932005001张三0001数学69删除除了自动编号不同,其他都相同的学生冗余信息A: delete tablename where 自动编号not in(select min(自动编号 ) from tablena

3、me group by 学号,姓名 ,课程编号 ,课程名称 ,分数 )： （1）在where 的左右都不能使用“组函数”;（2）一般来说，都可以使用exists代替in, 如果在in的条件中使用了“组函数”查名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 17 页 - - - - - - - - - 询到值，那么就不能使用exists来代替in了，就像本题3.一个叫 department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现

4、在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.你先按你自己的想法做一下，看结果有我的这个简单吗？答：selecta.name,b.namefromteam a,team bwhere a.name b.Debit101ccur*面试题：怎么把这样一个表儿查成这样一个结果yearmonth amountyearm1m2m3m4199111.11991 1.11.21.31.4199121.21992 2.12.22.32.4199131.3199141.4199212.1199222.2199232.3199242.4答案一、selectyear,(selectamount fr

5、omaaa m where month=1and m.year=aaa.year)as m1,(selectamount fromaaa m where month=2and m.year=aaa.year)as m2,(selectamount fromaaa m where month=3and m.year=aaa.year)as m3,(selectamount fromaaa m where month=4and m.year=aaa.year)as m4fromaaagroupby year这个是 ORACLE 中做的：select* from(selectname, yearb1

6、,lead(year)over(partitionby nameorder by year)b2, lead(m,2)over(partitionby nameorder by year)b3,rank()over(partitionby name orderby year)rkfromt)where rk=1;*精妙的 SQL语句！精妙 SQL语句名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 17 页 - - - - - - - - - 作者：不详发文时间： 2003

7、.05.2910:55:05说明：复制表 ( 只复制结构 , 源表名：a新表名： b)SQL: select* intob froma where 11说明：拷贝表 ( 拷贝数据 , 源表名：a目标表名： b)SQL: insertintob(a,b,c)selectd,e,ffromb;说明：显示文章、提交人和最后回复时间SQL: selecta.title,a.username,b.adddatefrom tablea,(selectmax(adddate)adddatefromtablewhere table.title=a.title)b说明：外连接查询 ( 表名1：a 表名 2：b)

8、SQL: selecta.a,a.b,a.c,b.c,b.d,b.ffroma LEFT OUTJOIN b ONa.a= b.c说明：日程安排提前五分钟提醒SQL: select* from日程安排where datediff(minute,f开始时间 ,getdate()5说明：两张关联表，删除主表中已经在副表中没有的信息SQL:deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)说明： -SQL:select*fromstudentinfowherenotexists(select*from

9、studentwherestudentinfo.id=student.id)and系 名 称 =&strdepartmentname&and专 业 名 称=&strprofessionname&orderby 性别, 生源地 , 高考总成绩说明：四表联查问题：SQL: select* from a leftinnerjoinb on a.a=b.brightinnerjoinc on a.a=c.cinnerjoind on a.a=d.dwhere .说明：得到表中最小的未使用的ID 号SQL:SELECT (CASE WHENEXISTS(SELECT *FROMHandlebWHEREb

10、.HandleID= 1)THENMIN(HandleID)+ 1 ELSE 1 END) as HandleIDFROMHandleWHERE NOTHandleIDIN (SELECT a.HandleID-1 FROMHandle a)*有两个表A和 B，均有 key 和 value 两个字段，如果B的 key 在 A中也有，就把B的 value换为 A 中对应的 value名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 17 页 - - - - - - - - -

11、 这道题的 SQL语句怎么写？updatebsetb.value=(selecta.valuefromawherea.key=b.key)whereb.idin(selectb.idfromb,awhereb.key=a.key);*高级 sql 面试题原表:为了便于阅读 , 查询此表后的结果显式如下( 及格分数为60):courseidcoursename scorecourseidcoursename scoremark1 java701 java70 pass2 oracle902 oracle90 pass3 xml 403 xml 40 fail4 jsp304 jsp30 fail

12、5 servlet805 servlet80 passSQLselectcourseid,coursename ,score,decode(sign(score-60),-1,fail,pass)as mark from course_v;*一个 SQL 面试题去年应聘一个职位未果 , 其间被考了一个看似简单的题, 但我没有找到好的大案.不知各位大虾有无好的解法?题为:有两个表,t1,t2,Tablet1:SELLER|NON_SELLER-A BA CA DB AB CB DC AC BC DD AD BD C名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - -

13、- - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 17 页 - - - - - - - - - Tablet2:SELLER|COUPON |BAL-A 9 100B 9 200C 9 300D 9 400A 9.5100B 9.520A 10 80要求用 SELECT语句列出如下结果 :-如A的 SUM(BAL) 为 B,C,D 的和,B 的 SUM(BAL) 为A,C,D 的和.且用的方法不要增加数据库负担, 如用临时表等.NON-SELLER|COUPON |SUM(BAL) -A 9 900B 9 800C 9 700D 9 600A 9.520

14、B 9.5100C 9.5120D 9.5120A 10 0B 10 80C 10 80D 10 80关于论坛上那个SQL微软面试题问题：一百个账户各有100$，某个账户某天如有支出则添加一条新记录，记录其余额。一百天后，请输出每天所有账户的余额信息这个问题的难点在于每个用户在某天可能有多条纪录，也可能一条纪录也没有（不包括第一天）返回的记录集是一个100 天*100 个用户的纪录集名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 17 页 - - - - - - - -

15、- 下面是我的思路：1. 创建表并插入测试数据：我们要求username 从 1-100CREATETABLE dbo.TABLE2(usernamevarchar(50)NOT NULL,- 用户名outdatedatetimeNOTNULL ,- 日期cashfloatNOTNULL - 余额) ON PRIMARYdeclarei intseti=1whilei=100begininserttable2values(convert(varchar(50),i),2001-10-1,100)inserttable2values(convert(varchar(50),i),2001-11-

16、1,50)seti=i+1endinserttable2values(convert(varchar(50),i),2001-10-1,90)select* fromtable2orderby outdate,convert(int,username)2. 组合查询语句：a. 我们必须返回一个从第一天开始到100 天的纪录集：如：2001-10-1 （这个日期是任意的）到 2002-1-8由于第一天是任意一天，所以我们需要下面的SQL语句：selecttop100 dateadd(d,convert(int,username)-1,min(outdate)as outdatefromtable

17、2groupby usernameorderby convert(int,username)这里的奥妙在于：convert(int,username)-1（记得我们指定用户名从1-100:-)groupby username,min(outdate):第一天就可能每个用户有多个纪录。返回的结果：outdate-2001-10-0100:00:00.000.2002-01-0800:00:00.000b. 返回一个所有用户名的纪录集：selectdistinctusername fromtable2返回结果：username-名师资料总结 - - -精品资料欢迎下载 - - - - - - -

18、- - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 17 页 - - - - - - - - - 110100.99c. 返回一个 100 天记录集和100 个用户记录集的笛卡尔集合：select* from(selecttop100 dateadd(d,convert(int,username)-1,min(outdate)as outdatefromtable2groupby usernameorderby convert(int,username) as ACROSSjoin(selectdistinctusername fromtabl

19、e2) as Borderby outdate,convert(int,username)返回结果 100*100 条纪录：outdateusername2001-10-0100:00:00.0001.2002-01-0800:00:00.000100d. 返回当前所有用户在数据库的有的纪录：selectoutdate,username,min(cash)as cash fromtable2groupby outdate,usernameorderby outdate,convert(int,username)返回纪录：outdateusernamecash2001-10-0100:00:00

20、.000190.2002-01-0800:00:00.00010050e. 将 c 中返回的笛卡尔集和d 中返回的纪录做leftjoin:selectC.outdate,C.username,D.cashfrom(select* from(selecttop100 dateadd(d,convert(int,username)-1,min(outdate)as outdate名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 17 页 - - - - - - - - - fr

21、omtable2groupby usernameorderby convert(int,username) as ACROSSjoin(selectdistinctusername fromtable2) as B) as Cleftjoin(selectoutdate,username,min(cash)as cash fromtable2groupby outdate,username) as Don(C.username=D.usernameand datediff(d,C.outdate,D.outdate)=0)orderby C.outdate,convert(int,C.user

22、name)注意： 用户在当天如果没有纪录，cash 字段返回NULL ，否则 cash 返回每个用户当天的余额outdateusernamecash2001-10-0100:00:00.0001902001-10-0100:00:00.0002100.2001-10-0200:00:00.0001902001-10-0200:00:00.0002NULL - 注意这里.2002-01-0800:00:00.00010050f. 好了，现在我们最后要做的就是，如果cash 为 NULL ，我们要返回小于当前纪录日期的第一个用户余额 ( 由于我们使用orderby cash, 所以返回top1 纪

23、录即可，使用min 应该也可以) ，这个余额即为当前的余额：case isnull(D.cash,0)when 0 then(selecttop1 cash fromtable2where table2.username=C.usernameand datediff(d,C.outdate,table2.outdate)0orderby table2.cash)elseD.cashend as cashg. 最后组合的完整语句就是selectC.outdate,C.username,名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - -

24、 名师精心整理 - - - - - - - 第 8 页，共 17 页 - - - - - - - - - case isnull(D.cash,0)when 0 then(selecttop1 cash fromtable2where table2.username=C.usernameand datediff(d,C.outdate,table2.outdate)b.scoreand a.s#=b.s#;2、查询平均成绩大于60 分的同学的学号和平均成绩；selectS#,avg(score)from scgroup by S# havingavg(score)60;3、查询所有同学的学号、

25、姓名、选课数、总成绩；selectStudent.S#,Student.Sname,count(SC.C#),sum(score)fromStudentleftOuterjoinSC on Student.S#=SC.S#groupby Student.S#,Sname4、查询姓“李”的老师的个数；selectcount(distinct(Tname)fromTeacherwhere Tname like 李%;5、查询没学过“叶平”老师课的同学的学号、姓名；selectStudent.S#,Student.Snamefrom StudentwhereS# notin(selectdistin

26、ct(SC.S#)fromSC,Course,Teacherwhere名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 17 页 - - - - - - - - - SC.C#=Course.C# and Teacher.T#=Course.T#and Teacher.Tname=叶平);6、查询学过“ 001”并且也学过编号“ 002”课程的同学的学号、姓名；selectStudent.S#,Student.SnamefromStudent,SCwhereStudent

27、.S#=SC.S#andSC.C#=001andexists(Select*fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=002);-exists是 Oraclesql 中的一个函数。表示是否存在符合某种条件的记录。7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；selectS#,SnamefromStudentwhereS# in(selectS# fromSC ,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=叶平groupbyS#havingcoun

28、t(SC.C#)=(selectcount(C#)from Course,Teacherwhere Teacher.T#=Course.T#andTname=叶平);8、查询课程编号“ 002”的成绩比课程编号“ 001”课程低的所有同学的学号、姓名；SelectS#,Sname from (selectStudent.S#,Student.Sname,score,(selectscore from SCSC_2 where SC_2.S#=Student.S#and SC_2.C#=002)score2fromStudent,SCwhere Student.S#=SC.S#and C#=00

29、1)S_2 where score260);10、查询没有学全所有课的同学的学号、姓名；selectStudent.S#,Student.Snamefrom Student,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(C#)=60THEN1 ELSE0 END)/COUNT(*) AS 及格百分数FROM SC T,Coursewhere t.C#=course.C#GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score,0)=60THEN1 ELSE

30、0 END)/COUNT(*)DESC20、查询如下课程平均成绩和及格率的百分数( 用1行显示):企业管理（ 001），马克思名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 17 页 - - - - - - - - - （002） ，OO&UML （003） ，数据库（ 004）SELECTSUM(CASE WHEN C# =001THENscoreELSE 0 END)/SUM(CASEC# WHEN 001THEN1 ELSE 0 END) AS 企业管理平均分,1

31、00 * SUM(CASE WHEN C# = 001ANDscore= 60 THEN1 ELSE0 END)/SUM(CASEWHEN C# = 001THEN1 ELSE0 END) AS 企业管理及格百分数,SUM(CASE WHEN C#= 002THENscoreELSE0 END)/SUM(CASE C#WHEN 002THEN1 ELSE 0 END) AS 马克思平均分,100 * SUM(CASE WHEN C# = 002ANDscore= 60 THEN1 ELSE0 END)/SUM(CASEWHEN C# = 002THEN1 ELSE0 END) AS 马克思及格

32、百分数,SUM(CASE WHEN C#= 003THENscoreELSE0 END)/SUM(CASE C#WHEN 003THEN1 ELSE 0 END) AS UML平均分,100 * SUM(CASE WHEN C# = 003ANDscore= 60 THEN1 ELSE0 END)/SUM(CASEWHEN C# = 003THEN1 ELSE0 END) AS UML及格百分数,SUM(CASE WHEN C#= 004THENscoreELSE0 END)/SUM(CASE C#WHEN 004THEN1 ELSE 0 END) AS 数据库平均分,100 * SUM(CA

33、SE WHEN C# = 004ANDscore= 60 THEN1 ELSE0 END)/SUM(CASEWHEN C# = 004THEN1 ELSE0 END) AS 数据库及格百分数FROMSC21、查询不同老师所教不同课程平均分从高到低显示SELECTmax(Z.T#)AS 教师 ID,MAX(Z.Tname) AS 教师姓名 ,C.C#AS 课程 ,MAX(C.Cname)AS 课程名称 ,AVG(Score)AS 平均成绩FROM SC AS T,CourseAS C ,TeacherAS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#OR

34、DER BY AVG(Score)DESC22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理（001） ，马克思（ 002），UML（003） ，数据库（ 004） 学生 ID,学生姓名 , 企业管理 , 马克思 ,UML,数据库, 平均成绩SELECTDISTINCT top3SC.S# As 学生学号,Student.SnameAS 学生姓名,T1.scoreAS 企业管理,T2.scoreAS 马克思,T3.scoreAS UML,T4.scoreAS 数据库,ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+I

35、SNULL(T4.score,0)as 总分FROMStudent,SCLEFT JOIN SC AS T1ONSC.S# = T1.S# ANDT1.C# = 001LEFT JOIN SC AS T2ONSC.S# = T2.S# ANDT2.C# = 002LEFT JOIN SC AS T3ONSC.S# = T3.S# ANDT3.C# = 003名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 17 页 - - - - - - - - - LEFT JOIN

36、SC AS T4ONSC.S# = T4.S# ANDT4.C# = 004WHERE student.S#=SC.S#andISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOTIN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)FROMscLEFT JOIN sc AS T1ONsc.S#= T1.S# ANDT1.C# = k

37、1LEFT JOIN sc AS T2ONsc.S#= T2.S# ANDT2.C# = k2LEFT JOIN sc AS T3ONsc.S#= T3.S# ANDT3.C# = k3LEFT JOIN sc AS T4ONsc.S#= T4.S# ANDT4.C# = k4ORDERBY ISNULL(T1.score,0)+ ISNULL(T2.score,0)+ ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC);23、统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60,60SELECTSC.C# as 课程 ID

38、,Cnameas 课程名称,SUM(CASEWHEN scoreBETWEEN 85 AND100 THEN1 ELSE 0 END) AS 100 -85,SUM(CASEWHENscoreBETWEEN 70 AND85 THEN1 ELSE 0 END) AS 85-70,SUM(CASEWHENscoreBETWEEN 60 AND70 THEN1 ELSE 0 END) AS 70-60,SUM(CASEWHENscore T2.平均成绩)as 名次,S# as 学生学号 , 平均成绩FROM (SELECT S#,AVG(score)平均成绩FROMSC名师资料总结 - - -精品

39、资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 17 页 - - - - - - - - - GROUP BY S#) AS T2ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:( 不考虑成绩并列情况)SELECTt1.S#as 学生 ID,t1.C#as 课程 ID,Scoreas 分数FROMSC t1WHERE scoreIN (SELECT TOP3 scoreFROMSCWHERE t1.C#=C#ORDER BY scoreDESC)ORDER BY t1.C#;2

40、6、查询每门课程被选修的学生数selectc#,count(S#)fromsc groupby C#;27、查询出只选修了一门课程的全部学生的学号和姓名selectSC.S#,Student.Sname,count(C#)AS 选课数fromSC ,Studentwhere SC.S#=Student.S#group by SC.S# ,Student.Snamehavingcount(C#)=1;28、查询男生、女生人数Selectcount(Ssex)as 男生人数fromStudentgroup by Ssex havingSsex= 男;Selectcount(Ssex)as 女生人数

41、fromStudentgroup by Ssex havingSsex= 女 ；29、查询姓“张”的学生名单SELECTSnameFROMStudentWHERE Snamelike 张%;30、查询同名同性学生名单，并统计同名人数selectSname,count(*)fromStudentgroupby Snamehavingcount(*)1;31、1981 年出生的学生名单 ( 注：Student表中 Sage 列的类型是datetime)selectSname, CONVERT(char(11),DATEPART(year,Sage)as agefrom studentwhere C

42、ONVERT(char(11),DATEPART(year,Sage)=1981;32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SelectC#,Avg(score)fromSC groupby C# orderby Avg(score),C#DESC;33、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩selectSname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S#group by SC.S#,Sname havingavg(score)85;34、查询课程名称为“数据库”，且

43、分数低于60 的学生姓名和分数SelectSname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S#and SC.C#=Course.C# and Course.Cname=数据库 andscore=70ANDSC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列selectc# fromsc where score 80and C#=003;39、求选了课程的学生人数selectcount(*)fromsc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩selectStu

44、dent.Sname,scorefrom Student,SC,CourseC,Teacherwhere Student.S#=SC.S#and SC.C#=C.C# and C.T#=Teacher.T#and Teacher.Tname=叶平and SC.score=(selectmax(score)fromSC where C#=C.C# );41、查询各个课程及相应的选修人数selectcount(*)fromsc groupby C#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩selectdistinctA.S#,B.scorefrom SCA ,SC B where

45、A.Score=B.Scoreand A.C# B.C# ;43、查询每门功成绩最好的前两名SELECTt1.S#as 学生 ID,t1.C#as 课程 ID,Scoreas 分数FROMSC t1WHERE scoreIN (SELECT TOP2 scoreFROMSCWHERE t1.C#=C#ORDER BY scoreDESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过 10 人的课程才统计） 。 要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列selectC# as 课程号 ,count(*)as 人数

46、from scgroup by C#orderby count(*)desc,c#45、检索至少选修两门课程的学生学号selectS#from scgroup by s#havingcount(*) = 246、查询全部学生都选修的课程的课程号和课程名selectC#,Cname名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 17 页 - - - - - - - - - from Coursewhere C# in(selectc# fromsc groupby c#)

47、47、查询没学过“叶平”老师讲授的任一门课程的学生姓名selectSnamefrom Student where S#not in (selectS#from Course,Teacher,SCwhereCourse.T#=Teacher.T#and SC.C#=course.C#and Tname=叶平);48、查询两门以上不及格课程的同学的学号及其平均成绩selectS#,avg(isnull(score,0)from SCwhere S# in (selectS#from SCwhere score2)groupby S#;49、检索“ 004”课程分数小于60，按分数降序排列的同学学号selectS# fromSC where C#=004andscore60 orderby scoredesc;50、删除“ 002”同学的“ 001”课程的成绩名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 17 页 - - - - - - - - -