Lab 10 — Capstone: Full Database Analysis

A real analyst's workflow. No fill-in-the-blanks. Your manager has 6 business questions. You have SQL.

⏳ Loading SQL engine...

📋 ALL 5 TABLES: departments, employees, projects, assignments, salesdepartments(id, name, budget, location) employees(id, name, dept_id, salary, years, hire_date) projects(id, name, dept_id, budget, status) assignments(employee_id, project_id, hours_per_week, role) sales(id, employee_id, amount, region, month) Key relationships: employees.dept_id --> departments.id projects.dept_id --> departments.id assignments.employee_id --> employees.id assignments.project_id --> projects.id sales.employee_id --> employees.id Sales data: 3 salespeople (Bob-id2, Eve-id5, Leo-id12), months Jan/Feb/Mar, regions West/East

💼 Scenario: Your Manager's 6 Questions

It is Monday morning. Your manager messages you: "Before the board meeting at 2pm, I need answers to six questions about our company data. You have SQL and the full database. Go."

Below are the six questions as separate editors. There are no blanks to fill — write complete SQL from scratch. Starter comments guide you on which tables to use.

Each editor has a collapsible sample solution. Try your own first — then compare.

1 Headcount and Average Salary by Department

Show each department's name, location, number of employees, average salary, and total salary bill. Sort by total salary bill descending.

-- Q1: Headcount and average salary by department
-- Tables to use: departments, employees
-- GROUP BY department, sort by total salary bill DESC

🔍 View Sample Solution

SELECT d.name AS department,
       d.location,
       COUNT(e.id) AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary,
       SUM(e.salary) AS total_salary_bill
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name, d.location
ORDER BY total_salary_bill DESC;

2 Top 3 Highest-Paid Employees with Their Department Names

Show the name, department name, salary, and hire date of the three highest-paid employees company-wide.

-- Q2: Top 3 highest-paid employees with department name
-- Tables to use: employees, departments
-- ORDER BY salary DESC LIMIT 3

🔍 View Sample Solution

SELECT e.name,
       d.name AS department,
       e.salary,
       e.hire_date
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC
LIMIT 3;

3 Active Projects with Assigned Employee Count

For every Active project, show project name, department, budget, and how many employees are assigned. Include projects with zero assignments. Sort by employee count descending.

-- Q3: Active projects with employee count assigned to each
-- Tables to use: projects, departments, assignments
-- WHERE p.status = 'Active'
-- Use LEFT JOIN for assignments so zero-count projects still appear

🔍 View Sample Solution

SELECT p.name AS project,
       d.name AS department,
       p.budget,
       COUNT(a.employee_id) AS employees_assigned
FROM projects p
INNER JOIN departments d ON p.dept_id = d.id
LEFT JOIN assignments a ON p.id = a.project_id
WHERE p.status = 'Active'
GROUP BY p.id, p.name, d.name, p.budget
ORDER BY employees_assigned DESC;

4 Total Sales Revenue by Region and by Month

Run two queries in this editor: first grouped by region, then grouped by month. Show transaction count and total revenue in each.

-- Q4a: Total revenue by REGION
-- Table to use: sales
-- GROUP BY region

-- Q4b: Total revenue by MONTH
-- Table to use: sales
-- GROUP BY month

🔍 View Sample Solution

-- By region
SELECT region,
       COUNT(*) AS transactions,
       ROUND(SUM(amount), 2) AS total_revenue,
       ROUND(AVG(amount), 2) AS avg_per_sale
FROM sales
GROUP BY region
ORDER BY total_revenue DESC;

-- By month
SELECT month,
       COUNT(*) AS transactions,
       ROUND(SUM(amount), 2) AS total_revenue
FROM sales
GROUP BY month
ORDER BY month;

5 Top Sales Performers — Are They in Marketing?

Show each salesperson's name, department, region, number of transactions, and total sales. Sort by total sales descending. The board wants to know if top performers come from Marketing.

-- Q5: Top sales performers with department names
-- Tables to use: sales, employees, departments
-- JOIN all three, GROUP BY employee, ORDER BY total DESC

🔍 View Sample Solution

SELECT e.name AS salesperson,
       d.name AS department,
       s.region,
       COUNT(s.id) AS transactions,
       ROUND(SUM(s.amount), 2) AS total_sales,
       ROUND(AVG(s.amount), 2) AS avg_sale
FROM sales s
INNER JOIN employees e ON s.employee_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
GROUP BY s.employee_id, e.name, d.name, s.region
ORDER BY total_sales DESC;

6 Department with the Highest Total Salary Bill

Return a single row: the department name, location, headcount, total salary bill, and what percentage of the company's total payroll that represents.

-- Q6: Department with highest total salary bill
-- Tables to use: departments, employees
-- Use a subquery for the company-wide total to compute pct_of_company
-- ORDER BY total DESC LIMIT 1

🔍 View Sample Solution

SELECT d.name AS department,
       d.location,
       COUNT(e.id) AS headcount,
       SUM(e.salary) AS dept_total,
       ROUND(SUM(e.salary) * 100.0 /
           (SELECT SUM(salary) FROM employees), 1) AS pct_of_company
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name, d.location
ORDER BY dept_total DESC
LIMIT 1;

✨ Bonus Challenge

Bonus — The 5-Table JOIN

Join all 5 tables in a single query. Show each employee's name, department name, project name, assignment role, hours per week, and their total sales amount. Employees without sales or assignments should still appear (use LEFT JOINs). Sort by total sales descending, then employee name.

💡 Hint: Start FROM employees, LEFT JOIN departments, LEFT JOIN assignments, LEFT JOIN projects ON assignment's project_id, LEFT JOIN sales. Use COALESCE to show 0 for employees with no sales.

-- Bonus: JOIN all 5 tables
-- Show: employee name, dept name, project name, role, hours/week, total sales
-- Employees without assignments or sales still appear (LEFT JOINs)

SELECT e.name AS employee,
       d.name AS department,
       p.name AS project,
       a.role,
       a.hours_per_week,
       COALESCE(ROUND(SUM(s.amount), 2), 0) AS total_sales
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
LEFT JOIN assignments a ON e.id = a.employee_id
LEFT JOIN projects p ON a.project_id = p.id
LEFT JOIN sales s ON e.id = s.employee_id
GROUP BY e.id, e.name, d.name, p.name, a.role, a.hours_per_week
ORDER BY total_sales DESC, e.name;

🏉 You Have Completed All 10 SQL Practice Labs!

From your very first SELECT to joining five tables and writing correlated subqueries — you now have the SQL toolkit that professional data analysts use every day.

Skills you have now:

Query any database • Filter, sort, and aggregate • Join multiple tables • Write subqueries • Manipulate strings and dates • Create and modify tables • Answer real business questions

Data Analyst Course → Back to All Labs