java数据库面试题集.docVIP

请写出SQL语句计算今天距离2005元旦的分钟数。答案： select datediff(minute,01/01/2005,getdate()) as minutes 请一句SQL写出：如果person(personname,deptname) 表中没有 “财务部”的“张三” ，那么请增加该人员。 set nocount on if EXISTS(select * from person where personname=张三and departname=财务部 ) begin raiserror((%s)的记录已经！,16,1,财务部的张三) return end begin transaction insert person(personname,departname) values(张三,财务部) if @@ROWCOUNT1 goto Error commit transaction return --提前返回 --错误处理 Error: rollback raiserror(添加失败,16,1) 答案二:if not EXISTS(select * from person where personname=张三 and deptname=财务部) insert into person(personname,deptname) values(张三,财务部) 请取出表 test (id identity(int,1,1)) 的第三条记录。 Select top 1 * from(select top 3 * from test order by id desc) temp 有表 test Row Val 1 101 1 102 2 1 请写SQL语句将表test 的内容显示为下边内容 Row Val 1 101 1 102 2 101 2 102 答案：select a.row,a.val from test as a left join test as b on b.val = a.row where a.valb.val union select a.row,b.val from test as a left join test as b on a.val = b.row where b.vala.row 计算库存结余 test Inv inouttype Iquantity 001 in 300 001 out 200 002 in 50 答案：：select sum(case when inouttype=in then iquantity else -iquantity end) as 结余金额 from test3 6．请将表 test Cus inv Money 北京 苹果 100 北京 李子 200 上海 苹果 400 转变成 Cus 苹果 李子 北京 100 200 上海 400 0 答案：declare @sql varchar(8000) set @sql = select cus select @sql = @sql + , sum(case inv when + inv + then money else 0 end) [ + inv + ] from (select distinct inv from test2) as a set @sql = @sql + from test2 group by cus exec(@sql) 更好的答案。。。 有表 test Personcode Personname 001 Anny 002 Liszt 003 Devy 写一个过程 create procedure getselperson(@s varchar(200)) begin end; IF EXISTS(SELECT name FROM sysobjects WHERE name = getselperson AND type = P) DROP PROCEDURE getselperson GO CREATE PROCEDURE getselperson( @@Personcode1 varchar(200) = 001, @@Personcode2 varchar(200) = 002) AS SET NOCOUNT ON begin TRANSACTION SELECT * FROM test7 WHERE P