-- 1. 创建表 (初始时不添加外键约束)
CREATE TABLE regions (
REGION_ID INT PRIMARY KEY,
REGION_NAME VARCHAR(50)
);
CREATE TABLE countries (
COUNTRY_ID CHAR(2) PRIMARY KEY,
COUNTRY_NAME VARCHAR(100),
REGION_ID INT
);
CREATE TABLE locations (
LOCATION_ID INT PRIMARY KEY,
STREET_ADDRESS VARCHAR(100),
POSTAL_CODE VARCHAR(20),
CITY VARCHAR(50),
STATE_PROVINCE VARCHAR(50),
COUNTRY_ID CHAR(2)
);
CREATE TABLE jobs (
JOB_ID VARCHAR(20) PRIMARY KEY,
JOB_TITLE VARCHAR(100),
MIN_SALARY DECIMAL(10,2),
MAX_SALARY DECIMAL(10,2)
);
CREATE TABLE departments (
DEPARTMENT_ID INT PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(50),
MANAGER_ID INT,
LOCATION_ID INT
);
CREATE TABLE employees (
EMPLOYEE_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
EMAIL VARCHAR(100),
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE,
JOB_ID VARCHAR(20),
SALARY DECIMAL(10,2),
COMMISSION_PCT DECIMAL(5,2),
MANAGER_ID INT,
DEPARTMENT_ID INT
);
CREATE TABLE job_history (
EMPLOYEE_ID INT,
START_DATE DATE,
END_DATE DATE,
JOB_ID VARCHAR(20),
DEPARTMENT_ID INT,
PRIMARY KEY (EMPLOYEE_ID, START_DATE)
);
-- 2. 插入数据 (顺序很重要，避免外键冲突)
-- ... (插入 regions, countries, locations, jobs 的数据) ...
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');
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');
-- 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 IDs from the 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. Set 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
FROM employees;
-- 7. Get the maximum and minimum salary from employees table
SELECT MAX(salary) AS Max_Salary, MIN(salary) AS Min_Salary
FROM employees;
-- 8. Get the number of employees working with the company
SELECT COUNT(*) AS Employee_Count
FROM employees;
-- 9. Get the number of unique jobs available in the employees table
SELECT COUNT(DISTINCT job_id) AS Job_Count
FROM employees;
-- 10. Display names 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;
-- 11. Display names and department IDs 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;
-- 12. Display names and salary for employees in departments 30 or 100 and 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 AND department_id IN (30, 100);
-- 13. Display names and hire date for employees hired in 1987.
SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR(hire_date) = 1987;
-- 14. Display first names of employees containing both "b" and "c".
SELECT first_name
FROM employees
WHERE first_name LIKE '%b%' AND first_name LIKE '%c%';
-- 15. Display last names, job IDs, and salaries 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);
-- 16. Display last names of employees with exactly 6 characters.
SELECT last_name
FROM employees
WHERE CHAR_LENGTH(last_name) = 6;
-- 17. Display last names of employees with 'e' as the third character.
SELECT last_name
FROM employees
WHERE last_name LIKE '__e%';
-- 18. Display the distinct job IDs available in the employees table.
SELECT DISTINCT job_id
FROM employees;
-- 19. Display name, salary, and PF (15% of salary) of all employees.
SELECT first_name, last_name, salary, salary * 0.15 AS PF
FROM employees;
-- 20. Aggregate Functions: Number of jobs available in the employees table.
SELECT COUNT(DISTINCT job_id) AS Job_Count
FROM employees;
-- 21. Get total salaries payable to employees.
SELECT SUM(salary) AS Total_Salaries
FROM employees;
-- 22. Get minimum salary from employees table.
SELECT MIN(salary) AS Min_Salary
FROM employees;
-- 23. Get maximum salary of an employee working as a Programmer.
SELECT MAX(salary) AS Max_Salary
FROM employees
WHERE job_id = 'IT_PROG';
-- 24. Get the average salary and number of employees working in department 20.
SELECT AVG(salary) AS Avg_Salary, COUNT(*) AS Employee_Count
FROM employees
WHERE department_id = 20;
-- 25. Get highest, lowest, sum, and average salary of all employees.
SELECT MAX(salary) AS Max_Salary, MIN(salary) AS Min_Salary, SUM(salary) AS Total_Salaries, AVG(salary) AS Avg_Salary
FROM employees;
-- 26. Get the number of employees with the same job.
SELECT job_id, COUNT(*) AS Employee_Count
FROM employees
GROUP BY job_id;
-- 27. Get the difference between the highest and lowest salaries.
SELECT (SELECT MAX(salary) FROM employees) - (SELECT MIN(salary) FROM employees) AS Salary_Difference;
-- 28. Find the manager ID and the salary of the lowest-paid employee for that manager.
SELECT manager_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
-- 29. Get department ID and total salary payable in each department.
SELECT department_id, SUM(salary) AS Total_Salaries
FROM employees
GROUP BY department_id;
-- 30. Get the average salary for each job ID excluding Programmer.
SELECT job_id, AVG(salary) AS Avg_Salary
FROM employees
WHERE job_id != 'IT_PROG'
GROUP BY job_id;
-- 31. Aggregate salary statistics for department ID 90.
SELECT job_id,
SUM(salary) AS total_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM employees
WHERE department_id = 90
GROUP BY job_id;
-- 32. Get job IDs with maximum salary >= $4000.
SELECT job_id, MAX(salary) AS max_salary
FROM employees
GROUP BY job_id
HAVING max_salary >= 4000;
-- 33. Get department IDs with more than 10 employees.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
-- 34. Get names of employees with salary greater than that of employee 'Bull'.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bull');
-- 35. Get names of employees in the IT department.
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');
-- 36. Get names of employees with a manager in the USA.
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'));
-- 37. Get distinct names of employees who are managers.
SELECT DISTINCT e.first_name, e.last_name
FROM employees e
WHERE e.employee_id IN (SELECT DISTINCT manager_id FROM employees);
-- 38. Get names of employees with salary greater than average salary.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 39. Get names of employees with the minimum salary in their job category.
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees WHERE job_id = employees.job_id);
-- 40. Get names of employees in IT with salary above average.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name LIKE 'IT%'));
-- 41. Get names of employees with salary greater than that of employee 'Bell'.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bell');
-- 42. Get names of employees with the lowest salary in each department.
SELECT first_name, last_name, salary
FROM employees
WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);
-- 43. Get names of employees with salary greater than average salary.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 44. Get names of employees with salary higher than max salary for '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;
-- 45. Get first names of employees who are not managers.
SELECT first_name, last_name
FROM employees
WHERE employee_id NOT IN (SELECT DISTINCT manager_id FROM employees);
-- 46. Get employee details along with their department names.
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;
-- 47. Get employees with IDs of their managers.
SELECT e.employee_id, e.first_name, 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;
-- 48. Get names of employees hired after employee 'Jones'.
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > (SELECT hire_date FROM employees WHERE last_name = 'Jones');
-- 49. Get department names and number of employees.
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;
-- 50. Get the job history for employees in department ID 90.
SELECT employee_id, job_id, DATEDIFF(end_date, start_date) AS days_between
FROM job_history
WHERE department_id = 90;
-- 51. Get department IDs and managers' first names.
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;
-- 52. Get department names, managers' names, and cities.
SELECT d.department_name, e.first_name AS manager_first_name
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id;
-- 53. Get average salary for each job.
SELECT job_id, AVG(salary) AS average_salary
FROM employees
GROUP BY job_id;
-- 54. Get each employee's salary difference from the minimum salary in their job category.
SELECT e.job_id, e.first_name, e.last_name,
(e.salary - (SELECT MIN(salary) FROM employees WHERE job_id = e.job_id)) AS salary_difference
FROM employees e;
-- 55. Get job history for employees with salary greater than $10,000.
SELECT *
FROM job_history
WHERE employee_id IN (SELECT employee_id FROM employees WHERE salary > 10000);
-- 56. Get departments managed by employees hired more than 15 years ago.
SELECT d.department_name, e.first_name, e.last_name, e.hire_date, e.salary
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id
WHERE YEAR(CURDATE()) - YEAR(e.hire_date) > 15;
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容