Sql面试宝典带答案
一、试用SQL查询语句表达下列对教学数据库中三个基本表 S、SC 、C 的查询: S(sno,sname,SAGE,SSEX) 各字段表示学号,姓名,年龄,性别 Sc(sno,cno,grade) 各字段表示学号,课程号,成绩、 C(cno,cname, TEACHER) 各字段表示课程号,课程名和教师名 其 中 SAGE, grade 是数值型,其他均为字符型。 要求用 SQL 查询语句实现如下处理:
1.求年龄大于所有女同学年龄的男学生姓名和年龄。 SELECTSNAME,SAGE FROM S AS X WHERE X.SSEX=' 男'AND X.SAGE >ALL (SELECT SAGE FROMS AS Y WHERE Y.SSEX=' 女')
2.求年龄大于女同学平均年龄的男学生姓名和年龄。 SELECT SNAME,SAGE FROM S WHERE SSEX=' 男' AND SAGE>(SELECTAVG(SAGE) FROM S WHERE SSEX='女') 3.在 SC 中检索成绩为空值的学生学号和课程号。 SELECT Sno,CnoFROM SC WHERE GRADE IS NULL
4.检索姓名以 WANG 打头的所有学生的姓名和年龄。 SELECT SNAME,SAGE FROM S WHERE SNAME LIKE 'WANG%'
5.检索学号比 WANG 同学大,而年龄比他小的学生姓名。 SELECTX.SNAME FROM S AS X, S AS Y WHERE Y .SNAME='WANG' AND X.Sno>Y.Sno AND X.SAGE 或: SELECT SNAME from s where sno>(select sno from s where SNAME='WANG') andSAGE<(select sAGE from s where SNAME='WANG') 6.统计每门课程的学生选修人数 (超过 2 人的课程才统计) 。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。 SELECTDISTINCT Cno,COUNT(Sno) FROM SC
GROUPBY Cno HAVING COUNT(Sno)>2
ORDER BY 2 DESC, Cno ASC 或: SELECT DISTINCT Cno,COUNT(Sno) as 人数 FROMSC GROUP BY Cno HAVING COUNT(Sno)>2 ORDER BY 人 数 DESC, Cno ASC
7.求 LIU 老师所授课程的每门课程的学生平均成绩。 SELECT AVG(GRADE) FROM SC join C on SC.Cno=C.Cno WHERE TEACHER='liu' GROUP BY c.Cno 另: SELECTCNAME,AVG(GRADE) FROM SC ,C WHERE SC.Cno=C.Cno AND TEACHER='liu' GROUP BY c.Cno,cname 8.求选修 C4 课程的学生的平均年龄。 SELECT AVG(SAGE ) FROMS WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='4') 或者, SELECT AVG(SAGE) FROM S,SC WHERES.Sno=SC.Sno AND Cno='4' 9.统计有学生选修的课程门数。 SELECT COUNT(DISTINCT Cno) FROM SC 二、试用 SQL 更新语句表达对教学数据库中三个基本表 S、 SC 、C的各个更新操作:
要求用 SQL 更新语句实现如下处理: 1 .在基本表 SC 中修改 4 号课程的成绩,若成绩小于等于 75 分时提高 5% , 若成绩大于 75 分时提高 4% (用两个 UPDATE 语句实现)。 UPDATE SC SETGRADE=GRADE*1.05 WHERE Cno='4' AND GRADE<=75 UPDATE SC SET GRADE=GRADE*1.04 WHERE Cno='4' AND GRADE>75
2 .把低于总平均成绩的女同学成绩提高 5% 。 UPDATE SC SETGRADE=GRADE*1.05
WHERE GRADE<(SELECT AVG(GRADE) FROM SC)
AND Sno IN (SELECT Sno FROM SWHERE SSEX=' 女')
3 .把选修数据库原理课不及格的成绩全改为空值。 UPDATE SC SET GRADE=NULL
WHERE GRADE<60 AND Cno IN(SELECT Cno FROM C
WHERE CNAME=' 数据库原理 ')
4 .把WANG 同学的学习选课和成绩全部删去。 DELETEFROM SC WHERE Sno IN(SELECT Sno FROM S WHERE SNAME='WANG') 5 .在基本表 SC 中删除尚无成绩的选课元组。 DELETE FROM SCWHERE GRADE IS NULL 6 .在基本表 S 中检索每一门课程成绩都大于等于 80 分的学生学号、姓名和性别, 并把检索到的值送往另一个已存在的基本表 S1 ( Sno , SNAME , SSEX )。 select Sno,SNAME,SSEX intos1 from student delete from s1 INSERT INTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX FROMS WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE<80 AND S.Sno=SC.Sno) select * from s1 考虑:以上会有什么问题? INSERTINTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE<80 AND S.Sno=SC.Sno or S.Sno=SC.Sno and gradeis null) and sno in (select sno from sc) 7.往基本表 S 中插入一个学生元组( ‘ S9’,‘ WU ’,18 )。 INSERT INTO S(Sno,SNAME,SAGE) VALUES('59','WU',18) 三、问题描述:为管理岗位业务培训信息,建立 3 个表 : S (Sno,SN,SD,SA) Sno,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (Cno,CN ) Cno,CN 分别代表课程编号、课程名称 SC ( Sno,Cno,G ) Sno,Cno,G 分别代表学号、所选修的课程编号、学习成绩 要求实现如下 5 个处理: 1.使用标准 SQL 嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
SELECT SN,SD FROM S WHERE [Sno] IN( SELECT [Sno] FROM C,SC WHERE C.[Cno]=SC.[Cno] AND CN=N' 税收基础') 2.使用标准 SQL 嵌套语句查询选修课程编号为’ C2 ’的学员姓名和所属单位 SELECTS.SN,S.SD FROM S,SC WHERE S.[Sno]=SC.[Sno] ANDSC.[Cno]='C2' 3.使用标准 SQL 嵌套语句查询不选修课程编号为’ C5 ’的学员姓名和所属单位 SELECT SN,SD FROM S WHERE [Sno] NOT IN(
SELECT [Sno] FROM SC
WHERE[Cno]='C5') 4.使用标准 SQL 嵌套语句查询只选修了一门课程的学员姓名和所属单位
SELECT SN,SD FROM S WHERE [Sno] IN( SELECT [Sno] FROM SC inner JOIN C ON SC.[Cno]=C.[Cno] GROUP BY [Sno] HAVINGCOUNT(*)=1) 5.查询选修了课程的学员人数 SELECT 学员人数 =COUNT(DISTINCT[Sno]) FROM SC 6.查询选修课程超过 5 门的学员学号和所属单位 SELECT SN,SD FROM S WHERE [Sno] IN( SELECT [Sno] FROM SC GROUP BY [Sno] HAVING COUNT(DISTINCT [Cno])>5) 四、问题描述:已知关系模式: S(SNO,SNAME ) 学生关系。 SNO 为学号, SNAME 为姓名 C (CNO,CNAME,TEACHER) 课程关系。 CNO 为课程号, CNAME 为课程名, TEACHER 为任课教师 SC(SNO,CNO,GRADE) 选课关系。 GRADE 为成绩 要求实现如下 5 个处理: 1 . 找出没有选修过“李明”老师讲授课程的所有学生姓名 select sname from swhere not exists (select * from c,sc where c.cno=sc.cno AND c.teacher=N' 李明 ' AND s.sno=sc.sno) 参考: select sno,sname from s where sno not in (select sno from sc,c where c.cno=sc.cno ANDc.teacher=N'liu') 2 .列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
SELECT S.SNO,SNAME,AVG_GRADE=AVG(SC.GRADE) FROM S,SC WHERE GRADE<60 and s.sno=sc.sno GROUP BY s.SNO,s.sname HAVINGCOUNT(DISTINCT CNO)>=2 或
SELECT S.SNO,S.SNAME,AVG_GRADE=AVG(SC.GRADE) FROM S,SC,( SELECT SNO FROM SC WHERE GRADE<60 GROUP BY SNO HAVINGCOUNT(DISTINCT CNO)>=2 )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME 3 . 列出既学过“ 1 ”号课程,又学过“ 2 ”号课程的所有学生姓名 SELECT S.SNO,S.SNAME FROM S,sc where S.SNO=SC.SNO and cno='1' and s.sno in (select S.snofrom S,sc where S.SNO=SC.SNO and cno='2') 或 SELECT S.SNO,S.SNAME FROM S,( SELECT SC.SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.cno IN('1','2') GROUP BY SNO HAVINGCOUNT(DISTINCT c.CNO)=2 )SC WHERES.SNO=SC.SNO 4 .列出“ 1 ”号课成绩比“ 04010002 ”号同学该门课成绩高的所有学生的学号 SELECT S.SNO,S.SNAME FROM S,SC WHERESC.CNO='1'and SC.sNO=S.sNO ANDgrade>(select grade from s,sc wheres.SNO='04010002'and SC.CNO='1'and SC.sNO=S.sNO) 5 . 列出“ 1 ”号课成绩比“ 2 ”号课成绩高的所有学生的学号及其“ 1 ”号课和“ 2 ”号课的成绩 SELECT SC1.SNO,[1 号课成绩 ]=SC1.GRADE,[2 号课成绩 ]=SC2.GRADE FROMSC SC1,SC SC2 WHERE SC1.CNO='1' AND SC2.CNO='2' ANDSC1.SNO=SC2.SNO ANDSC1.GRADE>SC2.GRADE 四、通配符理解题(请说出下列通配符的含义) 1.LIKE 'Mc%' 将搜索以字母 Mc 开头的所有字符串(如 McBadden)。 2.LIKE '%inger' 将搜索以字母 inger 结尾的所有字符串(如 Ringer 、Stringer )。 3.LIKE '%en%' 将搜索在任何位置包含字母 en 的所有字符串(如 Bennet 、Green、McBadden)。 4.LIKE '_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称(如Cheryl、Sheryl )。 5.LIKE '[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称(如 Ringer )。 6.LIKE 'M[^c]%' 将搜索以字母 M 开头,并且第二个字母不是 c 的所有名称(如 MacFeather )。 五.问答题 1.请自定义分页存储过程! 答:
CREATE PROCEDURE [dbo].[Pro_GetPageData] @TableName nvarchar(3000), -- 表名 @ReturnFields nvarchar(3000) = '*', -- 需要返回的列 @PageSize int = 10, -- 每页记录数 @PageIndex int = 0, -- 当前页码 @Where nvarchar(3000) = '', -- 查询条件 @OrderBy nvarchar(200), -- 排序字段名 最好为唯一主键 @OrderType int = 1 -- 排序类型 1:降序 其它为升序 AS DECLARE @TotalRecord int DECLARE @TotalPage int DECLARE @CurrentPageSize int DECLARE @TotalRecordForPageIndex int declare @CountSql nvarchar(4000) if @OrderType = 1 BEGIN set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc ' END else BEGIN set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' asc,') + ' asc ' END -- 总记录 set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out SET @TotalPage=(@TotalRecord-1)/@PageSize+1 -- 查询页数不得大于总页数 if(@PageIndex > @TotalPage) set @PageIndex = @TotalPage SET @CurrentPageSize=(@PageIndex-1)*@PageSize -- 返回记录 set @TotalRecordForPageIndex=@PageIndex*@PageSize exec ('SELECT * FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM FROM '+@TableName+ ' ' + @Where +' ) AS TempTable WHERE TempTable.ROWNUM > '+@CurrentPageSize) -- 返回总页数和总记录 SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
2.请列举数据库层面性能优化策略!把你了解到的一种或者多种实现出来! 答:从Sql语句的优化,少使用like “%xxx%”,双百分号的模糊查询,尽量使用系统函数,如果有需要可使用临时表,添加数据库索引,配置数据读写分离(详细见文档---数据库读写分离配置文档) 3.说说数据库作业的实现! 答:数据库作业就是从数据库层面的定时任务,可以设置执行时间,和执行逻辑,到时间点就可以自动执行任务。(详细另外见文档—数据库job自动备份数据库); 4.请尽可能多的描述数据库函数! 答:SubString(表达式,起始,长度):取子串 Right(表达式,长度) :右边取子串 Str(浮点数[,总长度[,小数位]]):数值型转换字符型 LTrim(表达式)、RTrim(表达式):去左、右空格 CharIndex(子串,母串) :返回子串起始位置 Convert(数据类型[(长度)],表达式[,日期转字符样式]):表达式类型转换 Cast(表达式 As 数据类型[(长度)]):表达式类型转换 Abs(表达式):取绝对值 Power(底,指数):底的指数次方 Rand([整形数]);随机数产生器 Round(表达式,精度):按精度四舍五入 Sqrt(表达式):算术平方根 GetDate():当前的日期和时间 Day(表达式):表达式的日期值 Month(表达式):表达式的月份值 Year(表达式):表达式的年份值 IsDate(表达式):是否合理日期 IsNULL(是否空值,替代值):若空用指定值代替 IsNumeric(表达式):是否为合理的数值 Exists(子查询):检查结果集 Avg([Distinct 列名]):取均值 Count([Distinct 列名]):行数 Max([Distinct 列名]):最大值 Min([Distinct 列名]):最小值 Sum([Distinct 列名]):求和