抱歉,您的浏览器无法访问本站

本页面需要浏览器支持(启用)JavaScript


了解详情 >
image

SQL Practice

图解SQL面试题:经典50题

No. Question Flag
1. 175. Combine Two Tables
2. 176. Second Highest Salary, ORDER BY, OFFSET, IFNULL(xxx, NULL)
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC LIMIT 1 OFFSET 1
✔️

3.
177. Nth Highest Salary, CREATE FUNCTION func_name RETURNS INT

6种方案诠释MySQL通用查询策略, BEGIN RETURN xxx/select END, SET

✔️
4. 178. Rank Scores, 摘要: 专用窗口函数rank, dense_rank, row_number有什么区别呢?

《通俗易懂的学会:SQL窗口函数》

   select score, dense_rank() over(order by Score desc) as Ranking from Scores;
5. 180. Consecutive Numbers,
   l1.Id=l2.Id-1 AND l2.Id=l3.Id-1 AND l1.Num=l2.Num AND l2.Num=l3.Num
6. 185 Department Top Three Salaries, dense_rank() over (partition by x order by y desc)
7. 184. Department Highest Salary, (Employee.DepartmentId , Salary) IN
8.
9. 196. Delete Duplicate Emails,    “delete” 和 “>” 的解释,推荐!
10. 181. Employees Earning More Than Their Managers, 1. 笛卡尔积+WHERE 2. 自连接+ON
11. 1179. Reformat Department Table, CASE WHEN condition1 THEN result1 END
12. 182. Duplicate Emails, 使用 GROUP BY 和 HAVING 条件
13. 197. Rising Temperature, JOIN … ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
14. 601. Human Traffic of Stadium
15. 183. 从不订购的客户 NOT IN
16. 627. Swap Salary 变更性别, sex=CASE WHEN sex='m' THEN 'f' ELSE 'm' END;
17. 626. Exchange Seats 换座位, (CASE WHEN MOD(id,2) END) AS id FROM table1, 5

627. Swap Salary 变更性别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
UPDATE salary
SET
sex =
CASE
WHEN sex='m' THEN 'f'
ELSE 'm'
END;

学习要点:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

626. Exchange Seats 换座位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;

1
2
3
4
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id

176. Second Highest Salary

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

If there is no second highest salary, then the query should return null.

1
2
3
4
5
6
7
SELECT IFNULL( 
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary

学习要点: OFFSET

177. Nth Highest Salary

MYSQL: CREATE FUNCTION

1
2
3
CREATE FUNCTION func_name ( [func_parameter] ) //括号是必须的,参数是可选的
RETURNS type
[ characteristic ...] routine_body

LIMIT 用法:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM orange LIMIT 5; 
mysql> SELECT * FROM orange LIMIT 0,5;
mysql> SELECT * FROM orange LIMIT 10,15; // 检索记录11-25

mysql> SELECT * FROM orange LIMIT 2 OFFSET 3;//查询4-5两条记录
mysql> SELECT * FROM orange LIMIT 3,2;

mysql> SELECT * FROM orange LIMIT 95,-1; // 检索记录96-last

getNthHighestSalary(N INT)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1; // 赋值语句 N = N-1
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END

180. Consecutive Numbers

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT
#l2.*,
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;

185. Department Top Three Salaries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Write your MySQL query statement below
SELECT
t2.Name as Department,
t1.Name as Employee,
t1.Salary
FROM
(
SELECT DepartmentId,Name,Salary
FROM (
SELECT *,
dense_rank() over (partition by DepartmentId
order by Salary desc) as ranking
FROM Employee) as a
WHERE ranking <= 3
) t1
JOIN Department t2
ON t1.DepartmentId = t2.Id

184. Department Highest Salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;

181. Employees Earning More Than Their Managers

1
2
3
4
5
6
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;

1179. Reformat Department Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Write your MySQL query statement below
select id,
sum(case month when 'Jan' then revenue end) as Jan_Revenue,
sum(case month when 'Feb' then revenue end) as Feb_Revenue,
sum(case month when 'Mar' then revenue end) as Mar_Revenue,
sum(case month when 'Apr' then revenue end) as Apr_Revenue,
sum(case month when 'May' then revenue end) as May_Revenue,
sum(case month when 'Jun' then revenue end) as Jun_Revenue,
sum(case month when 'Jul' then revenue end) as Jul_Revenue,
sum(case month when 'Aug' then revenue end) as Aug_Revenue,
sum(case month when 'Sep' then revenue end) as Sep_Revenue,
sum(case month when 'Oct' then revenue end) as Oct_Revenue,
sum(case month when 'Nov' then revenue end) as Nov_Revenue,
sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id

CASE WHEN condition1 THEN result1 END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

197. Rising Temperature

1
2
3
4
5
6
7
8
SELECT
w1.id AS 'Id'
FROM
weather as w1
JOIN
weather as w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
AND w1.Temperature > w2.Temperature;

concat_ws

1
2
3
4
5
6
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;

# output:
#
# ConcatenatedString
# SQL-Tutorial-is-fun!
EVT Topic ## Data Warehouse

建模: 说白了就是表字段设计

  1. 了解业务场景

平台维度
平台的收入,平台订单完成率 平台订单数量变化
最近7日,15日,30日,60日,90日

建模

主题确定,维度退化,轻度聚合

如何把建模说的高大上一些:

atlas

jdbc会把tinyint 认为是java.sql.Types.BIT,然后sqoop就会转为Boolean了。
在连接上加上一句话tinyInt1isBit=false

Mysql中存在tinyint(1)时,在数据导入到HDFS时,该字段默认会被转化为boolean数据类型。导致数据内容丢失。

tinyInt1isBit=false

sqoop从mysql导入hive中tinyint类型变成了boolean类型的问题

1
mysql.server restart --init-file=/Users/blair/init.sql

Reference

hive 行转列 lateral view explode
Hive Lateral view介绍

缓慢变化维的10种方式