SQL: Zero to Data Analyst — Query Guide

SQL HOME — what this course covers

This Rishtaara SQL course teaches you step by step — from your first SELECT to building real reports on a sample school database.

SQL (Structured Query Language) is the standard language for talking to relational databases like MySQL, PostgreSQL, SQL Server, and SQLite.

Real-life example: SQL is like asking a librarian precise questions — 'Show me all books by author X published after 2020' — instead of reading every shelf yourself.

• Part 1 (lessons 1–16): queries — SELECT, JOINs, GROUP BY, and more
• Part 2 (lessons 17–30): databases — CREATE TABLE, constraints, indexes, security
• Practice with /mcq/sql-mcq when you finish a topic block

Introduction — what is SQL?

SQL lets you store, read, update, and delete data in tables. Analysts use it for reports. Developers use it to power apps. Data teams use it every day.

A relational database stores data in tables (rows and columns). Tables link through keys — for example, a student_id in an enrollments table points to a row in students.

Real-life example: A school office keeps a paper register for each class. SQL is the digital register that never loses pages and answers questions in seconds.

-- students: one row per student
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100),
  grade_level INT,
  city VARCHAR(80)
);

-- courses: subjects offered
CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100),
  credits INT
);

-- enrollments: who takes what
CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2)
);

SQL Syntax — rules of the language

SQL statements end with a semicolon (;). Keywords like SELECT and FROM are not case-sensitive, but writing them in UPPERCASE makes queries easier to read.

Strings use single quotes: 'Delhi'. Numbers do not need quotes. Table and column names usually avoid spaces; use snake_case like full_name.

Real-life example: SQL syntax is like a fixed form at the bank — fill in the right boxes (SELECT columns, FROM table) in the right order, or the clerk rejects it.

SELECT full_name, city
FROM students
WHERE grade_level = 10;

-- Read data
SELECT * FROM students;

-- Add data
INSERT INTO students (student_id, full_name, grade_level, city)
VALUES (1, 'Aisha Khan', 10, 'Delhi');

-- Change data
UPDATE students SET city = 'Mumbai' WHERE student_id = 1;

-- Remove data
DELETE FROM students WHERE student_id = 1;

SELECT — read columns from a table

SELECT chooses which columns you want. FROM names the table. * means all columns — fine for learning, but in real work list only what you need.

Always start with a small SELECT before adding filters. This habit saves time when a query returns unexpected results.

Real-life example: SELECT is like choosing which columns to copy from a spreadsheet — name and city only, not every hidden column.

-- Specific columns (preferred)
SELECT student_id, full_name, city
FROM students;

-- All columns (quick peek only)
SELECT * FROM students;

SELECT DISTINCT — unique values only

DISTINCT removes duplicate rows from the result. Use it when you only care about unique values, such as all cities where students live.

DISTINCT applies to the whole row combination you select, not just one column.

Real-life example: DISTINCT is like asking 'Which cities appear on the list?' without reading every student name twice.

SELECT DISTINCT city
FROM students;

SELECT DISTINCT grade_level, city
FROM students
ORDER BY grade_level, city;

WHERE — filter rows

WHERE keeps only rows that match a condition. Without WHERE, SELECT returns every row in the table.

Compare columns to values with =, <>, >, >=, <, <=. Use single quotes for text.

Real-life example: WHERE is the bouncer at a concert — only people with a valid ticket (condition) get in.

SELECT full_name, city
FROM students
WHERE grade_level = 10;

SELECT full_name, grade_level
FROM students
WHERE city = 'Delhi';

AND — both conditions must be true

AND combines conditions. Every condition joined with AND must pass for the row to appear.

Real-life example: AND is like needing both a library card AND a student ID to borrow a textbook — missing either means no book.

SELECT full_name, city, grade_level
FROM students
WHERE grade_level = 10 AND city = 'Delhi';

OR — at least one condition must be true

OR keeps rows where any listed condition is true. Use parentheses when mixing AND and OR so the database understands your intent.

Real-life example: OR is like a sale open to seniors OR honor-roll students — either badge gets you the discount.

SELECT full_name, city
FROM students
WHERE city = 'Delhi' OR city = 'Mumbai';

-- Mix AND / OR safely with parentheses
SELECT full_name, city, grade_level
FROM students
WHERE (city = 'Delhi' OR city = 'Mumbai') AND grade_level >= 10;

NOT — reverse a condition

NOT flips a condition. WHERE NOT city = 'Delhi' means every city except Delhi.

You can write WHERE city <> 'Delhi' or WHERE city != 'Delhi' for the same idea on many databases.

Real-life example: NOT is like 'everyone except staff' on a guest list — you define who is excluded.

SELECT full_name, city
FROM students
WHERE NOT city = 'Delhi';

-- Same result, different style
SELECT full_name, city
FROM students
WHERE city <> 'Delhi';

ORDER BY — sort results

ORDER BY sorts rows. ASC means ascending (A→Z, small→large). DESC means descending. Default is ASC if you omit it.

You can sort by multiple columns: first by grade_level, then by full_name inside each grade.

Real-life example: ORDER BY is like sorting exam papers — first by class, then by roll number inside each class.

SELECT full_name, grade_level, city
FROM students
ORDER BY grade_level ASC, full_name ASC;

SELECT full_name, grade_level
FROM students
ORDER BY grade_level DESC;

SELECT TOP / LIMIT — only first N rows

Sometimes you only want a few rows — top 5 scores or first 10 students alphabetically. SQL Server and MS Access use SELECT TOP n. MySQL, PostgreSQL, and SQLite use LIMIT n at the end.

Combine ORDER BY with TOP or LIMIT to get 'highest' or 'lowest' rows, not random ones.

Real-life example: TOP/LIMIT is like reading only the first page of search results instead of all 10,000 matches.

-- SQL Server / Access
SELECT TOP 5 full_name, grade_level
FROM students
ORDER BY full_name;

-- MySQL, PostgreSQL, SQLite
SELECT full_name, grade_level
FROM students
ORDER BY full_name
LIMIT 5;

-- Skip first 5, take next 5 (pagination)
SELECT full_name, grade_level
FROM students
ORDER BY full_name
LIMIT 5 OFFSET 5;

INSERT INTO — add new rows

INSERT INTO adds one or more rows. List column names, then VALUES. Order of values must match column order.

Real-life example: INSERT is like writing a new name into the attendance register on the first day of school.

INSERT INTO students (student_id, full_name, grade_level, city)
VALUES (101, 'Ravi Patel', 11, 'Ahmedabad');

INSERT INTO courses (course_id, course_name, credits)
VALUES
  (1, 'Mathematics', 4),
  (2, 'Physics', 4),
  (3, 'English', 3);

NULL Values — missing or unknown data

NULL means 'no value' or 'unknown'. It is not zero and not an empty string. Use IS NULL and IS NOT NULL to test for NULL.

Real-life example: NULL is a blank line on a form — the student did not fill in their city yet, so you cannot assume any city.

INSERT INTO students (student_id, full_name, grade_level, city)
VALUES (102, 'Meera Singh', 10, NULL);

SELECT full_name
FROM students
WHERE city IS NULL;

UPDATE students
SET city = 'Pune'
WHERE student_id = 102 AND city IS NULL;

UPDATE — change existing rows

UPDATE sets new values. Always use WHERE unless you truly mean to change every row — a missing WHERE updates the whole table.

Real-life example: UPDATE is like correcting a typo in one student's address on the register, not rewriting every page.

UPDATE students
SET city = 'Bangalore'
WHERE student_id = 101;

UPDATE students
SET grade_level = grade_level + 1
WHERE grade_level = 10;

DELETE — remove rows

DELETE removes rows that match WHERE. DELETE FROM students with no WHERE deletes all students — dangerous in production.

Real-life example: DELETE is like crossing out one enrollment when a student drops a course, not shredding the whole file cabinet.

-- Remove one enrollment
DELETE FROM enrollments
WHERE enrollment_id = 5001;

-- Remove ALL rows from a table (rare — usually use TRUNCATE in admin tasks)
-- DELETE FROM enrollments;

Aggregate Functions — summary numbers

Aggregate functions crunch many rows into one number. They ignore NULL values except COUNT(*) which counts all rows.

Use them to answer questions like 'What is the average score?' or 'How many students enrolled?'

Real-life example: Aggregates are like the summary row at the bottom of a report — total sales, average rating, highest score.

SELECT
  COUNT(*) AS total_rows,
  COUNT(score) AS scored_rows,
  MIN(score) AS lowest_score,
  MAX(score) AS highest_score,
  SUM(score) AS score_sum,
  AVG(score) AS average_score
FROM enrollments;

MIN — smallest value

MIN returns the lowest value in a column. Works on numbers, dates, and text (alphabetically first).

Real-life example: MIN is like finding the earliest exam date on the calendar.

SELECT MIN(score) AS lowest_score
FROM enrollments;

MAX — largest value

MAX returns the highest value. Pair MIN and MAX to see the range of scores in a class.

Real-life example: MAX is the top mark on the leaderboard — who scored highest?

SELECT MAX(score) AS top_score
FROM enrollments
WHERE course_id = 1;

COUNT — how many rows

COUNT(*) counts all rows. COUNT(column) counts rows where that column is not NULL.

Real-life example: COUNT is headcount at assembly — how many students are present?

SELECT COUNT(*) AS student_count FROM students;

SELECT COUNT(score) AS graded_enrollments
FROM enrollments;

SUM — total of numbers

SUM adds numeric values. Useful for total credits enrolled or total points across assignments.

Real-life example: SUM is like adding every bill amount at month end to get the total spend.

SELECT SUM(c.credits) AS total_credits
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 101;

AVG — average value

AVG computes the mean. It skips NULL scores automatically.

Real-life example: AVG is the class average printed on the report card header.

SELECT AVG(score) AS class_average
FROM enrollments
WHERE course_id = 2;

LIKE — pattern matching on text

LIKE compares text to a pattern. It is slower than exact matches on large tables but very useful for partial names or emails.

Real-life example: LIKE is like searching your phone contacts by typing the first few letters of a name.

SELECT full_name FROM students WHERE full_name LIKE 'A%';
SELECT full_name FROM students WHERE full_name LIKE '%n';

Wildcards — % and _

% matches zero or more characters. _ matches exactly one character. On some databases you can use REGEXP for richer patterns.

Real-life example: % is a stretchy blank — 'A%' matches A, Ali, Aisha. _ is one single blank — 'A_sha' matches Aisha but not Ali.

-- Names with 'ee' anywhere
SELECT full_name FROM students WHERE full_name LIKE '%ee%';

-- Five-letter names starting with M
SELECT full_name FROM students WHERE full_name LIKE 'M____';

IN — match any value in a list

IN checks if a column equals any value in a list. It is cleaner than long chains of OR.

Real-life example: IN is like a VIP list at the gate — Delhi, Mumbai, or Bangalore gets entry.

SELECT full_name, city
FROM students
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');

SELECT course_name
FROM courses
WHERE course_id IN (1, 3);

BETWEEN — values in a range

BETWEEN low AND high includes both endpoints. Works well for numbers, dates, and sorted text.

Real-life example: BETWEEN is like 'students in grades 9 through 11' — both 9 and 11 count.

SELECT full_name, grade_level
FROM students
WHERE grade_level BETWEEN 9 AND 11;

SELECT student_id, score
FROM enrollments
WHERE score BETWEEN 70 AND 89;

Aliases — short names for columns and tables

An alias renames a column or table in the result only — it does not change the database. Use AS for clarity; AS is optional on many databases.

Table aliases (s, c, e) shorten queries and are required when the same table appears twice.

Real-life example: Aliases are nicknames in a classroom — 'Ravi P.' on the attendance sheet instead of the full legal name every time.

SELECT
  full_name AS student_name,
  grade_level AS grade
FROM students AS s
WHERE s.city = 'Delhi';

SELECT
  c.course_name AS subject,
  e.score AS marks
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id;

JOINs — combine tables

JOINs link rows from two or more tables using a related column — usually a primary key and foreign key.

Without JOINs you cannot answer 'student name + course name + score' because those live in different tables.

Real-life example: JOINs are like matching roll numbers on two lists — attendance sheet + marks sheet = full report.

SELECT
  s.full_name,
  c.course_name,
  e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

INNER JOIN — only matching rows

INNER JOIN returns rows where the join condition matches on both sides. Students with no enrollments do not appear.

Real-life example: INNER JOIN is a strict handshake — both people must show up or there is no meeting.

SELECT DISTINCT s.full_name, s.grade_level
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id;

LEFT JOIN — keep all left-table rows

LEFT JOIN keeps every row from the left table. If no match on the right, right columns are NULL.

Real-life example: LEFT JOIN is 'all students, and show their score if they enrolled' — blank if they did not.

SELECT s.full_name, c.course_name, e.score
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

RIGHT JOIN — keep all right-table rows

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table, NULLs on the left when missing.

Real-life example: RIGHT JOIN is 'every course, even if zero students signed up' — empty seats still listed.

SELECT c.course_name, s.full_name
FROM students s
RIGHT JOIN enrollments e ON s.student_id = e.student_id
RIGHT JOIN courses c ON e.course_id = c.course_id;

FULL OUTER JOIN — keep both sides

FULL OUTER JOIN returns all rows from both tables. Unmatched sides show NULL. SQL Server supports it; MySQL does not (use UNION of LEFT and RIGHT).

Real-life example: FULL OUTER JOIN is merging two guest lists and keeping everyone, even if they appear on only one list.

SELECT s.full_name, c.course_name
FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.course_id;

SELF JOIN — join a table to itself

SELF JOIN uses two aliases of the same table to compare rows within one table — peers in the same grade, employees and managers, etc.

Real-life example: SELF JOIN is like pairing students in the same class to find study buddies from the same grade_level.

SELECT
  a.full_name AS student_a,
  b.full_name AS student_b,
  a.grade_level
FROM students a
INNER JOIN students b
  ON a.grade_level = b.grade_level
 AND a.student_id < b.student_id;

UNION — combine result sets (unique rows)

UNION stacks the results of two SELECT queries. Columns must match in count and compatible types. UNION removes duplicate rows.

Real-life example: UNION is merging two attendance sheets from morning and afternoon sessions into one master list without duplicate names.

SELECT full_name, city FROM students WHERE city = 'Delhi'
UNION
SELECT full_name, city FROM students WHERE city = 'Mumbai';

UNION ALL — keep duplicates

UNION ALL is faster when you know duplicates are fine or impossible. It keeps every row from both queries.

Real-life example: UNION ALL is photocopying both lists and stapling them — if a name appears twice, it stays twice.

SELECT student_id, 'Math track' AS track FROM enrollments WHERE course_id = 1
UNION ALL
SELECT student_id, 'Science track' AS track FROM enrollments WHERE course_id = 2;

GROUP BY — one row per group

GROUP BY splits rows into groups — by course_id, city, or grade — then aggregates run on each group.

Every selected column must be in GROUP BY or inside an aggregate function.

Real-life example: GROUP BY is sorting exam papers into piles by class, then writing one average score per pile.

SELECT
  c.course_name,
  COUNT(e.enrollment_id) AS student_count,
  AVG(e.score) AS avg_score
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;

HAVING — filter groups after aggregation

WHERE filters rows before grouping. HAVING filters groups after GROUP BY — use it with aggregates like AVG(score) > 80.

Real-life example: HAVING is 'only show classes where average score is above 75' — you need the average first, then filter.

SELECT
  c.course_name,
  AVG(e.score) AS avg_score
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name
HAVING AVG(e.score) > 80;

EXISTS — true if subquery returns rows

EXISTS checks whether a related subquery finds any row. It stops at the first match and is often efficient for 'has at least one' checks.

Real-life example: EXISTS is asking 'Is there any enrollment for this student?' — yes or no, without listing every course.

SELECT s.full_name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.student_id
);

ANY — compare to any value in a subquery

ANY (or SOME) means the condition is true if it matches at least one row from the subquery. Often used with > ANY (...).

Real-life example: ANY is 'score higher than at least one passing student' — one match is enough.

SELECT student_id, score
FROM enrollments
WHERE score > ANY (
  SELECT score FROM enrollments WHERE course_id = 1
);

ALL — compare to every value in a subquery

ALL means the condition must be true for every value returned by the subquery. > ALL (...) means greater than the maximum of the set.

Real-life example: ALL is 'beat every score in the top class' — you must exceed them all, not just one.

SELECT student_id, score
FROM enrollments
WHERE score > ALL (
  SELECT score FROM enrollments WHERE course_id = 3 AND score IS NOT NULL
);

SELECT INTO — copy query results to a new table

SELECT INTO creates a new table from a query result. SQL Server and Access support it directly. MySQL/PostgreSQL use CREATE TABLE ... AS SELECT instead.

Real-life example: SELECT INTO is photocopying the honor roll onto a fresh sheet titled 'Top Students March'.

-- SQL Server / Access
SELECT student_id, full_name, grade_level
INTO top_students
FROM students
WHERE grade_level = 12;

CREATE TABLE top_students AS
SELECT student_id, full_name, grade_level
FROM students
WHERE grade_level = 12;

INSERT INTO SELECT — copy into existing table

INSERT INTO ... SELECT adds rows from a query into a table that already exists. Column types must match.

Real-life example: INSERT INTO SELECT is adding this month's new enrollments into the master archive table.

INSERT INTO honor_roll (student_id, course_id, score)
SELECT student_id, course_id, score
FROM enrollments
WHERE score >= 90;

CASE — conditional logic in queries

CASE works like if/else in SQL. Simple CASE matches one expression; searched CASE uses full conditions.

Real-life example: CASE is the grading rubric — 90+ is A, 80–89 is B, everything else is C.

SELECT
  student_id,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'Needs improvement'
  END AS letter_grade
FROM enrollments;

NULL Functions — COALESCE, IFNULL, NVL

COALESCE(a, b, c) returns the first non-NULL value. MySQL uses IFNULL(x, y). SQL Server uses ISNULL(x, y). Oracle uses NVL(x, y).

Real-life example: COALESCE is 'use city if filled, otherwise use Unknown' on a mailing label.

SELECT
  full_name,
  COALESCE(city, 'Unknown') AS display_city
FROM students;

-- MySQL
SELECT full_name, IFNULL(city, 'Unknown') AS display_city FROM students;

Operators — arithmetic and comparison

Arithmetic: +, -, *, / on numbers. Comparison: =, <>, >, <, >=, <=. Logical: AND, OR, NOT. Concatenation varies: || on PostgreSQL/SQLite, CONCAT() on MySQL, + on SQL Server.

Real-life example: Operators are the math and logic symbols on a calculator — add credits, compare scores, combine filters.

SELECT score, score + 5 AS curved_score FROM enrollments;

-- PostgreSQL / SQLite concat
SELECT full_name || ' (Grade ' || grade_level || ')' AS label FROM students;

-- MySQL concat
SELECT CONCAT(full_name, ' (Grade ', grade_level, ')') AS label FROM students;

Comments — notes for humans

Single-line comments start with --. Multi-line comments use /* ... */. Comments do not run — they document your query.

Real-life example: Comments are pencil notes in the margin — 'only active students' — for the next person reading your SQL.

-- Top students in Delhi only
SELECT full_name, city
FROM students
WHERE city = 'Delhi'; /* change city as needed */

/*
  Report: enrollments with scores
  Author: analyst team
*/
SELECT * FROM enrollments WHERE score IS NOT NULL;

Stored Procedures — saved SQL on the server

A stored procedure is a named block of SQL saved in the database. You CALL it instead of pasting the same query everywhere.

Procedures can accept parameters, run multiple statements, and enforce business rules in one place.

Real-life example: A stored procedure is a vending machine button — press 'ClassAverage' and the school office runs the same report every time.

DELIMITER //
CREATE PROCEDURE GetStudentsByGrade(IN p_grade INT)
BEGIN
  SELECT student_id, full_name, city
  FROM students
  WHERE grade_level = p_grade;
END //
DELIMITER ;

CALL GetStudentsByGrade(10);

CREATE PROCEDURE GetStudentsByGrade @Grade INT
AS
BEGIN
  SELECT student_id, full_name, city
  FROM students
  WHERE grade_level = @Grade;
END;

EXEC GetStudentsByGrade @Grade = 10;

CREATE DATABASE — start a new database

CREATE DATABASE makes a new empty container for tables. You then USE or connect to that database before creating tables.

Real-life example: CREATE DATABASE is opening a new filing cabinet labeled 'Springfield High 2026' — empty until you add folders (tables).

CREATE DATABASE school_db;

-- MySQL
USE school_db;

-- PostgreSQL: connect with \c school_db in psql

DROP DATABASE — delete entire database

DROP DATABASE permanently removes the database and all its tables. There is no undo — always backup first.

Real-life example: DROP DATABASE is shredding the whole cabinet — every folder inside goes with it.

-- Danger: removes everything in school_db
DROP DATABASE school_db;

BACKUP DATABASE — save a copy

Backups protect against mistakes and hardware failure. SQL Server uses BACKUP DATABASE. MySQL often uses mysqldump from the shell. PostgreSQL uses pg_dump.

Real-life example: BACKUP is photocopying the entire register before exam week — if ink spills, you restore from the copy.

BACKUP DATABASE school_db
TO DISK = 'C:\Backups\school_db.bak';

-- MySQL (terminal)
-- mysqldump -u root -p school_db > school_db_backup.sql

-- PostgreSQL (terminal)
-- pg_dump school_db > school_db_backup.sql

-- Restore MySQL
-- mysql -u root -p school_db < school_db_backup.sql

CREATE TABLE — define structure

CREATE TABLE lists column names and data types. Add constraints like PRIMARY KEY when you create the table or later with ALTER.

Real-life example: CREATE TABLE is designing a blank form — name box, date box, score box — before anyone fills it in.

CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  department VARCHAR(60),
  hire_date DATE
);

DROP TABLE — remove a table

DROP TABLE deletes the table and all its data. Use TRUNCATE to empty a table but keep its structure.

Real-life example: DROP TABLE is throwing away the whole form template and every filled copy — not just erasing one row.

-- Remove table completely
DROP TABLE teachers;

-- Empty rows but keep columns (syntax varies slightly)
TRUNCATE TABLE enrollments;

ALTER TABLE — change existing structure

ALTER TABLE adds, drops, or modifies columns. Use it when requirements change — new phone column, rename field, etc.

Real-life example: ALTER TABLE is adding a new 'email' line to every printed form without reprinting old entries from scratch.

ALTER TABLE students ADD email VARCHAR(120);

ALTER TABLE students MODIFY grade_level INT;  -- MySQL
-- ALTER TABLE students ALTER COLUMN grade_level TYPE INT;  -- PostgreSQL

ALTER TABLE students DROP COLUMN email;

Constraints — rules on data

Constraints enforce data quality at the database level — not only in app code. They prevent bad rows from being inserted.

Real-life example: Constraints are school rules printed on the handbook — no student without a name, no duplicate roll numbers.

• NOT NULL — column must have a value
• UNIQUE — no duplicate values in the column
• PRIMARY KEY — unique row identifier
• FOREIGN KEY — links to another table
• CHECK — custom validation rule
• DEFAULT — value when none is provided

NOT NULL — required values

NOT NULL rejects INSERT or UPDATE that leaves the column empty.

Real-life example: NOT NULL on full_name means every student row must have a name — blank rows are not allowed.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  city VARCHAR(80)
);

UNIQUE — no duplicates

UNIQUE ensures every value in the column (or column group) appears only once — like a unique email per student.

Real-life example: UNIQUE on email is one mailbox per student — two people cannot share the same address.

ALTER TABLE students ADD email VARCHAR(120) UNIQUE;

PRIMARY KEY — main row identifier

PRIMARY KEY uniquely identifies each row. One per table. It implies NOT NULL and UNIQUE.

Real-life example: PRIMARY KEY is the roll number — one number, one student, forever in that table.

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  PRIMARY KEY (student_id, course_id)
);

FOREIGN KEY — link tables safely

FOREIGN KEY points to a PRIMARY KEY in another table. It stops orphan rows — enrollments for students who do not exist.

Real-life example: FOREIGN KEY is 'this enrollment must belong to a real student_id in the students table'.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CHECK — custom rules

CHECK validates expressions — score between 0 and 100, grade_level positive, etc.

Real-life example: CHECK is the rule 'score cannot be above 100' enforced automatically on every insert.

ALTER TABLE enrollments
ADD CONSTRAINT chk_score CHECK (score >= 0 AND score <= 100);

DEFAULT — automatic value

DEFAULT fills a column when INSERT omits it — enrollment_date defaults to today, status defaults to 'active'.

Real-life example: DEFAULT is the pre-printed 'Date: ___' on a form that auto-stamps today's date if left blank.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  status VARCHAR(20) DEFAULT 'active',
  enrolled_at DATE DEFAULT CURRENT_DATE
);

CREATE INDEX — speed up lookups

An index is like a book index — the database finds rows faster without scanning every page. Too many indexes slow down writes.

Real-life example: CREATE INDEX on student_id is alphabet tabs on the attendance drawer — jump straight to 'P' for Patel.

CREATE INDEX idx_students_city ON students(city);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_score ON enrollments(course_id, score);

AUTO INCREMENT — automatic IDs

AUTO_INCREMENT (MySQL), SERIAL (PostgreSQL), and IDENTITY (SQL Server) generate the next integer ID automatically.

Real-life example: AUTO INCREMENT is the ticket machine at the deli — each new customer gets the next number without you choosing.

-- MySQL
CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);

INSERT INTO students (full_name) VALUES ('New Student');

-- PostgreSQL
CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);

Dates — store and query time

DATE stores calendar days. DATETIME or TIMESTAMP adds time. Use standard formats 'YYYY-MM-DD' in queries for clarity.

Real-life example: Dates are exam day on the calendar — query 'enrollments after 2025-01-01' like flipping to January in a planner.

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enrolled_at DATE,
  score DECIMAL(5, 2)
);

INSERT INTO enrollments (enrollment_id, student_id, course_id, enrolled_at, score)
VALUES (1, 101, 1, '2025-08-15', 88.5);

SELECT * FROM enrollments
WHERE enrolled_at BETWEEN '2025-08-01' AND '2025-08-31';

Views — saved virtual tables

A VIEW is a stored SELECT. Users query the view name like a table. It simplifies reports and hides complex joins.

Real-life example: A view is a pre-made dashboard — 'student_report_card' always shows name + course + score without rewriting the JOIN.

CREATE VIEW student_scores AS
SELECT
  s.full_name,
  c.course_name,
  e.score,
  e.enrolled_at
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

SELECT * FROM student_scores WHERE score >= 85;

SQL Injection — when user input breaks your query

SQL injection happens when untrusted text is pasted into SQL strings. Attackers can read or delete data by typing clever input.

Never build queries by concatenating raw user input: "SELECT * FROM students WHERE name = '" + input + "'" is dangerous.

Real-life example: Injection is a forged note slipped into the principal's inbox — 'Also show me every password' hidden in a normal request.

-- UNSAFE (never do this in app code)
-- query = "SELECT * FROM students WHERE full_name = '" + userInput + "'"
-- If userInput is: ' OR '1'='1
-- Whole table is exposed!

-- SAFE: use parameters / prepared statements (app sends values separately)

Parameters — placeholders for values

Parameterized queries use ? or @name placeholders. The database treats user values as data, not as SQL commands.

Real-life example: Parameters are numbered slots on a form — you write the answer in box 1, you cannot rewrite the form itself.

-- JDBC / many drivers
-- SELECT * FROM students WHERE city = ?

-- SQL Server
-- SELECT * FROM students WHERE city = @city

-- PostgreSQL
-- SELECT * FROM students WHERE city = $1

Prepared Statements — compile once, run many

Prepared statements parse SQL once, then bind new values for each run. They stop injection and can improve performance.

Real-life example: Prepared statements are a stamp with blank fields — same template, different name each time, shape never changes.

-- Step 1: prepare
PREPARE student_by_city (TEXT) AS
  SELECT student_id, full_name FROM students WHERE city = $1;

-- Step 2: execute with different cities
EXECUTE student_by_city('Delhi');
EXECUTE student_by_city('Mumbai');

Hosting — where databases live in production

Local SQLite or Docker is fine for learning. Production apps use hosted MySQL, PostgreSQL, or SQL Server on AWS RDS, Azure, PlanetScale, Supabase, etc.

You connect with a connection string (host, port, database, user, password). Never commit passwords to Git.

Real-life example: Hosting is renting a bank vault for school records instead of keeping papers in your bedroom drawer.

• Free tiers: Supabase (PostgreSQL), Neon, Railway, local XAMPP/WAMP for MySQL
• Always enable SSL/TLS for remote connections
• Use read-only accounts for reporting tools
• Schedule automated backups on the provider dashboard

-- postgresql://USER:PASSWORD@HOST:5432/school_db
-- mysql://USER:PASSWORD@HOST:3306/school_db

-- App env vars (example names)
-- DATABASE_URL=...
-- DB_HOST=...
-- DB_USER=...
-- DB_PASSWORD=...

Data Types overview

Pick types that match your data. Wrong types waste space or break math — storing scores as VARCHAR prevents AVG().

Real-life example: Data types are choosing box sizes — letters go in envelopes, bricks need crates, not the other way around.

• INT / BIGINT — whole numbers (student_id, count)
• DECIMAL(p,s) — exact decimals (money, scores)
• VARCHAR(n) — text up to n characters (names, cities)
• DATE / DATETIME / TIMESTAMP — calendar and clock
• BOOLEAN — true/false flags (is_active)

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  grade_level SMALLINT,
  gpa DECIMAL(3, 2),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Keywords quick reference

These words appear throughout SQL documentation and this course. They are reserved — avoid using them as table or column names.

Real-life example: Keywords are traffic signals — SELECT means go read, DELETE means stop and think twice.

• DDL: CREATE, ALTER, DROP, TRUNCATE
• DML: SELECT, INSERT, UPDATE, DELETE
• DCL: GRANT, REVOKE (permissions)
• Clauses: FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• Joins: INNER, LEFT, RIGHT, FULL, ON, UNION
• Logic: AND, OR, NOT, IN, BETWEEN, LIKE, EXISTS

SELECT c.course_name, AVG(e.score) AS avg_score
FROM enrollments e
INNER JOIN courses c ON e.course_id = c.course_id
WHERE e.score IS NOT NULL
GROUP BY c.course_name
HAVING AVG(e.score) > 75
ORDER BY avg_score DESC
LIMIT 10;

Database-specific functions — high-level map

Core SQL (SELECT, JOIN, GROUP BY) works everywhere. String, date, and math helper functions differ by vendor.

Check your database docs when a function fails — COALESCE is portable; GETDATE() vs NOW() vs CURRENT_DATE is not.

Real-life example: Functions are regional dialects — 'elevator' vs 'lift' — same idea, different word on MySQL vs SQL Server.

• MySQL: CONCAT(), IFNULL(), NOW(), DATE_FORMAT()
• SQL Server: + concat, ISNULL(), GETDATE(), FORMAT()
• Access: & concat, NZ(), Now(), Format()
• All: COUNT, SUM, AVG, MIN, MAX, COALESCE (mostly)

-- MySQL
SELECT CONCAT(full_name, ' - ', DATE_FORMAT(NOW(), '%Y-%m-%d')) AS label FROM students;

-- SQL Server
SELECT full_name + ' - ' + CONVERT(VARCHAR, GETDATE(), 23) AS label FROM students;

-- PostgreSQL / SQLite (portable)
SELECT full_name || ' - ' || DATE('now') AS label FROM students;

Top N per group

A classic pattern: highest score per course, latest enrollment per student. Often uses ROW_NUMBER() or GROUP BY + MAX.

Real-life example: Top N per group is picking the class captain from each grade — one winner per grade, not one for the whole school.

SELECT e.course_id, MAX(e.score) AS top_score
FROM enrollments e
GROUP BY e.course_id;

Existence checks — who has / who lacks

Use LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS to find students with no enrollments.

Real-life example: Finding who lacks enrollment is the list of students who never signed up for any club.

SELECT s.student_id, s.full_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;

Running totals and rankings

Window functions (where supported) compute rank and running sum without collapsing rows.

Real-life example: Running total is a savings passbook — each line adds to the balance so far.

SELECT
  student_id,
  course_id,
  score,
  RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rank_in_course
FROM enrollments
WHERE score IS NOT NULL;

Conditional aggregation

SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition inside one query.

Real-life example: Conditional aggregation is one survey sheet counting yes/no answers in a single table scan.

SELECT
  course_id,
  SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS honor_count,
  SUM(CASE WHEN score < 90 THEN 1 ELSE 0 END) AS other_count
FROM enrollments
GROUP BY course_id;

Certificate, Quiz & Exercises

Rishtaara covers this path with lesson drills plus MCQ practice at /mcq/sql-mcq.

After each part (queries vs database admin), pause and test yourself without looking at notes.

Real-life example: Quizzes are mock exams before boards — they show gaps before the real interview or project.

• MCQ practice: /mcq/sql-mcq
• Interview prep: /interview/sql-interview
• Full guide article: /knowledge/sql-zero-to-analyst

Syllabus & Study Plan

Week 1: lessons 1–8 (SELECT through aliases). Week 2: 9–16 (JOINs through procedures). Week 3: 17–24 (DDL and security). Week 4: 25–30 (patterns and project).

Real-life example: A study plan is a term syllabus — fixed weekly topics so you finish before placement season.

• Day 1–2: SELECT, WHERE, ORDER BY
• Day 3–4: INSERT/UPDATE/DELETE, aggregates
• Day 5–7: JOINs and GROUP BY
• Weekend: /mcq/sql-mcq set 1

Bootcamp & Training notes

Bootcamps compress this syllabus into 1–2 weeks with daily projects. Self-paced learners should code every example, not just read.

Real-life example: Bootcamp training is cricket nets every morning — repetition beats watching highlights once.

-- Without looking at notes, write:
-- 1) All grade 10 students in Delhi, sorted by name
-- 2) Average score per course with HAVING avg > 70
-- 3) Students with no enrollments (LEFT JOIN pattern)

-- Then check answers in lesson 26 and run them on sample data.

Class average report

Analysts deliver grouped metrics — averages, counts, totals — with clear column aliases for dashboards.

Real-life example: This report is the principal's Monday email — average score per subject last term.

SELECT
  c.course_name AS subject,
  COUNT(e.enrollment_id) AS students_graded,
  ROUND(AVG(e.score), 2) AS class_average,
  MIN(e.score) AS lowest,
  MAX(e.score) AS highest
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name
ORDER BY class_average DESC;

City-wise enrollment summary

Combine students and enrollments to show activity by city — common in marketing and ops reports.

Real-life example: City summary tells the school board which regions send the most enrollments online.

SELECT
  s.city,
  COUNT(DISTINCT s.student_id) AS student_count,
  COUNT(e.enrollment_id) AS total_enrollments,
  AVG(e.score) AS avg_score
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city
ORDER BY total_enrollments DESC;

Honor roll list

Filter, join, and sort for a printable list — score threshold, readable columns, stable ORDER BY.

Real-life example: Honor roll is the framed poster in the hallway — names above 90, sorted alphabetically.

SELECT
  s.full_name,
  c.course_name,
  e.score
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE e.score >= 90
ORDER BY s.full_name, c.course_name;

Drill 1 — INNER JOIN three tables

Write the full student + course + score row. Practice table aliases e, s, c.

Real-life example: Three-table JOIN is matching roll number, subject code, and marks from three separate registers.

SELECT s.full_name, c.course_name, e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

Drill 2 — LEFT JOIN find missing rows

List courses with zero enrollments using LEFT JOIN and WHERE e.enrollment_id IS NULL.

Real-life example: Empty courses are classrooms with lights on but no students — still part of the building tour.

SELECT c.course_id, c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

Drill 3 — SELF JOIN same grade peers

Pair students in the same grade_level without pairing a student with themselves.

Real-life example: Peer pairs for group projects — same class, different people.

SELECT a.full_name AS student_a, b.full_name AS student_b
FROM students a
INNER JOIN students b
  ON a.grade_level = b.grade_level
 AND a.student_id < b.student_id
WHERE a.grade_level = 10;

Drill 4 — JOIN + GROUP BY

Per student: how many courses and average score. Requires JOIN then aggregation.

Real-life example: Student transcript summary — three courses, 82.5 average — one line per student.

SELECT
  s.full_name,
  COUNT(e.enrollment_id) AS courses_taken,
  AVG(e.score) AS gpa_courses
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.full_name
ORDER BY gpa_courses DESC;

Project setup — sample school database

Build a mini school DB: students, courses, teachers, enrollments. Seed realistic rows. Run all capstone queries locally.

Real-life example: Mini project is a term-end practical exam — build the schema, insert data, answer five questions with SQL.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  grade_level INT,
  city VARCHAR(80)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100),
  credits INT
);

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students VALUES
  (1, 'Aisha Khan', 10, 'Delhi'),
  (2, 'Ravi Patel', 11, 'Ahmedabad'),
  (3, 'Meera Singh', 10, 'Pune');

INSERT INTO courses VALUES
  (1, 'Mathematics', 4),
  (2, 'Physics', 4),
  (3, 'English', 3);

INSERT INTO enrollments VALUES
  (101, 1, 1, 92.0),
  (102, 1, 3, 88.5),
  (103, 2, 2, 76.0),
  (104, 3, 1, 95.0);

Question 1 — Who tops each course?

Find the highest score per course with student name. Use JOIN + window RANK or subquery pattern.

Real-life example: Course toppers for the annual day announcement — one name per subject.

WITH ranked AS (
  SELECT
    c.course_name,
    s.full_name,
    e.score,
    RANK() OVER (PARTITION BY c.course_id ORDER BY e.score DESC) AS rk
  FROM enrollments e
  JOIN students s ON e.student_id = s.student_id
  JOIN courses c ON e.course_id = c.course_id
)
SELECT course_name, full_name, score
FROM ranked
WHERE rk = 1;

Question 2 — Students below class average in any course

Compare each enrollment score to that course's average using a subquery or JOIN to aggregated stats.

Real-life example: Remedial class list — anyone below their subject's average gets extra help.

SELECT s.full_name, c.course_name, e.score, avg_scores.avg_score
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_id
) avg_scores ON e.course_id = avg_scores.course_id
WHERE e.score < avg_scores.avg_score;

Question 3 — Final dashboard query

One query pipeline: city, student count, average score, honor count (90+). Deliver as a view for reuse.

Real-life example: Dashboard is the principal's single-screen summary before the board meeting.

CREATE VIEW city_dashboard AS
SELECT
  s.city,
  COUNT(DISTINCT s.student_id) AS students,
  ROUND(AVG(e.score), 2) AS avg_score,
  SUM(CASE WHEN e.score >= 90 THEN 1 ELSE 0 END) AS honor_rows
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city;

SELECT * FROM city_dashboard ORDER BY avg_score DESC;

Project checklist

You finished this SQL syllabus when you can write these without notes and explain JOIN vs WHERE vs HAVING.

Real-life example: Checklist is packing for graduation — schema, queries, /mcq/sql-mcq, and one portfolio screenshot.

• Create schema with PRIMARY KEY and FOREIGN KEY
• Insert at least 10 students and 15 enrollments
• Write 5 reports: inner join, left join, group by, having, case
• Add one INDEX and explain why
• Complete /mcq/sql-mcq and review wrong answers