Skip to content

Employee & Project Management System (SQL Lab) 🚀

Mentor's Note: This is a "Grand Finale" lab. It combines everything you've learned—DDL, DML, Joins, and Subqueries—into one realistic project. Master these 23 queries, and you will be ready for any RDBMS exam! 💡

🌟 The Scenario: The Tech Startup 🏢

Imagine you are the Database Administrator for a new tech startup in Surat.

  • The Data: You have three main sets of information:
    1. Departments 🏢 (IT, HR, Sales).
    2. Employees 👥 (The people working there).
    3. Projects 💻 (The work being done).
  • The Challenge: The CEO wants complex reports like "Who is working on what?" or "Which department has the highest budget?"
  • The Result: You use SQL to link these tables and provide instant answers. ✅

🎨 Visual Logic: The Database Schema

erDiagram
    DEPARTMENT ||--o{ EMPLOYEE : "has"
    DEPARTMENT ||--o{ PROJECT : "manages"

    DEPARTMENT {
        number dept_id PK
        string dept_name
        string location
    }
    EMPLOYEE {
        number emp_id PK
        string emp_name
        number salary
        number dept_id FK
    }
    PROJECT {
        number project_id PK
        string project_name
        number dept_id FK
    }

💻 Step 1: Building the Structure (DDL)

-- 🏗️ Creating the Tables
CREATE TABLE DEPARTMENT (
    dept_id NUMBER(5) PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL,
    location VARCHAR2(50)
);

CREATE TABLE EMPLOYEE (
    emp_id NUMBER(5) PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    salary NUMBER(10, 2),
    dept_id NUMBER(5) REFERENCES DEPARTMENT(dept_id)
);

CREATE TABLE PROJECT (
    project_id NUMBER(5) PRIMARY KEY,
    project_name VARCHAR2(50) NOT NULL,
    dept_id NUMBER(5) REFERENCES DEPARTMENT(dept_id)
);

💻 Step 2: Adding the Team (DML)

-- 📥 Stocking the tables with sample data
INSERT INTO DEPARTMENT VALUES (101, 'IT', 'Bangalore');
INSERT INTO DEPARTMENT VALUES (102, 'HR', 'Mumbai');
INSERT INTO DEPARTMENT VALUES (103, 'Finance', 'Surat');
INSERT INTO DEPARTMENT VALUES (104, 'Admin', 'Surat');
INSERT INTO DEPARTMENT VALUES (105, 'Sales', 'Delhi');

INSERT INTO EMPLOYEE VALUES (1, 'Arjun', 55000, 101);
INSERT INTO EMPLOYEE VALUES (2, 'Priya', 62000, 101);
INSERT INTO EMPLOYEE VALUES (3, 'Rahul', 28000, 102);
INSERT INTO EMPLOYEE VALUES (4, 'Sneha', 35000, 103);
INSERT INTO EMPLOYEE VALUES (5, 'Vikram', 45000, NULL);

INSERT INTO PROJECT VALUES (501, 'Website Redesign', 101);
INSERT INTO PROJECT VALUES (502, 'Employee Portal', 102);
INSERT INTO PROJECT VALUES (503, 'Tax Filing', 103);
INSERT INTO PROJECT VALUES (504, 'Office Reno', 104);
INSERT INTO PROJECT VALUES (505, 'Legacy Migration', 101);

💻 Step 3: Query Solutions (The 23 Challenges) 🧩

🔹 Basic Joins & Display

  1. Display employee name and department name. 
    SELECT E.emp_name, D.dept_name FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id;
  2. List employees working in the "IT" department. 
    SELECT E.emp_name FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id WHERE D.dept_name = 'IT';
  3. Display employee details along with department location. 
    SELECT E.*, D.location FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id;
  4. Show project name with corresponding department name. 
    SELECT P.project_name, D.dept_name FROM PROJECT P JOIN DEPARTMENT D ON P.dept_id = D.dept_id;

🔹 Outer Joins (Handling NULLs)

  1. Display all departments including those with no employees. 
    SELECT D.dept_name, E.emp_name FROM DEPARTMENT D LEFT JOIN EMPLOYEE E ON D.dept_id = E.dept_id;
  2. Show all employees even if they are not assigned to any department. 
    SELECT E.emp_name, D.dept_name FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.dept_id = D.dept_id;
  3. Display all projects including departments with no projects. 
    SELECT D.dept_name, P.project_name FROM DEPARTMENT D LEFT JOIN PROJECT P ON D.dept_id = P.dept_id;
  4. Display all employees and departments, even if no matching records exist. 
    SELECT E.emp_name, D.dept_name FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.dept_id = D.dept_id;

🔹 Filtering & Sorting

  1. Display employees earning more than ₹30,000 sorted by salary. 
    SELECT * FROM EMPLOYEE WHERE salary > 30000 ORDER BY salary ASC;
  2. Display employee details who belong to departments located in ‘Surat’. 
    SELECT E.* FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id WHERE D.location = 'Surat';
  3. Display department-wise employee list ordered by employee name. 
    SELECT D.dept_name, E.emp_name FROM DEPARTMENT D JOIN EMPLOYEE E ON D.dept_id = E.dept_id ORDER BY D.dept_name, E.emp_name;
  4. Display employees who are not earning the maximum salary in their department. 
    SELECT E.emp_name, E.salary, E.dept_id FROM EMPLOYEE E WHERE E.salary < (SELECT MAX(salary) FROM EMPLOYEE WHERE dept_id = E.dept_id);

🔹 Aggregates (Count, Sum, Max)

  1. Display department-wise total salary. 
    SELECT D.dept_name, SUM(E.salary) AS Total_Salary FROM DEPARTMENT D JOIN EMPLOYEE E ON D.dept_id = E.dept_id GROUP BY D.dept_name;
  2. Display number of employees in each department. 
    SELECT D.dept_name, COUNT(E.emp_id) AS Emp_Count FROM DEPARTMENT D LEFT JOIN EMPLOYEE E ON D.dept_id = E.dept_id GROUP BY D.dept_name;
  3. Display department name and total number of employees. 
    SELECT D.dept_name, COUNT(E.emp_id) FROM DEPARTMENT D JOIN EMPLOYEE E ON D.dept_id = E.dept_id GROUP BY D.dept_name;
  4. Display department name and project count for each department. 
    SELECT D.dept_name, COUNT(P.project_id) AS Project_Count FROM DEPARTMENT D LEFT JOIN PROJECT P ON D.dept_id = P.dept_id GROUP BY D.dept_name;

🔹 Complex Joins & Logic

  1. Display employee name, department name, and department location. 
    SELECT E.emp_name, D.dept_name, D.location FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id;
  2. Display project name along with employee names working in the same department. 
    SELECT P.project_name, E.emp_name FROM PROJECT P JOIN EMPLOYEE E ON P.dept_id = E.dept_id;
  3. Display employee name and project name where employee department matches project department. 
    SELECT E.emp_name, P.project_name FROM EMPLOYEE E JOIN PROJECT P ON E.dept_id = P.dept_id;
  4. Display employees working in departments that have projects assigned. 
    SELECT DISTINCT E.emp_name FROM EMPLOYEE E JOIN PROJECT P ON E.dept_id = P.dept_id;
  5. Display employees whose department ID exists in both DEPARTMENT and PROJECT tables. 
    SELECT * FROM EMPLOYEE WHERE dept_id IN (SELECT dept_id FROM PROJECT);
  6. Display project details for departments that have more than one employee. 
    SELECT P.* FROM PROJECT P WHERE P.dept_id IN (SELECT dept_id FROM EMPLOYEE GROUP BY dept_id HAVING COUNT(emp_id) > 1);
  7. Display employee name, salary, and department name for employees earning more than the average salary. 
    SELECT E.emp_name, E.salary, D.dept_name FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id WHERE E.salary > (SELECT AVG(salary) FROM EMPLOYEE);

📊 Sample Dry Run (Query 23)

Avg Salary = 48,000

Employee Salary Salary > 48k? Result
Arjun 55,000 ✅ Yes SHOW 🌟
Priya 62,000 ✅ Yes SHOW 🌟
Rahul 28,000 ❌ No HIDE 🗑️

💡 Interview Tip & Board Focus 👔

"Interviewers love this specific schema. Be prepared to explain Foreign Key constraints and what happens if you try to delete a Department that still has Employees. (Answer: It fails unless you use ON DELETE CASCADE)!"

💡 Pro Tip: "One step at a time is enough for me." - Mahatma Gandhi

📈 Learning Path