🧑‍💻 Oracle SQL Tutorial: Populate Employee and Project Data

🎯 Objective: Learn how to manage employee and project data in Oracle SQL using essential commands. This hands-on SQL lab uses Bangladeshi names and real-world scenarios.

📁 1. Table Structure

EMPLOYEE Table

CREATE TABLE EMPLOYEE (
  EMP_ID     NUMBER PRIMARY KEY,
  EMP_NAME   VARCHAR2(50),
  JOB_TITLE  VARCHAR2(50),
  SALARY     NUMBER,
  DEPT_ID    NUMBER
);

PROJECT Table

CREATE TABLE PROJECT (
  PROJ_ID     NUMBER PRIMARY KEY,
  PROJ_NAME   VARCHAR2(100),
  START_DATE  DATE,
  END_DATE    DATE
);

EMP_PROJ Table (Many-to-Many)

CREATE TABLE EMP_PROJ (
  EMP_ID      NUMBER,
  PROJ_ID     NUMBER,
  ASSIGN_DATE DATE,
  CONSTRAINT FK_EMP FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
  CONSTRAINT FK_PROJ FOREIGN KEY (PROJ_ID) REFERENCES PROJECT(PROJ_ID)
);

📝 2. Inserting Data

INSERT (Single Row)

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, JOB_TITLE, SALARY, DEPT_ID)
VALUES (104, 'Afsana Rahman', 'Tester', 50000, 30);

INSERT ALL (Multiple Rows)

INSERT ALL
  INTO EMPLOYEE VALUES (105, 'Tanvir Hossain', 'Developer', 58000, 20)
  INTO EMPLOYEE VALUES (106, 'Nasrin Akter', 'Support Engineer', 47000, 30)
  INTO EMPLOYEE VALUES (107, 'Mizanur Rahman', 'System Analyst', 62000, 20)
SELECT * FROM DUAL;

Insert into PROJECT Table

INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, START_DATE, END_DATE)
VALUES (1003, 'Automation in Public Sector', TO_DATE('2025-07-01', 'YYYY-MM-DD'), TO_DATE('2025-12-01', 'YYYY-MM-DD'));

✏️ 3. UPDATE Data

UPDATE EMPLOYEE
SET SALARY = SALARY + 5000
WHERE EMP_NAME = 'Tanvir Hossain';

🗑️ 4. DELETE Data

DELETE FROM EMPLOYEE
WHERE EMP_ID = 106;

🔄 5. MERGE (UPSERT)

MERGE INTO EMPLOYEE E
USING (
  SELECT 108 AS EMP_ID, 'Sharmin Sultana' AS EMP_NAME, 'UI Designer' AS JOB_TITLE, 57000 AS SALARY, 40 AS DEPT_ID FROM DUAL
) SRC
ON (E.EMP_ID = SRC.EMP_ID)
WHEN MATCHED THEN
  UPDATE SET E.SALARY = SRC.SALARY, E.JOB_TITLE = SRC.JOB_TITLE
WHEN NOT MATCHED THEN
  INSERT (EMP_ID, EMP_NAME, JOB_TITLE, SALARY, DEPT_ID)
  VALUES (SRC.EMP_ID, SRC.EMP_NAME, SRC.JOB_TITLE, SRC.SALARY, SRC.DEPT_ID);

🔍 6. Sample Queries

Employees with Assigned Projects

SELECT E.EMP_NAME, P.PROJ_NAME, EP.ASSIGN_DATE
FROM EMPLOYEE E
JOIN EMP_PROJ EP ON E.EMP_ID = EP.EMP_ID
JOIN PROJECT P ON EP.PROJ_ID = P.PROJ_ID;

Employees Without Any Project

SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_ID NOT IN (SELECT EMP_ID FROM EMP_PROJ);

Project-wise Employee Count

SELECT PROJ_ID, COUNT(*) AS EMP_COUNT
FROM EMP_PROJ
GROUP BY PROJ_ID;

💡 7. Practice Tasks

Insert 5 new Bangladeshi employees using INSERT ALL
Update salary and department of selected employees
Delete unassigned employees
Use MERGE to sync employee info from another table

📘 Keep practicing and explore joins, views, transactions, and constraints in SQL!

Trust Coding : Programming

🎯 Oracle SQL Lab: Insert, Update, Delete with BD Names