SQL — Structured Query Language

SQL Basics & Terminology

Imagine you have thousands of customer records. You could store them in a giant Excel file — but what happens when 50 people need to read and update that file at the same time? Chaos. Data gets overwritten, duplicated, corrupted.

That is exactly why databases exist. A database is like a super-powered Excel that can handle millions of rows, lets hundreds of people access it simultaneously, and ensures data never gets corrupted. SQL (Structured Query Language) is the language you use to talk to this database — to ask questions, insert data, update records, or delete rows.

SQL Command Categories

SQL commands are grouped by what they do. This is one of the most commonly tested concepts — expect at least one MCQ on this.

Data Types

Before you store data, the database needs to know what kind of data each column will hold. You would not store someone's name in a number field, right? Data types are like labels on filing cabinet drawers — "this drawer only holds numbers," "this one holds text up to 50 characters."

DDL — CREATE, ALTER, DROP

DDL is about building and modifying the structure — the table itself, not the data inside it. Think of it as construction work: you are building the filing cabinet (CREATE), adding a new drawer to it (ALTER), or demolishing the entire cabinet (DROP).

CREATE TABLE

CREATE TABLE employees (
    emp_id    NUMBER(6)     PRIMARY KEY,
    emp_name  VARCHAR2(50) NOT NULL,
    salary    NUMBER(10,2) DEFAULT 0,
    dept_id   NUMBER(4),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

ALTER TABLE

Your table is already live with data in it, but you realize you forgot to add an email column. You cannot DROP and recreate — you'd lose all data. ALTER lets you modify a live table.

-- Add a column
ALTER TABLE employees ADD email VARCHAR2(100);

-- Modify a column
ALTER TABLE employees MODIFY emp_name VARCHAR2(100);

-- Drop a column
ALTER TABLE employees DROP COLUMN email;

-- Rename a column (Oracle)
ALTER TABLE employees RENAME COLUMN emp_name TO full_name;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT chk_sal CHECK (salary > 0);

DROP & TRUNCATE

-- Drop table entirely (structure + data gone)
DROP TABLE employees;

-- Remove all rows but keep structure (cannot rollback)
TRUNCATE TABLE employees;

DML — INSERT, UPDATE, DELETE

If DDL builds the cabinet, DML is what you do with the files inside it every day — adding new files (INSERT), updating information on existing files (UPDATE), and removing files you no longer need (DELETE).

INSERT

-- Insert single row
INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (101, 'Darshan', 50000, 10);

-- Insert multiple rows (MySQL)
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (102, 'Ankit', 45000),
       (103, 'Priya', 55000);

-- Insert from another table
INSERT INTO emp_backup
SELECT * FROM employees WHERE dept_id = 10;

UPDATE

-- Update specific rows
UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 10;

-- Update multiple columns
UPDATE employees
SET salary = 60000, dept_id = 20
WHERE emp_id = 101;

DELETE

-- Delete specific rows
DELETE FROM employees WHERE emp_id = 103;

-- Delete all rows (can be rolled back)
DELETE FROM employees;

SELECT & Filtering

SELECT is the most important SQL command — it is how you ask questions to your database. Think of it like Google Search for your data. Want to know which employees earn more than 50,000? SELECT. Want a list of all departments? SELECT. You will use this in literally every exam and every real project.

Basic SELECT

-- Select all columns (the * means "everything")
SELECT * FROM employees;

-- Select specific columns (always better — don't fetch what you don't need)
SELECT emp_name, salary FROM employees;

-- Alias — give a column a friendly display name
SELECT emp_name AS "Employee Name", salary * 12 AS annual_salary
FROM employees;

-- DISTINCT — remove duplicates
SELECT DISTINCT dept_id FROM employees;

WHERE Clause — Filtering Rows

Without WHERE, SELECT returns every row. WHERE is your filter — like telling Excel "show me only rows where salary > 50000." The database scans through all rows and only returns the ones that match your condition.

SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE dept_id = 10 AND salary >= 40000;
SELECT * FROM employees WHERE dept_id = 10 OR dept_id = 20;
SELECT * FROM employees WHERE NOT dept_id = 30;

ORDER BY

By default, SQL does not guarantee any order of results. If you want sorted results — highest salary first, alphabetical names, etc. — you must explicitly ask with ORDER BY.

-- Ascending (default) — lowest to highest
SELECT * FROM employees ORDER BY salary ASC;

-- Descending — highest to lowest
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns: first sort by dept, then within each dept sort by salary descending
SELECT * FROM employees ORDER BY dept_id ASC, salary DESC;

LIMIT / ROWNUM / FETCH

-- MySQL: LIMIT
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- Oracle: ROWNUM (old style)
SELECT * FROM employees WHERE ROWNUM <= 5;

-- Oracle 12c+: FETCH
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

Operators & Conditions

Operators are the building blocks of your WHERE clause. They let you express conditions like "between these dates," "in this list," or "matching this pattern." Master these and you can filter anything.

LIKE Wildcards

LIKE is SQL's version of "search with wildcards." Two special characters: % means "any number of characters" (including zero), and _ means "exactly one character."

SQL Functions

Functions in SQL are pre-built tools that transform data. Instead of doing calculations in your application code, SQL lets you do it right inside the query. There are two types:

• Single-row functions — work on each row individually (like applying a formula to each cell in Excel)
• Aggregate functions — work on a group of rows and return one result (like SUM or AVERAGE in Excel)

Single-Row Functions (work on each row)

String Functions:

Number Functions:

Date Functions (Oracle):

SELECT SYSDATE FROM dual;                     -- Current date
SELECT MONTHS_BETWEEN('01-MAR-26', '01-JAN-26') FROM dual;  -- 2
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;       -- 3 months later
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;  -- Next Friday
SELECT LAST_DAY(SYSDATE) FROM dual;            -- Last day of month
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;  -- 2026

Conversion Functions:

TO_CHAR(SYSDATE, 'DD-MON-YYYY')   -- '22-MAR-2026'
TO_CHAR(salary, '$99,999.00')     -- '$50,000.00'
TO_DATE('22-03-2026', 'DD-MM-YYYY') -- date value
TO_NUMBER('12345')                 -- 12345

NULL-handling Functions:

NULL is the trickiest part of SQL. Any operation with NULL returns NULL: 5 + NULL = NULL, 'hello' || NULL = NULL. These functions help you handle NULLs gracefully.

NVL(commission, 0)          -- If NULL, return 0
NVL2(comm, 'Has', 'None')  -- If NOT NULL → 'Has', else 'None'
NULLIF(10, 10)              -- Returns NULL (both equal)
COALESCE(a, b, c, 0)       -- First non-NULL value

Aggregate Functions (work on groups of rows)

Aggregate functions crunch multiple rows into a single result. Think of them like Excel's SUM, AVERAGE, COUNT functions at the bottom of a column.

GROUP BY & HAVING

Why does GROUP BY exist? Imagine your manager asks: "What is the average salary in each department?" You cannot just run AVG(salary) — that gives you ONE number for the entire company. You need to first group employees by department, and THEN calculate the average within each group.

-- Count employees per department
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id;

-- Average salary per department
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
ORDER BY avg_sal DESC;

HAVING (filter groups)

Why can't we just use WHERE? Because WHERE runs before grouping happens. If you want to say "show me only departments that have more than 5 employees," you need a filter that runs after the groups are formed. That is what HAVING does.

-- Departments with more than 5 employees
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;

-- Departments where average salary exceeds 50000
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 10000
GROUP BY dept_id
HAVING AVG(salary) > 50000;

SQL Execution Order

SQL does NOT execute in the order you write it. Understanding this order is the key to understanding why WHERE cannot use aliases and HAVING can use aggregates.

JOINs

Why do JOINs exist? In a well-designed database, data is split across multiple tables to avoid repetition. Employees are in one table, departments in another. But when your manager asks "show me each employee with their department name," that data lives in TWO tables. A JOIN combines them.

INNER JOIN

Only returns rows where there is a match in BOTH tables. If an employee has no department (dept_id is NULL), that employee is excluded from the results.

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

LEFT JOIN

Returns ALL rows from the left table (employees), even if there is no match in the right table (departments). Unmatched right-side columns show NULL.

-- All employees, even those without a department
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

RIGHT JOIN

The mirror of LEFT JOIN. Returns ALL rows from the right table, even if there is no match in the left table.

-- All departments, even those with no employees
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

FULL OUTER JOIN

Returns EVERYTHING from both tables. No row is left behind — unmatched rows from both sides appear with NULLs.

SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

CROSS JOIN

-- Every employee × every department (rarely useful)
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;

SELF JOIN

A table joined with itself. The classic use case: an employees table where each employee has a manager_id that points to another employee in the SAME table.

-- Find each employee's manager
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Subqueries

Why do subqueries exist? Sometimes the answer to your question depends on another question. Example: "Show me employees who earn more than the average salary." You cannot write WHERE salary > AVG(salary) — that is illegal in SQL (aggregate functions are not allowed in WHERE). So you need a subquery to first calculate the average, and then the outer query uses that result.

Single-Row Subquery

The inner query returns exactly ONE value. You can use =, >, <, >=, <= with it.

-- Employees earning more than the average salary
SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multi-Row Subquery (use IN, ANY, ALL)

When the inner query returns MULTIPLE values, you cannot use =. You need IN (matches any value in the list), ANY, or ALL.

-- Employees in departments located in Mumbai
SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Mumbai');

-- Salary greater than ALL in dept 20 (greater than the maximum)
SELECT emp_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 20);

-- Salary greater than ANY in dept 20 (greater than the minimum)
SELECT emp_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 20);

Correlated Subquery

In a normal subquery, the inner query runs once and its result is reused. In a correlated subquery, the inner query runs once for EACH row of the outer query — because it references a value from the outer query. This is slower but more powerful.

-- Employees earning more than the average of THEIR OWN department
SELECT emp_name, salary, dept_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

EXISTS

EXISTS checks if the subquery returns ANY rows at all. It does not care about the actual values — just whether rows exist. It is often faster than IN for large datasets.

-- Departments that have at least one employee
SELECT dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees WHERE dept_id = d.dept_id);

Set Operations

Set operations let you combine the results of two separate SELECT queries. Think of them like math operations on sets: union (combine), intersection (overlap), and difference (subtract).

-- All cities where we have employees or departments
SELECT city FROM employees
UNION
SELECT city FROM departments;

-- Employees who are also managers
SELECT emp_id FROM employees
INTERSECT
SELECT manager_id FROM employees;

Constraints & Keys

Why do constraints exist? Without constraints, anyone could insert garbage data: an employee with a negative salary, two employees with the same ID, an employee assigned to a department that does not exist. Constraints are the database's bouncers — they enforce rules so your data stays clean and consistent.

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    name       VARCHAR2(50) NOT NULL,
    email      VARCHAR2(100) UNIQUE,
    age        NUMBER CHECK (age >= 18),
    status     VARCHAR2(10) DEFAULT 'active',
    dept_id    NUMBER REFERENCES departments(dept_id)
);

Composite Primary Key

Sometimes a single column cannot uniquely identify a row. In an "enrollments" table, neither student_id alone nor course_id alone is unique (a student can enroll in many courses, a course can have many students). But the COMBINATION of both is unique — a student can enroll in a specific course only once.

CREATE TABLE enrollments (
    student_id NUMBER,
    course_id  NUMBER,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

ON DELETE actions (Foreign Key)

What happens when you delete a department that still has employees assigned to it? The ON DELETE action decides.

Normalization

Why does normalization matter? Imagine a table where every employee row stores the department name, department location, and department manager. Now the HR department moves to a new office. You have to update the location in 200 employee rows. Miss even one? Your data is now inconsistent — some rows say "Mumbai," others say "Pune" for the same department.

Normalization is the process of organizing your tables so that each piece of information is stored in exactly ONE place. Update it once, and it is correct everywhere. It eliminates redundancy and prevents the kind of update anomalies described above.

Views

Why do views exist? Imagine you have a complex query with 5 JOINs that you run 10 times a day. Instead of writing that monster query every time, you save it as a view — a virtual table that runs the query behind the scenes whenever you access it. Views also let you control what data different users can see.

-- Create a view
CREATE VIEW high_earners AS
SELECT emp_name, salary, dept_id
FROM employees
WHERE salary > 50000;

-- Use the view like a table
SELECT * FROM high_earners;

-- Replace a view
CREATE OR REPLACE VIEW high_earners AS
SELECT emp_name, salary FROM employees WHERE salary > 60000;

-- Drop a view
DROP VIEW high_earners;

Indexes

Why do indexes exist? Without an index, searching for an employee named "Darshan" in a table of 1 million rows means the database scans every single row — like reading a 500-page book from page 1 to find one topic. An index is like the book's index at the back — you look up "Darshan," find it points to row 47892, and jump straight there.

-- Create an index
CREATE INDEX idx_emp_name ON employees(emp_name);

-- Create a unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Composite index (useful when you often search by both columns together)
CREATE INDEX idx_dept_sal ON employees(dept_id, salary);

-- Drop an index
DROP INDEX idx_emp_name;

Transactions & TCL

Why do transactions exist? Imagine transferring Rs. 1000 from your account to a friend's account. This involves TWO operations: (1) subtract 1000 from your balance, (2) add 1000 to friend's balance. What if the system crashes after step 1 but before step 2? Your money vanishes into thin air. Transactions prevent this by treating both operations as ONE atomic unit — either BOTH succeed, or BOTH are undone.

ACID Properties

Every transaction follows four rules, remembered as ACID. This is a guaranteed exam question.

-- Bank transfer: a classic transaction example
UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 2;
COMMIT;  -- Save changes permanently — cannot be undone after this

-- Something went wrong? Undo everything since last COMMIT
ROLLBACK;

-- Savepoints — partial rollback
SAVEPOINT sp1;
UPDATE employees SET salary = 70000 WHERE emp_id = 101;
SAVEPOINT sp2;
DELETE FROM employees WHERE emp_id = 103;
ROLLBACK TO sp2;  -- Undo DELETE but keep the UPDATE

DCL — GRANT & REVOKE

Why does DCL exist? In a company, not everyone should have the same access. An intern should not be able to DELETE employee records. The CEO's assistant might need to read salary data but never modify it. DCL controls who can do what.

-- Grant permissions
GRANT SELECT, INSERT ON employees TO user1;
GRANT ALL ON employees TO admin_user;

-- Revoke permissions
REVOKE INSERT ON employees FROM user1;

-- Grant with pass-along ability
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

Miscellaneous Concepts

CASE Expression

CASE is SQL's version of if-else. It lets you create calculated columns based on conditions — like adding a "level" label based on salary ranges.

SELECT emp_name, salary,
  CASE
    WHEN salary > 80000 THEN 'Senior'
    WHEN salary > 50000 THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

CREATE TABLE AS SELECT (CTAS)

-- Create a backup table from existing data
CREATE TABLE emp_backup AS
SELECT * FROM employees WHERE dept_id = 10;

RENAME & COMMENT

-- Rename a table
RENAME employees TO staff;

-- Add a comment on table
COMMENT ON TABLE employees IS 'Main employee table';

Pseudo-columns (Oracle)

DECODE (Oracle-specific IF-THEN)

SELECT emp_name,
  DECODE(dept_id,
    10, 'Finance',
    20, 'IT',
    30, 'HR',
    'Other') AS dept_name
FROM employees;

Sequences (Oracle — auto-increment)

-- Create a sequence
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

-- Use in INSERT
INSERT INTO employees (emp_id, emp_name)
VALUES (emp_seq.NEXTVAL, 'Darshan');

-- Get current value
SELECT emp_seq.CURRVAL FROM dual;

Synonyms (Oracle)

-- Create a synonym (alias for a table)
CREATE SYNONYM emp FOR hr.employees;

-- Now use the short name
SELECT * FROM emp;

Practice Questions