Database Program

DDL COMMANDS (Create Structure)

Step 1: Create Department Table

CREATE TABLE Department (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(50) NOT NULL UNIQUE,

    location VARCHAR(50) DEFAULT 'Head Office'

);

Step 2: 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)

);

Step 3: 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)

);

Step 4: Create Index (Performance Optimization)

CREATE INDEX idx_employee_name

ON Employee(emp_name);

PART 2 — DML COMMANDS (Insert & Manipulate Data)

Step 5: Insert into Department

INSERT INTO Department (dept_id, dept_name, location) VALUES

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

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

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

Using DEFAULT value example:

INSERT INTO Department (dept_id, dept_name)

VALUES (4, 'Admin');

Step 6: 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);

join_date auto-filled by DEFAULT.

 Step 7: 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);

PART 3 — Other DML Operations

Update Data

UPDATE Employee

SET salary = 65000

WHERE emp_id = 101;

Delete Data

DELETE FROM Appraisal

WHERE appraisal_id = 3;

Select Data

SELECT * FROM Department;

SELECT * FROM Employee;

SELECT * FROM Appraisal;

PART 4 — INVALID DATA Example

INSERT INTO Appraisal

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

❌ Errors because:

• Year < 2020

• Score > 10

NOTE

DDL → Defines structure (CREATE, ALTER, DROP)
DML → Manipulates data (INSERT, UPDATE, DELETE, SELECT)

1. CREATE TABLE

Used to create a new table.

CREATE TABLE Department (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(50),

    location VARCHAR(50)

);

 2. DESCRIBE TABLE (View Structure)

DESC Department;

OR

DESCRIBE Department;

Shows columns, datatype, constraints.

3. ALTER TABLE

Used to modify existing table.

➤ Add Column

ALTER TABLE Department

ADD contact_no VARCHAR(15);

➤ Drop Column

ALTER TABLE Department

DROP contact_no;

➤ Modify Column

ALTER TABLE Department

MODIFY dept_name VARCHAR(100);

➤ Rename Column

ALTER TABLE Department

RENAME COLUMN dept_name TO dname;

4. RENAME TABLE

RENAME TABLE Department TO Dept;

 5. TRUNCATE TABLE

Deletes all rows but keeps structure.

TRUNCATE TABLE Dept;

👉 Faster than DELETE
👉 Cannot be rolled back in many DBs

6. DROP TABLE

Deletes table permanently.

DROP TABLE Dept;

👉 Structure + data both removed.

7. COPY TABLE

➤ Copy structure only

CREATE TABLE Dept_Copy

AS SELECT * FROM Department WHERE 1=0;

➤ Copy structure + data

CREATE TABLE Dept_Copy

AS SELECT * FROM Department;

8. COMMENT ON TABLE

COMMENT ON TABLE Department IS 'Stores department details';

 9. CREATE INDEX (Table performance)

CREATE INDEX idx_deptname

ON Department(dept_name);

🔹 10. DROP INDEX

DROP INDEX idx_deptname;

Summary

Command

Purpose

CREATE

Create table

ALTER

Modify table

DROP

Delete table

TRUNCATE

Delete all data

RENAME

Rename table

DESC

View structure

✅ TABLE DATA OPERATIONS (DML COMMANDS)

🔹 1. INSERT (Add Data)

➤ Insert Single Row

INSERT INTO Employee

VALUES (101, 'Amit', 'Manager', 60000);

➤ Insert with Column Names

INSERT INTO Employee (emp_id, emp_name, designation, salary)

VALUES (102, 'Neha', 'Developer', 50000);

➤ Insert Multiple Rows

INSERT INTO Employee

VALUES

(103, 'Rahul', 'Analyst', 45000),

(104, 'Sneha', 'HR', 40000);

🔹 2. SELECT (Retrieve Data)

➤ View All Data

SELECT * FROM Employee;

➤ View Specific Columns

SELECT emp_name, salary FROM Employee;

➤ With Condition

SELECT * FROM Employee

WHERE salary > 50000;

➤ Using AND/OR

SELECT * FROM Employee

WHERE salary > 40000 AND designation='Manager';

➤ Using LIKE

SELECT * FROM Employee

WHERE emp_name LIKE 'A%';

➤ Using BETWEEN

SELECT * FROM Employee

WHERE salary BETWEEN 40000 AND 60000;

➤ Sorting Data

SELECT * FROM Employee

ORDER BY salary DESC;

🔹 3. UPDATE (Modify Data)

➤ Update One Record

UPDATE Employee

SET salary = 65000

WHERE emp_id = 101;

➤ Update Multiple Columns

UPDATE Employee

SET designation='Senior Manager',

    salary=70000

WHERE emp_id=101;

⚠️ Without WHERE updates all rows:

UPDATE Employee

SET salary = 30000;

🔹 4. DELETE (Remove Data)

➤ Delete Specific Row

DELETE FROM Employee

WHERE emp_id = 104;

➤ Delete All Rows

DELETE FROM Employee;

👉 Table remains, data removed.

🔹 5. TRUNCATE (Fast Delete)

TRUNCATE TABLE Employee;

👉 Removes all rows
👉 Faster than DELETE
👉 Cannot rollback in many DBs

🔹 6. MERGE (Insert + Update)

MERGE INTO Employee E

USING NewEmployee N

ON (E.emp_id = N.emp_id)

WHEN MATCHED THEN

UPDATE SET E.salary = N.salary

WHEN NOT MATCHED THEN

INSERT VALUES(N.emp_id, N.emp_name, N.designation, N.salary);

🔹 7. Aggregate Functions

➤ Count

SELECT COUNT(*) FROM Employee;

➤ Sum

SELECT SUM(salary) FROM Employee;

➤ Average

SELECT AVG(salary) FROM Employee;

➤ Maximum & Minimum

SELECT MAX(salary), MIN(salary)

FROM Employee;

🔹 8. GROUP BY

SELECT designation, AVG(salary)

FROM Employee

GROUP BY designation;

🔹 9. HAVING

SELECT designation, AVG(salary)

FROM Employee

GROUP BY designation

HAVING AVG(salary) > 50000;

🔹 10. LIMIT

SELECT * FROM Employee

LIMIT 5;

🎯 Viva Quick Difference

Command

Use

INSERT

Add data

SELECT

Retrieve data

UPDATE

Modify data

DELETE

Remove data

TRUNCATE

Remove all data

✅ 1. DATABASE OPERATIONS

🔹 Create Database

CREATE DATABASE company_db;

🔹 Use Database

USE company_db;

🔹 Show Databases

SHOW DATABASES;

🔹 Drop Database

DROP DATABASE company_db;

✅ 2. VIEW COMMANDS

🔹 Create View

CREATE VIEW emp_view AS

SELECT emp_name, salary

FROM Employee;

🔹 View Data

SELECT * FROM emp_view;

🔹 Drop View

DROP VIEW emp_view;

✅ 3. INDEX COMMANDS

🔹 Create Index

CREATE INDEX idx_name

ON Employee(emp_name);

🔹 Show Index

SHOW INDEX FROM Employee;

🔹 Drop Index

DROP INDEX idx_name ON Employee;

✅ 4. CONSTRAINT COMMANDS

🔹 Add Constraint

ALTER TABLE Employee

ADD CONSTRAINT chk_salary

CHECK (salary > 0);

🔹 Drop Constraint

ALTER TABLE Employee

DROP CONSTRAINT chk_salary;

✅ 5. TRANSACTION CONTROL (TCL)

🔹 Start Transaction

START TRANSACTION;

🔹 Commit Changes

COMMIT;

🔹 Rollback Changes

ROLLBACK;

🔹 Savepoint

SAVEPOINT sp1;

✅ 6. USER & PERMISSION COMMANDS (DCL)

🔹 Grant Permission

GRANT SELECT, INSERT

ON Employee

TO user1;

🔹 Revoke Permission

REVOKE INSERT

ON Employee

FROM user1;

✅ 7. STORED PROCEDURES

🔹 Create Procedure

CREATE PROCEDURE GetEmployees()

BEGIN

   SELECT * FROM Employee;

END;

🔹 Call Procedure

CALL GetEmployees();

🔹 Drop Procedure

DROP PROCEDURE GetEmployees;

✅ 8. FUNCTIONS

🔹 Create Function

CREATE FUNCTION bonus(salary DECIMAL(10,2))

RETURNS DECIMAL(10,2)

RETURN salary * 0.10;

🔹 Use Function

SELECT bonus(50000);

✅ 9. TRIGGERS

🔹 Create Trigger

CREATE TRIGGER before_insert_emp

BEFORE INSERT ON Employee

FOR EACH ROW

SET NEW.salary = NEW.salary + 1000;

🔹 Drop Trigger

DROP TRIGGER before_insert_emp;

🎯Summary

Category

Commands

Database

CREATE, DROP, USE

Views

CREATE VIEW, DROP VIEW

Index

CREATE INDEX

TCL

COMMIT, ROLLBACK

DCL

GRANT, REVOKE

Procedures

CREATE/CALL

Triggers

CREATE TRIGGER