Practice and reinforce the concepts from Lesson 6
First, let's set up the company database you'll be investigating:
-- Create the company database for investigation
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE,
manager_id INT
);
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
employee_id INT,
sale_date DATE,
amount DECIMAL(10, 2),
product_category VARCHAR(50),
region VARCHAR(50),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
CREATE TABLE departments (
department_name VARCHAR(50) PRIMARY KEY,
budget DECIMAL(12, 2),
location VARCHAR(100)
);
-- Insert investigation data
INSERT INTO employees VALUES
(1, 'Sarah', 'Johnson', 'Sales', 75000, '2020-01-15', NULL),
(2, 'Mike', 'Chen', 'Sales', 82000, '2019-03-22', 1),
(3, 'Emily', 'Davis', 'Sales', 68000, '2021-06-10', 1),
(4, 'James', 'Wilson', 'Marketing', 65000, '2020-11-05', NULL),
(5, 'Lisa', 'Anderson', 'Sales', 95000, '2018-07-20', 1),
(6, 'Tom', 'Brown', 'IT', 78000, '2019-09-15', NULL),
(7, 'Jessica', 'Taylor', 'Sales', 71000, '2021-02-28', 1),
(8, 'David', 'Lee', 'Marketing', 62000, '2022-01-10', 4),
(9, 'Amanda', 'White', 'IT', 88000, '2019-04-15', 6),
(10, 'Robert', 'Garcia', 'Finance', 92000, '2018-11-20', NULL),
(11, 'Michelle', 'Martinez', 'Finance', 76000, '2020-08-10', 10),
(12, 'Chris', 'Thompson', 'Sales', 79000, '2020-05-15', 1);
INSERT INTO sales VALUES
(1, 2, '2024-01-15', 15000, 'Electronics', 'North'),
(2, 2, '2024-01-20', 22000, 'Electronics', 'North'),
(3, 3, '2024-01-18', 8000, 'Accessories', 'South'),
(4, 5, '2024-01-10', 35000, 'Electronics', 'East'),
(5, 5, '2024-01-25', 28000, 'Software', 'East'),
(6, 7, '2024-01-22', 12000, 'Accessories', 'West'),
(7, 2, '2024-02-05', 18000, 'Software', 'North'),
(8, 3, '2024-02-10', 9500, 'Electronics', 'South'),
(9, 5, '2024-02-15', 42000, 'Electronics', 'East'),
(10, 7, '2024-02-20', 16000, 'Software', 'West'),
(11, 12, '2024-01-30', 25000, 'Electronics', 'North'),
(12, 12, '2024-02-25', 31000, 'Software', 'North'),
(13, 2, '2024-03-01', 5000, 'Accessories', 'North'),
(14, 5, '2024-03-05', 48000, 'Electronics', 'East'),
(15, 3, '2024-03-10', 11000, 'Software', 'South');
INSERT INTO departments VALUES
('Sales', 500000, 'New York'),
('Marketing', 300000, 'Los Angeles'),
('IT', 400000, 'San Francisco'),
('Finance', 350000, 'Chicago');
(SELECT AVG(salary) FROM employees)SELECT
first_name,
last_name,
department,
salary,
ROUND(salary - (SELECT AVG(salary) FROM employees), 2) as above_average_by
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY above_average_by DESC;
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department = e1.department)SELECT
e1.first_name,
e1.last_name,
e1.department,
e1.salary,
(SELECT COUNT(*)
FROM employees e2
WHERE e2.department = e1.department) as dept_size
FROM employees e1
WHERE e1.salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department = e1.department
)
ORDER BY e1.salary DESC;
-- First, find sales employees with their performance
SELECT
e.first_name,
e.last_name,
COALESCE(
(SELECT SUM(amount)
FROM sales s
WHERE s.employee_id = e.employee_id), 0
) as total_sales,
CASE
WHEN EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.employee_id)
THEN 'Active Seller'
ELSE 'No Sales'
END as status
FROM employees e
WHERE e.department = 'Sales'
ORDER BY total_sales DESC;
-- Bonus: Compare to average seller
WITH sales_summary AS (
SELECT
e.employee_id,
e.first_name,
e.last_name,
COALESCE(SUM(s.amount), 0) as total_sales
FROM employees e
LEFT JOIN sales s ON e.employee_id = s.employee_id
WHERE e.department = 'Sales'
GROUP BY e.employee_id, e.first_name, e.last_name
)
SELECT
*,
ROUND(total_sales - (
SELECT AVG(total_sales)
FROM sales_summary
WHERE total_sales > 0
), 2) as vs_avg_seller
FROM sales_summary
ORDER BY total_sales DESC;
SELECT DISTINCT
e.first_name,
e.last_name,
e.department,
e.salary,
(SELECT ROUND(AVG(salary), 2)
FROM employees e2
WHERE e2.department = e.department) as dept_avg_salary,
(SELECT ROUND(AVG(s.amount), 2)
FROM sales s
WHERE s.employee_id = e.employee_id) as avg_sale_size,
(SELECT MAX(s.amount)
FROM sales s
WHERE s.employee_id = e.employee_id) as largest_sale
FROM employees e
WHERE
-- Earns above department average
e.salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e.department
)
-- Has made at least one sale above $20,000
AND EXISTS (
SELECT 1
FROM sales s
WHERE s.employee_id = e.employee_id
AND s.amount > 20000
)
-- Average sale size is above company average
AND (
SELECT AVG(amount)
FROM sales s
WHERE s.employee_id = e.employee_id
) > (
SELECT AVG(amount)
FROM sales
)
ORDER BY avg_sale_size DESC;