使用HR数据库进行练习之连接
Practice 1
编写一条SQL查询语句，获取所有department的地址（location_id，street_address，city，state_province，country_name)，提示：使用自然连接
|
1 2 |
SELECT location_id, street_address, city, state_province, country_name FROM locations NATURAL JOIN countries; |
Practice 2
编写一条SQL查询语句，获取所有employee的名字（first_name，last_name），department ID和department名字
|
1 2 3 |
SELECT first_name, last_name, employees.department_id, departments.department_name FROM employees INNER JOIN departments WHERE employees.department_id = departments.department_id; |
Practice 3
编写一条SQL查询语句，获取所有在London工作的employee的名字（first_name，last_name），job，department ID和department 名字
|
1 2 3 4 |
SELECT e.first_name, e.last_name, e.job_id, d.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON l.location_id = d.location_id WHERE LOWER(l.city) = 'london'; |
Practice 4
编写一条SQL查询语句，查找employee的id，名字（last_name）和他们的manager_id和名字（last_name）
|
1 2 3 |
SELECT e.employee_id, e.last_name, e.manager_id, m.last_name manager_last_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id; |
Practice 5
编写一条SQL查询语句，查找雇用日期在‘Jones’的雇用日期之后的employees的名字（first_name，last_name）和hire date
这个exercise可以使用子查询获取，如下
|
1 2 |
SELECT first_name, last_name, hire_date FROM employees WHERE hire_date > (SELECT hire_date FROM employees WHERE last_name = 'Jones'); |
使用连接方法解这个exercise如下：
|
1 2 3 |
SELECT e1.first_name, e1.last_name, e1.hire_date FROM employees e1 JOIN employees e2 ON e2.last_name = 'Jones' WHERE e2.hire_date < e1.hire_date; |
Practice 6
编写一条SQL查询语句，获取department的名字和该department的employees数量
|
1 2 3 |
SELECT d.department_name, COUNT(e.department_id) FROM employees e JOIN departments d ON d.department_id = e.department_id GROUP BY e.department_id; |
Practice 7
编写一条SQL查询语句，显示manager管理的department的ID，名字和manager的first name
|
1 2 |
SELECT d.department_id, d.department_name, e.first_name FROM departments d JOIN employees e ON d.manager_id = e.employee_id; |
Practice 8
编写一条SQL查询语句，显示department的名字，department的manager名字和city
|
1 2 3 |
SELECT d.department_name, e.first_name, l.city FROM departments d JOIN employees e ON d.manager_id = e.employee_id JOIN locations l ON d.location_id = l.location_id; |
Practice 9
编写一条SQL查询语句，显示employee的job title和平均salary
|
1 2 3 |
SELECT job_title, AVG(salary) FROM employees NATURAL JOIN jobs GROUP BY job_title; |
Practice 10
编写一条SQL查询语句，显示employee的job title，名字和该employee的salary和其job的最低salary之间的差距
|
1 2 3 |
SELECT job_title, first_name, salary-min_salary difference FROM employees NATURAL JOIN jobs; |
Practice 11
编写一条SQL查询语句，显示工作经验超过15年的manager的department name，名字（first_name，last_name），hire date和salary
|
1 2 3 |
SELECT d.department_name, e.first_name, e.last_name, e.hire_date, e.salary, DATEDIFF(NOW(), hire_date)/365 Experience FROM departments d JOIN employees e ON d.manager_id = e.employee_id WHERE DATEDIFF(NOW(), hire_date)/365 > 15; |
