SQL for Sample Problem

ProjectIDEmployeeID
P01E01
P01E02
P02E03

Step 1: Create the Tables

1. Projects Table

CREATE TABLE Projects (
    ProjectID CHAR(3) PRIMARY KEY,
    ProjectName VARCHAR(50) NOT NULL
);

2. Employees Table

CREATE TABLE Employees (
    EmployeeID CHAR(3) PRIMARY KEY,
    EmployeeName VARCHAR(50) NOT NULL,
    DepartmentID CHAR(3),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

3. Departments Table

CREATE TABLE Departments (
    DepartmentID CHAR(3) PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL
);

4. Assignment Table

CREATE TABLE Assignment (
    ProjectID CHAR(3),
    EmployeeID CHAR(3),
    PRIMARY KEY (ProjectID, EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Step 2: Insert Sample Data

Insert Data into Departments Table

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES 
    ('D01', 'HR'),
    ('D02', 'Finance');

Insert Data into Employees Table

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES
    ('E01', 'Alice', 'D01'),
    ('E02', 'Bob', 'D01'),
    ('E03', 'Charlie', 'D02');

Insert Data into Projects Table

INSERT INTO Projects (ProjectID, ProjectName)
VALUES
    ('P01', 'Alpha'),
    ('P02', 'Beta');

Insert Data into Assignment Table

INSERT INTO Assignment (ProjectID, EmployeeID)
VALUES
    ('P01', 'E01'),
    ('P01', 'E02'),
    ('P02', 'E03');

Step 3: Sample Queries

1. Retrieve All Projects and the Employees Assigned to Them

SELECT 
    P.ProjectName,
    E.EmployeeName
FROM 
    Assignment A
JOIN 
    Projects P ON A.ProjectID = P.ProjectID
JOIN 
    Employees E ON A.EmployeeID = E.EmployeeID;

Result:

ProjectNameEmployeeName
AlphaAlice
AlphaBob
BetaCharlie

2. Find All Employees and Their Departments

SELECT 
    E.EmployeeName,
    D.DepartmentName
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID;

Result:

EmployeeNameDepartmentName
AliceHR
BobHR
CharlieFinance

3. Retrieve All Employees Working on the “Alpha” Project

SELECT 
    E.EmployeeName
FROM 
    Assignment A
JOIN 
    Projects P ON A.ProjectID = P.ProjectID
JOIN 
    Employees E ON A.EmployeeID = E.EmployeeID
WHERE 
    P.ProjectName = 'Alpha';

Result:

EmployeeName
Alice
Bob

4. List All Departments and the Employees in Them

SELECT 
    D.DepartmentName,
    E.EmployeeName
FROM 
    Departments D
LEFT JOIN 
    Employees E ON D.DepartmentID = E.DepartmentID;

Result:

DepartmentNameEmployeeName
HRAlice
HRBob
FinanceCharlie

5. Retrieve the Names of All Projects Along with Their Assigned Departments

SELECT 
    P.ProjectName,
    D.DepartmentName
FROM 
    Assignment A
JOIN 
    Projects P ON A.ProjectID = P.ProjectID
JOIN 
    Employees E ON A.EmployeeID = E.EmployeeID
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID;

Result:

ProjectNameDepartmentName
AlphaHR
AlphaHR
BetaFinance

Step 4: Why This Structure Works Well

  1. Data Integrity: Foreign key constraints ensure consistent relationships.
  2. Flexibility: Adding a new department, employee, or project is simple and doesn’t cause duplication.
  3. Query Simplicity: Data relationships are clearly defined, making queries straightforward.
  4. Scalability: The design handles growth (e.g., adding more projects, departments, or employees) efficiently.

6. Advanced Queries

6.1 Retrieve All Employees and the Projects They Are Assigned To

This query shows which employees are working on which projects, along with their department names.

SELECT 
    E.EmployeeName,
    D.DepartmentName,
    P.ProjectName
FROM 
    Assignment A
JOIN 
    Employees E ON A.EmployeeID = E.EmployeeID
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
JOIN 
    Projects P ON A.ProjectID = P.ProjectID;

Result:

EmployeeNameDepartmentNameProjectName
AliceHRAlpha
BobHRAlpha
CharlieFinanceBeta

6.2 Find All Projects Without Assigned Employees

This query identifies projects that currently have no employees assigned.

SELECT 
    P.ProjectName
FROM 
    Projects P
LEFT JOIN 
    Assignment A ON P.ProjectID = A.ProjectID
WHERE 
    A.EmployeeID IS NULL;

Result: (If no employees are assigned to a project)

ProjectName
(none)

6.3 Count the Number of Employees in Each Department

This query counts how many employees belong to each department.

SELECT 
    D.DepartmentName,
    COUNT(E.EmployeeID) AS EmployeeCount
FROM 
    Departments D
LEFT JOIN 
    Employees E ON D.DepartmentID = E.DepartmentID
GROUP BY 
    D.DepartmentName;

Result:

DepartmentNameEmployeeCount
HR2
Finance1

6.4 List Projects Along with the Number of Assigned Employees

This query shows how many employees are assigned to each project.

SELECT 
    P.ProjectName,
    COUNT(A.EmployeeID) AS AssignedEmployees
FROM 
    Projects P
LEFT JOIN 
    Assignment A ON P.ProjectID = A.ProjectID
GROUP BY 
    P.ProjectName;

Result:

ProjectNameAssignedEmployees
Alpha2
Beta1

6.5 Find Departments Without Employees

This query identifies departments that have no employees assigned.

SELECT 
    D.DepartmentName
FROM 
    Departments D
LEFT JOIN 
    Employees E ON D.DepartmentID = E.DepartmentID
WHERE 
    E.EmployeeID IS NULL;

Result: (If there are departments without employees)

DepartmentName
(none)

6.6 Find Employees Who Work on More Than One Project

This query identifies employees who are assigned to multiple projects.

SELECT 
    E.EmployeeName,
    COUNT(A.ProjectID) AS ProjectCount
FROM 
    Employees E
JOIN 
    Assignment A ON E.EmployeeID = A.EmployeeID
GROUP BY 
    E.EmployeeID, E.EmployeeName
HAVING 
    COUNT(A.ProjectID) > 1;

Result:

EmployeeNameProjectCount
(none)(if no employee works on more than one project)

6.7 Retrieve Projects and Departments Where Employees Belong

This query maps projects to the departments of the employees assigned to those projects.

SELECT 
    P.ProjectName,
    D.DepartmentName
FROM 
    Assignment A
JOIN 
    Projects P ON A.ProjectID = P.ProjectID
JOIN 
    Employees E ON A.EmployeeID = E.EmployeeID
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
GROUP BY 
    P.ProjectName, D.DepartmentName;

Result:

ProjectNameDepartmentName
AlphaHR
BetaFinance

7. Explanation of Key SQL Features

7.1 Aggregations with GROUP BY

  • Use GROUP BY to group data based on one or more columns and apply aggregate functions (like COUNT, SUM, AVG, etc.).
  • Example: Counting employees in each department (COUNT with GROUP BY).

7.2 Joins

  • INNER JOIN: Combines rows from two tables where there is a match in both.
  • LEFT JOIN: Includes all rows from the left table and matches from the right table. If no match is found, NULL is returned for the right table.
  • Use LEFT JOIN for queries like “Find projects without assigned employees.”

7.3 Filtering with HAVING

  • HAVING is used to filter groups after aggregation (e.g., employees working on multiple projects).

8. Problems to Solve

  1. Query Practice
    • Write a query to list all employees and the number of projects they are assigned to.
    • Find employees who belong to the “Finance” department and are working on projects.
    • List departments that have employees assigned to projects.
  2. Design Problem
    • Add a new table for Client with attributes like ClientID, ClientName, and ContactInfo. Link Clients to Projects and write queries to:
      • Find all projects for a specific client.
      • List clients and the number of projects they are managing.
Scroll to Top