Module 2: Table Joins and Linking

1. Retrieve the name and salary of staff members working in Luton.

SELECT s.name, s.base_salary
FROM staff AS s
INNER JOIN location_data AS l ON s.dept_id = l.dept_id
WHERE l.city = 'LUTON';

2. Combine the location_data table with the staff table and display the results sorted by department ID.

SELECT l.dept_label, s.name, s.role, s.base_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
ORDER BY l.dept_id;

3. Display the names of all salespeople assigned to the Sales department.

SELECT s.name
FROM staff s
WHERE s.dept_id = (SELECT dept_id FROM location_data WHERE dept_label = 'SALES') AND s.role = 'SALESMAN';

4. Identify all departments that currently have no assigned staff.

SELECT dept_id, dept_label
FROM location_data
WHERE dept_id NOT IN (SELECT DISTINCT dept_id FROM staff);

5. For any employee earning more than their supervisor, list the employee's name and salary alongside the manager's name and salary.

SELECT e.name AS Worker_Name, e.base_salary AS Worker_Salary, m.name AS Manager_Name, m.base_salary AS Manager_Salary
FROM staff e
INNER JOIN staff m ON e.boss_id = m.emp_id
WHERE e.base_salary > m.base_salary;

6. Identify staff members who report directly to Blake.

SELECT name
FROM staff
WHERE boss_id = (SELECT emp_id FROM staff WHERE name = 'BLAKE');

7. List every staff member's name along with their manager's name, including staff who do not have a manager.

SELECT s.name AS Worker_Name, COALESCE(m.name, 'No Manager') AS Manager_Name
FROM staff s
LEFT JOIN staff m ON s.boss_id = m.emp_id;

Module 3: SQL Functions

1. Count the total number of staff members who do not have a manager (typically top-level executives) without listing their names.

SELECT COUNT(*)
FROM staff
WHERE boss_id IS NULL;

2. Calculate the average annual compensation (salary plus commission) for all salespeople.

SELECT AVG(base_salary + COALESCE(commission_amount, 0)) AS Avg_Total_Comp
FROM staff
WHERE role = 'SALESMAN';

3. Determine the highest and lowest salaries in the company and calculate the difference between them in a single query.

SELECT MAX(base_salary) AS max_pay, MIN(base_salary) AS min_pay, MAX(base_salary) - MIN(base_salary) AS pay_range
FROM staff;

4. Find the length of the longest department name.

SELECT MAX(LENGTH(dept_label)) AS max_label_length
FROM location_data;

5. In department 30, count how many people receive a salary and how many receive a commission in a single statement.

SELECT
  SUM(CASE WHEN base_salary IS NOT NULL THEN 1 ELSE 0 END) AS with_salary,
  SUM(CASE WHEN commission_amount IS NOT NULL THEN 1 ELSE 0 END) AS with_commission
FROM staff
WHERE dept_id = 30;

6. Calculate the average commission for employees who actually receive one, and the average commission for all employees (treating non-recipients as zero).

SELECT
  AVG(commission_amount) AS avg_comm_receivers,
  AVG(COALESCE(commission_amount, 0)) AS avg_comm_all
FROM staff;

7. Calculate the average salary, average commission, average total compensation for commission-receivers, and average total compensation for all employees in one query.

SELECT
  AVG(base_salary) AS avg_salary,
  AVG(commission_amount) AS avg_comm,
  AVG(CASE WHEN commission_amount IS NOT NULL THEN base_salary + commission_amount ELSE 0 END) AS avg_total_receiver,
  AVG(base_salary + COALESCE(commission_amount, 0)) AS avg_total_all
FROM staff;

8. Compute the daily and hourly wage for department 30 staff, rounding to the nearest penny. Assume 22 working days per month and 8 hours per day.

SELECT name,
    ROUND(base_salary / 22, 2) AS daily_wage,
    ROUND((base_salary / 22) / 8, 2) AS hourly_wage
FROM staff
WHERE dept_id = 30;

9. Repeat the previous query, but truncate the values to the nearest penny instead of rounding.

SELECT name,
       TRUNCATE(base_salary / 22, 2) AS daily_wage,
       TRUNCATE((base_salary / 22) / 8, 2) AS hourly_wage
FROM staff
WHERE dept_id = 30;

Module 4: Date and Time Handling

1. Select the name, role, and hire date for department 20, formatting the date as MM/DD/YY.

SELECT name, role, DATE_FORMAT(hire_date, '%m/%d/%y') AS formatted_date
FROM staff
WHERE dept_id = 20;

2. Format the hire date to show the Day of Week, Month Name, Day of Month, and Year.

SELECT name,
    CONCAT(DAYNAME(hire_date), ', ', MONTHNAME(hire_date), ' ', DAY(hire_date), ', ', YEAR(hire_date)) AS full_date
FROM staff;

3. Identify employees hired in March.

SELECT name, hire_date
FROM staff
WHERE MONTH(hire_date) = 3;

4. Identify employees hired on a Tuesday.

SELECT name, hire_date
FROM staff
WHERE DAYNAME(hire_date) = 'Tuesday';

5. Check if any employees have tenure exceeding 16 years.

SELECT EXISTS(
  SELECT 1
  FROM staff
  WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) > 16
) AS has_long_term_employees;

6. Display the weekday of the first day of the month for each employee's hire date.

SELECT name, DAYNAME(STR_TO_DATE(CONCAT(YEAR(hire_date), '-', MONTH(hire_date), '-01'), '%Y-%m-%d')) AS first_day_weekday
FROM staff;

7. Show hire dates and the date of the first payday (the last Friday of the month). Create a function to calculate this.

DELIMITER //

CREATE FUNCTION GetFirstPayday(start_date DATE)
RETURNS DATE
DETERMINISTIC
NO SQL
BEGIN
  DECLARE end_of_month DATE;
  DECLARE final_friday DATE;

  SET end_of_month = LAST_DAY(start_date);
  SET final_friday = end_of_month - INTERVAL((WEEKDAY(end_of_month) + 3) % 7) DAY;

  RETURN final_friday;
END//

DELIMITER ;

SELECT name, hire_date, GetFirstPayday(hire_date) AS First_Payday
FROM staff;

8. Refine the function so that if an employee is hired after the last Friday of the month, their first payday is the last Friday of the following month.

DELIMITER //

CREATE FUNCTION GetFirstPayday(start_date DATE)
RETURNS DATE
DETERMINISTIC
NO SQL
BEGIN
  DECLARE end_of_month DATE;
  DECLARE final_friday DATE;

  SET end_of_month = LAST_DAY(start_date);
  SET final_friday = end_of_month - INTERVAL((WEEKDAY(end_of_month) + 3) % 7) DAY;

  IF start_date > final_friday THEN
    SET end_of_month = LAST_DAY(start_date + INTERVAL 1 MONTH);
    SET final_friday = end_of_month - INTERVAL((WEEKDAY(end_of_month) + 3) % 7) DAY;
  END IF;

  RETURN final_friday;
END//

DELIMITER ;

SELECT name, hire_date, GetFirstPayday(hire_date) AS First_Payday
FROM staff;

Module 5: Grouping and Filtering

1. List the department ID and average salary for each department.

SELECT l.dept_id, AVG(s.base_salary) AS avg_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
GROUP BY l.dept_id;

2. Group employees by department and then by job title within each department. Count the members and calculate the average salary for each subgroup.

SELECT l.dept_id, s.role, COUNT(*) AS headcount, AVG(s.base_salary) AS avg_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
GROUP BY l.dept_id, s.role;

3. Repeat the previous query, but display the department name instead of the ID.

SELECT l.dept_label AS department, s.role, AVG(s.base_salary) AS avg_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
GROUP BY l.dept_label, s.role;

4. List the average salary for job titles that have more than 2 employees.

SELECT role, AVG(base_salary) AS avg_salary
FROM staff
GROUP BY role
HAVING COUNT(*) > 2;

5. Find departments where the average commission is greater than 25% of the average salary.

SELECT l.dept_id
FROM location_data l
INNER JOIN staff s ON l.dept_id = s.dept_id
GROUP BY l.dept_id
HAVING AVG(s.commission_amount) > (AVG(s.base_salary) * 0.25);

6. Calculate the average salary for each department, excluding Managers and the President.

SELECT l.dept_id, AVG(s.base_salary) AS avg_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
WHERE s.role NOT IN ('MANAGER', 'PRESIDENT')
GROUP BY l.dept_id;

7. List Department IDs and Names where there is at least one Manager, two Clerks, and the average salary exceeds the company-wide average.

SELECT l.dept_id, l.dept_label
FROM location_data l
INNER JOIN staff s ON l.dept_id = s.dept_id
WHERE s.role = 'MANAGER'
GROUP BY l.dept_id, l.dept_label
HAVING COUNT(CASE WHEN s.role = 'CLERK' THEN s.emp_id END) >= 2 AND AVG(s.base_salary) > (SELECT AVG(base_salary) FROM staff);

8. Identify the manager who supervises the most employees.

SELECT s.name AS manager_name
FROM staff s
WHERE s.role = 'MANAGER'
GROUP BY s.emp_id, s.name
ORDER BY COUNT(*) DESC
LIMIT 1;

9. List all managers who supervise at least 2 employees.

SELECT s.name AS manager_name
FROM staff s
WHERE s.role = 'MANAGER'
GROUP BY s.emp_id, s.name
HAVING COUNT(*) >= 2;

Module 6: Advanced Subqueries

1. Find the name and role of employees who share the same job title as Jones.

SELECT s.name AS name, s.role AS role
FROM staff s
WHERE s.role = (
  SELECT role
  FROM staff
  WHERE name = 'JONES'
);

2. Identify employees in Department 10 who hold a job title that exists in Department 30.

SELECT s1.name AS name, s1.role AS role
FROM staff s1
WHERE s1.dept_id = 10
  AND EXISTS (
    SELECT 1
    FROM staff s2
    WHERE s2.dept_id = 30
      AND s1.role = s2.role
  );

3. List employees who have the same job as Jones or earn a salary equal to or greater than Ford. Include their department name.

SELECT s.name, s.role, l.dept_label
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
WHERE (s.role = (SELECT role FROM staff WHERE name = 'JONES') OR s.base_salary >= (SELECT base_salary FROM staff WHERE name = 'FORD'));

4. Find employees in Department 10 whose job title matches anyone in the Sales department.

SELECT s1.name AS name, s1.role AS role
FROM staff s1
INNER JOIN location_data l1 ON s1.dept_id = l1.dept_id
WHERE s1.dept_id = 10
  AND EXISTS (
    SELECT 1
    FROM staff s2
    INNER JOIN location_data l2 ON s2.dept_id = l2.dept_id
    WHERE l2.dept_label = 'Sales'
      AND s1.role = s2.role
  );

5. Find employees in Liverpool who have the same job as Allen. Sort alphabetically by name.

SELECT s.name, s.role
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
WHERE s.role = (
  SELECT role
  FROM staff
  WHERE name = 'ALLEN'
)
AND l.city = 'Liverpool'
ORDER BY s.name;

6. Identify employees earning more than the average salary of their specific department.

SELECT s.name, s.role, s.base_salary
FROM staff s
WHERE s.base_salary > (
  SELECT AVG(base_salary)
  FROM staff
  WHERE dept_id = s.dept_id
);

7. Find employees who earn more than JONES, using table aliases.

SELECT s.name, s.role, s.base_salary
FROM staff AS s
WHERE s.base_salary > (
  SELECT base_salary
  FROM staff AS j
  WHERE j.name = 'JONES'
);

8. List the names of employees with the highest and second-highest salaries.

SELECT name
FROM staff
ORDER BY base_salary DESC
LIMIT 2;

Module 7: Data Definition and Manipulation

1. Create a table named financial_records with columns LNO (medium int), EMPNO (int), TYPE (char), AMNT (decimal). Add Primary Key, Foreign Key, and Check constraints.

CREATE TABLE financial_records (
  record_id MEDIUMINT PRIMARY KEY,
  staff_ref INT NOT NULL,
  record_type CHAR(1) CHECK (record_type IN ('C', 'M', 'H')),
  amount DECIMAL(8,2) CHECK (amount > 0),
  FOREIGN KEY (staff_ref) REFERENCES staff(emp_id)
);

2. Insert the following data into the table.

INSERT INTO financial_records (record_id, staff_ref, record_type, amount)
VALUES (23, 7499, 'M', 20000.00),
       (42, 7499, 'C', 2000.00),
       (65, 7844, 'M', 3564.00);

3. Verify that 3 records were created.

SELECT * FROM financial_records;

4. Alter the table to add a column named OUTST (decimal).

ALTER TABLE financial_records ADD (balance_due DECIMAL(8,2));

5. Apply a 10% interest rate to all loans of type 'M'. Set balance to amount for others.

UPDATE financial_records
SET balance_due = amount * 1.1
WHERE record_type = 'M';

UPDATE financial_records
SET balance_due = amount
WHERE record_type != 'M';

6. Delete all records with an outstanding balance less than 3000.

DELETE FROM financial_records
WHERE balance_due < 3000;

7. Rename the table to accounts_ledger.

RENAME TABLE financial_records TO accounts_ledger;

8. Rename column LNO to LOAN_REF.

ALTER TABLE accounts_ledger CHANGE record_id loan_ref MEDIUMINT;

9. Create a view for Department 30 personnel showing name, number, job, and hire date.

CREATE VIEW v_dept30_personnel AS
SELECT s.name, s.emp_id, s.role, s.hire_date
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
WHERE l.dept_id = 30;

10. Use the view to show non-salesmen in Department 30.

SELECT *
FROM v_dept30_personnel
WHERE role <> 'SALESMAN';

11. Create a view summarizing information for each department.

CREATE VIEW v_dept_summary AS
SELECT l.dept_label,
       COUNT(*) AS employee_count,
       AVG(s.base_salary) AS avg_dept_salary
FROM staff s
INNER JOIN location_data l ON s.dept_id = l.dept_id
GROUP BY l.dept_label;