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

---

💻 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)

5. 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;

6. 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;

7. 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;

8. 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

9. Display employees earning more than ₹30,000 sorted by salary.

SELECT * FROM EMPLOYEE WHERE salary > 30000 ORDER BY salary ASC;

10. 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';

11. 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;

12. 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)

13. 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;

14. 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;

15. 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;

16. 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

17. 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;

18. 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;

19. 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;

20. 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;

21. Display employees whose department ID exists in both DEPARTMENT and PROJECT tables.

SELECT * FROM EMPLOYEE WHERE dept_id IN (SELECT dept_id FROM PROJECT);

22. 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);

23. 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

• ← Back: Product Update
• Next: Advanced SQL Tutorial →