Design and Query an Employee-Project Assignment Schema
Create a normalized database schema to manage employees, projects, and their assignments. Write SQL queries to retrieve project involvement and employee workloads.
Challenge prompt
You are tasked with designing a simple relational schema to represent employees, projects, and the assignments of employees to projects. Your mini project consists of two parts: 1. Design a normalized schema with three tables: Employees, Projects, and Assignments. The Employees table should store employee ID and name. The Projects table should store project ID and project name. The Assignments table should link employees to projects and include the number of hours an employee is assigned to that project. 2. Write a SQL query to list all employees along with the total number of hours assigned across all their projects. Include employees with zero assignments. 3. Write a SQL query to show each project and the count of employees assigned to it. Assume employee_id and project_id are integers and primary keys for their respective tables.
Guidance
- • Create tables with appropriate primary and foreign keys to maintain data integrity.
- • Use aggregate functions with proper GROUP BY clauses to calculate total hours and count employees.
- • Use LEFT JOIN where necessary to include employees or projects without assignments.
Hints
- • The Assignments table needs foreign keys referencing Employees and Projects.
- • To include employees with no assignments, consider using LEFT JOIN from Employees to Assignments.
- • Use COALESCE to replace NULLs in total hours with zero.
Starter code
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100)
);
CREATE TABLE Projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100)
);
CREATE TABLE Assignments (
assignment_id INT PRIMARY KEY,
employee_id INT,
project_id INT,
hours_assigned INT,
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);
-- Query 1: Total hours per employee
-- Query 2: Employee count per project
Expected output
Query 1 result example: employee_id | employee_name | total_hours 1 | Alice | 35 2 | Bob | 0 3 | Charlie | 20 Query 2 result example: project_id | project_name | employee_count 101 | Project X | 2 102 | Project Y | 1 103 | Project Z | 0
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.