SQL Essentials

SQL (Structured Query Language) is the language used to communicate with relational databases. Every database operation — reading, writing, updating, deleting, and managing schema — is done through SQL.

SQL is not optional for DBMS interviews. Every company expects you to write clean, correct SQL queries.

This chapter covers the complete SQL command set with practical examples.

---

Learning Objectives

After completing this chapter, you will be able to:

• Classify SQL commands into DDL, DML, DCL, and TCL.
• Create, alter, and drop database objects.
• Insert, select, update, and delete data.
• Understand WHERE, ORDER BY, LIMIT, and DISTINCT.
• Write practical SQL queries.
• Answer interview SQL questions confidently.

---

SQL Command Categories

Category	Full Form	Purpose
DDL	Data Definition Language	Define and modify schema
DML	Data Manipulation Language	Manipulate data
DCL	Data Control Language	Manage permissions
TCL	Transaction Control Language	Manage transactions

---

DDL — Data Definition Language

DDL commands define the structure of the database.

CREATE

Creates tables, views, indexes, or databases.

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT CHECK (Age > 0),
    Email VARCHAR(255) UNIQUE,
    Enrolled_Date DATE DEFAULT CURRENT_DATE
);

ALTER

Modifies existing tables.

-- Add a column
ALTER TABLE Students ADD COLUMN Phone VARCHAR(15);

-- Modify a column
ALTER TABLE Students ALTER COLUMN Age SET DATA TYPE SMALLINT;

-- Rename a column
ALTER TABLE Students RENAME COLUMN Phone TO Mobile;

-- Drop a column
ALTER TABLE Students DROP COLUMN Mobile;

-- Add a constraint
ALTER TABLE Students ADD CONSTRAINT chk_age CHECK (Age < 100);

DROP

Deletes objects permanently.

DROP TABLE Students;        -- Deletes table and all data
DROP TABLE IF EXISTS Students;  -- Safe version (no error if missing)
DROP DATABASE School;       -- Deletes entire database

TRUNCATE

Removes all rows from a table quickly (cannot be rolled back in some databases).

TRUNCATE TABLE Logs;  -- Removes all rows, keeps table structure

---

DML — Data Manipulation Language

DML commands handle the data inside tables.

INSERT

Adds new rows.

-- Insert with all columns
INSERT INTO Students VALUES (101, 'Rahul', 22, 'rahul@mail.com', '2024-01-15');

-- Insert with specific columns
INSERT INTO Students (Student_ID, Name, Email) VALUES (102, 'Priya', 'priya@mail.com');

-- Insert multiple rows
INSERT INTO Students VALUES
    (103, 'Amit', 23, 'amit@mail.com', '2024-02-01'),
    (104, 'Sneha', 21, 'sneha@mail.com', '2024-02-01');

-- Insert from another table
INSERT INTO TopStudents (Student_ID, Name)
SELECT Student_ID, Name FROM Students WHERE Grade = 'A';

SELECT

Retrieves data.

-- All columns
SELECT * FROM Students;

-- Specific columns
SELECT Name, Email FROM Students;

-- With WHERE filter
SELECT * FROM Students WHERE Age > 21;

-- With ORDER BY
SELECT * FROM Students ORDER BY Age DESC;

-- With LIMIT
SELECT * FROM Students LIMIT 10;

-- With DISTINCT (unique values)
SELECT DISTINCT City FROM Students;

-- With aggregate
SELECT COUNT(*), AVG(Age), MAX(Age), MIN(Age) FROM Students;

UPDATE

Modifies existing rows.

-- Update all rows (DANGEROUS — usually needs WHERE)
UPDATE Students SET Age = 23;

-- Update specific rows
UPDATE Students SET Age = 23 WHERE Student_ID = 101;

-- Update multiple columns
UPDATE Students SET Age = 24, Email = 'rahul.new@mail.com' WHERE Student_ID = 101;

UPDATE Safety

Always use a WHERE clause unless you intend to update every row.

Best practice: SELECT first to verify the WHERE condition, then run UPDATE.

-- Step 1: Preview
SELECT * FROM Students WHERE Student_ID = 101;

-- Step 2: Update
UPDATE Students SET Age = 24 WHERE Student_ID = 101;

DELETE

Removes rows.

-- Delete specific rows
DELETE FROM Students WHERE Student_ID = 101;

-- Delete all rows (but keep table)
DELETE FROM Students;

-- Delete all rows (faster, but different transaction behavior)
TRUNCATE TABLE Students;

DELETE vs TRUNCATE vs DROP

Command	Removes Data	Removes Structure	Can Rollback	Speed
DELETE	Yes (some/all rows)	No	Yes (in transaction)	Slow (row by row)
TRUNCATE	Yes (all rows)	No	Varies	Fast
DROP	Yes (all rows)	Yes	Varies	Fastest

---

DCL — Data Control Language

DCL commands manage user permissions.

GRANT

Gives permissions to users.

-- Grant SELECT on a table
GRANT SELECT ON Students TO user_rahul;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON Students TO user_rahul;

-- Grant all privileges
GRANT ALL PRIVILEGES ON Database School TO admin_user;

-- Grant with option to pass permissions
GRANT SELECT ON Students TO user_rahul WITH GRANT OPTION;

REVOKE

Removes permissions.

-- Revoke a specific privilege
REVOKE INSERT ON Students FROM user_rahul;

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON Students FROM user_rahul;

---

TCL — Transaction Control Language

TCL commands manage database transactions.

COMMIT

Saves all changes made in the current transaction permanently.

BEGIN;
UPDATE Accounts SET Balance = Balance - 1000 WHERE Account_ID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE Account_ID = 2;
COMMIT;  -- Both updates are saved permanently

ROLLBACK

Undoes all changes made in the current transaction.

BEGIN;
UPDATE Accounts SET Balance = Balance - 1000 WHERE Account_ID = 1;
-- Oops, system crashes or we detect an error
ROLLBACK;  -- Balance is restored to original

SAVEPOINT

Sets a point within a transaction that can be rolled back to.

BEGIN;
INSERT INTO Logs VALUES ('Step 1');
SAVEPOINT sp1;
INSERT INTO Logs VALUES ('Step 2');
ROLLBACK TO sp1;  -- 'Step 2' is undone, 'Step 1' remains
COMMIT;           -- Only 'Step 1' is saved

---

SQL Execution Order

SQL queries follow a specific logical execution order (though the database may optimize the actual execution):

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Example

SELECT City, COUNT(*) AS Count
FROM Students
WHERE Age > 20
GROUP BY City
HAVING COUNT(*) > 5
ORDER BY Count DESC
LIMIT 10;

Execution:

1. FROM — Start with Students table.
2. WHERE — Filter to students aged > 20.
3. GROUP BY — Group by City.
4. HAVING — Keep only cities with > 5 students.
5. SELECT — Pick City and count.
6. ORDER BY — Sort by count descending.
7. LIMIT — Show top 10.

---

Common SQL Mistakes

Mistake	Example	Correct
Missing WHERE on UPDATE	UPDATE Students SET Age=23	Add WHERE
NULL comparison with =	WHERE Name = NULL	WHERE Name IS NULL
String quotes	WHERE Name = "Rahul"	WHERE Name = 'Rahul'
GROUP BY without aggregate	SELECT Name, COUNT(*) ... GROUP BY Name	Correct if Name is the group column
Forgetting semicolon	SELECT * FROM Students	Add semicolon

---

Interview Deep Dive

---

Key Takeaways

• SQL has four categories: DDL, DML, DCL, TCL.
• CREATE, ALTER, DROP manage schema.
• SELECT, INSERT, UPDATE, DELETE manage data.
• GRANT, REVOKE manage permissions.
• COMMIT, ROLLBACK, SAVEPOINT manage transactions.
• WHERE filters rows, HAVING filters groups.
• Always use WHERE with UPDATE/DELETE unless intentional.
• Understand the SQL execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).