面试题一
 
假设只有一个table，名为pages，有四个字段，id, url,title,body。里面储存了很多网页，网页的url地址，title和网页的内容，然后你用一个sql查询将url匹配的排在最前，title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。
 
答：
 
就是上面这道面试题，让我想了一个下午，在网上找资料，最后用下面方法实现

SELECT *
FROM page where url like '%baidu%' or title like '%baidu%' or like ''
ORDER BY CHARINDEX('baidu', url) DESC, CHARINDEX('baidu', title) DESC,
      CHARINDEX('baidu', body) DESC
但我感觉这种方法并不是最简单的，后来把这个方法发给面试的人，他给我了一种更简单方法，只要用基本的Sql语句就可以实现。代码如下
 
select a.[id],a.mark from
(
select [page].[id],100 as mark from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as mark from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as mark from [page] where [page].[body] like '%baidu%'
) as a  order by mark desc
用union 实现联合查询，在每个查询语句中定义一个临时变量mark 并给mark赋值，在最后的输出时采用mark来排序，这样实现真的好简单。其实这都考验我们对Sql的编成思想。
 
面试题二
 
自定义函数和存储过程的区别是什么，什么情况下只能用自定义函数，什么情况下只能用存储过程？
 
答：
 
自定义函数：一般用于在其他SQL中调用。自定义函数中有诸多限制，有许多语句不能使用，许多功能不能实现
 
存储过程：功能远强于函数，可以执行包括修改表等一系列数据库操作，可以由外部应用程序进行调用或在SQL中调用。以下两种情况下只能用存储过程，而不能用自定义函数 ：1.要执行DML,修改数据。2.要执行动态SQL语句,执行任何存储过程。
 
具体比较如下：
  • 存储过程，在SQL中用 EXECUTE 语句执行。
  • 自定义函数，在SQL中通常在查询语句中调用。如：select  yourfunc(...)  from  table
 
  • 存储过程，其返回值不能被直接引用。
  • 自定义函数，其返回值可以被直接引用。
 
  • 存储过程，可返回记录集。
  • 自定义函数，可以返回表变量。
 
  • 存储过程，功能强大，可以执行包括修改表等一系列数据库操作，也可以创建为 SQL Server 启动时自动运行的存储过程。
  • 自定义函数，用户定义函数不能用于执行修改数据库的操作。(待验证)
 
 
---------------------------------------------------------------
 
详细说明：
 
存储过程： 
 
存储过程是 SQL 语句和控制流语句(可选)的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，允许用户声明变量、有条件执行以及其它强大的编程功能。
 
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 
它具有以下优点： 
1、可以在单个存储过程中执行一系列 SQL 语句。 
2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。 
3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 

用户定义函数： 

自定义函数分为：标量值函数 和 表值函数
  • 如果 RETURNS 子句指定一种标量数据类型（如 int 类型），则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。
  • 如果 RETURNS 子句指定 TABLE，则函数为表值函数。
表值函数：又可分为 内嵌表值函数（行内函数）和  多语句函数
  • 如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
  • 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数。
示例：标量值函数
CREATE FUNCTION dbo.Foo()
RETURNS int
AS 
BEGIN
    declare @n int
    select @n=3
    return @n
END
 
 
示例：内嵌表值函数
CREATE FUNCTION dbo.Foo()
RETURNS TABLE
AS 
    return select id, title from msgs
内嵌表值函数只有一个 select 语句。
 
 
示例：多语句表值函数（部分）
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
...
注意其 RETURNS 部分。
多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。
  • 赋值语句。
  • 控制流语句。
  • DECLARE 语句，该语句定义函数局部的数据变量和游标。
  • SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
  • 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。
  • INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。
  • EXECUTE 语句调用扩展存储过程。
 
面试题三
 
SQL 编程题：

有表A，结构如下：      
    A:       p_ID                   p_Num                   s_id      
                1                           10                       01      
                1                           12                       02      
                2                           8                         01      
                3                           11                       01      
                3                           8                         03      
    其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
 
请用SQL语句实现将上表中的数据合并，合并后的数据为：      
    p_ID                 s1_id                       s2_id                   s3_id      
    1                           10                           12                         0      
    2                             8                           0                           0      
    3                           11                           0                           8      
    其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
 
答：
 
SELECT     P_ID,
SUM(CASE S_ID WHEN 01 THEN p_NUM ELSE 0 END) AS 第一仓库,
SUM(CASE S_ID WHEN 02 THEN P_NUM ELSE 0 END) AS 第二仓库,
SUM(CASE S_ID WHEN 03 THEN P_NUM ELSE 0 END) AS 第三仓库
FROM         TEMP2
GROUP BY P_ID
 
本题考查的是 Group by  和  Case 语句。

面试题四
 
SQL 编程题：
 
有表 Table_1 ,数据如下
ID    NAM    JIEGUO
1        A         WIN
2        A         LOST
3        A         WIN
4        A         LOST
6        B         WIN
5        A         WIN
7        B         LOST
8        B         LOST
 
要求统计出，如下结果
NAM    Win_num  Lost_num
  A              3            2
  B              1            2
 
答：
 
select nam,
count(case jieguo when 'WIN' THEN 1 ELSE Null END) as Win_num,
count(case jieguo when 'LOST' THEN 1 ELSE Null END) as Lost_num
from TABLE_1
group by nam
 
或者
 
select nam,
sum(case jieguo when 'WIN' THEN 1 ELSE 0 END) as Win_num,
sum(case jieguo when 'LOST' THEN 1 ELSE 0 END) as Lost_num
from TABLE_1
group by nam
 
本题考查的是 Group by  和  Case 语句。
 
 
面试题五
 
SQL 编程题：
 
 
给下面这样的一个表记录：

------------------------------------
CUSTOMER_NAME      TRADE_NAME    NUM
A                     甲          2
 B                 乙        4
C                 丙        1
A                 丁        2
B                 丙        5
给出所有购入商品为两种或两种以上的购物人记录。
 
 
 
答：
 
方法一（推荐，使用了 count(distinct 列名) ）
 
select * from T1
where customer_name in
(select customer_name
from T1 as b
group by customer_name
having count(distinct trade_name)>=2)
 
说明：
having count ，发生在分组之后，对每个分组包含的行，进行统计count(distinct trade_name)，根据这个统计值（每个分组一个），确定是否输出此分组。
 
方法二（容易看迷糊，用了2次 group by）
 
select * from T1 WHERE CUSTOMER_NAME IN
(SELECT * FROM
(select CN1 as CN from (select customer_name AS CN1,trade_name AS TN1 from T1 as B group by customer_name,trade_name) as A group by CN1
having count(TN1)>=2) as B )
 
说明：
1.group by customer_name,trade_name ：用于排除 （customer_name + trade_name） 重复的行，只留一行）。
 
2.group by CN1 having count(TN1)>=2 ：用于排除只买了一种商品的顾客。

 
 
面试题六
 
SQL 编程题：
 
Student表有三列,分别是姓名、课程、成绩(见下表)，其中stu_name列有约1000行，course列除了已经列出来的 马哲、数学、英语、语文外，还有一些学科种类没有列完（即 学科种类数不定），未参加考试的学生 Mark 列值为0 。
 
stu_name      course       mark
城南              马哲         70
城南              数学         65
城南              英语         58
城南              语文         79
李四              马哲         61
李四              数学         80
李四              英语         77
李四              语文         80
王朝              马哲         52
王朝              数学         55
王朝              英语         59
王朝              语文         90
张三              马哲         66
张三              数学         88
张三              英语         61
张三              语文         70
 
要求查询：
1、每一门课程都及格的学生的姓名
2、总分排名在前三名的学生的姓名
 
 
答：
 
1、
 
SELECT stu_name
FROM T2
GROUP BY stu_name
HAVING (COUNT(CASE WHEN mark >= 60 THEN 1 ELSE NULL END) = COUNT(Mark))
 
说明：此题的关键点是 “学科种类数不定” 和 “每一门都及格”，此点用 COUNT(CASE WHEN mark >= 60 THEN 1 ELSE NULL END) = COUNT(Mark)   来实现。
 
还有更加简单、易理解的解答：
从 原始表 中 排出 那些 不及格的人，就可以啦。
SELECT Distinct name  FROM StudentScore
WHERE name   NOT   IN
(Select Distinct name from StudentScore where score<60);
 
2、
 
select top 3 stu_name,sum(mark) as 总分
from T2
group by stu_name
order by sum(mark) desc
 
说明：此题很简单，会用 Top 即可。
 
还有一种解答，SQL2005以上版本才能用的。
SELECT
ROW_NUMBER()  OVER(ORDER BY SUM(SCORE) DESC)  AS  ID,
NAME,
SUM(SCORE)  AS  TOTAL
FROM StudentScore
GROUP BY name;
 
 
 
面试题七(推荐)
 
SQL 编程题：
 
表className中有如下分类:
 
classID   className
1              衣服
2              裤子
5              帽子
10            鞋子
 
 
表productInfo有如下记录:
 
productID             productName            parentID            clickNum

1                            男士衣服                      1                         90     --衣服类别中点击率最高
2                            女士衣服                      1                         80
3                            男士裤子                      2                         70
4                            女士裤子                      2                         90     --裤子类别中点击率最高
5                            男士帽子                      5                         15
6                            女士帽子                      5                         30     --帽子类别中点击率最高
7                            男士鞋子                      10                       65     --鞋子类别中点击率最高
8                            女士鞋子                      10                       52
9                            女士鞋子1                    10                       54
 
现在要求分别把衣服,裤子,帽子,鞋子这些类别中点击率最高的一条记录找出来,然后再降序排列,结果应如下:
 
productID             productName            clickNum
1                            男士衣服                      90
4                            女士裤子                      90
7                            男士鞋子                      65
6                            女士帽子                      30
 
答：
 
select productid,productname,clicknum
from ProductInfo
inner join
(select parentid as P_id,max(clicknum) as Maxclick
from  ProductInfo  group by parentid) as A
on T3.parentid=A.P_id and T3.clicknum=A.Maxclick
order by clicknum desc,productid asc
 
说明：
不要被题目给出的信息迷惑，本题中第一个表 className ，对于统计过程来讲，是多余的（根本用不到）。
本题考查的是 多表内联接查询——根据ProductInfo查询生成一个新表 A （包含 类别、该类别最高点击率），然后再对 ProductInfo表 和 A表 ，进行多表查询。
 
多表内联接查询：
方法1：用   ...   inner  join  ...  on  ... 
方法2：也可以用 from  表1  ,   表2    where .....
 
 
 
 
面试题八
 
SQL 编程题：
 
有三个表：

一张 老师表TecTable，字段是T_ID,T_NAME；
一张 学生表StuTable，字段是S_ID,S_NAME；
一张 班级表ClassTable，字段是T_ID,S_ID,C_NAME，其中C_NAME的取值只有‘大班’和‘小班’。

请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带的学生数。
 
 
答：
 
未验证
 
select
TecTable.Techer_ID,
Techer_NAME,
count(case Class_name when '大班' then 1 else null end) as Big_stu_num,
count(case Class_name when '小班' then 1 else null end) as Little_stu_num
from TecTable,StuTable,ClassTable
where
 TecTable.Techer_ID=ClassTable.Techer_ID and StuTable.Stu_id=ClassTable.Stu_id
group by Techer_ID,Techer_NAME
having Big_stu_num > Little_stu_num
 
说明：
 
本题没有给出具体数据，只给出了每个表的定义，一下子不容易看懂。
但是，仔细看懂后，发现其实很简单。
1、需要对3个表，进行 内联接，生成一个新表，统计工作在此新表上进行。
2、用 Group by 进行分类，用 Count（大班 ) 、Count（小班 ) 对每一个分组进行内部统计。
3、运用 Having 语句，对分组进行筛选——Count（大班 )  > Count（小班 ) ，以确定 最终输出哪些分组。
 
 
网上还有一种解法：
 
select * from T,
(select count(*) as x,T_ID from C where c_name='小班' group by T_ID) a,
(select count(*) as x,T_ID from C where c_name='大班' group by T_ID) b where b.x >a.x and a.T_ID=b.T_ID and T.T_ID=b.T_ID
 
group by后面还要多加个班级名。
 
 
 
 
面试题九
 
SQL 编程题：
 
已知一个表T4, 结构为：

stu_name     course     result
   张三          语文        20
   张三          数学        30
   张三          英语        50
   李四          语文        70
   李四          数学        60
   李四          英语        90

问：怎样通过SQL 的 Select 语句输出以下结构的数据
 
stu_name    语文    数学    英语
   张三         20        30        50
   李四         70        60        90
 
 
 
答：
 
SELECT
A1.STU_NAME,A1.RESULT AS '语文',A2.RESULT AS '数学',A3.RESULT AS '英语'
FROM
T4 A1 INNER JOIN T4 A2 ON A1.STU_NAME=A2.STU_NAME
INNER JOIN T4 A3 ON A2.STU_NAME=A3.STU_NAME
WHERE
A1.COURSE='语文' AND A2.COURSE='数学' AND A3.COURSE='英语'
 
注意：本题中，因为 A1、A2、A3 表 完全相同，所以 INNER JOIN ...  ON ...  ，可以用 LEFT JOIN  ...   ON  或  RIGHT JOIN ...   ON  ... 来替代。
 
 
说明：
本题主要考察，多个表（3个表）的连接查询、自连接查询。
需要充分了解 内连接inner join ，外连接 left join / right join ，交叉连接 cross join ，这三种连接的区别和语法。
 
INNER JOIN ...  ON ...  WHERE ...
 
等值连接，根据 ON 的条件，相等的行进行连接，不相等的行不进行连接。
1、INNER JOIN 必须和 ON 配对出现，缺少 ON  将出现语法错误！
2、多表（3个或3个以上表）等值连接的写法只能是—— ... INNER JOIN ...  ON ...  INNER JOIN ...  ON ...
 
 
 
LEFT  JOIN ...  ON ... WHERE ...
 
左连接，根据 ON 的条件，相等的行进行连接， 不相等的行不进行连接。
1、LEFT JOIN 必须和 ON 配对出现，缺少 ON 将出现语法错误！
2、多表（3个或3个以上表）左连接的写法只能是—— ... LEFT JOIN ...  ON ...  LEFT JOIN ...  ON ...
3、结果表以“左表” 的行 为基准行。
4、如果是 表自身进行连接（如本题中A1 、A2、 A3其实都是T4表），LEFT JOIN  与 INNER JOIN 的结果完全一样。
5、如果是 不同的表进行连接，如 Table1  LEFT JOIN  Table2 ON ... ，则左表 Table1 的所有行将出现的结果表中（“左表” 的行做为基准行），右表 Table2 中满足 ON 条件的行，才出现的结果表中，右表 Table2 中不满足 ON 条件的行，全部以 NULL 代替。
 
LEFT JOIN  ...  ON  ...  举例 ：
 
id     course         id         result
1       yuwen         1            70
2       shuxue       NULL    NULL
3       yinyu         NULL     NULL
 
 
RIGHT JOIN ...  ON ...  WHERE ...
 
 
右连接，根据 ON 的条件，相等的行进行连接，不相等的行不进行连接。
1、RIGHT JOIN 必须和 ON 配对出现，缺少 ON 将出现语法错误！
2、多表（3个或3个以上表）左连接的写法只能是—— ... RIGHT JOIN ...  ON ...  RIGHT JOIN ...  ON ...
3、结果表以“右表” 的行 为基准行。
4、如果是 表自身进行连接（如本题中A1 、A2、 A3其实都是T4表），RIGHT JOIN  与 INNER JOIN 的结果完全一样。
5、如果是 不同的表进行连接，如 Table1  RIGHT JOIN  Table2 ON ... ，则右表 Table2 的所有行将出现的结果表中（“右表” 的行做为基准行），左表 Table21中满足 ON 条件的行，才出现的结果表中，左表 Table1 中不满足 ON 条件的行，全部以 NULL 代替。
 
 
RIGHT  JOIN  ...  ON  ...  举例 ：
 
  id         course    id     result
NULL     NULL     11       60
NULL     NULL     12       80
NULL     NULL     13       90
    1         yuwen     1        70
 
 
CROSS  JOIN ...  ON ...  WHERE ...
 
1、交叉连接，不能使用 ON 关键字，只能用Where 。
2、交叉连接 ，可以对多个表进行（2个表、3个表、...)。
3、交叉连接的结果 是 笛卡尔乘积。
 
 
 
 
面试题十
SQL 编程题：
有一个学生表，结构如下：
id  name course score
1学生1 语文  88.50
2学生1 数学  95.00
3学生1 英语  85.50
4学生2 语文  100.00
5学生2 数学  76.00
6学生2 英语  70.50
7学生3 语文  68.50
8学生3 数学  77.50
9学生3 英语  91.50
10学生4 语文  72.50
11学生4 数学  86.50
12学生4 英语  77.50
要求进行行转列，得出如下结果：
 
name    语文      数学    英语
学生2   100.00   76.00    70.50
学生1   88.50     95.00    85.50
学生4   72.50     86.50    77.50
学生3   68.50     77.50    91.50
 
 
答：
此题其实很简单，用到 group by 、  case... when... then... end  、sum()
(已经调试通过)
 
select
name,
sum(case cource when '语文' then score else 0 end) as '语文',
sum(case cource when '数学' then score else 0 end) as '数学',
sum(case cource when '英语' then score else 0 end) as '英语'
from Score
group by name
order by '语文' desc