数据库考试代码

点我一键打开复制好的代码的MySQL在线编辑器网站

MySQL数据库在线编辑器：https://onecompiler.com/mysql

CREATE TABLE regions (
    region_id INT,
    region_name VARCHAR(255)
);

CREATE TABLE countries (
    country_id CHAR(2),
    country_name VARCHAR(255),
    region_id INT
);

CREATE TABLE locations (
    location_id INT,
    street_address VARCHAR(255),
    postal_code VARCHAR(255),
    city VARCHAR(255),
    state_province VARCHAR(255),
    country_id CHAR(2)
);

CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(255),
    manager_id INT,
    location_id INT
);

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    phone_number VARCHAR(255),
    hire_date DATE,
    job_id VARCHAR(255),
    salary DECIMAL(10, 2),
    commission_pct DECIMAL(3, 2),
    manager_id INT,
    department_id INT
);

CREATE TABLE job_history (
    employee_id INT,
    start_date DATE,
    end_date DATE,
    job_id VARCHAR(255),
    department_id INT
);

CREATE TABLE jobs (
    job_id VARCHAR(255),
    job_title VARCHAR(255),
    min_salary DECIMAL(10, 2),
    max_salary DECIMAL(10, 2)
);

CREATE TABLE job_grades (
    grade VARCHAR(255),
    lowest_sal DECIMAL(10, 2),
    highest_sal DECIMAL(10, 2)
);

INSERT INTO `countries` (`COUNTRY_ID`, `COUNTRY_NAME`, `REGION_ID`) VALUES
('AR', 'Argentina', '2'),
('AU', 'Australia', '3'),
('BE', 'Belgium', '1'),
('BR', 'Brazil', '2'),
('CA', 'Canada', '2'),
('CH', 'Switzerland', '1'),
('CN', 'China', '3'),
('DE', 'Germany', '1'),
('DK', 'Denmark', '1'),
('EG', 'Egypt', '4'),
('FR', 'France', '1'),
('HK', 'HongKong', '3'),
('IL', 'Israel', '4'),
('IN', 'India', '3'),
('IT', 'Italy', '1'),
('JP', 'Japan', '3'),
('KW', 'Kuwait', '4'),
('MX', 'Mexico', '2'),
('NG', 'Nigeria', '4'),
('NL', 'Netherlands', '1'),
('SG', 'Singapore', '3'),
('UK', 'United Kingdom', '1'),
('US', 'United States of America', '2'),
('ZM', 'Zambia', '4'),
('ZW', 'Zimbabwe', '4');



INSERT INTO `departments` (`DEPARTMENT_ID`, `DEPARTMENT_NAME`, `MANAGER_ID`, `LOCATION_ID`) VALUES
('10', 'Administration', '200', '1700'),
('20', 'Marketing', '201', '1800'),
('30', 'Purchasing', '114', '1700'),
('40', 'Human Resources', '203', '2400'),
('50', 'Shipping', '121', '1500'),
('60', 'IT', '103', '1400'),
('70', 'Public Relations', '204', '2700'),
('80', 'Sales', '145', '2500'),
('90', 'Executive', '100', '1700'),
('100', 'Finance', '108', '1700'),
('110', 'Accounting', '205', '1700'),
('120', 'Treasury', '0', '1700'),
('130', 'Corporate Tax', '0', '1700'),
('140', 'Control And Credit', '0', '1700'),
('150', 'Shareholder Services', '0', '1700'),
('160', 'Benefits', '0', '1700'),
('170', 'Manufacturing', '0', '1700'),
('180', 'Construction', '0', '1700'),
('190', 'Contracting', '0', '1700'),
('200', 'Operations', '0', '1700'),
('210', 'IT Support', '0', '1700'),
('220', 'NOC', '0', '1700'),
('230', 'IT Helpdesk', '0', '1700'),
('240', 'Government Sales', '0', '1700'),
('250', 'Retail Sales', '0', '1700'),
('260', 'Recruiting', '0', '1700'),
('270', 'Payroll', '0', '1700');


INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PHONE_NUMBER`, `HIRE_DATE`, `JOB_ID`, `SALARY`, `COMMISSION_PCT`, `MANAGER_ID`, `DEPARTMENT_ID`) VALUES
('100', 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', '24000.00', '0.00', '0', '90'),
('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1987-06-18', 'AD_VP', '17000.00', '0.00', '100', '90'),
('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1987-06-19', 'AD_VP', '17000.00', '0.00', '100', '90'),
('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1987-06-20', 'IT_PROG', '9000.00', '0.00', '102', '60'),
('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1987-06-21', 'IT_PROG', '6000.00', '0.00', '103', '60'),
('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1987-06-22', 'IT_PROG', '4800.00', '0.00', '103', '60'),
('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '1987-06-23', 'IT_PROG', '4800.00', '0.00', '103', '60'),
('107', 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1987-06-24', 'IT_PROG', '4200.00', '0.00', '103', '60'),
('108', 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '1987-06-25', 'FI_MGR', '12000.00', '0.00', '101', '100'),
('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1987-06-26', 'FI_ACCOUNT', '9000.00', '0.00', '108', '100'),
('110', 'John', 'Chen', 'JCHEN', '515.124.4269', '1987-06-27', 'FI_ACCOUNT', '8200.00', '0.00', '108', '100'),
('111', 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '1987-06-28', 'FI_ACCOUNT', '7700.00', '0.00', '108', '100'),
('112', 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '1987-06-29', 'FI_ACCOUNT', '7800.00', '0.00', '108', '100'),
('113', 'Luis', 'Popp', 'LPOPP', '515.124.4567', '1987-06-30', 'FI_ACCOUNT', '6900.00', '0.00', '108', '100'),
('114', 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '1987-07-01', 'PU_MAN', '11000.00', '0.00', '100', '30'),
('115', 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '1987-07-02', 'PU_CLERK', '3100.00', '0.00', '114', '30'),
('116', 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '1987-07-03', 'PU_CLERK', '2900.00', '0.00', '114', '30'),
('117', 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '1987-07-04', 'PU_CLERK', '2800.00', '0.00', '114', '30'),
('118', 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '1987-07-05', 'PU_CLERK', '2600.00', '0.00', '114', '30'),
('119', 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '1987-07-06', 'PU_CLERK', '2500.00', '0.00', '114', '30'),
('120', 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '1987-07-07', 'ST_MAN', '8000.00', '0.00', '100', '50');





INSERT INTO `job_history` (`EMPLOYEE_ID`, `START_DATE`, `END_DATE`, `JOB_ID`, `DEPARTMENT_ID`) VALUES
('102', '1993-01-13', '1998-07-24', 'IT_PROG', '60'),
('101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'),
('101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'),
('201', '1996-02-17', '1999-12-19', 'MK_REP', '20'),
('114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'),
('122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'),
('200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'),
('176', '1998-03-24', '1998-12-31', 'SA_REP', '80'),
('176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'),
('200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90');



INSERT INTO `jobs` (`JOB_ID`, `JOB_TITLE`, `MIN_SALARY`, `MAX_SALARY`) VALUES
('AD_PRES', 'President', '20000', '40000'),
('AD_VP', 'Administration Vice President', '15000', '30000'),
('AD_ASST', 'Administration Assistant', '3000', '6000'),
('FI_MGR', 'Finance Manager', '8200', '16000'),
('FI_ACCOUNT', 'Accountant', '4200', '9000'),
('AC_MGR', 'Accounting Manager', '8200', '16000'),
('AC_ACCOUNT', 'Public Accountant', '4200', '9000'),
('SA_MAN', 'Sales Manager', '10000', '20000'),
('SA_REP', 'Sales Representative', '6000', '12000'),
('PU_MAN', 'Purchasing Manager', '8000', '15000'),
('PU_CLERK', 'Purchasing Clerk', '2500', '5500'),
('ST_MAN', 'Stock Manager', '5500', '8500'),
('ST_CLERK', 'Stock Clerk', '2000', '5000'),
('SH_CLERK', 'Shipping Clerk', '2500', '5500'),
('IT_PROG', 'Programmer', '4000', '10000'),
('MK_MAN', 'Marketing Manager', '9000', '15000'),
('MK_REP', 'Marketing Representative', '4000', '9000'),
('HR_REP', 'Human Resources Representative', '4000', '9000'),
('PR_REP', 'Public Relations Representative', '4500', '10500');



INSERT INTO `locations` (`LOCATION_ID`, `STREET_ADDRESS`, `POSTAL_CODE`, `CITY`, `STATE_PROVINCE`, `COUNTRY_ID`) VALUES
('1000', '1297 Via Cola di Rie', '989', 'Roma', '', 'IT'),
('1100', '93091 Calle della Testa', '10934', 'Venice', '', 'IT'),
('1200', '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'),
('1300', '9450 Kamiya-cho', '6823', 'Hiroshima', '', 'JP'),
('1400', '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'),
('1500', '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'),
('1600', '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'),
('1700', '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'),
('1800', '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'),
('1900', '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'),
('2000', '40-5-12 Laogianggen', '190518', 'Beijing', '', 'CN'),
('2100', '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'),
('2200', '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'),
('2300', '198 Clementi North', '540198', 'Singapore', '', 'SG'),
('2400', '8204 Arthur St', '', 'London', '', 'UK'),
('2500', '"Magdalen Centre', ' The Oxford ', 'OX9 9ZB', 'Oxford', 'UK'),
('2600', '9702 Chester Road', '9629850293', 'Stretford', 'Manchester', 'UK'),
('2700', 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'),
('2800', 'Rua Frei Caneca 1360', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'),
('2900', '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'),
('3000', 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'),
('3100', 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'),
('3200', 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal', 'MX');


INSERT INTO `regions` (`REGION_ID`, `REGION_NAME`) VALUES
('1', 'Europe'),
('2', 'Americas'),
('3', 'Asia'),
('4', 'Middle East and Africa');

-- Exercise 3. SELECT statement

-- 1. Display the names (first_name, last_name) using alias name "First Name", "Last Name"
SELECT first_name AS "First Name", last_name AS "Last Name"
FROM employees;

-- 2. Get unique department ID from employee table
SELECT DISTINCT department_id
FROM employees;

-- 3. Get all employee details from the employee table ordered by first name, descending
SELECT *
FROM employees
ORDER BY first_name DESC;

-- 4. Get the names (first_name, last_name), salary, PF of all employees (PF is 15% of salary)
SELECT first_name, last_name, salary, salary * 0.15 AS PF
FROM employees;

-- 5. Get the employee ID, names (first_name, last_name), salary in ascending order of salary
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary ASC;

-- 6. Get the total salaries payable to employees
SELECT SUM(salary) AS total_salaries_payable
FROM employees;

-- 7. Get the maximum and minimum salary from employees table
SELECT MAX(salary) AS maximum_salary, MIN(salary) AS minimum_salary
FROM employees;

-- 8. Get the number of employees working with the company
SELECT COUNT(*) AS number_of_employees
FROM employees;

-- 9. Get the number of jobs available in the employees table
SELECT COUNT(DISTINCT job_id) AS number_of_jobs
FROM employees;


-- Exercise 4. Restricting and sorting data

-- 1. Display the name (first_name, last_name) and salary for employees whose salary is not in the range $10,000 through $15,000
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 10000 AND 15000;

-- 2. Display the name (first_name, last_name) and department ID of employees in departments 30 or 100 in ascending order
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (30, 100)
ORDER BY first_name ASC;

-- 3. Display the name (first_name, last_name) and salary for employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 10000 AND 15000
  AND department_id IN (30, 100);

-- 4. Display the name (first_name, last_name) and hire date for employees hired in 1987
SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR(hire_date) = 1987;

-- 5. Display the first_name of employees who have both "b" and "c" in their first name
SELECT first_name
FROM employees
WHERE first_name LIKE '%b%' AND first_name LIKE '%c%';

-- 6. Display the last name, job, and salary for employees whose job is Programmer or Shipping Clerk, and whose salary is not $4,500, $10,000, or $15,000
SELECT last_name, job_id, salary
FROM employees
WHERE job_id IN ('IT_PROG', 'SH_CLERK')
  AND salary NOT IN (4500, 10000, 15000);

-- 7. Display the last name of employees whose names have exactly 6 characters
SELECT last_name
FROM employees
WHERE LENGTH(last_name) = 6;

-- 8. Display the last name of employees having 'e' as the third character
SELECT last_name
FROM employees
WHERE last_name LIKE '__e%';

-- 9. Display the jobs/designations available in the employees table
SELECT DISTINCT job_id
FROM employees;

-- 10. Display the name (first_name, last_name), salary, and PF (15% of salary) of all employees
SELECT first_name, last_name, salary, salary * 0.15 AS PF
FROM employees;


-- Exercise 5. Aggregate functions

-- 1. List the number of jobs available in the employees table
SELECT COUNT(DISTINCT job_id) AS number_of_jobs
FROM employees;

-- 2. Get the total salaries payable to employees
SELECT SUM(salary) AS total_salaries_payable
FROM employees;

-- 3. Get the minimum salary from employees table
SELECT MIN(salary) AS minimum_salary
FROM employees;

-- 4. Get the maximum salary of an employee working as a Programmer
SELECT MAX(salary) AS maximum_programmer_salary
FROM employees
WHERE job_id = 'IT_PROG';

-- 5. Get the average salary and number of employees working in department 90
SELECT AVG(salary) AS average_salary, COUNT(*) AS number_of_employees
FROM employees
WHERE department_id = 90;

-- 6. Get the highest, lowest, sum, and average salary of all employees
SELECT MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM employees;

-- 7. Get the number of employees with the same job
SELECT job_id, COUNT(*) AS number_of_employees
FROM employees
GROUP BY job_id;

-- 8. Get the difference between the highest and lowest salaries
SELECT MAX(salary) - MIN(salary) AS salary_difference
FROM employees;

-- 9. Find the manager ID and the salary of the lowest-paid employee for that manager
SELECT manager_id, MIN(salary) AS lowest_paid_salary
FROM employees
GROUP BY manager_id;

-- 10. Get the department ID and the total salary payable in each department
SELECT department_id, SUM(salary) AS total_salary_payable
FROM employees
GROUP BY department_id;

-- 11. Get the average salary for each job ID excluding Programmer
SELECT job_id, AVG(salary) AS average_salary
FROM employees
WHERE job_id != 'IT_PROG'
GROUP BY job_id;

-- 12. Get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only
SELECT job_id,
       SUM(salary) AS total_salary,
       MAX(salary) AS maximum_salary,
       MIN(salary) AS minimum_salary,
       AVG(salary) AS average_salary
FROM employees
WHERE department_id = 90
GROUP BY job_id;

-- 13. Get the job ID and maximum salary of employees where maximum salary is greater than or equal to $4000
SELECT job_id, MAX(salary) AS maximum_salary
FROM employees
GROUP BY job_id
HAVING MAX(salary) >= 4000;

-- 14. Get the average salary for all departments employing more than 10 employees
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;


-- Exercise 6. Subquery

-- 1. Find the name (first_name, last_name) and salary of employees who have a higher salary than the employee whose last_name = 'Bull'
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bull');

-- 2. Find the name (first_name, last_name) of all employees who work in the IT department
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

-- 3. Find the name (first_name, last_name) of employees who have a manager and worked in a USA-based department
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NOT NULL
  AND department_id IN (
      SELECT department_id
      FROM departments
      WHERE location_id IN (
          SELECT location_id
          FROM locations
          WHERE country_id = 'US'
      )
  );

-- 4. Find the name (first_name, last_name) of employees who are managers
SELECT first_name, last_name
FROM employees
WHERE employee_id IN (SELECT manager_id FROM departments);

-- 5. Find the name (first_name, last_name) and salary of employees whose salary is greater than the average salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 6. Find the name (first_name, last_name) and salary of employees whose salary is equal to the minimum salary for their job grade
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees WHERE job_id = employees.job_id);

-- 7. Find the name (first_name, last_name) and salary of employees who earn more than the average salary and work in any IT department
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
  AND department_id IN (SELECT department_id FROM departments WHERE department_name LIKE '%IT%');

-- 8. Find the name (first_name, last_name) and salary of employees who earn more than Mr. Bell
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bell');

-- 9. Find the name (first_name, last_name) and salary of employees who earn the same salary as the minimum salary for all departments
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

-- 10. Find the name (first_name, last_name) and salary of employees whose salary is greater than the average salary of all departments
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 11. Find the name (first_name, last_name) and salary of employees who earn a salary higher than all Shipping Clerks (JOB_ID = 'SH_CLERK')
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK')
ORDER BY salary ASC;

-- 12. Find the name (first_name, last_name) of employees who are not supervisors
SELECT first_name, last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL);

-- 13. Display the employee ID, first name, last name, and department names of all employees
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 14. Display the employee ID, first name, last name, salary of employees whose salary is above average for their department
SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- 15. Find the 5th maximum salary in the employees table
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 4;

-- 16. Find the 4th minimum salary in the employees table
SELECT DISTINCT salary
FROM employees
ORDER BY salary ASC
LIMIT 1 OFFSET 3;

-- 17. Select the last 10 records from the employees table
SELECT *
FROM employees
ORDER BY employee_id DESC
LIMIT 10;

-- 18. List the department ID and name of departments where no employee is working
SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

-- 19. Get the 3 maximum salaries
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- 20. Get the 3 minimum salaries
SELECT DISTINCT salary
FROM employees
ORDER BY salary ASC
LIMIT 3;


-- Exercise 7. Joins

-- 1. Find the addresses (location_id, street_address, city, state_province, country_name) of all departments
SELECT l.location_id, l.street_address, l.city, l.state_province, c.country_name
FROM locations l
JOIN countries c ON l.country_id = c.country_id
JOIN departments d ON l.location_id = d.location_id;

-- 2. Find the name (first_name, last_name), department ID, and name of all employees
SELECT e.first_name, e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 3. Find the name (first_name, last_name), job, department ID, and name of employees who work in London
SELECT e.first_name, e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'London';

-- 4. Find the employee ID, name (last_name), along with their manager ID and name (last_name)
SELECT e.employee_id, e.last_name, e.manager_id, m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- 5. Find the name (first_name, last_name) and hire date of employees hired after Jones
SELECT e.first_name, e.last_name, e.hire_date
FROM employees e
WHERE e.hire_date > (SELECT hire_date FROM employees WHERE last_name = 'Jones');

-- 6. Get the department name and number of employees in the department
SELECT d.department_name, COUNT(e.employee_id) AS number_of_employees
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- 7. Find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90
SELECT jh.employee_id, j.job_title, DATEDIFF(jh.end_date, jh.start_date) AS days_between
FROM job_history jh
JOIN jobs j ON jh.job_id = j.job_id
WHERE jh.department_id = 90;

-- 8. Display the department ID, name, and first name of the manager
SELECT d.department_id, d.department_name, e.first_name AS manager_first_name
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id;

-- 9. Display the department name, manager name, and city
SELECT d.department_name, CONCAT(e.first_name, ' ', e.last_name) AS manager_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;

-- 10. Display the job title and average salary of employees
SELECT j.job_title, AVG(e.salary) AS average_salary
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY j.job_title;

-- 11. Display job title, employee name, and the difference between salary of the employee and minimum salary for the job
SELECT j.job_title, CONCAT(e.first_name, ' ', e.last_name) AS employee_name, e.salary - j.min_salary AS salary_difference
FROM employees e
JOIN jobs j ON e.job_id = j.job_id;

-- 12. Display the job history of employees currently drawing more than $10,000
SELECT jh.*
FROM job_history jh
JOIN employees e ON jh.employee_id = e.employee_id
WHERE e.salary > 10000;

-- 13. Display department name, name (first_name, last_name), hire date, salary of managers with more than 15 years of experience
SELECT d.department_name, CONCAT(e.first_name, ' ', e.last_name) AS manager_name, e.hire_date, e.salary
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id
WHERE TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) > 15;