思扣网SQLintern取数师能力测评

马上就要面试了
你到底行不行

开始测试，了解你的SQL真实水平，找到提升方向！

3分钟

完成测试

6题

精选题目

120分

满分挑战

注意：本页面为不定项选择题，共6题，全部答对20分，答对部分10分，选错、选多0分；满分120分。

SELECT * FROM employees WHERE NOT (department = 'HR' OR department = 'Finance') VS SELECT * FROM employees WHERE department != 'HR' AND department != 'Finance'
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000 VS SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales'
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31' VS SELECT * FROM employees WHERE hire_date >= '2020-01-01' OR hire_date <= '2020-12-31'
SELECT * FROM employees WHERE manager_id IS NULL VS SELECT * FROM employees WHERE manager_id = NULL

SELECT SUBSTR(email, LOCATE('@', email)) AS domain FROM employees;
SELECT RIGHT(email, LENGTH(email) - LOCATE('@', email)) AS domain FROM employees;
SELECT SUBSTRING_INDEX(email, '@', 1) AS domain FROM employees;
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM employees;

a	b	c
3	2	2
2	2	3

DENSE_RANK() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)
ROW_NUMBER() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)
DENSE_RANK() OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_amount DESC)
RANK() OVER (PARTITION BY employee_id, MONTH(sale_date) ORDER BY sale_amount DESC)

使用FIRST_VALUE：FIRST_VALUE(active_date) OVER(PARTITION BY user_id ORDER BY active_date)
使用自连接：通过user_id自关联找到最小日期
使用FIRST_VALUE：FIRST_VALUE(active_date) OVER(PARTITION BY user_id ORDER BY active_date)
使用窗口函数：MIN(active_date) OVER(PARTITION BY user_id)