sql 人员按部门不同编号_SQL_join27题(万_门)

首先基础文件csv有6组，分别如下所示

countries.csv

departments.csv

employees.csv

job_history.csv

jobs.csv

locations.csv

---

#1. Write a query in SQL to display the first name, last name, department number, and department name for each employee

#1 在 SQL 中写一条查询语句来显示每个雇员的名字，姓氏，部门编号和部门名称

SELECT 
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name
FROM
    departments AS d
        JOIN
    employees AS e ON d.department_id = e.department_id;

#2. Write a query in SQL to display the first and last name, department, city, and state province for each employee

#2 在 SQL 中写一条查询语句来显示每个雇员的名字和姓氏，部门，城市，所在省

SELECT 
    e.first_name,
    e.last_name,
    d.department_id,
    l.city,
    l.state_province
FROM
    employees AS e
        JOIN
    departments AS d ON e.department_id = d.department_id
        JOIN
    locations AS l ON d.location_id = l.location_id;

#3. Write a query in SQL to display the first name, last name, salary, and job grade for all employees

#3 在 SQL 中写一条查询语句来显示每个雇员的名字，姓氏，工资 级别

#Sample table: job_grades

#GRADE_LEVEL LOWEST_SAL HIGHEST_SAL

#------------ ---------- -----------

#A 1000 2999

#B 3000 5999

#C 6000 9999

#D 10000 14999

#E 15000 24999

#F 25000 40000

SELECT 
    first_name,
    last_name,
    salary,
    (CASE
        WHEN salary BETWEEN 1000 AND 2999 THEN '#A'
        WHEN salary BETWEEN 3000 AND 5999 THEN '#B'
        WHEN salary BETWEEN 6000 AND 9999 THEN '#C'
        WHEN salary BETWEEN 10000 AND 14999 THEN '#D'
        WHEN salary BETWEEN 15000 AND 24999 THEN '#E'
        WHEN salary BETWEEN 25000 AND 40000 THEN '#F'
        ELSE 'G+'
    END) AS job_grades
FROM
    employees;

#4. Write a query in SQL to display the first name, last name, department number and department name, for all employees for departments 80 or 40

#4 在 SQL 中写一条查询语句来显示所有属于部门编号为 80 或者 40 的雇员的名字，姓氏，部门编号和部门名称

SELECT 
    e.first_name,
    e.last_name,
    d.department_id,
    d.department_name
FROM
    employees AS e
        JOIN
    departments AS d ON e.department_id = d.department_id
WHERE
    e.department_id IN (40 , 80)
ORDER BY e.last_name;


-- 此处多了解一下left and join
SELECT 
    e.first_name,
    e.last_name,
    d.department_id,
    d.department_name
FROM
    employees AS e
	left JOIN
    departments AS d ON e.department_id = d.department_id
where  
    e.department_id IN (40 , 80)
ORDER BY e.last_name;

#5. Write a query in SQL to display those employees who contain a letter z to their first name and also display their last name, department, city, and state province

#5 在 SQL 中写一条查询语句来显示那些名字中存在字母 ‘ z ’ 的雇员，同时也请显示出其相应的姓氏，部门，所在城市和所在省

SELECT 
    e.first_name,
    e.last_name,
    d.department_id,
    l.city,
    l.state_province
FROM
    employees AS e
        JOIN
    departments AS d ON e.department_id = d.department_id
        JOIN
    locations AS l ON d.location_id = l.location_id
        AND e.first_name LIKE '%z%';

#6. Write a query in SQL to display all departments including those where does not have any employee

#6 在 SQL 中写一条查询语句来显示所有的部门，包括那些没有雇员的部门

SELECT 
    d.department_id,  #e.department_id
    d.department_name,
    e.first_name,
    e.last_name
FROM
    employees AS e
        RIGHT JOIN
    departments AS d ON e.department_id = d.department_id;

#7. Write a query in SQL to display the first and last name and salary for those employees who earn less than the employee earn whose number is 182

#7 在 SQL 中写一条查询语句来显示工资少于【雇员编号为 182 号的雇员的工资】的雇员的名字，姓氏和工资

SELECT 
    e.first_name, e.last_name, e.salary
FROM
    employees AS e
        JOIN
    employees AS s ON e.salary < s.salary AND s.employee_id = 182;

--- 
select  first_name, last_name,salary  
from employees e
where e.salary < (select salary from employees where employee_id = 182);

---
SELECT 
    e.first_name, e.last_name, e.salary
FROM
    employees e
      left JOIN
    (SELECT 
        salary, employee_id
    FROM
        employees
    WHERE
        employee_id = 182) AS f_e on e.employee_id and f_e.employee_id
WHERE
    e.salary < f_e.salary;

#8. Write a query in SQL to display the first name of all employees including the first name of their manager

#8 在 SQL 中写一条查询语句来显示所有雇员的名字（不包括姓氏），也请同时显示其经理的名字（不包括姓氏）

SELECT 
    e.first_name AS 'employee_name',
    s.first_name AS 'manager_name'
FROM
    employees AS e
        JOIN
    employees AS s ON e.manager_id = s.employee_id;

#9. Write a query in SQL to display the department name, city, and state province for each department

#9 在 SQL 中写一条查询语句来显示每个部门的部门名称，所在城市，所在省

SELECT 
    d.department_name, l.city, l.state_province
FROM
    departments AS d
        JOIN
    locations AS l ON d.location_id = l.location_id;

#10. Write a query in SQL to display the first name, last name, department number and name, for all employees who have or have not any department

#10 在 SQL 中写一条查询语句来显示雇员的名字，姓氏，部门编号和部门名称

SELECT 
    e.first_name,
    e.last_name,
    d.department_id,
    d.department_name
FROM
    employees AS e
        JOIN
    departments AS d ON e.department_id = d.department_id;

#11. Write a query in SQL to display the first name of all employees and the first name of their manager including those who does not working under any manager

#11 在 SQL 中写一条查询语句来显示所有雇员的名字和他们相应的经理的名字，包括那些没有经理的雇员

SELECT 
    e.first_name AS 'employee_name',
    s.first_name AS 'manager_name'
FROM
    employees AS e
        LEFT JOIN
    employees AS s ON e.manager_id = s.employee_id;

#12. Write a query in SQL to display the first name, last name, and department number for those employees who works in the same department as the employee who holds the last name as Taylor

#12 在 SQL 中写一条查询语句来显示和【姓氏为 ‘ Taylor’的雇员】工作在同一部门下的雇员的名字，姓氏和部门编号

SELECT 
    e.first_name, e.last_name, e.department_id
FROM
    employees AS e
        JOIN
    employees AS s ON e.department_id = s.department_id
        AND e.last_name = 'Taylor';

#13. Write a query in SQL to display the job title, department name, full name (first and last name ) of employee, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997

#13 在 SQL 中写一条查询语句来显示雇员的职务名称，部门名称，姓名（姓氏和名字），并且显示职务起始时间在 1993 年 1 月 1 日之后，并且终止时间在 1997 年 8 月 31 日之前的职位

SELECT DISTINCT
    job_title,
    department_name,
    first_name,
    last_name,
    start_date
FROM
    job_history
        JOIN
    jobs USING (job_id)
        JOIN
    departments USING (department_id)
        JOIN
    employees USING (employee_id)
WHERE
    start_date >= '1993-01-01'
        AND start_date <= '1997-08-31'; 

#14. Write a query in SQL to display job title, full name (first and last name ) of employee, and the difference between maximum salary for the job and salary of the employee

#14 在 SQL 中写一条查询语句来显示雇员的职务名称，姓名（名字和姓氏），并且显示他们所得工资与该职位最大工资的上限的差

SELECT 
    job_title,
    first_name || ' ' || last_name AS employee_name,
    max_salary - salary AS salary_difference
FROM
    employees
        JOIN
    jobs ON employees.job_id = jobs.job_id;

#15. Write a query in SQL to display the name of the department, average salary and number of employees working in that department who got commission

#15 在 SQL 中写一条查询语句来显示部门的部门名称，平均工资和在该部门工作并获得提成的员工的数量

SELECT 
    department_name, AVG(salary), COUNT(employee_id)
FROM
    departments
        JOIN
    employees USING (department_id)
WHERE
    commission_pct != 0
GROUP BY department_name;

#16. Write a query in SQL to display the full name (first and last name ) of employee, and job title of those employees who is working in the department which ID 80 and deserve a commission percentage

#16 在 SQL 中写一条查询语句来显示雇员所在部门的部门编号为 80 ，同时应该得到一个提成率的雇员的姓名（名字和姓氏）和职务名称

SELECT 
    first_name, last_name, job_title
FROM
    employees
        JOIN
    jobs USING (job_id)
WHERE
    department_id = 80
        AND commission_pct != 0;

#17. Write a query in SQL to display the name of the country, city, and the departments which are running there

#17 在 SQL 中写一条查询语句来显示国家名称，城市和在该城市的部门

SELECT 
    country_name, city, department_name
FROM
    countries
        JOIN
    locations USING (country_id)
        JOIN
    departments USING (location_id);

#18. Write a query in SQL to display department name and the full name (first and last name) of the manager

#18 在 SQL 中写一条查询语句来显示部门名称和相应部门的经理的姓名（包括名字和姓氏）

SELECT 
    d.department_name, e.first_name, e.last_name
FROM
    departments d
        JOIN
    employees e USING (department_id)
WHERE
    e.employee_id = d.manager_id;

#19. Write a query in SQL to display job title and the average salary of employees

#19 在 SQL 中写一条查询语句来显示职务名称和相应员工的平均工资

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

#20. Write a query in SQL to display the details of jobs which was done by any of the employees who is presently earning a salary on and above 12000

#20 在 SQL 中写一条查询语句来显示其员工当前工资水平在 12000 及以上的职务的细节内容

SELECT 
    jh.*
FROM
    job_history AS jh
        JOIN
    employees AS e ON jh.employee_id = e.employee_id
WHERE
    salary >= 12000;

#21. Write a query in SQL to display the country name, city, and number of those departments where at leaste 2 employees are working

#21 在 SQL 中写一条查询语句来显示国家名称，城市和【有至少两位员工的部门的数量】

SELECT 
    country_name, city, COUNT(department_id)
FROM
    countries
        JOIN
    locations USING (country_id)
        JOIN
    departments USING (location_id)
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            employees
        GROUP BY department_id
        HAVING COUNT(employee_id) >= 2)
GROUP BY country_name , city;

----
SELECT 
    country_name, city, COUNT(department_id)
FROM
    (SELECT 
        country_name, city, department_id
    FROM
        countries
    INNER JOIN locations USING (country_id)
    INNER JOIN departments USING (location_id)
    INNER JOIN employees USING (department_id)
    GROUP BY country_name , city , department_id
    HAVING COUNT(employee_id) >= 2) AS a
GROUP BY country_name , city;

#22. Write a query in SQL to display the department name, full name (first and last name) of manager, and their city

#22 在 SQL 中写一条查询语句来显示部门名称，相应部门经理的姓名（名字和姓氏），并且显示其所在城市

SELECT 
    department_name, first_name, last_name, city
FROM
    employees
        JOIN
    departments USING (department_id)
        JOIN
    locations USING (location_id)
WHERE
    departments.manager_id = employees.employee_id;

#23. Write a query in SQL to display the employee ID, job name, number of days worked in for all those jobs in department 80

#23 在 SQL 中写一条查询语句来显示部门编号为 80 的职务的员工的员工 ID ，职务名称，工作天数

SELECT 
    employee_id, job_title, end_date - start_date DAYS
FROM
    job_history
        NATURAL JOIN
    jobs
WHERE
    department_id = 80;

#24. Write a query in SQL to display the full name (first and last name), and salary of those employees who working in any department located in London

#24 在 SQL 中写一条查询语句来显示姓名（名字和姓氏），和任何工作在部门所在地为伦敦的部门的员工的工资

SELECT 
    e.first_name, e.last_name, e.salary, l.city
FROM
    employees AS e
        JOIN
    departments AS d USING (department_id)
        JOIN
    locations AS l USING (location_id)
WHERE
    l.city = 'London';

#25. Write a query in SQL to display full name(first and last name), job title, starting and ending date of last jobs for those employees with worked without a commission percentage

#25 在 SQL 中写一条查询语句来显示没有提成率的员工的姓名（名字和姓氏），职务名称，上一份工作的起始日期和终止日期

SELECT 
    CONCAT(c.first_name, ' ', c.last_name) AS employee_name,
    job_title,
    start_date,
    end_date
FROM
    job_history AS a
        JOIN
    jobs AS b USING (job_id)
        JOIN
    employees AS c ON (a.employee_id = c.employee_id)
WHERE
    commission_pct = 0;

#26. Write a query in SQL to display the department name and number of employees in each of the department

#26 在 SQL 中写一条查询语句来显示部门名称和在相应部门工作的员工数量

SELECT 
    d.department_name, COUNT(*) AS 'employees_count'
FROM
    departments d
        INNER JOIN
    employees e ON e.department_id = d.department_id
GROUP BY d.department_name;

#27. Write a query in SQL to display the full name (firt and last name ) of employee with ID and name of the country presently where (s)he is working

#27 在 SQL 中写一条查询语句来显示员工姓名（名字和姓氏），及其正在工作的国家 ID 和国家名称

SELECT 
    first_name, last_name, employee_id, country_name
FROM
    employees
        JOIN
    departments USING (department_id)
        JOIN
    locations USING (location_id)
        JOIN
    countries USING (country_id);