当前位置：文档之家› SQL常见面试题

SQL常见面试题

SQL 常见面试题

1.用一条SQL语句查询出每门课都大于80分的学生姓名

name kecheng fenshu

张三语文81

张三数学75

李四语文76

李四数学90

王五语文81

王五数学100

王五英语90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

2.学生表如下:

自动编号学号姓名课程编号课程名称分数

1 2005001 张三0001 数学69

2 2005002 李四0001 数学89

3 2005001 张三0001 数学69

删除除了自动编号不同,其他都相同的学生冗余信息

A: delete tablename where 自动编号not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)

3. 表A(单位名称，单位帐号), 表B(单位编号，个人账号)

列出各单位的名称，账号，以及单位的人数

select https://www.doczj.com/doc/b1332483.html,, A.dwzh, isnull(Ct.Quantity,'0') as Quantity from A

left join

(select dwzh, count(*) as Quantity from B

group by dwzh) as Ct

on A.dwzh = Ct.dwzh

4. 股票表(股票代码，买卖类型，数量)

按照股票代码列出，买的数量，卖的数量。

select isnull(a.StockID, b.StockID), isnull(a.S,'0'), isnull(b.B,'0') from (

select StockID,sum(quantity) as S from stocks

where sType = 's'

group by StockID ) a

full join (

select StockID,sum(quantity) as B from stocks

where sType = 'b'

group by StockID ) b

on a.StockID = b.StockID

5. select * from tempT where ','+ tempT.description + ',' like '%,1,%'

SQL Server 数据库的高级操作

(1) 批处理

(2) 变量

(3) 逻辑控制

(4) 函数

(5) 高级查询

(1)批处理

将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，

如果在编译时，其中，有一条出现语法错误，将会导致编译失败！create table t

(

a int,

b int

)

-- 注释

-- 如果多行注释中包含了批处理的标识符go

-- 在编译的过程中代码将会被go分割成多个部分来分批编译

-- 多行注释的标记将会被分隔而导致编译出错

-- 以下几条语句是三个非常经典的批处理

-- 你猜一下会添加几条记录！

insert into t values (1,1)

insert into t values (2,2)

insert into t values (3,3)

-- 查询看添加了几条记录

select * from t

truncate table t

(2)变量

-- 全局变量

SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！-- 查看SQL Server版本

print @@version

-- 服务器名称

print @@servername

-- 系统错误编号

insert into t values ('a','a')

print @@error

insert into t values ('a','a')

if @@error = 245

print 'Error'

-- SQL Server 版本的语言信息

print @@LANGUAGE

-- 一周的第一天从星期几算起

print @@datefirst

-- CPU 执行命令所耗费时间的累加

print @@cpu_busy

-- 获取最近添加的标识列的值

create table tt

(

a int identity(3, 10),

b int

)

insert into tt (b) values (1)

print @@identity

select * from tt

-- 局部变量

局部变量由用户定义，仅可在同一个批处理中调用和访问

declare @intAge tinyint

set @intAge = 12

print @intAge

declare @strName varchar(12)

select @strName = 'state'

print @strName

select au_lname, @strName from authors

(3)逻辑控制

-- IF条件判断

declare @i int

set @i = 12

if (@i > 10)

begin -- {

print 'Dadadada!'

end -- }

else

begin

print 'XiaoXiao!'

end

-- While循环控制

declare @i int;

set @i = 12;

print @i

return;

while (@i < 18)

begin

print @i;

set @i = @i + 1;

if @i < 17

continue;

if @i > 15

break;

end;

-- CASE 分支判断

select au_lname, state, '犹他州' from authors where state = 'UT'

select au_lname, state, '密西西比州' from authors where state = 'MI' select au_lname, state, '肯塔基州' from authors where state = 'KS' select au_lname, state,

case state

when 'UT' then '犹他州'

when 'MI' then '密西西比州'

when 'KS' then '肯塔基州'

when 'CA' then '加利福利亚'

else state

end

from authors

(4.1)系统函数

-- 获取指定字符串中左起第一个字符的ASC码

print ascii('ABCDEF')

-- 根据给定的ASC码获取相应的字符

print char(65)

-- 获取给定字符串的长度

print len('abcdef')

-- 大小写转换

print lower('ABCDEF')

print upper('abcdef')

-- 去空格

print ltrim(' abcd dfd df ')

print rtrim(' abcd dfd df ')

-- 求绝对值

print abs(-12)

-- 幂

-- 3 的2 次方

print power(3,2)

print power(3,3)

-- 随机数

-- 0 - 1000 之间的随机数

print rand() * 1000

-- 获取圆周率

print pi()

-- 获取系统时间

print getdate()

-- 获取3天前的时间

print dateadd(day, -3 , getdate())

-- 获取3天后的时间

print dateadd(day, 3 , getdate())

-- 获取3年前的时间

print dateadd(year, -3 , getdate())

-- 获取3年后的时间

print dateadd(year, 3 , getdate())

-- 获取3月后的时间

print dateadd(month, 3 , getdate())

-- 获取9小时后的时间

print dateadd(hour, 9 , getdate())

-- 获取9分钟后的时间

print dateadd(minute, 9 , getdate())

-- 获取指定时间之间相隔多少年

print datediff(year, '2005-01-01', '2008-01-01')

-- 获取指定时间之间相隔多少月

print datediff(month, '2005-01-01', '2008-01-01')

-- 获取指定时间之间相隔多少天

print datediff(day, '2005-01-01', '2008-01-01')

-- 字符串合并

print 'abc' + 'def'

print 'abcder'

print 'abc' + '456'

print 'abc' + 456

-- 类型转换

print 'abc' + convert(varchar(10), 456)

select title_id, type, price from titles

-- 字符串连接必须保证类型一致(以下语句执行将会出错)

-- 类型转换

select title_id + type + price from titles

-- 正确

select title_id + type + convert(varchar(10), price) from titles

print '123' + convert(varchar(3), 123)

print '123' + '123'

print convert(varchar(12), '2005-09-01',110)

-- 获取指定时间的特定部分

print year(getdate())

print month(getdate())

print day(getdate())

-- 获取指定时间的特定部分

print datepart(year, getdate())

print datepart(month, getdate())

print datepart(day, getdate())

print datepart(hh, getdate())

print datepart(mi, getdate())

print datepart(ss, getdate())

print datepart(ms, getdate())

-- 获取指定时间的间隔部分

-- 返回跨两个指定日期的日期和时间边界数

print datediff(year, '2001-01-01', '2008-08-08')

print datediff(month, '2001-01-01', '2008-08-08')

print datediff(day, '2001-01-01', '2008-08-08')

print datediff(hour, '2001-01-01', '2008-08-08')

print datediff(mi, '2001-01-01', '2008-08-08')

print datediff(ss, '2001-01-01', '2008-08-08')

-- 在向指定日期加上一段时间的基础上，返回新的datetime 值print dateadd(year, 5, getdate())

print dateadd(month, 5, getdate())

print dateadd(day, 5, getdate())

print dateadd(hour, 5, getdate())

print dateadd(mi, 5, getdate())

print dateadd(ss, 5, getdate())

-- 其他

print host_id()

print host_name()

print db_id('pubs')

print db_name(5)

-- 利用系统函数作为默认值约束

drop table ttt

create table ttt

(

stu_name varchar(12),

stu_birthday datetime default (getdate())

)

alter table ttt

add constraint df_ttt_stu_birthday default (getdate()) for stu_birthday insert into ttt values ('ANiu', '2005-04-01')

insert into ttt values ('ANiu', getdate())

insert into ttt values ('AZhu', default)

sp_help ttt

select * from ttt

（4.2）自定义函数

select title_id

from titles

where type = 'business'

select stuff(title_id,1,3,'ABB'), type

from titles

where type = 'business'

select count(title_id) from titles where type = 'business'

select title_id from titles where type = 'business'

select *,count(dbo.titleauthor.title_id)

FROM dbo.authors INNER JOIN

dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id

select au_id, count(title_id)

from titleauthor

group by au_id

SELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS '作品数量' FROM dbo.authors left outer JOIN

dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id GROUP BY dbo.authors.au_id

order by '作品数量'

-- 自定义函数的引子（通过这个子查询来引入函数的作用）

-- 子查询

-- 统计每个作者的作品数

-- 将父查询中的作者编号传入子查询

-- 作为查询条件利用聚合函数count统计其作品数量

select au_lname,

(select count(title_id)

from titleauthor as ta

where ta.au_id = a.au_id

) as TitleCount

from authors as a

order by TitleCount

-- 是否可以定义一个函数

-- 将作者编号作为参数统计其作品数量并将其返回

select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authors

order by TitleCount

-- 根据给定的作者编号获取其相应的作品数量

create function GetTitleCountByAuID(@au_id varchar(12))

returns int

begin

return (select count(title_id)

from titleauthor

where au_id = @au_id)

end

-- 利用函数来显示每个作者的作品数量

create proc pro_CalTitleCount

select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authors

order by TitleCount

-- 执行存储过程

execute pro_CalTitleCount

-- vb中函数定义格式

function GetTitleCountByAuID(au_id as string) as integer

.......

GetTitleCountByAuID = ?

end function

-- SALES 作品销售信息

select * from sales

-- 根据书籍编号查询其销售记录（其中，qty 表示销量）

select * from sales where title_id = 'BU1032'

-- 根据书籍编号统计其总销售量（其中，qty 表示销量）

select sum(qty) from sales where title_id = 'BU1032'

-- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）select title_id, sum(qty) from sales group by title_id

-- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量

-- 然后，将其应用到任何一条包含了书籍编号的查询语句中

select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales

from titles

order by TotalSales

-- 定义一个函数根据书籍编号来计算其总销售量

create function GetTotalSaleByTitleID(@tid varchar(24))

returns int

begin

return(select sum(qty) from sales where title_id = @tid)

end

-- 统计书籍销量的前10位

-- 其中，可以利用函数计算结果的别名作为排序子句的参照列

select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales

from titles

order by TotalSales desc

-- 根据书籍编号计算其销量排名

create function GetTheRankOfTitle(@id varchar(20))

returns int

begin

return(select count(TotalSales)

from titles

where ToalSales >(

select TotalSales

from titles

where title_id=@id))

end

-- 根据书籍编号计算其销量排名

select dbo.GetTheRankOfTitle('pc1035') from titles

select count(title_id) + 1

from titles

where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035') -- 删除函数

drop function GetRankByTitleId

-- 根据书籍编号计算其销量排名

create function GetRankByTitleId(@tid varchar(24))

returns int

begin

return (select count(title_id) + 1

from titles

where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(@tid)) end

-- 在查询语句中利用函数统计每本书的总销量和总排名

select title_id, title,

dbo.GetTotalSaleByTitleID(title_id) as TotalSales,

dbo.GetRankByTitleId(title_id) as TotalRank

from titles

order by TotalSales desc

-- 查看表结构

sp_help titles

-- 查看存储过程的定义内容

sp_helptext GetRankByTitleId

sp_helptext sp_helptext

sp_helptext xp_cmdshell

-- [ORDER DETAILS] 订单详细信息

select * from [order details]

select * from [order details] where productid = 23

-- 根据产品编号在订单详细信息表中统计总销售量

select sum(quantity) from [order details] where productid = 23

-- 构造一个函数根据产品编号在订单详细信息表中统计总销售量

create function GetTotalSaleByPID(@Pid varchar(12))

returns int

begin

return(select sum(quantity) from [order details] where productid = @Pid)

end

select * from products

-- 在产品表中查询，统计每一样产品的总销量

select productid, productname, dbo.GetTotalSaleByPID(productid) from products

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )

RETURNS @OrderShipperTab TABLE

(

ShipperID int,

ShipperName nvarchar(80),

OrderID int,

ShippedDate datetime,

Freight money

)

BEGIN

INSERT @OrderShipperTab

SELECT S.ShipperID, https://www.doczj.com/doc/b1332483.html,panyName,

O.OrderID, O.ShippedDate, O.Freight

FROM Shippers AS S INNER JOIN Orders AS O

ON S.ShipperID = O.ShipVia

WHERE O.Freight > @FreightParm

RETURN

END

SELECT * FROM LargeOrderShippers( $500 )

-- 根据作者编号计算其所得版权费

create function fun_RoyalTyper ( @au_id id)

begin

declare @rt int

select @rt = sum(royaltyper) from titleauthor where au_id = @au_id

return (@rt)

end

select top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权费' from authors

order by dbo.fun_RoyalTyper(au_id) desc

create function fun_MaxRoyalTyper_Au_id ()

returns id

begin

declare @au_id id

select @au_id = au_id

from authors

order by dbo.fun_RoyalTyper(au_id)

return(@au_id)

end

select dbo.fun_MaxRoyalTyper_Au_id()

select au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权税'

from authors

where au_id = dbo.fun_MaxRoyalTyper_Au_id()

(5)高级查询

select title_id, price from titles

-- 查找最高价格

select max(price) from titles

-- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏select top 1 title_id, price

order by price desc

-- 查找最贵书籍的价格(子查询)

select title_id, price

from titles

where price = (select max(price) from titles)

-- 查询指定出版社出版的书(连接)

select p.pub_name as '出版社', t.title as '书籍名称'

from publishers as p join titles as t on p.pub_id = t.pub_id

where pub_name = 'New Moon Books'

-- 查询指定出版社出版的书(子查询)

select title

from titles

where pub_id = (select pub_id

from publishers

where pub_name = 'New Moon Books')

-- 查询指定出版社出版的书(分开查询)

select title from titles where pub_id = '0736'

select pub_id

from publishers

where pub_name = 'New Moon Books'

-- 重点

-- 理解相关子查询的基础

select * from titles where type = 'business'

select * from titles where type = 'business123'

select * from titles where 1 = 1

-- 在订单表中寻找满足以下条件的订单编号以及相应的客户编号

-- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品-- 然后将产品编号为23的产品订购量返回判断是否大于20

USE northwind

SELECT orderid, customerid

FROM orders AS or1

WHERE 20 < (SELECT quantity FROM [order details] AS od

WHERE or1.orderid = od.orderid

AND od.productid = 23)

SELECT au_lname, au_fname

FROM authors

WHERE 100 IN

(

SELECT royaltyper FROM titleauthor

WHERE titleauthor.au_ID = authors.au_id

)

select authors.au_lname,authors.au_fname

from authors join titleauthor on titleauthor.au_ID=authors.au_id where titleauthor.royaltyper =100

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE au_id IN

(SELECT au_id

FROM titleauthor

WHERE title_id IN

(SELECT title_id

FROM titles

WHERE type = 'popular_comp'))

select distinct t.type, a.au_lname, a.au_fname

from authors as a join titleauthor as ta on a.au_id = ta.au_id

join titles as t on ta.title_id = t.title_id

where t.type = 'business'

-- 查找类型为'business'或是'trad_cook'类型的书籍

select * from titles where type = 'business'

select * from titles where type = 'trad_cook'

-- 查找类型为'business'或是'trad_cook'类型的书籍(Or)

select * from titles

where type = 'business' or type = 'trad_cook'

-- 查找类型为'business'或是'trad_cook'类型的书籍(In)

select * from titles

where type in ('business', 'trad_cook')

-- 查找来自'KS'或是'UT'的作者