<database_setup>DROP database IF EXISTS company\_db; CREATE database IF NOT EXISTS company\_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE company\_db; DROP TABLE IF EXISTS departments; CREATE TABLE departments ( dept\_id int(2) unsigned zerofill NOT NULL COMMENT 'Department identifier', dept\_name varchar(14) DEFAULT NULL COMMENT 'Department name', location varchar(13) DEFAULT NULL COMMENT 'Department location' ); DROP TABLE IF EXISTS employees; CREATE TABLE employees ( employee\_id int(6) unsigned zerofill NOT NULL COMMENT 'Employee identifier', employee\_name varchar(10) DEFAULT NULL COMMENT 'Employee name', position varchar(9) DEFAULT NULL COMMENT 'Job position', manager\_id int(4) unsigned zerofill DEFAULT NULL COMMENT 'Manager identifier', hire\_date datetime DEFAULT NULL COMMENT 'Hire date', salary decimal(7,2) DEFAULT NULL COMMENT 'Monthly salary', commission decimal(7,2) DEFAULT NULL COMMENT 'Commission', dept\_id int(2) unsigned zerofill DEFAULT NULL COMMENT 'Department identifier' ); DROP TABLE IF EXISTS salary\_grades; CREATE TABLE salary\_grades ( level int(11) DEFAULT NULL COMMENT 'Grade level', min\_salary int(11) DEFAULT NULL COMMENT 'Minimum salary for this grade', max\_salary int(11) DEFAULT NULL COMMENT 'Maximum salary for this grade' ); -- Insert data into departments table INSERT INTO departments (dept_id, dept_name, location) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO departments (dept_id, dept_name, location) VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO departments (dept_id, dept_name, location) VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO departments (dept_id, dept_name, location) VALUES (40, 'OPERATIONS', 'BOSTON'); -- Insert data into employees table INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20); INSERT INTO employees (employee_id, employee_name, psoition, manager_id, hire_date, salary, commission, dept_id) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20); INSERT INTO employees (employee_id, employee_name, position, manager_id, hire_date, salary, commission, dept_id) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10); -- Insert data into salary_grades table INSERT INTO salary_grades (level, min_salary, max_salary) VALUES (1, 700, 1200); INSERT INTO salary_grades (level, min_salary, max_salary) VALUES (2, 1201, 1400); INSERT INTO salary_grades (level, min_salary, max_salary) VALUES (3, 1401, 2000); INSERT INTO salary_grades (level, min_salary, max_salary) VALUES (4, 2001, 3000); INSERT INTO salary_grades (level, min_salary, max_salary) VALUES (5, 3001, 9999); </database_setup><basic_queries>Fundamental Query Examples

Retrieve employees with salary greater than 500 or position as MANAGER, whose names start with 'J':

SELECT employee_name FROM employees WHERE (salary > 500 OR position = 'MANAGER') AND employee_name LIKE 'J%';

Sort results by department ID in ascending order and by salary in descending order:

SELECT * FROM employees ORDER BY dept_id ASC, salary DESC;

Display annual salary calculation and sort by it in descending order:

SELECT employee_name, salary*12 + IFNULL(commission, 0) AS annual_income FROM employees ORDER BY annual_income DESC;

Show the name and position of the highest-paid employee:

SELECT employee_name, position FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Display information about employees earning more than the average salary:

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Show the average and maximum salary for each department:

SELECT dept_id, FORMAT(AVG(salary), 2), MAX(salary) FROM employees GROUP BY dept_id;

Display departments with average salary below 2000, showing their IDs and average salaries:

SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id HAVING AVG(salary) < 2000;

Show count of employees and average salary for each position:

SELECT position AS job_title, COUNT(*) AS employee_count, FORMAT(AVG(salary), 2) AS avg_salary 
FROM employees GROUP BY position;

</basic_queries><multi_table_queries>Multi-Table Queries

In real-world scenarios, data is often distributed across multiple tables, requiring joins for comprehensive retrieval.

Display employee names, salaries, and department names:

SELECT e.employee_name, e.salary, d.dept_name 
FROM employees e, departments d 
WHERE e.dept_id = d.dept_id;

Show department name, employee name, and salary for department ID 10:

SELECT d.dept_name, e.employee_name, e.salary 
FROM employees e, departments d 
WHERE e.dept_id = d.dept_id AND d.dept_id = 10;

Display employee names, salaries, and their corresponding salary grades:

SELECT e.employee_name, e.salary, s.level 
FROM employees e, salary_grades s 
WHERE e.salary BETWEEN s.min_salary AND s.max_salary;

</multi_table_queries><self_joins>Self Joins

A self join occurs when a table is joined with itself to establish relationships within the same dataset.

Find the manager ID and name for employee FORD:

Using a subquery approach:

SELECT manager_id, employee_name FROM employees 
WHERE employee_id = (SELECT manager_id FROM employees WHERE employee_name = 'FORD');

Using a self join approach:

SELECT e2.employee_id, e2.employee_name 
FROM employees e1, employees e2 
WHERE e1.manager_id = e2.employee_id AND e1.employee_name = 'FORD';

</self_joins>Subqueries

Subqueries can be nested within other SQL statements to provide more complex data retrieval capabilities.

Single-Row Subqueries

Retrieve employees in the same department as SMITH:

SELECT * FROM employees 
WHERE dept_id = (SELECT dept_id FROM employees WHERE employee_name = 'SMITH');

Multi-Row Subqueries

The IN keyword: satisfies any condition in the list

Find employees with the same positions as those in department 10, excluding department 10 employees:

SELECT employee_name, position, salary, dept_id 
FROM employees 
WHERE position IN (SELECT DISTINCT position FROM employees WHERE dept_id = 10) 
AND dept_id <> 10;

The ALL keyword: satisfies all conditions in the list

Show employees earning more than everyone in department 30:

SELECT employee_name, salary, dept_id 
FROM employees 
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 30);

The ANY keyword: satisfies at least one condition in the list

Show employees earning more than at least one person in department 30:

SELECT employee_name, salary, dept_id 
FROM employees 
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 30);

Multi-Column Subqueries

MySQL supports comparing multiple columns simultaneously

Find employees with the same position and department as SMITH, excluding SMITH:

SELECT * FROM employees 
WHERE (position, dept_id) = (SELECT position, dept_id FROM employees WHERE employee_name = 'SMITH') 
AND employee_name <> 'SMITH';

Using Subqueries in the FROM Clause

Treat a subquery as a temporary table for further processing

Display employees earning more than their department's average:

SELECT e.employee_name, e.dept_id, e.salary, FORMAT(d.avg_salary, 2) 
FROM employees e, 
     (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) d 
WHERE e.dept_id = d.dept_id AND e.salary > d.avg_salary;

Find the highest-paid employee in each department:

SELECT e.employee_name, e.salary, e.dept_id, d.max_salary 
FROM employees e, 
     (SELECT dept_id, MAX(salary) AS max_salary FROM employees GROUP BY dept_id) d 
WHERE e.dept_id = d.dept_id AND e.salary = d.max_salary;

Show department information (name, ID, location) along with employee count:

SELECT d.dept_name, d.dept_id, d.location, e.count 
FROM departments d, 
     (SELECT dept_id, COUNT(*) AS count FROM employees GROUP BY dept_id) e 
WHERE d.dept_id = e.dept_id;

<union_queries>Union Queries

UNION Operator

Combines results from two SELECT statements, removing duplicate rows (requires same number and type of columns in both queries)

Find employees with salary greater than 2500 or position as MANAGER:

SELECT * FROM employees WHERE salary > 2500 
UNION 
SELECT * FROM employees WHERE position = 'MANAGER';

UNION ALL Operator

Combines results from two SELECT statements, including all rows even if duplicates exist (requires same number and type of columns in both queries)

Find employees with salary greater than 2500 or position as MANAGER, including duplicates:

SELECT * FROM employees WHERE salary > 2500 
UNION ALL 
SELECT * FROM employees WHERE position = 'MANAGER';

</union_queries>