SQL — Complete Interview Notes

DDL

DDL commands define the structure of the database. CREATE builds it, ALTER modifies it, DROP deletes it entirely, TRUNCATE empties it but keeps the structure, and RENAME changes its name. Unlike DML commands, DDL commands are auto-commit, meaning they cannot be rolled back Here is your Ultra-Short DDL Query Guide – only queries and one important point per command. No theory.

1. CREATE

CREATE TABLE Students (
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);

Important: Defines table structure. NOT NULL means column cannot be empty.

2. ALTER (Add Column)

ALTER TABLE Students ADD Email VARCHAR(100);

Important: Adds a new column to existing table.

3. ALTER (Modify Column)

ALTER TABLE Students MODIFY Name VARCHAR(100);

Important: Changes column datatype/size.

4. ALTER (Drop Column)

ALTER TABLE Students DROP COLUMN Age;

Important: Permanently removes a column.

5. DROP

DROP TABLE Students;

Important: Deletes table + data + structure completely. Cannot undo.

6. TRUNCATE

TRUNCATE TABLE Students;

Important: Deletes all rows but keeps empty table structure. Cannot undo.

7. RENAME

RENAME TABLE Students TO Learners;

Important: Changes table name. Data stays intact.

8. ALTER (Rename Column - DB specific)

ALTER TABLE Students RENAME COLUMN Name TO Student_Name;

Important: Some DBs use CHANGE (MySQL) or RENAME COLUMN (PostgreSQL).

9. ALTER (Add Primary Key)

ALTER TABLE Students ADD PRIMARY KEY (Roll_No);

Important: Adds PK after table creation.

10. ALTER (Drop Primary Key)

ALTER TABLE Students DROP PRIMARY KEY;

Important: Removes PK constraint.

DCL

Data Control Language

1. GRANT (Give Permissions)

GRANT SELECT, INSERT ON Students TO User1;

Important: Gives User1 permission to read (SELECT) and add (INSERT) data into the Students table.

GRANT ALL PRIVILEGES ON Students TO User1;

Important: Gives User1 all permissions (SELECT, INSERT, UPDATE, DELETE, ALTER, etc.) on the Students table.

GRANT SELECT ON Students TO User1, User2, User3;

Important: You can grant permissions to multiple users at once.

GRANT SELECT ON Students TO PUBLIC;

Important: PUBLIC means every user in the database gets this permission.

GRANT SELECT ON Students TO User1 WITH GRANT OPTION;

Important: WITH GRANT OPTION allows User1 to further grant the SELECT permission to other users.

2. REVOKE (Take Back Permissions)

REVOKE INSERT ON Students FROM User1;

Important: Removes User1's permission to insert data into the Students table.

REVOKE ALL PRIVILEGES ON Students FROM User1;

Important: Removes all permissions that User1 had on the Students table.

REVOKE GRANT OPTION ON Students FROM User1;

Important: Removes only the GRANT OPTION power from User1. User1 keeps other permissions but cannot grant them to others.

TCL :

Here is your Ultra-Short TCL (Transaction Control Language) Guide – only queries and one important point per command.

The Setup (One Table)

CREATE TABLE Accounts (
    Acc_ID INT PRIMARY KEY,
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts VALUES (1, 1000), (2, 500);

1. COMMIT (Make Changes Permanent)

UPDATE Accounts SET Balance = Balance - 200 WHERE Acc_ID = 1;
UPDATE Accounts SET Balance = Balance + 200 WHERE Acc_ID = 2;
COMMIT;

Important: Saves all changes permanently to the database. After COMMIT, you cannot rollback.

2. ROLLBACK (Undo All Changes)

UPDATE Accounts SET Balance = Balance - 200 WHERE Acc_ID = 1;
UPDATE Accounts SET Balance = Balance + 200 WHERE Acc_ID = 2;
ROLLBACK;

Important: Undoes all changes since the last COMMIT. Database returns to original state (Acc1 = 1000, Acc2 = 500).

3. SAVEPOINT (Partial Rollback)

SAVEPOINT sp1;
UPDATE Accounts SET Balance = Balance - 200 WHERE Acc_ID = 1;

SAVEPOINT sp2;
UPDATE Accounts SET Balance = Balance + 200 WHERE Acc_ID = 2;

ROLLBACK TO sp2;   -- Undoes only the 2nd update
COMMIT;

Important: ROLLBACK TO sp2 undoes changes after sp2 only. The first update (Acc1 = 800) is kept.

4. ROLLBACK TO SAVEPOINT (Detailed Example)

SAVEPOINT start;
UPDATE Accounts SET Balance = 800 WHERE Acc_ID = 1;   -- Step 1

SAVEPOINT step1;
UPDATE Accounts SET Balance = 700 WHERE Acc_ID = 1;   -- Step 2

SAVEPOINT step2;
UPDATE Accounts SET Balance = 600 WHERE Acc_ID = 1;   -- Step 3

ROLLBACK TO step1;   -- Undoes Step 2 & Step 3. Balance is back to 800.
COMMIT;

Important: ROLLBACK TO step1 keeps changes until step1 (Balance = 800) and discards everything after.

5. RELEASE SAVEPOINT (Delete a Savepoint)

SAVEPOINT sp1;
UPDATE Accounts SET Balance = 800 WHERE Acc_ID = 1;
RELEASE SAVEPOINT sp1;

Important: Deletes the savepoint sp1. You can no longer rollback to it.

6. Auto-Commit (Interview Trap)

-- In MySQL, auto-commit is ON by default.
SET AUTOCOMMIT = OFF;   -- Now every query waits for manual COMMIT.
UPDATE Accounts SET Balance = 800 WHERE Acc_ID = 1;
COMMIT;   -- Now changes are permanent.

Important: In most databases, AUTOCOMMIT is ON by default. Every INSERT/UPDATE/DELETE is auto-committed unless you start a transaction explicitly.

The Only Trap Question

Interviewer: "What happens if you ROLLBACK after COMMIT?"

Answer: Nothing. ROLLBACK only works on changes made after the last COMMIT. Once committed, changes are permanent and cannot be undone.

Real Interview Scenario (Step-by-Step)

Question: "You are transferring ₹200 from Account 1 to Account 2. Write a TCL-safe transaction."

START TRANSACTION;   -- Explicitly start

UPDATE Accounts SET Balance = Balance - 200 WHERE Acc_ID = 1;
UPDATE Accounts SET Balance = Balance + 200 WHERE Acc_ID = 2;

-- If both updates succeed:
COMMIT;

-- If any update fails (e.g., Acc1 has insufficient balance):
ROLLBACK;

Here is your Ultra-Short SQL Cheat Sheet covering all essential commands in a concise, interview-friendly format.

1. SELECT & FILTERING

2. DML (Data Manipulation)

3. NULL Handling

4. AGGREGATE FUNCTIONS

5. PATTERN MATCHING (LIKE)

6. IN & BETWEEN

7. JOINS

8. GROUP BY & HAVING

Key Point: WHERE filters rows before grouping. HAVING filters groups after grouping.

9. UNION

Rule: Both SELECT statements must have the same number of columns and similar data types.

10. DDL (Data Definition Language)

11. DCL (Data Control Language)

12. TCL (Transaction Control Language)

13. CONSTRAINTS (Quick Reference)

SUBQUERIES (Query inside a Query)

Subquery with IN / EXISTS / ANY / ALL

Subquery in SELECT / FROM / WHERE / HAVING