数据库面试题集合

现在的位置: 首页 > 综合 > 正文

数据库面试题集合

2014年11月22日 ⁄ 综合 ⁄ 共 27968字 ⁄ 字号小中大 ⁄ 评论关闭

在整理准备数据库面试的过程中，先是在网上一顿海搜，找到历史面试题，然后一个骨头一个骨头的啃完，现在基本上这些问题（或者说叫做实践）都没有问题了。遇到的困难是：PL/SQL居多，T-SQL太少，所以需要筛选，修改答案，甚至有一些在T-SQL里面还没有支持。

下一步再把数据库T-SQL经典教程在翻看一遍，基本上对数据库就算告一段落了，前前后后共整整1个多月的时间（去年10.1是二周，下载是三周），学习的还行吧。

下面的就是全部内容，大段摘录的，或者是抄的，我都写了出处；有一些实在忘记了，请见谅：向大家共享知识，想必也是作者的本愿吧。

1. 三个范式

即：属性唯一, 记录唯一, 表唯一

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y

2. 一些常识：http://www.enet.com.cn/article/2007/0802/

² 简要叙述一下SQL Server 2000中使用的一些数据库对象

表格、视图、用户定义的函数，存储过程，触发器等。

² NULL是什么意思

NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

使用ISNULL（var，value）来进行NULL判断：当var为NULL的时候，var = value，并且返回value

² 什么是索引?SQL Server 2000里有什么类型的索引?

索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格，物理顺序和逻辑顺序一致。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。它允许每个表格有多个非聚集索引。

² 什么是主键?什么是外键?

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表(可能是同一表)里的字段。那么这些相连的字段就是外键。

² 什么是触发器?SQL Server 2000有什么不同类型的触发器?

有INSTEAD-OF和AFTER两种触发器。触发器是一种专用类型的存储过程，它被捆绑到表格或者视图上。INSTEAD-OF触发器是替代数据操控语言(DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA的INSTEAD-OF-UPDATE触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。

AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。

² 您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?

第一个答案(而且是您希望听到的答案)是使用外键限制。外键限制用来维护引用的完整性integrity。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。
另外一种答案是触发器。触发器可以被用来保证以另外一种方式实现与限制相同的作用，但是它非常难设置与维护，而且性能一般都很糟糕。由于这个原因，微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。

² 对一个投入使用的在线事务处理表格(OLTP)有过多索引需要有什么样的性能考虑?

对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。

² 你可以用什么来确保表格里的字段只接受特定范围里的值?

Check限制，它在数据库表格里被定义，用来限制输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

² 返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。

OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。

² 什么是相关子查询?如何使用这些查询?

相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

11. 某一列允许NULL值，但希望确保所有的非空（Non-NULL）值都是唯一的
SQL Server没有实现非NULL值唯一性的内建机制，因此需要通过自定义的trigger：

Create trigger mytrigger on t1 for insert, update as

BEGIN

IF (select max(cnt) from (select count(i.c1)as cnt

from t1, inserted i where t1.c1=i.c1 group by i.c1) x) > 1

ROLLBACK TRAN

END

3. 某列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据

http://www.blogjava.net/looline/archive/2006/12/08/86367.html

** HAVING子句对GROUP BY子句设置条件的方式与WHERE子句和SELECT语句交互的方式类似。WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。

² 显示数据库中的最后一条记录的所有字段（ID是自增的）

SELECT top 1 * FROM Table_Name ORDER BY ID DESC -- 或者

SELECT * FROM Table_Name WHERE ID=（SELECT MAX (ID) FROM Table_Name）

² 显示数据库中的最后十条记录的所有字段（ID 是自增的 DESC 做降序 ASC 做升序）

SELECT top 10 * FROM Table_Name ORDER BY ID DESC

² 对来自表 authors 的前十个作者的 state 列进行更新

UPDATE s SET s.saleprice = s.saleprice+2

FROM (SELECT TOP 10 * FROM sales ORDER BY saleid) AS t1, sales
s

WHERE s.saleid=t1.saleid

-- 或者

UPDATE s SET s.saleprice = s.saleprice+2 FROM sales
s

WHERE s.saleid in (SELECT TOP 10 saleid FROM sales ORDER BY saleid)

² 找出公司里收入最高的前三名员工

select top 3 * from t1 order by a desc --或者

Select top 3 *, ROW_NUMBER() OVER(order by a DESC) as No from t1

(根据分析，执行计划中的顺序：sort (order by )+ top 3, 然后是where等条件)

² 找出公司里收入最高(低)的三->五名员工

select top 3 a from t1 where a in ( select top 5 a from t1 order by a asc) order by a desc

--弊端：参与排序的一定要是index，或者unique，且选出来的只能是单一的一个列

-- 所以用下面的方法

SELECT top (10-3+1) * FROM (SELECT TOP 10 * FROM customers Order by zip asc)t order by zip desc

² 取出表A中第31条到第40条记录（SQLServer，以自动增长的ID为主键，注意：ID可能不是连续的。）

-- top 10 可以省略

SELECT top 10 * FROM A WHERE ID
not in (SELECT top 30 id FROM A)

² 显示出员工的平均工资大于3000元的部门名称（用SQL语句）

注意Full outer join，left join, right join，inner
join区别和联系

SELECT Dept_Name

FROM t_Dept

WHERE ID in (SELECT Dept_IDFROM t_Salary

GROUP BY Dept_ID --对部门分组（即：相同部门的，进行同一操作）

Having avg(Salary)>3000)

² 找出那些工资高于他们所在部门的平均工资的员工

select last_name, dept_id, salary from s_emp a
where salary>(select avg(salary) from s_emp where dept_id=a.dept_id)

² 找出那些工资高于他们所在部门的 manager 的工资的员工。

select id, last_name, salary, manager_id from s_emp a
where salary>(select salary from s_emp where id=a.manager_id)

² 有两个表分别如下：
表A(varchar(32) NAME,int GRADE)

数据：ZHANGSHAN 80, LISI 60, WANGWU 84
表B(varchar(32) NAME,int AGE)
数据：ZHANGSHAN 26, LISI 24, WANGWU 26, WUTIAN 26
1）写SQL语句得到如下查询结果：
NAME GRADE AGE
ZHANGSHAN    80 26
LISI 60 24
WANGWU 84 26
WUTIAN    NULL 26

答：select * from A right join B on A.NAME = B.NAME
2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。

答：avg(grade) group by name, age

4. 横表竖起来
请写出 SQl 语句实现题目要求的结果:写一个 SQL完成左边的表变成右边的表。

表的结构

要求结果

ProductID SALE_YEAR SALES

001 2001 10

002 2001 15

003 2002 12

003 2003 10

productID 2001 2002 2003

   001      10
002      15
003       12 10

² 交叉表的列数是确定的

select name,

sum(case subject when '数学' then source else 0 end) as '数学',

sum(case subject when '英语' then source else 0 end) as '英语',

sum(case subject when '语文' then source else 0 end) as '语文'

from test group by name

² 交叉表的列数是不确定的

declare @sql varchar(8000)

set @sql = 'select name,'

select @sql = @sql + 'sum(case subject when '''+subject+'''

then source else 0 end) as '''+subject+''','

from (select distinct subject from test) as a

select @sql = left(@sql,len(@sql)-1) + '
from test group by name'

exec(@sql)

5. SQLServer删除重复数据记录

http://www.cnblogs.com/luohoufu/archive/2008/06/05/1214286.html

有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复。

² 写出 SQl 语句（或 SQL 语句组），查询所有 id_no 重复的记录。

select dept_ID from salary

group by dept_ID having count(dept_ID) > 1

² 对于第一种重复，比较容易解决，使用

select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
truncate table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

² 这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下：

假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

² 部分关键字段重复，且记录中有ID. (***这个比较实用***)

第一种方法可一次删除所有重复的..(只保留重复中ID最小的记录)。
delete from table where id not in ( select min(id) from table group by name)第二种方法每次只删除重复中ID最大的一条记录。

delete from table where id in ( select max(id) from table group by name having count(*)>1)

² 使用SQL程序删除

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having
count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

² 自己还得出的办法：

select * from user1 where id not in (select top 1 id from user1 where name = user1.name) -- 两个name相等比较重要，否则就不对了。但是group
by更加好一些

删就这样写

delete from user1 where id not in (select top 1 id from user1 where name=user1.name)

或

delete from user where id not in ( select max(id) from user where name=user.name)

delete from user where id not in (select max(id) from user group by name having count(*) > 1)

其他方法

A:保留id最大(或者最小)的行,删除其它行

--方法1

delete [user] from [user] t

inner join(select name,max(id) as id from [user] group by name) a

on t.name = a.name and t.id <> a.id

--方法2

delete [user] from [user] t

where exists(select * from [user] where name = t.name and id > t.id)

B:删除所有重复的name行,一行也不留

delete [user] from [user] t

inner join

(select id from [user] a where exists(select * from [user] where name = a.name group by name having count(*) > 1)) as b

on t.id = b.id

6. 一些高难度的SQL

http://www.teecool.com/post/2007072807.html

² 如果表的结构和数据：

表1：usertable
USERID USERNAME
1 user1
2 null
3 user3
4 null
5 user5
6 user6

表2： usergrade;
USERID USERNAME GRADE
1 user1 90
2 null 80
7 user7 80
8 user8 90

那么，执行语句 select count(*) from usergrade where username not in (select username from usertable);

select count(*) from usergrade g where not exists (select null from usertable t where t.userid=g.userid and t.username=g.username);

结果为：语句1( 0 ) 语句2 ( 3 ) A: 0 B:1 C:2 D:3 E:NULL --- 不懂

² 在以下的表的显示结果中，以下语句的执行结果是（知识点：in/exists+rownum）

SQL> select * from usertable;
USERID USERNAME
1 user1
2 user2
3 user3
4 user4
5 user5

SQL> select * from usergrade;
USERNAME GRADE
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80

那么，执行语句Select count(*) from usertable t1 where username in
(select username from usergrade t2);

Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username );

以上语句的执行结果是：( c) (c ) A: 0 B: 1 C: 2 D: 3

count(*)和count(GRADE)有区别：前者统计NULL行；后者忽略NULL行

² 关联更新

有表一的查询结果如下，该表为学生成绩表select id,grade from student_grade
ID GRADE
1 50
2 40
3 70
4 80
5 30
6 90

表二为补考成绩表 select id,grade from student_makeup
ID GRADE
1 60
2 80
5 60

执行语句：
update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id);

-- 这里，把1、2、5更新为对应的结果，但是3、4、6也会被更新，但是由于没有对应的值，所以都被更新为了NULL

请问之后查询： select GRADE from student_grade where id = 3;结果为： (c)

A: 0 B: 70 C: null D: 以上都不对

7. 英文

http://www.teecool.com/post/2007072808.html

² Question 1： calculating the Number of Days in a Month

declare @now datetime

--select @now = getdate()

select @now = '20090211'

-- 方法是：下月日-本月日的日期差。所以先构造本月日，在构造下月日，减

select datediff(dd, dateadd(dd, -1* datepart(dd, @now) + 1, @now),

dateadd(MM, 1, dateadd(dd, -1* datepart(dd, @now) + 1, @now)) )

-- 方法：下月日的前一天就是本月的月末，这一天的‘dd’就是本月天数。

select datepart(dd,

dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)

+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))));

² Question2：How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?

select bookid,bookname,

price=case when price is null then 　　'unknown'

when price between 10 and 20 then '10 to 20' else price end

from books

Question3：to find duplicate values! Find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result:
au_lname number_dups
Ringer 2
Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname

--count(1)和count(*)结果一样，都是根据au_lname分组进行的组内全部统计

² Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?

　　You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quality. Table stores record all store
information.
I want to get the result look like as below:
Output:

stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
Barnum's   50 0 50 0 0
Bookbeat   55 25  30 0 0
Doc-U-Mat:   85 0 85 0 0
Fricative    60 35  0 0   25
Total     250 60 165 0 25

Answer 4：用动态SQL实现，前面有。不过看起来很复杂

use pubs

declare @s varchar(8000)

set @s='select isnull(stores.stor_name,''Total'') '

select @s=@s + ',[Qtr' + cast(datepart(q,ord_date) as char(1))

+ ']=sum(case when datepart(q,ord_date)='''

+ cast(datepart(q,ord_date) as char(1)) + '''
then qty else 0 end)'

from Sales

group by datepart(q,ord_date)

set @s=@s + ' ,sum(qty) "Total" from Sales inner join stores on Sales.stor_id = stores.stor_id '

set @s=@s + ' where datepart(yyyy,ord_date) = ''1993'' '

set @s=@s + ' group by stores.stor_name WITH ROLLUP '

set @s=@s + ' order by stores.stor_name '

print @s

exec(@s)

-- print @s的结果是这样的：

select isnull(stores.stor_name,'Total') , --
isnull把rollup造成的NULL值改成了Total，技巧

[Qtr1]=sum(case when datepart(q,ord_date)='1' then qty else 0 end),

[Qtr2]=sum(case when datepart(q,ord_date)='2' then qty else 0 end),

[Qtr3]=sum(case when datepart(q,ord_date)='3' then qty else 0 end),

[Qtr4]=sum(case when datepart(q,ord_date)='4' then qty else 0 end),

sum(qty) "Total"

from Sales inner join stores on Sales.stor_id = stores.stor_id

where datepart(yyyy,ord_date) = '1993'

group by stores.stor_name WITH ROLLUP --
rollup是一个很重要的东西

order by stores.stor_name -- order by，貌似不需要，或者用desc更好

² Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure
is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?

Tips: sp_recompile can recomplie a store procedure each time

Answer 5：在执行存储过程时,使用 with recompile 选项强制编译新的计划；使用sp_recompile系统存储过程强制在下次运行时进行重新编译

Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
line-no title_id
1          BU1032
2          BU1111
3          BU2075
4          BU7832
5          MC2222
6          MC3021
7          MC3026
Answer 6：
-- row_number()的这种用法据我了解不行，他必须和over连用

select row_number() over(order by title_id) as line_no ,title_id from titles

²  Question 7: the difference of two SQL statements at performance of execution?
Statement 1:
if NOT EXISTS ( select * from publishers where state = ‘NY’)
  SELECT ‘Sales force needs to penetrate New York market’
else
  SELECT ‘We have publishers in New York’
Statement 2:
if EXISTS ( select * from publishers where state = ‘NY’)
  SELECT ‘We have publishers in New York’
else
  SELECT ‘Sales force needs to penetrate New York market’
Answer 7：
不同点:初步感觉应该是第二个性能比较好，因为不需要遍历整个数据表，只要有一个存在的就可以结束。但是从执行计划的时间和IO看，结果是一样的。

² Question9: How can I get a list of the stores that have bought both ‘bussiness’ and ‘mod_cook’ type books?
In database pubs, use three table stores,sales and titles to implement this requestment. Now I want to get the result as below:
stor_id stor_name

...
7896 Fricative Bookshop
...
...
...
Answer 9：
use pubs

select distinct a.stor_id, a.stor_name from stores
a,sales b,titles c

where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and

exists(select 1 from sales k,titles g where stor_id=b.stor_id

and k.title_id=g.title_id and g.type='mod_cook');

-- 或者使用个连续的join也可以

select distinct a.stor_id, a.stor_name from stores
a

join sales b on a.stor_id=b.stor_id

join titles c on b.title_id=c.title_id and c.type='business'

and exists (select * from sales
k,titles g where stor_id=b.stor_id

and k.title_id=g.title_id and g.type='mod_cook')

Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type title price
business The Busy Executive’s Database Guide 19.9900
...
...
Answer 11：
select distinct t1.price, t1.type from titles t1, titles
t2

where t1.price <

( select avg(price) from titles
t2 where t1.type = t2.type group by type )

-- 或者

select distinct t1.price, t1.type from titles
t1

join titles

on t1.price <

( select avg(price) from titles
t2 where t1.type = t2.type group by type )

8. 其他

http://www.teecool.com/post/2007071809.html

² 有订单表SO_Table，单号字段RefNo VARCHAR(10)，需要实现自动编号，格式为YYYYMMXXXX，其中XXXX为序号，如：2004050001，2004050002……2004059999等，采用Transact-SQL实现新订单编号的思路。

思路：

1，IDENTITY(smallint, 100, 1)只有在select…into这样的数据插入里面可用；

2，6位长度的年月可以用：convert(char(6),
getdate(), 112)；

或者cast(datepart(YY, @date) as varchar(4)) +

right(cast(datepart(MM, @date)+100 as varchar(3)), 2)

3，如果必须在SP里面工作的话，可以获得上次的最大序号，加1

² 有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何确何数据的完整性。

使用trigger可以做到；另外，添加过程要在一个transaction中进行；

² 如何求表中相邻(按聚集索引相邻)的两条记录的某字段的值之差。

select s2.saleid, p=(s1.saleprice-s2.saleprice)

from

(select *, id=row_number() over(order by saleid) from sales) s1,

(select *, id=row_number() over(order by saleid) from sales) s2

where s2.id-s1.id=1

-- 下面的办法不行，因为如果相邻的ID不是连续的就不行了。另外，可以使用cursor

select a.source - b.source from test a, test
b

where (a.id - b.id) = 1

order by a.id

² 如何删除表中的重复数据。上面有答案。

²  人员情况表（employee）中字段包括，员工号（ID），姓名（name），年龄（age），文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。结果如下：
学历       年龄      人数        百分比
本科以上   20        34           14
大专       20        33           13
高中       20        33           13
初中以下   20        100          40
本科以上   21        50           20
。。。。。。

Transact-SQL查询语句如何写？

-- Count(*) * 100 由group by限定；SELECT Count(*) FROM是总数。

SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,

Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比

FROM employee GROUP BY wh,age

²  表一(AAA)
商品名称a   商品总量b
   A          100
   B          120
表二(BBB)
商品名称a   出库数量b
    A          10
    A          20
    B          10
    B          20
    B          30

用一条Transact-SQL语句算出商品A,B目前还剩多少？

select sum(b)

from (select * from a

union all select a, b*(-1) from b) x

group by a

² 找到连续编号中断的那一个的最小值

select min(t.id) as id from (select id=id+1 from tt) t
where id not in (select id from tt)

9. 实际应用

A) 为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

² 查询选修课程名称为’税收基础’的学员学号和姓名

Select SN,SD FROM S -- 实际上，使用3个join才是合理的

Where [S#] IN(

Select [S#] FROM C,SC

Where C.[C#]=SC.[C#]

AND CN=N'税收基础')

² 查询选修课程编号为’C2’的学员姓名和所属单位

Select S.SN,S.SD FROM S,SC

Where S.[S#]=SC.[S#]

AND SC.[C#]='C2'

² 查询不选修课程编号为’C5’的学员姓名和所属单位

Select SN,SD FROM S

Where [S#] NOT IN( -- not in是正确的。不能使用=，会遗漏

Select [S#] FROM SC

Where [C#]='C5')

² 4. 查询选修全部课程的学员姓名和所属单位
-- 1）RIGHT JOIN可产生NULL行；2）COUNT(*),COUNT(S#)的结果不同

-- 解释：如果COUNT(*)<>COUNT(S#)，

-- 说明C中有某个C#没有在sc中出现，也就是说：这门课程没有被S#同学选中。

Select SN,SD FROM S

Where [S#] IN(

Select [S#] FROM SC

RIGHT JOIN

C ON SC.[C#]=C.[C#] GROUP BY [S#]

HAVING COUNT(*)=COUNT([S#]))

² 5. 查询选修了课程的学员人数

Select 学员人数=COUNT(DISTINCT [S#]) FROM SC

² 6. 查询选修课程超过5门的学员学号和所属单位

-- 关注having子句中的count()等聚合函数的使用

Select SN,SD FROM S

Where [S#] IN(

Select [S#] FROM SC

GROUP BY [S#]

HAVING COUNT(DISTINCT [C#])>5)

B)该题目中，很多都有歧义，所以只要掌握的基本的方法，不需要深究细节。

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

² 找出没有选修过“李”老师讲授课程的所有学生姓名

select distinct sname from s

where s.sno not in --因为有这种情况：S.SNO不在SC中存在。

(select sno from sc,c where sc.CNO=c.cno and cteacher = '李')

² 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,(

Select SNO FROM SC Where SCGRADE<60

GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2

)A -- 实际上，使用 IN 进行条件连接也行

-- where控制条件，having控制分组条件并聚合

Where S.SNO=A.SNO AND SC.SNO=A.SNO

GROUP BY S.SNO,S.SNAME

--下面是错误结果：它只是把没有及格的科目进行了avg，并不是所有的科目的avg

select avg(sc.scgrade), s.sname

from s

join sc on sc.sno = s.sno and sc.scgrade < 60

group by s.sname having count(sc.scgrade)>=2

² 列出既学过“c1”号课程，又学过“c2”号课程的所有学生姓名

select s.sname

from s, (select * from sc

where sc.sno in (select sc.sno from sc where sc.cno = 'c1')

and sc.cno = 'c2') SS -- 把sc.sno in换为exists也可以。两种方案

where s.sno = ss.sno

² 列出“c1”号课成绩比“s2”号同学该门课成绩高的所有学生的学号

select sc.sno, sc.scgrade from sc

where sc.cno='c1' and

sc.scgrade>(select sc.scgrade from sc where sc.cno='c1' and sc.sno='s2')

² 列出“c1”号课成绩比“c2”号课成绩高的所有学生的学号及其“c1”号课和“c2”号课的成绩

select sc1.sno, sc1.cno, sc1.scgrade

from sc sc1,

(select sc1.sno, sc1.cno, sc1.scgrade

from sc sc1, sc sc2

where sc1.cno='c1' and sc2.cno='c2' and sc1.scgrade>sc2.scgrade

and sc1.sno=sc2.sno) sc2

where sc2.sno=sc1.sno and (sc1.cno='c1' or sc1.cno='c2')

1. 老外的一套题

我需要一个模式和一些数据来运行所要考核的查询，列表A创建了所需的这些数据：

IF OBJECT_ID('Sales')>0 DROP TABLE Sales

IF OBJECT_ID('Customers')>0 DROP TABLE Customers

IF OBJECT_ID('Products')>0 DROP TABLE Products

CREATE TABLE Customers(CustomerID INT IDENTITY PRIMARY KEY ,

FirstName VARCHAR(50),

LastName VARCHAR(50),

City VARCHAR(50),

State CHAR(2),

Zip VARCHAR(10) )

CREATE TABLE Products(

ProductID TINYINT IDENTITY PRIMARY KEY ,

ProductName VARCHAR(20),

RecommendedPrice MONEY,

Category VARCHAR(10))

CREATE TABLE Sales(SaleID INT IDENTITY PRIMARY KEY ,

ProductID TINYINT NOT NULL REFERENCES Products(ProductID),

CustomerID INT NOT NULL REFERENCES Customers(CustomerID),

SalePrice MONEY NOT NULL,SaleDate SMALLDATETIME NOT NULL)

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('DVD',105,'LivingRoom')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Microwave',98,'Kitchen')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Monitor',200,'Office')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Speakers',85,'Office')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Refrigerator',900,'Kitchen')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('VCR',165,'LivingRoom')

INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('CoffeePot',35,'Kitchen')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('John','Miller','Asbury','NY','23433')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Fred','Hammill','Basham','AK','85675')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Stan','Mellish','Callahan','WY','38556')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Adrian','Caparzo','Denver','CO','12377')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Mike','Horvath','Easton','IN','47130')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Irwin','Wade','Frankfurt','KY','45902')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('George','Marshall','Gallipoli','ND','34908')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Frank','Costello','Honolulu','HI','23905')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Billy','Costigan','Immice','SC','75389')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Shelly','Sipes','Lights','AZ','35263')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Chirsty','Melton','Spade','CA','97505')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Amanda','Owens','Flask','CN','50386')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Brittany','Smits','Bourbon','KY','24207')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Kristy','Bryant','Tarp','FL','58960')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Kelly','Street','TableTop','ID','57732')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Tricia','Hill','Camera','ME','46738')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Holly','Raines','Compact','MS','35735')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Natalie','Woods','Woods','IN','87219')

INSERT INTO Customers(FirstName,LastName,City,State,Zip)

VALUES('Wendy','Hilton','Action','KY','47093')