程序员面试题分享（Sql篇）

程序员面试题分享（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 列名])：求和