数据库面试题

sql面试题

1，写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的。

答：

→ 解1: select top 10 * from A where id not in (select top 30 id from A)

演变步骤：

1）select top 30 id from T_FilterWords–取前条

2）select * from T_FilterWords where id not in (select top 30 id from T_FilterWords)–取id不等于前三十条的–也就是把前条排除在外

3）select top 10 * from T_FilterWords where id not in (select top 30 id from T_FilterWords)

–取把前条排除在外的前条，也就是-40条

→ 解2: select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)

→ 解3：用ROW_NUMBER实现

/———————————————–+———————————————–\

下面的SQL题是常考题中的常考题，必须重视！！！！！！！！

2，横表、纵表转换（常考!!!）

1）纵表结构 TableA

NameCourseGrade张三语文75张三数学80张三英语90李四语文95李四数学55

横表结构 TableB

Name语文数学英语张三758090李四95550

先理解：

select Name，
(case Course when ‘语文‘ then Grade else 0 end) as 语文，
(case Course when ‘数学‘ then Grade else 0 end) as 数学，
(case Course when ‘英语‘ then Grade else 0 end) as 英语
from TableA

然后理解标准答案：

select Name，
sum(case Course when ‘语文‘ then Grade else 0 end) as 语文，
sum(case Course when ‘数学‘ then Grade else 0 end) as 数学，
sum(case Course when ‘英语‘ then Grade else 0 end) as 英语
from TableA
group by Name

2）、横表转纵表的”SQL”示例

横表结构: TEST_H2Z

ID 姓名语文数学英语

1 张三 80 90 70

2 李四 90 85 95

3 王五 88 75 90

转换后的表结构:

ID 姓名科目成绩

1 张三语文 80

2 张三数学 90

3 张三英语 70

4 李四语文 90

5 李四数学 80

6 李四英语 99

7 王五语文 85

8 王五数学 96

9 王五英语 88

横表转纵表SQL示例:

SELECT 姓名,’语文’ AS 科目,语文 AS 成绩 FROM TEST_H2Z UNION ALL

SELECT 姓名,’数学’ AS 科目,数学 AS 成绩 FROM TEST_H2Z UNION ALL

SELECT 姓名,’英语’ AS 科目,英语 AS 成绩 FROM TEST_H2Z

ORDER BY 姓名,科目 DESC;

3，删除姓名、年龄重复的记录(只保留Id最大的一条)（常考！！！）

Id name age salary

1 yzk 80 1000

2 yzk 80 2000

3 tom 20 20000

4 tom 20 20000

5 im 20 20000

//取得不重复的数据

select * from Persons
where Id in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name, Age
)

→ 根据姓名、年龄分组，取出每组的Id最大值，然后将Id最大值之外的排除。

删除重复的数据：

delete from Persons
where Id not in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name, Age
)

4，下面是一个由*号组成的4行倒三角形图案。

→ 要求：1、输入倒三角形的行数，行数的取值3-21之间，对于非法的行数，要求抛出提示“非法行数！”；2、在屏幕上打印这个指定了行数的倒三角形。

*******

*****

***

5，一个文本文件含有如下内容：

4580616022644994|3000|赵涛

4580616022645017|6000|张屹

4580616022645090|3200|郑欣夏

→ 上述文件每行为一个转账记录，第一列表示帐号，第二列表示金额，第三列表示开户人姓名。

创建一张数据库表（MS SQLServer数据库，表名和字段名自拟），请将上述文件逐条插入此表中。

6、一个文本文件含有如下内容，分别表示姓名和成绩：

张三 90

李四 96

王五 78

赵六 82

→ 提供用户一个控制台界面，允许用户输入要查询的姓名，输入姓名并且按回车以后，打印出此人的成绩，如果不输入姓名直接按回车则显示所有人的姓名以及成绩。（注意：不能使用数据库）

7，表A字段Id为numberic(18,0)，哪个SQL语句是错误的:

select * from A where id=”;
select * from A where id=’13′;
select * from A where id=null;
select * from A where id=’ 13′;

8，在SQLServer中求当前时间与2012-01-01 0：0:0相差的秒数？

9，做一个表格，三行三列，第一列，前两行合一；第二行，后两列合一。

10，

表一：student_info

学号姓名性别出生年月家庭住址备注

0001 张三男 1981-8-9 北京 NULL

表二：curriculum

课程编号课程名称学分

0001 计算机基础20002 C语言 2

表三：grade

学号课程编号分数

0001 0001800001000 290

题目：

→ 条件查询：

在GRADE表中查找80-90份的学生学号和分数

select 学号,分数 from grade where 分数 between 80 and 90

在GRADE 表中查找课程编号为003学生的平均分

select avg(分数) from grade where 课程编号=’003′

在GRADE 表中查询学习各门课程的人数

Select 课程编号,count(学号) as 人数from grade group by 课程编号

查询所有姓张的学生的学号和姓名

select 姓名,学号 from student_info where 姓名 like ‘张%’

→ 嵌套查询：

1，查询和学号’0001’的这位同学性别相同的所有同学的姓名和出生年月

select 姓名,出生年月 from student_info where 性别 in(select 性别 from student_info where sno=’0001′)

2，查询所有选修课程编号为0002 和0003的学生的学号、姓名和性别

select 学号,姓名,性别 from student_info where 学号 in(select 学号 from grade where 课程编号=’0002′ and 学号 in(select 学号 from grade where 课程编号=’0001′))

3，查询出学号为0001的学生的分数比0002号学生最低分高的课程编号的课程编号和分数

select 课程编号, 分数 from grade where 学号=’0001′ and 分数>(select min(分数) from grade where 学号=’0002′)

→ 多表查询：

1，查询分数在80-90分的学生的学号、姓名、分数

select student_info.学号,student_info.姓名,grade.分数 from student_info,grade where grade.分数 between 80 and 90

2，查询学习了’C语言’课程的学生学号、姓名和分数

select student_info.学号,student_info.姓名,grade.成绩from student_info,grade,curriculum where student_info.学号=grade.学号and grade.课程号=curriculum.课程号and curriculum.课程名=’C语言’

3，查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选课的学生总成绩为空。

select grade.学号,student_info.姓名,sum(grade.成绩) as 总成绩from student_info,grade where grade.学号=student_info.学号group by grade.学号,student_info.姓名

11，题目、活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）

1、写出设计以上表格的语句（4分）

2、创建一个触发器TR1完成下面内容：

→ 当向“存取款单”表中插入数据时，如果存取标志＝１则应该更改储户表让存款额加上存取金额，如果存取标志＝0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。

CREATE TABLE CREATE TRIGGER tr1 on qukuan after insert
AS
BEGIN
declare @sid nvarchar(50)
declare @type int
declare @qian int
declare @yuer int
select @sid=sid,@type=[type],@m=m from inserted
select @yuer=yuer from cunkuan
if(@type=1)
begin
update cunkuan set yuer=yuer+@qian
end
else
begin
if(@yuer<@qian)
begin
print ‘余额不足’
end
else
begin
update cunkuan set yuer=yuer-@qian
end
end
END
GO

12，本题用到下面三个关系表：www.chengxiaoxiao.com

CARD 借书卡: (CNO 卡号，NAME 姓名，CLASS 班级)
BOOKS 图书: (BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 )
BORROW 借书记录: (CNO 借书卡号，BNO 书号，RDATE 还书日期

→ 备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

→ 要求实现如下处理：

1)，写出自定义函数，要求输入借书卡号能得到该卡号所借书金额的总和

CREATE FUNCTION getSUM
(
@CNO int
)
RETURNS int
AS
BEGIN
declare @sum int
select @sum=sum(price) from BOOKS where bno in (select bno from BORROW where cno=@CNO)
return @sum
END
GO

2)，找出借书超过5本的读者,输出借书卡号及所借图书册数。

select CNO,count(BNO) as 借书数量from BORROW group by CNO having count(BNO)>3

3)，查询借阅了”水浒”一书的读者，输出姓名及班级。

select name,class from card where cno in( select cno from borrow where bno in(
select bno from BOOKS where bname=’水浒’))

4)，查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

select CNO,BNO,RDATE from borrow where getdate()>RDATE

5)，查询书名包括”网络”关键词的图书，输出书号、书名、作者。

select bno,bname,author from books where bname like ‘网络%’

6)，查询现有图书中价格最高的图书，输出书名及作者。

select bname,author from books where price in(select max(price) from books )

7)，查询当前借了”计算方法”但没有借”计算方法习题集”的读者，输出其借书卡号，并按卡号降序排序输出。

select cno from borrow where bno in (select bno from books where bname=’计算方法’) and cno not in ( select cno from borrow where bno in(select bno from books where bname=’计算方法习题集’)) order by cno desc

或

SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N’计算方法’
AND NOT EXISTS(
SELECT * FROM BORROW aa,BOOKS bb
WHERE aa.BNO=bb.BNO
AND bb.BNAME=N’计算方法习题集’
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC

8)，将”C01″班同学所借图书的还期都延长一周。

update borrow set rdate=dateadd(day,7,rdate) from BORROW where cno in(select cno from card where class=’一班’)

9)，从BOOKS表中删除当前无人借阅的图书记录。

DELETE A FROM BOOKS a WHERE NOT EXISTS( SELECT * FROM BORROW WHERE BNO=a.BNO)

10)，如果经常按书名查询图书信息，请建立合适的索引。(这个不确定对不 90%应该是对的自己看了下书写的)

CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11)，在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是”数据库技术及应用”，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。

CREATE TRIGGER TR_SAVE ON BORROW FOR INSERT,UPDATE AS IF @@ROWCOUNT>0 INSERT BORROW_SAVE SELECT i.* FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO AND b.BNAME=N’数据库技术及应用’

12)，建立一个视图，显示”力01″班学生的借书信息（只要求显示姓名和书名）。

CREATE VIEW V_VIEW AS select name,bname from books,card,borrow where borrow.cno=card.cno and borrow.bno=books.bno and class=’一班’

13)．查询当前同时借有”计算方法”和”组合数学”两本书的读者，输出其借书卡号，并按卡号升序排序输出。

select a.cno from borrow a,borrow b where a.cno=b.cno and a.bno in(select bno from books where bname=’计算方法’) and b.bno in(select bno from books where bname=’组合数学’) order by a.cno desc

或

SELECT a.CNO FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME IN(‘计算方法’,’组合数学’) GROUP BY a.CNO HAVING COUNT(*)=2 ORDER BY a.CNO DESC

14)，用事务实现如下功能：一个借书卡号借走某书号的书，则该书的库存量减少1，当某书的库存量不够1本的时候，该卡号不能借该书

alter PROCEDURE pro_jieshu
@cno int,
@bno int,
@date datetime
AS
BEGIN
begin tran
declare @quantity int
select @quantity=quantity from books where bno=@bno
insert into borrow values(@cno,@bno,@date)
update books set quantity=@quantity-1 where bno=@bno
if(@quantity>0)
begin
commit tran
end
else
begin
print ‘已无库存’
rollback
end
END
GO

15)，用游标实现将书号为‘A001’的书本的价格提高10元

declare @bno int
declare @bname nvarchar(50)
declare @author nvarchar(50)
declare @price int
declare @quantity int
declare mycursor cursor for select * from books
open mycursor
fetch next from mycursor into @bno,@bname,@author,@price,@quantity
while(@@fetch_status=0)
begin
if(@bno=2)
begin
update books set price=@price+10 where current of mycursor
end
fetch next from mycursor into @bno,@bname,@author,@price,@quantity
end
close mycursor
deallocate mycursor

本文链接：

http://www.chengxiaoxiao.com/?p=899

转载请注明来源：成笑笑博客 .net技术学习交流《数据库面试题-sql语句》