SQL basics

One 60-second card per skill, in learning order. 10 of 10 cards ready.

Topic 1

SELECT and WHERE

Choosing columns · Filtering rows · AND, OR and IN

Open the lesson Practise this topic

60-second card

Choosing columns

SELECT chooses which columns you see; FROM says which table they come from.

SELECT name, city FROM students; shows two columns.
SELECT * shows every column.
DISTINCT removes repeated rows: SELECT DISTINCT city.

SELECT DISTINCT city FROM students ORDER BY city;

Result

Chennai
Delhi
Pune

Remember: List only the columns you need; it's clearer and faster.

Common mistake: Putting commas wrongly, like SELECT name city. That shows name, renamed to 'city'.

60-second card

Filtering rows

WHERE keeps only the rows that match a condition.

Compare with =, <>, >, <, >= and <=.
Text goes in single quotes: WHERE city = 'Pune'.
LIKE 'D%' matches text starting with D; BETWEEN 60 AND 80 includes both ends.

SELECT name FROM students WHERE marks > 80;

Result

Asha
Meena
Divya

Remember: WHERE filters rows before anything else happens.

Common mistake: Writing WHERE city = Pune without quotes. SQL thinks Pune is a column name.

60-second card

AND, OR and IN

Combine conditions with AND (both true) and OR (either true). IN checks against a list.

city = 'Pune' AND marks > 85 needs both.
city IN ('Pune', 'Delhi') is a short way to write several ORs.
AND is checked before OR, so use brackets to be clear.

SELECT name FROM students WHERE city IN ('Pune', 'Delhi') AND marks > 85;

Result

Meena
Divya

Remember: When you mix AND and OR, add brackets.

Common mistake: Writing city = 'Pune' OR 'Delhi'. Each side needs its own comparison, or use IN.

Topic 2

Sorting and limits

Sorting · Top results

Open the lesson Practise this topic

60-second card

Sorting

ORDER BY sorts the result by one or more columns.

ASC (the default) is smallest first; DESC is largest first.
Sort by several columns: ORDER BY city, marks DESC.
Without ORDER BY, the row order isn't guaranteed.

SELECT name, marks FROM students ORDER BY marks DESC;

Result

Meena | 91
Divya | 88
Asha | 82
Karan | 74
Ravi | 67

Remember: If the order matters, always write ORDER BY.

Common mistake: Expecting rows to come back in the order they were added. They might not.

60-second card

Top results

LIMIT keeps only the first few rows, often after sorting, to get the top results.

ORDER BY marks DESC LIMIT 3 gives the top 3.
LIMIT comes last in the query.
OFFSET 3 skips the first 3 rows, useful for pages of results.

SELECT name FROM students ORDER BY marks DESC LIMIT 2;

Result

Meena
Divya

Remember: Top N = ORDER BY ... DESC, then LIMIT N.

Common mistake: Using LIMIT without ORDER BY and expecting the 'top' rows. You just get any rows.

Topic 3

Aggregates and GROUP BY

COUNT, SUM, AVG · GROUP BY · HAVING

Open the lesson Practise this topic

60-second card

COUNT, SUM, AVG

Aggregate functions turn many rows into one number.

COUNT(*) counts rows; SUM and AVG add up and average a column.
MIN and MAX give the smallest and largest value.
Combine with WHERE to summarise only some rows.

SELECT COUNT(*), MAX(marks), AVG(marks) FROM students;

Result

5 | 91 | 80.4

Remember: Aggregates squash all the matching rows into one result.

Common mistake: Mixing an aggregate with a plain column without GROUP BY, like SELECT city, AVG(marks).

60-second card

GROUP BY

GROUP BY splits rows into groups and gives one summary row per group.

GROUP BY city gives one row per city.
Use an aggregate for the other columns: COUNT(*), AVG(marks).
Every column in SELECT must be grouped or aggregated.

SELECT city, COUNT(*) FROM students GROUP BY city ORDER BY city;

Result

Chennai | 1
Delhi | 2
Pune | 2

Remember: One output row per group.

Common mistake: Selecting name while grouping by city. Which name should a city's row show? It's an error.

60-second card

HAVING

HAVING filters groups after GROUP BY, the way WHERE filters rows before it.

WHERE marks > 70 keeps rows; HAVING COUNT(*) > 1 keeps groups.
HAVING can use aggregates; WHERE can't.
Order: WHERE, then GROUP BY, then HAVING, then ORDER BY.

SELECT city FROM students GROUP BY city HAVING AVG(marks) > 80 ORDER BY city;

Result

Pune

Remember: Rows: WHERE. Groups: HAVING.

Common mistake: Writing WHERE COUNT(*) > 1. Aggregates belong in HAVING.

Topic 4

Joins

Inner joins · Left joins

Open the lesson Practise this topic

60-second card

Inner joins

An inner join combines rows from two tables where a column matches, and drops rows with no match.

JOIN departments d ON s.dept_id = d.id links them.
Short names (aliases) like s and d save typing.
Rows with no partner on the other side are left out.

SELECT s.name, d.dept_name
FROM students s
JOIN enrolments e ON e.student_id = s.id
JOIN departments d ON d.id = e.dept_id
ORDER BY s.id;

Result

Asha | CSE
Ravi | ECE
Meena | CSE

Remember: JOIN keeps only the matches.

Common mistake: Forgetting the ON condition. You'd pair every row with every row.

60-second card

Left joins

A left join keeps every row from the left table, with blanks (NULL) where there's no match.

Use it to find rows with no partner: WHERE right_table.id IS NULL.
Order matters: the table after FROM is the one kept in full.
COUNT(right.id) counts 0 for rows with no match.

SELECT d.dept_name, COUNT(e.student_id)
FROM departments d
LEFT JOIN enrolments e ON e.dept_id = d.id
GROUP BY d.dept_name ORDER BY d.dept_name;

Result

CSE | 2
Civil | 0
ECE | 1

Remember: LEFT JOIN = all of the left table, matches where they exist.

Common mistake: Comparing with = NULL. Use IS NULL instead.