📢
Admissions Open | Free Career Counselling | Limited Scholarships
Register Now →

Learning Guides

SQL Commands: Cheat Sheet + 50 Practice Questions with Answers

SQL commands cheat sheet for DDL, DQL, DML, DCL and TCL, plus 50 practice questions with answers tested on PostgreSQL and SQLite. Free dataset included.

SQL commands cheat sheet for DDL, DQL, DML, DCL and TCL, plus 50 practice questions with answers tested on PostgreSQL and SQLite. Free dataset included.

You've read the SQL commands list five times.

Then someone asks you to "find students who never paid" and your mind goes blank.

That's normal. Reading syntax is not the same as writing queries. The fix is a clean reference you can trust, plus enough practice that the patterns stick.

This guide gives you both:

  • A one-page SQL commands cheat sheet for all five command types
  • The "TRUNCATE can't be rolled back" myth, checked against official documentation for three databases and tested on PostgreSQL 16
  • 50 practice SQL questions on one small dataset, every answer run on PostgreSQL 16 and SQLite 3.53

SQL commands are instructions you send to a relational database to create tables, add or change data, read data, control who can access it and manage transactions. They fall into five groups: DDL, DQL, DML, DCL and TCL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE and ALTER TABLE are the commands most people use every day.

Quick answer: SQL commands (November 2026)

  • DDL commands (CREATE, ALTER, DROP, TRUNCATE) define and change table structure.
  • DQL has one command, SELECT, which reads data with clauses like WHERE, GROUP BY, HAVING and ORDER BY.
  • DML commands (INSERT, UPDATE, DELETE) add, change and remove rows.
  • DCL commands (GRANT, REVOKE) control access, and TCL commands (COMMIT, ROLLBACK, SAVEPOINT) manage transactions.
  • TRUNCATE can be rolled back inside a transaction in PostgreSQL and SQL Server, but not in MySQL or Oracle, where it commits implicitly.
  • SQL was used by 58.4% of developers in the Stack Overflow 2026 Developer Survey, second only to JavaScript.

Contents

  1. What Are SQL Commands?
  2. The 5 Types of SQL Commands
  3. SQL Commands Cheat Sheet
  4. The Daily Dozen: 12 SQL Commands Analysts Use Most
  5. DELETE vs TRUNCATE vs DROP (and the Rollback Myth)
  6. SQL Commands in MySQL vs PostgreSQL vs SQL Server
  7. Practice SQL: Set Up the Dataset
  8. 50 SQL Practice Questions with Answers
  9. 5 Mistakes Beginners Make When Writing SQL
  10. Now It's Your Turn
  11. FAQ

What Are SQL Commands?

SQL commands are the statements a database management system (DBMS) understands, such as SELECT to read rows or CREATE TABLE to build a new table. Every relational database (MySQL, PostgreSQL, Microsoft SQL Server, Oracle, SQLite) uses the same core commands, with small differences in syntax.

SQL is still one of the most used languages in tech. In the Stack Overflow 2026 Developer Survey, 58.4% of respondents said they used SQL, just behind JavaScript at 62.0%. The same survey put PostgreSQL first among databases at about 58%, ahead of SQLite (about 42%) and MySQL (about 35%).

So the commands in this guide are not theory for exams. They're what data analysts, backend developers and data scientists type every working day.

A command is made of three parts:

  • Keyword: the action, like SELECT or UPDATE.
  • Clauses: extra instructions, like WHERE or ORDER BY. Our guide to clauses in SQL covers them one by one.
  • Arguments: the table names, columns and values you pass in.

Two rules apply everywhere. SQL keywords are not case-sensitive (select works like SELECT), and each statement should end with a semicolon.

Note: Table and column names can be case-sensitive depending on the database and operating system. Stick to lowercase names with underscores and you'll never hit this problem.

The 5 Types of SQL Commands

The five types of SQL commands are DDL, DQL, DML, DCL and TCL, grouped by what each command does to the database. Here's the quick map:

Type Full form What it does Commands
DDL Data Definition Language Creates and changes structure CREATE, ALTER, DROP, TRUNCATE, RENAME
DQL Data Query Language Reads data SELECT
DML Data Manipulation Language Adds, changes, removes rows INSERT, UPDATE, DELETE, MERGE
DCL Data Control Language Controls access GRANT, REVOKE
TCL Transaction Control Language Saves or undoes changes COMMIT, ROLLBACK, SAVEPOINT

Now, a detail most cheat sheets skip:

"DQL" is a teaching label, not a universal standard. Oracle's current SQL Language Reference lists SELECT among its DML statements (Oracle Database 23 SQL Language Reference). Older Oracle references describe it as "a limited form of DML" because it reads data without changing it. Many tutorials and Indian college syllabi split it out as DQL instead.

Either classification can work in an interview if you explain the difference. Saying "SELECT is DQL in most textbooks, though Oracle files it under DML" shows you have read the docs.

For a longer walkthrough of each group, see our guide to languages in SQL, plus the deep dives on Data Definition Language and Data Manipulation Language.

SQL Commands Cheat Sheet

This SQL commands cheat sheet lists the syntax you need for each command type, with one working example per line. Every example uses the practice tables you'll build later (courses, students, payments).

Keep it open in a second tab while you work. A good SQL cheat sheet saves you from searching the same syntax ten times a day.

DDL commands: build the structure

Command Syntax Example
CREATE TABLE CREATE TABLE t (col type constraints, ...); CREATE TABLE batches (batch_id INT PRIMARY KEY, start_date DATE NOT NULL);
ALTER TABLE (add) ALTER TABLE t ADD COLUMN col type; ALTER TABLE students ADD COLUMN phone VARCHAR(15);
ALTER TABLE (drop) ALTER TABLE t DROP COLUMN col; ALTER TABLE students DROP COLUMN phone;
RENAME ALTER TABLE t RENAME TO new_t; ALTER TABLE batches RENAME TO cohorts;
TRUNCATE TRUNCATE TABLE t; TRUNCATE TABLE payments;
DROP DROP TABLE t; DROP TABLE IF EXISTS cohorts;
CREATE INDEX CREATE INDEX name ON t (col); CREATE INDEX idx_city ON students (city);

DQL: read data with SELECT

Clause What it does Example
SELECT ... FROM Pick columns from a table SELECT name, city FROM students;
WHERE Filter rows WHERE city = 'Nagpur'
AND / OR / NOT Combine conditions WHERE city = 'Pune' AND course_id = 2
IN / BETWEEN / LIKE Match a list, a range, a pattern WHERE name LIKE 'P%'
IS NULL Find missing values WHERE city IS NULL
DISTINCT Remove duplicate rows SELECT DISTINCT city FROM students;
ORDER BY Sort ORDER BY fee_inr DESC
LIMIT Return the first n rows LIMIT 3
GROUP BY Group rows for totals GROUP BY city
HAVING Filter groups HAVING COUNT(*) > 1
COUNT / SUM / AVG / MIN / MAX Aggregate functions (see multi-row functions in SQL) SELECT SUM(amount_inr) FROM payments;
AS Rename a column or table SUM(amount_inr) AS total

SQL runs these clauses in a fixed logical order, which explains most beginner errors: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't use a SELECT alias inside WHERE.

Joins and combining results

Command Returns Example
INNER JOIN Only matching rows from both tables FROM students s JOIN courses c ON s.course_id = c.course_id
LEFT JOIN All rows from the left table, matches from the right FROM students s LEFT JOIN payments p ON p.student_id = s.student_id
RIGHT JOIN All rows from the right table FROM payments p RIGHT JOIN students s ON ...
FULL OUTER JOIN All rows from both (not in MySQL) FROM a FULL OUTER JOIN b ON ...
UNION / UNION ALL Stack two result sets SELECT ... UNION SELECT ...

Joins deserve their own page. Our SQL joins guide shows every type with examples.

Subqueries, CASE, CTEs and window functions

Feature Syntax Example
Subquery WHERE col > (SELECT ...) WHERE fee_inr > (SELECT AVG(fee_inr) FROM courses)
EXISTS WHERE EXISTS (SELECT 1 FROM ...) Students with at least one cash payment
CASE CASE WHEN ... THEN ... ELSE ... END Label courses Short, Medium or Long
COALESCE First non-NULL value COALESCE(SUM(amount_inr), 0)
CTE WITH name AS (SELECT ...) SELECT ... Total paid per student, then compare to fee
Window function fn() OVER (PARTITION BY ... ORDER BY ...) ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY paid_on)

DML commands: change the data

Command Syntax Example
INSERT INSERT INTO t (cols) VALUES (...); INSERT INTO students (student_id, name, city, join_date, course_id) VALUES (111, 'Riya Sharma', 'Nagpur', '2026-06-01', 5);
UPDATE UPDATE t SET col = value WHERE ...; UPDATE courses SET fee_inr = 16500 WHERE course_id = 3;
DELETE DELETE FROM t WHERE ...; DELETE FROM payments WHERE payment_id = 1012;

DCL and TCL commands: access and transactions

Command Syntax Example
GRANT GRANT privilege ON object TO user; GRANT SELECT ON courses TO report_user;
REVOKE REVOKE privilege ON object FROM user; REVOKE SELECT ON courses FROM report_user;
BEGIN / START TRANSACTION Start a transaction BEGIN;
COMMIT Save all changes since BEGIN COMMIT;
ROLLBACK Undo all changes since BEGIN ROLLBACK;
SAVEPOINT Mark a point you can roll back to SAVEPOINT before_fee_change;

Resource: Bookmark this section, or print it. It covers every command in the 50 practice questions below.

The Daily Dozen: 12 SQL Commands Analysts Use Most

The Daily Dozen is Fireblaze AI School's name for the 12 SQL commands and clauses we suggest learning first, because they cover most of what a junior data analyst writes at work: SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, COUNT/SUM/AVG, INNER JOIN, LEFT JOIN, CASE, a CTE (WITH) and ROW_NUMBER().

Notice what's missing: CREATE, DROP, GRANT. Analysts usually query tables that engineers have already built.

If you're still deciding on the career, our guide on what a data analyst does shows where SQL fits in a real workday. You need to recognise DDL and DCL, but you'll write SELECT queries far more often.

Learners often try to memorise all five command types on day one and forget most of them. A steadier route is to write the Daily Dozen every day for two weeks, then pick up DDL and TCL once the reading patterns feel automatic.

So here's the order we recommend:

  1. Week 1: SELECT, WHERE, ORDER BY, LIMIT, aggregate functions (practice questions 1 to 20).
  2. Week 2: GROUP BY, HAVING, INNER JOIN, LEFT JOIN (questions 21 to 34).
  3. Week 3: CASE, subqueries, CTEs, window functions (questions 35 to 45).
  4. Week 4: DDL, DML, TCL and DCL on a practice database you're allowed to break (questions 46 to 50).

Pro tip: Type every query yourself. Copy-pasting answers feels productive but builds little memory. Retype a query from scratch a day or two after you first solve it.

DELETE vs TRUNCATE vs DROP (and the Rollback Myth)

DELETE removes chosen rows, TRUNCATE removes all rows but keeps the table, and DROP removes the table itself. That part every guide gets right.

Here's what most guides get wrong:

Almost every SQL interview list says "TRUNCATE cannot be rolled back". That's only true in some databases. We checked the official documentation for three databases and ran the test ourselves on PostgreSQL 16.

DELETE TRUNCATE DROP
Type DML DDL DDL
Removes Rows you choose (WHERE) All rows The whole table and its structure
WHERE allowed? Yes No No
Fires ON DELETE triggers? Yes No No
Resets auto-increment / identity? No Yes in MySQL and SQL Server; PostgreSQL only with RESTART IDENTITY Table is gone
Rollback in MySQL 8.4 Yes No, implicit commit No, implicit commit
Rollback in PostgreSQL Yes Yes, inside a transaction Yes, inside a transaction
Rollback in SQL Server Yes Yes, inside a transaction Yes, inside a transaction

The evidence:

  • MySQL: the MySQL 8.4 manual says truncate operations cause an implicit commit "and so cannot be rolled back", and classifies TRUNCATE as DDL (MySQL 8.4 Reference Manual). CREATE, ALTER, DROP and RENAME TABLE also commit implicitly in MySQL (MySQL implicit commit docs).
  • PostgreSQL: the docs call TRUNCATE "transaction-safe": the truncation is rolled back if the surrounding transaction doesn't commit (PostgreSQL TRUNCATE docs).
  • SQL Server: Microsoft's documentation states that a TRUNCATE TABLE operation can be rolled back within a transaction (Microsoft Learn).
  • Oracle: Oracle lists TRUNCATE as DDL, and the database implicitly commits before and after every DDL statement (Oracle SQL Language Reference).

Here's the test we ran on PostgreSQL 16.15, using the practice payments table (12 rows):

BEGIN;
TRUNCATE payments;
SELECT COUNT(*) FROM payments;   -- returns 0
ROLLBACK;
SELECT COUNT(*) FROM payments;   -- returns 12 again

All 12 rows came back.

The same test shows the identity caveat in the table: in PostgreSQL, TRUNCATE does not reset an identity column unless you add RESTART IDENTITY. We confirmed that the next inserted row continued the old sequence.

So the best interview answer is: "It depends on the database. In MySQL and Oracle, TRUNCATE commits implicitly and can't be rolled back. In PostgreSQL and SQL Server, it can be rolled back if it runs inside a transaction."

That one answer shows you know SQL beyond a single tutorial. Interviewers notice.

Important: Never test TRUNCATE or DROP on a shared or production database "to see what happens". Use a local practice copy.

SQL Commands in MySQL vs PostgreSQL vs SQL Server

SQL commands are about 90% the same across databases, but a handful of everyday tasks use different syntax. These are the differences that trip up learners when they move from a course to a job:

Task MySQL 8.4 PostgreSQL SQL Server
First 5 rows LIMIT 5 LIMIT 5 SELECT TOP 5 ... or OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
Auto-number column INT AUTO_INCREMENT INT GENERATED ALWAYS AS IDENTITY INT IDENTITY(1,1)
Join text CONCAT(a, b) CONCAT(a, b) or a \|\| b CONCAT(a, b) or a + b
Case-insensitive search LIKE (default collations ignore case) ILIKE LIKE (default collations ignore case)
Year from a date YEAR(d) EXTRACT(YEAR FROM d) YEAR(d)
Format as YYYY-MM DATE_FORMAT(d, '%Y-%m') TO_CHAR(d, 'YYYY-MM') FORMAT(d, 'yyyy-MM')
FULL OUTER JOIN Not supported (use UNION of LEFT and RIGHT) Supported Supported
Start a transaction START TRANSACTION; BEGIN; BEGIN TRANSACTION;

Which one should you learn first?

For data analyst roles, either MySQL or PostgreSQL is a sound start. PostgreSQL ranked first among databases in the Stack Overflow 2026 survey, and MySQL is also widely used. Because most differences are the syntax shown in the table above, switching later is usually a matter of days of adjustment rather than starting again.

If you're installing MySQL today, pick MySQL 8.4 LTS. MySQL 8.0 reached end of life in April 2026 with its final release, 8.0.46, and Oracle encourages users to move to 8.4 LTS or a later supported release (MySQL 8.0 Release Notes).

Practice SQL: Set Up the Dataset

The fastest way to practice SQL is one small, realistic dataset you can query again and again. Ours is a mini training institute: 5 courses, 10 students and 12 payments in Indian rupees.

It's deliberately small so you can check every answer by eye. It also has a few traps built in: a student with no city (NULL), a student with no course, a course with no students and students with more than one payment.

Copy this script into any of these free options:

  • SQLite: any online SQLite editor, or DB Browser for SQLite on your laptop.
  • PostgreSQL: a local install with pgAdmin, or any free online Postgres sandbox.
  • MySQL 8.4: MySQL Workbench on your laptop.
CREATE TABLE courses (
  course_id      INT PRIMARY KEY,
  course_name    VARCHAR(60) NOT NULL,
  fee_inr        INT NOT NULL,
  duration_weeks INT NOT NULL
);

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(60) NOT NULL,
  city       VARCHAR(40),
  join_date  DATE NOT NULL,
  course_id  INT REFERENCES courses(course_id)
);

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  student_id INT NOT NULL REFERENCES students(student_id),
  amount_inr INT NOT NULL,
  paid_on    DATE NOT NULL,
  mode       VARCHAR(10) NOT NULL
);

INSERT INTO courses VALUES
 (1, 'Data Analytics',   45000, 16),
 (2, 'Data Science',     85000, 24),
 (3, 'Power BI',         15000,  6),
 (4, 'Full Stack MERN',  60000, 20),
 (5, 'SQL Foundations',   8000,  4);

INSERT INTO students VALUES
 (101, 'Aarav Deshmukh', 'Nagpur',   '2026-01-12', 1),
 (102, 'Sneha Patil',    'Pune',     '2026-01-20', 2),
 (103, 'Rohan Kulkarni', 'Nagpur',   '2026-02-03', 3),
 (104, 'Priya Wankhede', 'Amravati', '2026-02-15', 1),
 (105, 'Karan Mehta',    'Mumbai',   '2026-03-01', 4),
 (106, 'Ananya Joshi',   'Nagpur',   '2026-03-10', 2),
 (107, 'Vikram Rao',     NULL,       '2026-03-22', 3),
 (108, 'Neha Bhoyar',    'Wardha',   '2026-04-05', 1),
 (109, 'Ishaan Gupta',   'Pune',     '2026-04-18', NULL),
 (110, 'Pooja Thakre',   'Nagpur',   '2026-05-02', 4);

INSERT INTO payments VALUES
 (1001, 101, 20000, '2026-01-12', 'UPI'),
 (1002, 101, 25000, '2026-02-12', 'UPI'),
 (1003, 102, 40000, '2026-01-20', 'Card'),
 (1004, 103, 15000, '2026-02-03', 'Cash'),
 (1005, 104, 15000, '2026-02-15', 'UPI'),
 (1006, 105, 30000, '2026-03-01', 'Card'),
 (1007, 106, 85000, '2026-03-10', 'NetBanking'),
 (1008, 107,  7500, '2026-03-22', 'UPI'),
 (1009, 108, 45000, '2026-04-05', 'UPI'),
 (1010, 110, 20000, '2026-05-02', 'Cash'),
 (1011, 102, 20000, '2026-02-20', 'Card'),
 (1012, 105, 30000, '2026-04-01', 'UPI');

Note: The names and numbers are made up for practice. They are not real Fireblaze students or fees.

50 SQL Practice Questions with Answers

The 50 SQL practice questions below start with simple SELECT queries and finish with window functions and transactions. We ran every answer on PostgreSQL 16 and SQLite 3.53. All 50 run without error. Two need a note: Q46 to Q49 change data, so run them on a copy, and Q50 needs the role report_user to exist first on PostgreSQL. SQLite has no GRANT. Where a question has no ORDER BY, we added one so the rows come back in the same order everywhere.

Try each question yourself before reading the answer. The expected result lets you check your output in seconds.

Level 1: SELECT, WHERE, ORDER BY and LIMIT (Questions 1 to 10)

These questions cover reading and filtering rows, the base of every SQL query.

Q1. Show every column for every student.

SELECT * FROM students;

Expected: 10 rows.

Q2. Show only the name and city of each student.

SELECT name, city FROM students;

Expected: 10 rows, with Vikram Rao's city empty (NULL).

Q3. List all students from Nagpur.

SELECT name FROM students WHERE city = 'Nagpur';

Expected (4 rows): Aarav Deshmukh; Rohan Kulkarni; Ananya Joshi; Pooja Thakre.

Q4. List courses that cost more than ₹40,000.

SELECT course_name, fee_inr FROM courses WHERE fee_inr > 40000;

Expected (3 rows): Data Analytics, 45000; Data Science, 85000; Full Stack MERN, 60000.

Q5. Show all courses sorted from the most expensive to the cheapest.

SELECT course_name, fee_inr FROM courses ORDER BY fee_inr DESC;

Expected (5 rows): Data Science, 85000; Full Stack MERN, 60000; Data Analytics, 45000; Power BI, 15000; SQL Foundations, 8000.

Q6. Show the 3 most recent students to join.

SELECT name, join_date FROM students ORDER BY join_date DESC LIMIT 3;

Expected (3 rows): Pooja Thakre, 2026-05-02; Ishaan Gupta, 2026-04-18; Neha Bhoyar, 2026-04-05. SQL Server: SELECT TOP 3 ... ORDER BY join_date DESC.

Q7. Find students whose name starts with 'P'.

SELECT name FROM students WHERE name LIKE 'P%';

Expected (2 rows): Priya Wankhede; Pooja Thakre. LIKE is case-sensitive in PostgreSQL; use ILIKE there for case-insensitive search.

Q8. Find students who joined between 1 February and 31 March 2026.

SELECT name, join_date FROM students WHERE join_date BETWEEN '2026-02-01' AND '2026-03-31';

Expected (5 rows): Rohan Kulkarni, 2026-02-03; Priya Wankhede, 2026-02-15; Karan Mehta, 2026-03-01; Ananya Joshi, 2026-03-10; Vikram Rao, 2026-03-22.

Q9. List students from Pune, Mumbai or Wardha.

SELECT name, city FROM students WHERE city IN ('Pune', 'Mumbai', 'Wardha');

Expected (4 rows): Sneha Patil, Pune; Karan Mehta, Mumbai; Neha Bhoyar, Wardha; Ishaan Gupta, Pune.

Q10. Find students with no city recorded.

SELECT name FROM students WHERE city IS NULL;

Expected (1 row): Vikram Rao. Never write city = NULL. It returns no rows.

Pro tip: If Q10 returned nothing, check that you wrote IS NULL. = NULL is never true in SQL, because NULL means unknown.

Level 2: DISTINCT, Aggregates and Aliases (Questions 11 to 20)

Aggregate functions turn many rows into one number: a count, a total, an average.

Q11. List each city only once.

SELECT DISTINCT city FROM students WHERE city IS NOT NULL ORDER BY city;

Expected (5 rows): Amravati; Mumbai; Nagpur; Pune; Wardha.

Q12. How many students are there?

SELECT COUNT(*) AS total_students FROM students;

Expected (1 row): 10.

Q13. How many students have a city recorded?

SELECT COUNT(city) AS with_city FROM students;

Expected (1 row): 9. COUNT(column) skips NULLs. COUNT(*) does not.

Q14. What is the total fee collected so far?

SELECT SUM(amount_inr) AS total_collected FROM payments;

Expected (1 row): 352500.

Q15. What is the average course fee?

SELECT AVG(fee_inr) AS avg_fee FROM courses;

Expected (1 row): 42600.

Q16. What are the cheapest and the most expensive course fees?

SELECT MIN(fee_inr) AS cheapest, MAX(fee_inr) AS costliest FROM courses;

Expected (1 row): 8000, 85000.

Q17. Show each course with its fee per week, rounded to the nearest rupee.

SELECT course_name, ROUND(fee_inr * 1.0 / duration_weeks) AS fee_per_week FROM courses;

Expected (5 rows): Data Analytics, 2813; Data Science, 3542; Power BI, 2500; Full Stack MERN, 3000; SQL Foundations, 2000. Multiply by 1.0 to avoid integer division in PostgreSQL and SQLite.

Q18. Show each student's name and city as one text like 'Aarav Deshmukh (Nagpur)'.

SELECT CONCAT(name, ' (', city, ')') AS label FROM students WHERE city IS NOT NULL;

Expected: 9 rows, starting with Aarav Deshmukh (Nagpur). CONCAT works in MySQL, PostgreSQL, SQL Server and SQLite 3.44+.

Q19. How many payments were made by UPI?

SELECT COUNT(*) AS upi_payments FROM payments WHERE mode = 'UPI';

Expected (1 row): 6.

Q20. What is the largest single payment?

SELECT MAX(amount_inr) AS biggest_payment FROM payments;

Expected (1 row): 85000.

Level 3: GROUP BY and HAVING (Questions 21 to 27)

GROUP BY splits rows into groups so aggregates run per city, per mode or per month.

Q21. Count students in each city.

SELECT city, COUNT(*) AS students FROM students GROUP BY city ORDER BY students DESC, city NULLS LAST;

Expected (6 rows): Nagpur, 4; Pune, 2; Amravati, 1; Mumbai, 1; Wardha, 1; and a final row with an empty city (NULL), 1. NULL forms its own group.

Q22. Total amount collected per payment mode.

SELECT mode, SUM(amount_inr) AS total FROM payments GROUP BY mode ORDER BY total DESC;

Expected (4 rows): UPI, 142500; Card, 90000; NetBanking, 85000; Cash, 35000.

Q23. Which cities have more than one student?

SELECT city, COUNT(*) AS students FROM students GROUP BY city HAVING COUNT(*) > 1 ORDER BY students DESC, city;

Expected (2 rows): Nagpur, 4; Pune, 2. WHERE filters rows before grouping; HAVING filters groups after.

Q24. Total paid by each student, highest first.

SELECT student_id, SUM(amount_inr) AS paid FROM payments GROUP BY student_id ORDER BY paid DESC, student_id;

Expected (9 rows): 106, 85000; 102, 60000; 105, 60000; 101, 45000; 108, 45000; 110, 20000; 103, 15000; 104, 15000; 107, 7500.

Q25. Which students made more than one payment?

SELECT student_id, COUNT(*) AS payments FROM payments GROUP BY student_id HAVING COUNT(*) > 1 ORDER BY student_id;

Expected (3 rows): 101, 2; 102, 2; 105, 2.

Q26. Number of students who joined in each month of 2026.

SELECT SUBSTR(CAST(join_date AS VARCHAR(10)), 1, 7) AS join_month, COUNT(*) AS students FROM students GROUP BY SUBSTR(CAST(join_date AS VARCHAR(10)), 1, 7) ORDER BY join_month;

Expected (5 rows): 2026-01, 2; 2026-02, 2; 2026-03, 3; 2026-04, 2; 2026-05, 1. Portable version. In MySQL use DATE_FORMAT(join_date, '%Y-%m'); in PostgreSQL TO_CHAR(join_date, 'YYYY-MM').

Q27. Payment modes whose average payment is above ₹25,000.

SELECT mode, AVG(amount_inr) AS avg_payment FROM payments GROUP BY mode HAVING AVG(amount_inr) > 25000 ORDER BY mode;

Expected (2 rows): Card, 30000; NetBanking, 85000.

Level 4: SQL Joins (Questions 28 to 34)

Joins combine tables. Most real analyst questions need at least one.

Q28. Show each student with the name of their course.

SELECT s.name, c.course_name FROM students s INNER JOIN courses c ON s.course_id = c.course_id;

Expected: 9 rows. INNER JOIN drops Ishaan (no course).

Q29. Show every student and their course, including students with no course.

SELECT s.name, c.course_name FROM students s LEFT JOIN courses c ON s.course_id = c.course_id;

Expected: 10 rows, with Ishaan Gupta's course empty (NULL).

Q30. Which courses have no students enrolled?

SELECT c.course_name FROM courses c LEFT JOIN students s ON s.course_id = c.course_id WHERE s.student_id IS NULL;

Expected (1 row): SQL Foundations.

Q31. Which students have never made a payment?

SELECT s.name FROM students s LEFT JOIN payments p ON p.student_id = s.student_id WHERE p.payment_id IS NULL;

Expected (1 row): Ishaan Gupta.

Q32. Count students in each course, including courses with zero.

SELECT c.course_name, COUNT(s.student_id) AS students FROM courses c LEFT JOIN students s ON s.course_id = c.course_id GROUP BY c.course_name ORDER BY students DESC, c.course_name;

Expected (5 rows): Data Analytics, 3; Data Science, 2; Full Stack MERN, 2; Power BI, 2; SQL Foundations, 0. Count s.student_id, not *, or empty courses show 1.

Q33. Total fees collected per course.

SELECT c.course_name, SUM(p.amount_inr) AS collected FROM payments p JOIN students s ON s.student_id = p.student_id JOIN courses c ON c.course_id = s.course_id GROUP BY c.course_name ORDER BY collected DESC;

Expected (4 rows): Data Science, 145000; Data Analytics, 105000; Full Stack MERN, 80000; Power BI, 22500. Three-table join.

Q34. Show each student's outstanding balance (course fee minus total paid).

SELECT s.name, c.fee_inr - COALESCE(SUM(p.amount_inr), 0) AS balance FROM students s JOIN courses c ON c.course_id = s.course_id LEFT JOIN payments p ON p.student_id = s.student_id GROUP BY s.name, c.fee_inr ORDER BY balance DESC, s.name;

Expected (9 rows): Pooja Thakre, 40000; Priya Wankhede, 30000; Sneha Patil, 25000; Vikram Rao, 7500; then five students with a balance of 0. COALESCE turns NULL (no payments) into 0.

Bottom line: If you can solve Q31 to Q34 without looking, you can handle most join questions in a junior data analyst interview.

Level 5: Subqueries, CASE, CTEs and UNION (Questions 35 to 41)

These tools let one query answer a question that needs two steps.

Q35. Which courses cost more than the average course fee?

SELECT course_name, fee_inr FROM courses WHERE fee_inr > (SELECT AVG(fee_inr) FROM courses);

Expected (3 rows): Data Analytics, 45000; Data Science, 85000; Full Stack MERN, 60000.

Q36. List students enrolled in the most expensive course.

SELECT name FROM students WHERE course_id = (SELECT course_id FROM courses ORDER BY fee_inr DESC LIMIT 1);

Expected (2 rows): Sneha Patil; Ananya Joshi.

Q37. Label each course 'Short' (up to 6 weeks), 'Medium' (up to 16) or 'Long'.

SELECT course_name, CASE WHEN duration_weeks <= 6 THEN 'Short' WHEN duration_weeks <= 16 THEN 'Medium' ELSE 'Long' END AS length FROM courses;

Expected (5 rows): Data Analytics, Medium; Data Science, Long; Power BI, Short; Full Stack MERN, Long; SQL Foundations, Short.

Q38. Show students who have paid their full course fee.

WITH paid AS (SELECT student_id, SUM(amount_inr) AS total_paid FROM payments GROUP BY student_id) SELECT s.name FROM students s JOIN courses c ON c.course_id = s.course_id JOIN paid ON paid.student_id = s.student_id WHERE paid.total_paid >= c.fee_inr;

Expected (5 rows): Aarav Deshmukh; Rohan Kulkarni; Karan Mehta; Ananya Joshi; Neha Bhoyar. WITH creates a named temporary result (a CTE).

Q39. Find students who paid in cash at least once, using EXISTS.

SELECT s.name FROM students s WHERE EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.student_id AND p.mode = 'Cash');

Expected (2 rows): Rohan Kulkarni; Pooja Thakre.

Q40. Combine Nagpur students and Pune students into one list without duplicates.

SELECT name, city FROM students WHERE city = 'Nagpur' UNION SELECT name, city FROM students WHERE city = 'Pune' ORDER BY city, name;

Expected (6 rows): Aarav Deshmukh, Nagpur; Ananya Joshi, Nagpur; Pooja Thakre, Nagpur; Rohan Kulkarni, Nagpur; Ishaan Gupta, Pune; Sneha Patil, Pune. UNION removes duplicates; UNION ALL keeps them and is faster.

Q41. Count payments as digital (UPI, Card, NetBanking) vs cash.

SELECT CASE WHEN mode = 'Cash' THEN 'Cash' ELSE 'Digital' END AS channel, COUNT(*) AS payments FROM payments GROUP BY CASE WHEN mode = 'Cash' THEN 'Cash' ELSE 'Digital' END ORDER BY channel;

Expected (2 rows): Cash, 2; Digital, 10.

Level 6: Window Functions (Questions 42 to 45)

Window functions calculate across rows without collapsing them, which makes rankings and running totals easy.

Q42. Rank courses by fee, highest first.

SELECT course_name, fee_inr, RANK() OVER (ORDER BY fee_inr DESC) AS fee_rank FROM courses;

Expected (5 rows): Data Science, 85000, 1; Full Stack MERN, 60000, 2; Data Analytics, 45000, 3; Power BI, 15000, 4; SQL Foundations, 8000, 5. Window functions need MySQL 8.0+, PostgreSQL, SQL Server 2012+ or SQLite 3.25+.

Q43. Show each payment with a running total by date.

SELECT payment_id, paid_on, amount_inr, SUM(amount_inr) OVER (ORDER BY paid_on, payment_id) AS running_total FROM payments ORDER BY paid_on, payment_id;

Expected: 12 rows, ending with a running total of 352500.

Q44. Number each student's payments in date order (1st, 2nd...).

SELECT student_id, paid_on, amount_inr, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY paid_on) AS payment_no FROM payments ORDER BY student_id, payment_no;

Expected: 12 rows. Students 101, 102 and 105 show payment numbers 1 and 2.

Q45. Show each student's first payment only.

WITH ranked AS (SELECT student_id, paid_on, amount_inr, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY paid_on) AS rn FROM payments) SELECT student_id, paid_on, amount_inr FROM ranked WHERE rn = 1 ORDER BY student_id;

Expected (9 rows): 101, 2026-01-12, 20000; 102, 2026-01-20, 40000; 103, 2026-02-03, 15000; 104, 2026-02-15, 15000; 105, 2026-03-01, 30000; 106, 2026-03-10, 85000; 107, 2026-03-22, 7500; 108, 2026-04-05, 45000; 110, 2026-05-02, 20000. A classic interview pattern: ROW_NUMBER inside a CTE, then filter rn = 1.

Level 7: DDL, DML, TCL and DCL Commands (Questions 46 to 50)

Run these on your practice copy only. Wrap them in BEGIN and ROLLBACK if you want to undo them.

Q46. Create a table called batches with an id, a course_id and a start_date.

CREATE TABLE batches (batch_id INT PRIMARY KEY, course_id INT REFERENCES courses(course_id), start_date DATE NOT NULL);

DDL. MySQL commits it immediately, even inside a transaction.

Q47. Add a phone column to students.

ALTER TABLE students ADD COLUMN phone VARCHAR(15);

DDL.

Q48. Add a new student, Riya Sharma from Nagpur, joining SQL Foundations on 1 June 2026.

INSERT INTO students (student_id, name, city, join_date, course_id) VALUES (111, 'Riya Sharma', 'Nagpur', '2026-06-01', 5);

DML.

Q49. Raise the Power BI fee by 10%, then undo it.

BEGIN;
UPDATE courses SET fee_inr = fee_inr * 1.1 WHERE course_name = 'Power BI';
ROLLBACK;

TCL. Without WHERE, every course would get the raise. In PostgreSQL, the Power BI fee reads 16500 inside the transaction and 15000 again after ROLLBACK.

Q50. Give a reporting user read-only access to the courses table.

GRANT SELECT ON courses TO report_user;

DCL. Create the user first with CREATE USER (MySQL) or CREATE ROLE (PostgreSQL); SQLite has no users, so it has no GRANT.

5 Mistakes Beginners Make When Writing SQL

The most common SQL command mistakes are small syntax habits that give wrong answers without any error message. That's what makes them dangerous. Here are the five that cause the most trouble for learners:

  1. UPDATE or DELETE without WHERE. DELETE FROM payments; empties the whole table. Write the WHERE clause first, run it as a SELECT to see which rows match, then change SELECT to DELETE.
  2. Comparing with = NULL. WHERE city = NULL returns nothing. Use IS NULL or IS NOT NULL.
  3. Using WHERE instead of HAVING for totals. WHERE COUNT(*) > 1 fails, because WHERE runs before grouping. Filter aggregates with HAVING.
  4. *COUNT() after a LEFT JOIN. It counts the empty row too, so a course with no students shows 1. Count a column from the right table instead, as in Q32.
  5. Selecting columns that aren't grouped. SELECT name, city, COUNT(*) ... GROUP BY city breaks in PostgreSQL and SQL Server, and gives unpredictable names in older MySQL setups. Every selected column must be grouped or aggregated.

Pro tip: Before any UPDATE or DELETE on real data, type BEGIN; first. If the row count looks wrong, ROLLBACK;. Just remember from the table above that DDL commands like TRUNCATE commit immediately in MySQL.

Now It's Your Turn

SQL commands come in five groups, but you don't need all of them on day one. Learn the Daily Dozen, practice on one small dataset until the patterns feel automatic, and pick up DDL, DCL and TCL as you go.

Start with Q1 today. Aim for ten questions a day, and you'll finish all 50 within a week.

When you're ready for interview-level problems, move on to our SQL interview questions for data analysts and the broader data analyst interview questions.

And if you'd rather learn SQL commands with a trainer, live projects and feedback on every query, take a look at the SQL course in Nagpur at Fireblaze AI School, available in the classroom or live online.

Which question tripped you up first?

FAQ

What are the basic SQL commands?

The basic SQL commands are SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE and DROP TABLE. SELECT reads data, INSERT, UPDATE and DELETE change rows, and CREATE, ALTER and DROP manage table structure. Most beginners learn SELECT with WHERE and ORDER BY first.

How many types of SQL commands are there?

There are five types of SQL commands: DDL (Data Definition Language), DQL (Data Query Language), DML (Data Manipulation Language), DCL (Data Control Language) and TCL (Transaction Control Language). Oracle's documentation counts SELECT as DML, which gives four types instead of five.

Is SELECT a DML or DQL command?

SELECT is usually taught as the only DQL command, because it reads data without changing it. Oracle's current SQL Language Reference lists SELECT among its DML statements, and older Oracle references call it a limited form of DML. Either answer can work in an interview if you explain the difference.

Can TRUNCATE be rolled back?

TRUNCATE can be rolled back in PostgreSQL and SQL Server when it runs inside a transaction. In MySQL and Oracle it causes an implicit commit, so it cannot be rolled back. DELETE can be rolled back in all four databases.

How can I practice SQL commands for free?

You can practice SQL commands for free in an online SQLite editor, or by installing PostgreSQL or MySQL 8.4 on your laptop. Load a small dataset like the one in this guide, then solve practice questions from easy to hard. Writing queries daily matters more than which database you choose.

Are SQL commands case-sensitive?

SQL keywords like SELECT and WHERE are not case-sensitive, so select and SELECT work the same. Text values inside quotes can be case-sensitive depending on the database: PostgreSQL's LIKE is case-sensitive, while MySQL's default collations ignore case.

Sources

  1. Stack Overflow: The results of the 2026 Developer Survey are here, Stack Overflow, October 2026
  2. Oracle Database 23 SQL Language Reference: Types of SQL Statements, Oracle
  3. MySQL 8.4 Reference Manual: TRUNCATE TABLE, Oracle
  4. MySQL 8.4 Reference Manual: Statements That Cause an Implicit Commit, Oracle
  5. PostgreSQL Documentation: TRUNCATE, PostgreSQL Global Development Group
  6. Microsoft Learn: TRUNCATE TABLE (Transact-SQL), Microsoft
  7. MySQL 8.0 Release Notes (end of life notice), Oracle, 2026