SQL 练习

1. SQL 执行的顺序

FROM [ left_table ]
ON < join_condition >
< join_type > JOIN < right_table >
WHERE < where_condition >
GROUP BY < group_by_list >
WITH < CUBE | RollUP >
HAVING < having_condition >
SELECT
DISTINCT
ORDER BY < order_by_list >
< Top Num > < select list >

按执行顺序,首先确定FROM…JOIN的表,然后是WHERE条件,然后是GROUP BY

2. 题目来源

SQL一般面试题

3. 数据来源

使用mysql自带的测试数据

4. 查询每个月倒数第2天入职的员工的信息

employees表的hire_date字段是入职时间;
函数last_day()返回该月的最后一天对应的值

SELECT last_name, hire_date  
FROM employees.employees
WHERE hire_date = last_day(hire_date) – 1
limit 10;

5. 查询出last_name 为 ‘Zhang’ 的 manager 的信息

employees表有emp_no,last_name字段
dept_manager表emp_no字段

SELECT last_name,count(*)
FROM employees
GROUP BY last_name
ORDER BY 2 DESC
LIMIT 10
;


select a.emp_no,a.first_name,a.last_name
from employees a JOIN dept_manager b ON a.emp_no=b.emp_no
WHERE a.last_name='Zhang';

6. 查询2000度(2000-01-01到2000-12-31)年薪高于70000 的部门 id 和它的平均年薪

salaries表emp_no,salary,from_date,to_date
dept_emp表emp_no,dept_no

SELECT b.dept_no as dep,avg(a.salary) as avg
FROM salaries a JOIN dept_emp b ON a.emp_no=b.emp_no
WHERE a.from_date='2000-01-01' AND a.to_date='2000-12-31'
GROUP BY dep
HAVING avg > 70000
;

7. 查询1990到2000年平均年薪最低的10位员工信息:last_name, salary

salaries表emp_no,salary,from_date,to_date
employees表有emp_no,last_name字段

SELECT a.emp_no,a.last_name,avg(b.salary) as avg
FROM employees a JOIN salaries b ON a.emp_no=b.emp_no
WHERE b.from_date>'1990-01-01' AND b.from_date <'2000-01-01'
GROUP BY a.emp_no
ORDER BY avg
LIMIT 10;

8. 查询1990到2000年平均年薪最低的部门信息

salaries表emp_no,salary,from_date,to_date
dept_emp表emp_no,dept_no
departments表dept_no,dept_name

SELECT c.dept_no,c.dept_name,avg(a.salary) as avg
FROM salaries a JOIN dept_emp b ON a.emp_no=b.emp_no
JOIN departments c ON b.dept_no=c.dept_no
WHERE a.from_date>'1990-01-01' AND a.from_date <'2000-01-01'
GROUP BY b.dept_no
ORDER BY avg
limit 1;