1️⃣ DDL Commands (Create Tables)

Create Department Table

CREATE TABLE Department (

   dept_id INT PRIMARY KEY,

   dept_name VARCHAR(50) NOT NULL UNIQUE,

   location VARCHAR(50) DEFAULT 'Head Office'

);

Create Employee Table

CREATE TABLE Employee (

   emp_id INT PRIMARY KEY,

   emp_name VARCHAR(50) NOT NULL,

   designation VARCHAR(50) NOT NULL,

   salary DECIMAL(10,2) CHECK (salary > 0),

   gender CHAR(1) CHECK (gender IN ('M','F')),

   join_date DATE DEFAULT CURRENT_DATE,

   dept_id INT,

   FOREIGN KEY (dept_id) REFERENCES Department(dept_id)

);

Create Appraisal Table

CREATE TABLE Appraisal (

   appraisal_id INT PRIMARY KEY,

   emp_id INT NOT NULL,

   appraisal_year INT CHECK (appraisal_year BETWEEN 2020 AND 2030),

   performance_score INT CHECK (performance_score BETWEEN 1 AND 10),

   rating VARCHAR(20) DEFAULT 'Pending',

   FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)

);

Create Index

CREATE INDEX idx_employee_name

ON Employee(emp_name);

2️⃣ Insert Data (DML)

Insert into Department

INSERT INTO Department (dept_id, dept_name, location) VALUES

(1, 'HR', 'Mumbai'),

(2, 'IT', 'Pune'),

(3, 'Finance', 'Delhi');

Using DEFAULT Value

INSERT INTO Department (dept_id, dept_name)

VALUES (4, 'Admin');

Insert into Employee

INSERT INTO Employee

(emp_id, emp_name, designation, salary, gender, dept_id)

VALUES

(101, 'Amit Sharma', 'Manager', 60000, 'M', 1),

(102, 'Neha Verma', 'Developer', 50000, 'F', 2),

(103, 'Rahul Patil', 'Analyst', 45000, 'M', 3);

Insert into Appraisal

INSERT INTO Appraisal

(appraisal_id, emp_id, appraisal_year, performance_score, rating)

VALUES

(1, 101, 2024, 9, 'Excellent'),

(2, 102, 2024, 8, 'Very Good'),

(3, 103, 2024, 6, 'Good');

Using DEFAULT rating

INSERT INTO Appraisal

(appraisal_id, emp_id, appraisal_year, performance_score)

VALUES

(4, 101, 2025, 8);

3️⃣ Other DML Operations

Update Data

UPDATE Employee

SET salary = 65000

WHERE emp_id = 101;

Delete Data

DELETE FROM Appraisal

WHERE appraisal_id = 3;

4️⃣ Simple Select Queries

SELECT * FROM Department;

SELECT * FROM Employee;

SELECT * FROM Appraisal;

5️⃣ Primary Key Based Queries

Find Department using Primary Key

SELECT *

FROM Department

WHERE dept_id = 1;

Find Employee using Primary Key

SELECT *

FROM Employee

WHERE emp_id = 101;

Find Appraisal using Primary Key

SELECT *

FROM Appraisal

WHERE appraisal_id = 1;

6️⃣ Foreign Key Based Queries

Employees in a Specific Department

SELECT *

FROM Employee

WHERE dept_id = 2;

Appraisal of Specific Employee

SELECT *

FROM Appraisal

WHERE emp_id = 101;

7️⃣ Join Queries (PK–FK Relationship)

Employee with Department Name

SELECT E.emp_id, E.emp_name, D.dept_name

FROM Employee E

JOIN Department D

ON E.dept_id = D.dept_id;

Appraisal with Employee Name

SELECT A.appraisal_id, E.emp_name, A.performance_score, A.rating

FROM Appraisal A

JOIN Employee E

ON A.emp_id = E.emp_id;

8️⃣ Join of Three Tables

SELECT

E.emp_name,

D.dept_name,

A.appraisal_year,

A.performance_score,

A.rating

FROM Employee E

JOIN Department D

ON E.dept_id = D.dept_id

JOIN Appraisal A

ON E.emp_id = A.emp_id;

9️⃣ Aggregate Queries (Common in Practical Exams)

Count Employees

SELECT COUNT(*)

FROM Employee;

Average Salary

SELECT AVG(salary)

FROM Employee;

Highest Salary

SELECT MAX(salary)

FROM Employee;

Department-wise Employee Count

SELECT dept_id, COUNT(*)

FROM Employee

GROUP BY dept_id;

🔟 Invalid Data Example

INSERT INTO Appraisal

VALUES (5, 101, 2018, 15, 'Excellent');

❌ Error because

• Year < 2020
  
  

• Score > 10