|
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
横表结构 TableB
先理解： 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
表二：curriculum
表三：grade
题目： → 条件查询：
select 学号,分数 from grade where 分数 between 80 and 90
select avg(分数) from grade where 课程编号=’003′
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. 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./?p=899 |
|
|
