Jimmy Chen

A Programmer

使用HR数据库进行练习之连接

使用HR数据库进行练习之连接

Practice 1

  编写一条SQL查询语句,获取所有department的地址(location_id,street_address,city,state_province,country_name),提示:使用自然连接

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名字

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 名字

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)

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可以使用子查询获取,如下

SELECT first_name, last_name, hire_date FROM employees
    WHERE hire_date > (SELECT hire_date FROM employees WHERE last_name = 'Jones');

使用连接方法解这个exercise如下:

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数量

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

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

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

SELECT job_title, AVG(salary) FROM employees 
    NATURAL JOIN jobs 
    GROUP BY job_title;

Practice 10

  编写一条SQL查询语句,显示employee的job title,名字和该employee的salary和其job的最低salary之间的差距

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

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;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: