45个sql面试问题

本文是对英文原文的翻译,部分做了删减.如有冒犯请与本人联系.

正文

以下所有的都是在ANSI标准下的sql

1. union 和 union all 之间的区别

1. union 会去重排序,union all不会
2. union all 性能更加好

2. 说明join不同join类型

1. 内连接inner join (cross join /product join)
2. 外连接outer join left right
3. 注意左连接有后面的表有多条记录,左表也会显示多条记录

3. 看题说说结果

SELECT count(*) AS total FROM orders;
+-------+
| total |
+-------+
| 100 |
+-------+
SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = '123';
+----------------+
| cust_123_total |
+----------------+
| 15 |
+----------------+
SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> '123';
如果剩下的有一个null ,结果是100 -15 -1 = 84;
考察的是<>不会记录null


4.说说以下运行结果

select case when null = null then 'Yup' else 'Nope' end as Result;


null 判断不能使用 = 要用 is ;这个结果是一直不成立,Nope

5.说说以下运行结果

sql> SELECT * FROM runners;
+----+--------------+
| id | name |
+----+--------------+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa Romero |
+----+--------------+
sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event | winner_id |
+----+----------------+-----------+
| 1 | 100 meter dash | 2 |
| 2 | 500 meter dash | 3 |
| 3 | cross-country | 2 |
| 4 | triathalon | NULL |
+----+----------------+-----------+
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)
因为这里有null ,所以说可以返回结果是空,runners有所有的数据


6. 说说以下运行结果


CREATE TABLE dbo.envelope(id int, user_id int);
CREATE TABLE dbo.docs(idnum int, pageseq int, doctext varchar(100));
INSERT INTO dbo.envelope VALUES
(1,1),
(2,2),
(3,3);
INSERT INTO dbo.docs(idnum,pageseq) VALUES
(1,5),
(2,6),
(null,0);
UPDATE docs SET doctext=pageseq FROM docs INNER JOIN envelope ON envelope.id=docs.idnum
WHERE EXISTS (
SELECT 1 FROM dbo.docs
WHERE id=envelope.id
);
idnum pageseq doctext
1 5 5
2 6 6
NULL 0 NULL
由于ID is not a member of dbo.docs,所以一直成立exist
由于null 一直得不到匹配,不会给他设置值


7. 改错

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE BillingYear >= 2010;


错误在于,不能直接使用BillingYear,改正的为

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;


8. 改错


Id Name ReferredBy
1 John Doe NULL
2 Jane Smith NULL
3 Anne Jenkins 2
4 Eric Branford NULL
5 Pat Richards 1
6 Alice Barnes 2
查出referredBy 不是 jane smith 的数据
SELECT Name FROM Customers WHERE ReferredBy <> 2;


由于 null 不能直接用<> = 这类进行比较

所以改成

SELECT Name FROM Customers WHERE ReferredBy <> 2 or ReferredBy is null;
注意
SELECT Name FROM Customers WHERE ReferredBy <> 2 or ReferredBy = null;-- 错误.
null 不能用 = <> 只能用 is null/ is not null


9. 编写sql


SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName
FROM Invoices i
LEFT JOIN Customers c ON i.CustomerId = c.Id
LEFT JOIN Customers r ON c.ReferredBy = r.Id
ORDER BY i.BillingDate;


10. 说说运行结果

说说以下的运行结果

Select * From Emp, Dept


5*10 = 50;

这里是一个cross join (product join),会做乘积查询

11.编写sql

Id Name Sex Salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500


只使用一条sql m和f交换

UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END


12.编写sql

create table test_a(id numeric);
create table test_b(id numeric);
insert into test_a(id) values
(10),
(20),
(30),
(40),
(50);
insert into test_b(id) values
(10),
(30),
(50);


查询出在a表中存在,b表中不存在的数据;不能使用not

注意oracle中不用用上述insert 语法,需要一条一条插入


insert into test_a(id) values (10);
insert into test_a(id) values (20);
insert into test_a(id) values (30);
insert into test_a(id) values (40);
insert into test_a(id) values (50);
insert into test_b(id) values (10);
insert into test_b(id) values (30);
insert into test_b(id) values (50);


答案:

select a.id from test_a as a left join test_b on a.id=b.id where b.id is null;


使用oracle

select * from test_a
except
select * from test_b;


13.编写sql

一张给定的表,tbl有个Nmbr字段

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1


写一个sql ,当number = 0 加2,number = 1加3

update TBL set Nmbr = case when Nmbr =0 then Nmbr +2 else Nmbr + 3 end;


14.编写一个sql ,查询出第10大的数据

先查出top 10 ,再查出top 1

SELECT TOP (1) Salary FROM
(
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary


mysql可以这样写

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 9,1;


15.说说为什么大家使用union all加where 进行去重, 而不是使用union

主要是union 会去重和排序,性能差很多,
我们一般是这样使用

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X


16.编写sql


SELECT * FROM users;
user_id username
1 John Doe
2 Jane Don
3 Alice Jones
4 Lisa Romero
SELECT * FROM training_details;
user_training_id user_id training_id training_date
1 1 1 "2015-08-02"
2 2 1 "2015-08-03"
3 3 2 "2015-08-02"
4 4 2 "2015-08-04"
5 2 2 "2015-08-03"
6 1 1 "2015-08-02"
7 3 2 "2015-08-04"
8 4 3 "2015-08-03"
9 1 4 "2015-08-03"
10 3 1 "2015-08-02"
11 4 2 "2015-08-04"
12 3 2 "2015-08-02"
13 1 1 "2015-08-02"
14 4 3 "2015-08-03"


答案如下:

select b.username from (select a.user_id as user_id,a.training_id as training_id from training_details as a group by a.user_id ,a.training_date having count(a.user_id) > 1 ) as kk inner join users as b on kk.user_id= b.user_id;


SELECT
u.user_id,
username,
training_id,
training_date,
count( user_training_id ) AS count
FROM users u JOIN training_details t ON t.user_id = u.user_id
GROUP BY u.user_id,
username,
training_id,
training_date
HAVING count( user_training_id ) > 1
ORDER BY training_date DESC;


17.讲讲执行计划

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL
server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer
understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the
query or stored procedure.

一般是用explain ,用于查看query 或者存储过程

18. 讲讲ACID 原则,怎么确保数据库事务操作是可靠的

1. atomicity 原子性
2. consistency 一致性
3. isolation 隔离性
4. durability 持续性

19 .获取前一百奇数的用户(odd user_id)value


SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id


在Teradata 中应该这样写

select top 100 user_id from dbo.users where user_id mod 2 = 1 order by user_id;


20.获取奇数行(even number record),获取偶数行(odd number record)


Select * from table where id % 2 = 0
Select * from table where id % 2 != 0


21.说说 NVL 和 NVL2 之间的区别

nvl(exp1,exp2),exp1不为null返回exp1 ,exp1 为null返回 exp2

nvl2(exp1,exp2,exp3) 加强版,前面为空就取后面的值 if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

22.说说rank 和 dense_rank之间的区别

For example, consider the set {25, 25, 50, 75, 75, 100}.
For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped),
whereas DENSE_RANK() will return {1,1,2,3,3,4}.

23.说说where h和 having 之间的区别?

1. 如果没有用group by ,where 和 having 是等价的
2. 如果使用了group ,where 在group by 之前过滤,having 对group by 之后数据进行过滤

24.编写sql

有以下表


CustomerID CustomerName
1 Prashant Kaurav
2 Ashish Jha
3 Ankit Varma
4 Vineet Kumar
5 Rahul Kumar


得到以下结果

Prashant Kaurav; Ashish Jha; Ankit Varma; Vineet Kumar; Rahul Kumar


答案

SELECT CustomerName+ '; '
From Customer
For XML PATH('')


teradata 目前不支持 xml path

25.给出运行结果


select empName from Employee order by 2 desc;


会抛出运行错误,2 表示第2个查询的列.这里只是查询了一列

26.给出运行结果


BEGIN TRAN
TRUNCATE TABLE Employees
ROLLBACK
SELECT * FROM Employees


结果:

会返回10条结果,尽管有截断表,但是log是begin来保存的

27.说说single row function和 multi row funcaion

1. single 作用于单行 ,multi 作用于多行
2. group by 用于汇总信息

28. char 和 varchar 之间的区别

1. varchar是可变的,例如varchar2(1999)放50 ,他会使用52byte
2. char是不可以变的,例如char(1999)放50,他会使用2000byte

29. 编写sql

将CAPONE用

C
A
P
O
N
E


展示

结果:
使用print

Declare @a nvarchar(100)='capone';
Declare @length INT;
Declare @i INT=1;
SET @lenght=LEN(@a)
while @i<=@length
BEGIN
print(substring(@a,@i,1));
set @i=@i+1;
END


在oracle中

SELECT SUBSTR('CAPONE', LEVEL, 1)
FROM DUAL CONNECT BY LEVEL <= LENGTH('CAPONE');


30. 隐式插入

SET IDENTITY_INSERT TABLE1 ON
INSERT INTO TABLE1 (ID,NAME)
SELECT ID,NAME FROM TEMPTB1
SET IDENTITY_INSERT OFF


31.sum(1)/sum(2)/sum(3)之间的区别

对结果进行1 / 2/*3


Testdb=# Select * FROM "Test"."EMP";
ID
----
1
2
3
4
5
(5 rows)
Select SUM(1) FROM "Test"."EMP";
Select SUM(2) FROM "Test"."EMP";
Select SUM(3) FROM "Test"."EMP";


结果

5
10
15


32.编写sql

ID C1 C2 C3
1 Red Yellow Blue
2 NULL Red Green
3 Yellow NULL Violet

写出C1/C2/C3中包含yellow的列,不能使用OR

答案:

SELECT * FROM table
WHERE 'Yellow' IN (C1, C2, C3)


33.编写sql将col更新成与col相反


Col1 Col2
1 0
0 1
0 1
0 1
1 0
0 1
1 0
1 0
update table set col2 = case when col1 = 1 then 0 else 1 end
update table set col2 = 1 - col1


34.不用max 获取最大的id

select id from table order by id desc limit 1


35.in 和 exist 之间的区别

in

1. in作用于结果集
2. 不能作用于顺序结果,在虚拟表中,多个列
3. 会对结果集每一个进行比较
4. 在大结果集情况下,很慢

exist

1 可以用于虚表中
2 用于 相关查询
3 匹配后才会进行比较
4 相对来说会快一些,在大的结果集

36.插入数据

有一张表,里面有7条记录,用户想插入一条数据;但是从10开始

使用CHECKIDENT

create table tableA 来处理
(id int identity,
name nvarchar(50)
)
insert into tableA values ('ram')
insert into tableA values ('rahim')
insert into tableA values ('roja')
insert into tableA values ('rahman')
insert into tableA values ('rani')
insert into tableA values ('raja')
insert into tableA values ('raga')
select * From tableA
DBCC CHECKIDENT(tableA,RESEED,9)
insert into tableA values ('roli')
insert into tableA values ('rosy')
insert into tableA values ('raka')
insert into tableA values ('rahul')
insert into tableA values ('rihan')
insert into tableA values ('bala')
insert into tableA values ('gala')


37.使用公用表达式CTE(common table expression)f返回5th的数据

Declare @N int
set @N = 5;
WITH CTE AS
(
SELECT Name, Salary, EmpID, RN = ROW_NUMBER()
OVER (ORDER BY Salary DESC)
FROM Employee
)
SELECT Name, Salary, EmpID
FROM CTE
WHERE RN = @N


38. 写sql

['d', 'x', 'T', 8, 'a', 9, 6, 2, 'V'


写出sql ,数字输出fizz ,字母输出bizz

['Buzz', 'Buzz', 'Buzz', 'Fizz', 'Buzz','Fizz', 'Fizz', 'Fizz', 'Buzz']


解题关键:

数字的大小写是一样的

SELECT col, case when upper(col) = lower(col) then 'Fizz' else 'Buzz' end as FizzBuzz from table;


39. 写sql

不适用子查询或者CTE 来获取第3大的数据


SELECT salary from Employee order by salary DESC LIMIT 2,1


40. 写sql

汇总大于0 和 小于 0 的数据的和

x
------
2
-2
4
-4
-3
0
2


答案

select sum(case when x>0 then x else 0 end)sum_pos,sum(case when x<0 then x else 0 end)sum_neg from a;


41. 写sql

将小数点两边分开,截取数据

weight
5.67
34.567
365.253
34



weight kg gms
5.67 5 67
34.567 34 567
365.253 365 253
34 34 0


select weight, trunc(weight) as kg, nvl(substr(weight - trunc(weight), 2), 0) as gms
from mass_table;


42. 写sql

Emp_Id Emp_name Salary Manager_Id
10 Anil 50000 18
11 Vikas 75000 16
12 Nisha 40000 18
13 Nidhi 60000 17
14 Priya 80000 18
15 Mohit 45000 18
16 Rajesh 90000 –
17 Raman 55000 16
18 Santosh 65000 17


输出结果为


Manager_Id Manager Average_Salary_Under_Manager
16 Rajesh 65000
17 Raman 62500
18 Santosh 53750


结果为

select b.emp_id as "Manager_Id",
b.emp_name as "Manager",
avg(a.salary) as "Average_Salary_Under_Manager"
from Employee a,
Employee b
where a.manager_id = b.emp_id
group by b.emp_id, b.emp_name
order by b.emp_id;


43.写 sql

从一个表赋值数据

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;


44.判断语句等价


SELECT name FROM customer WHERE state = 'VA';
SELECT name FROM customer WHERE state IN ('VA');


45. 怎么找一重复的记录

1. 一列


SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1


1. 两列

SELECT name, email, COUNT(*)
FROM users
GROUP BY name, email
HAVING COUNT(*) > 1


参考文档

45 Essential SQL Interview Questions