SQL Fundamentals: Querying Databases

Every school keeps a Sports Day register. On the day itself, a teacher stands with a clipboard listing every student who registered, which event they signed up for, and their score. Now suppose the Principal walks up and asks: "Which students from Class 8A scored above 80?" With a clipboard of 40 names, you scan line by line, checking two conditions for every row, and hope you don't miss one. With a register of 4,000 students across a whole school, that manual scan stops being reasonable at all. This is exactly the problem SQL was invented to solve — not searching faster with your eyes, but describing precisely what you want and letting a database engine do the scanning for you, correctly, every time, no matter how large the register gets.

SQL stands for Structured Query Language. It is not a general-purpose programming language like Python or C++ — you don't write loops or if-statements in it. Instead, SQL is a declarative language: you declare the shape of the answer you want, and the database figures out how to produce it. This chapter builds that skill from the ground up using one running example — a Sports Day registration table — so that every new keyword is grounded in a table you can see and trace by hand before it becomes an abstract rule.

Anatomy of a Table: Rows, Columns, and Keys

A relational database stores data in tables — grids that look like a well-organised spreadsheet, but with stricter rules. Each table has a fixed set of columns (also called fields or attributes), and each column has one, and only one, type of data — a name, a number, a date. Each horizontal line in the table is a row (also called a record or tuple), and one row represents one real-world thing being described — in our case, one student's Sports Day registration.

Here is the table we will use throughout this chapter, called SPORTS_DAY:

RollNo | Name           | Class | Event      | Fee_Paid | Score
-------|----------------|-------|------------|----------|------
1      | Aarav Sharma   | 8A    | Running    | 100      | 78
2      | Diya Patel     | 8B    | Running    | 100      | 85
3      | Kabir Singh    | 8A    | Long Jump  | 150      | 62
4      | Meera Iyer     | 8B    | Chess      | 50       | 91
5      | Rohan Gupta    | 8A    | Running    | 100      | 90
6      | Ishita Rao     | 8B    | Chess      | 50       | 88
7      | Vivaan Nair    | 8A    | Long Jump  | 150      | 70
8      | Ananya Das     | 8B    | Running    | 100      | 65

RollNo is what is called the table's primary key — a column (or combination of columns) guaranteed to hold a unique value for every row, so that no two rows can ever be confused with each other. No two students share a Roll Number, so it is a safe way for the database to tell rows apart even if two students happened to share the exact same name. Every well-designed table needs a primary key; it is the single most important design decision you make before you ever write a query against a table.

This table already exists and is already filled with data (creating tables and inserting rows is the job of the CREATE TABLE and INSERT commands, which belong to a different part of SQL called Data Definition and Data Manipulation). In this chapter, we focus entirely on reading data that is already there — the part of SQL called querying, built almost entirely around one command: SELECT.

The SELECT Statement: Choosing Columns

The simplest possible query asks for everything in the table:

SELECT * FROM SPORTS_DAY;

The asterisk * means "every column." This returns all six columns for all eight rows, exactly as shown above. But usually you don't need every column — you need specific ones. Suppose you only want each student's name and the event they registered for:

SELECT Name, Event
FROM SPORTS_DAY;

Trace this by hand: the database looks at the FROM SPORTS_DAY clause first to know which table to read, then keeps only the Name and Event columns from every row, discarding RollNo, Class, Fee_Paid, and Score entirely. The result has 8 rows and exactly 2 columns:

Name           | Event
---------------|-----------
Aarav Sharma   | Running
Diya Patel     | Running
Kabir Singh    | Long Jump
Meera Iyer     | Chess
Rohan Gupta    | Running
Ishita Rao     | Chess
Vivaan Nair    | Long Jump
Ananya Das     | Running

Notice the general shape of every query so far: SELECT names the columns you want (or * for all), and FROM names the table to read them from. A semicolon ; ends the statement. SQL keywords are conventionally written in UPPERCASE and table/column names in the case they were created with — this is a style convention for readability, not a hard rule, since most database systems treat keywords as case-insensitive either way.

Filtering Rows with WHERE

SELECT alone controls which columns you keep. To control which rows you keep, you add a WHERE clause with a condition. Back to the Principal's question — which students are in Class 8A?

SELECT *
FROM SPORTS_DAY
WHERE Class = '8A';

Two things to notice immediately. First, text values are wrapped in single quotes — '8A', not 8A — because SQL needs to tell a text string apart from a bare column name or number; numbers like Score or Fee_Paid are written without quotes. Second, SQL's equality test is a single =, not the double == you may have seen in Python or JavaScript — an easy habit to import by mistake.

Tracing this query row by row against the table: Aarav Sharma (8A) — keep. Diya Patel (8B) — drop. Kabir Singh (8A) — keep. Meera Iyer (8B) — drop. Rohan Gupta (8A) — keep. Ishita Rao (8B) — drop. Vivaan Nair (8A) — keep. Ananya Das (8B) — drop. Four rows survive: Aarav Sharma, Kabir Singh, Rohan Gupta, Vivaan Nair — exactly the four 8A students, each with all six of their original columns since we used *.

WHERE also works with numeric comparisons, using the operators =, <> (or !=, "not equal"), >, <, >=, and <=. Which students scored above 80?

SELECT Name, Score
FROM SPORTS_DAY
WHERE Score > 80;

Checking each row's Score against 80: 78 (no), 85 (yes), 62 (no), 91 (yes), 90 (yes), 88 (yes), 70 (no), 65 (no). Four rows pass: Diya Patel (85), Meera Iyer (91), Rohan Gupta (90), Ishita Rao (88), listed in whatever order they happened to sit in the table — which, as the next section shows, you should never rely on.

Sorting Results with ORDER BY

Unless you explicitly ask for an order, a database is free to return rows in whatever order is convenient for it internally — usually, but not guaranteed, the order they were inserted. If order matters to your answer, you must say so with ORDER BY:

SELECT Name, Score
FROM SPORTS_DAY
WHERE Score > 80
ORDER BY Score DESC;

DESC means descending (highest first); its opposite, ASC (ascending, lowest first), is the default if you omit it. Sorting our four survivors by score, highest to lowest: Meera Iyer (91), Rohan Gupta (90), Ishita Rao (88), Diya Patel (85). Note the clause order in the query text: SELECT, then FROM, then WHERE, then ORDER BY — this fixed sequence is part of SQL's grammar and cannot be rearranged, even though, as you'll see shortly, it is not the order in which the database actually evaluates them.

Combining Conditions: AND, OR, BETWEEN, IN, LIKE

Real questions are rarely single conditions. "Which Running participants scored 80 or more?" needs two conditions joined with AND — both must be true for a row to survive:

SELECT Name, Score
FROM SPORTS_DAY
WHERE Event = 'Running' AND Score >= 80;

Checking the four Running rows — Aarav (78, fails the score test), Diya (85, passes both), Rohan (90, passes both), Ananya (65, fails) — leaves exactly two rows: Diya Patel and Rohan Gupta. Swap AND for OR and the meaning changes completely: OR keeps a row if either condition is true, so WHERE Event = 'Running' OR Score >= 80 would keep every Running participant regardless of score, plus every non-Running participant who still scored 80 or above (Meera Iyer and Ishita Rao from Chess) — six rows in total, not two. Mixing up AND and OR is one of the most common ways a query silently returns the wrong answer while still running without any error, so always re-read a compound condition in plain English before trusting it.

Three more comparison tools save you from writing long chains of AND/OR. BETWEEN tests an inclusive range:

SELECT Name, Score
FROM SPORTS_DAY
WHERE Score BETWEEN 70 AND 90;

"Inclusive" means 70 and 90 themselves count as matches, not just values strictly between them. Checking all eight scores — 78, 85, 62, 91, 90, 88, 70, 65 — against the range [70, 90]: 78 ✓, 85 ✓, 62 ✗, 91 ✗ (one point too high), 90 ✓ (exactly the boundary, included), 88 ✓, 70 ✓ (the other boundary), 65 ✗. Five rows survive: Aarav (78), Diya (85), Rohan (90), Ishita (88), Vivaan (70).

IN shortens a list of OR-equality checks on the same column. "Students in Chess or Long Jump" is:

SELECT Name, Event
FROM SPORTS_DAY
WHERE Event IN ('Chess', 'Long Jump');

This matches Kabir Singh, Meera Iyer, Ishita Rao, and Vivaan Nair — the four rows whose Event is not Running.

LIKE matches text patterns using the wildcard %, which stands for "zero or more of any character." Students whose name begins with A:

SELECT Name
FROM SPORTS_DAY
WHERE Name LIKE 'A%';

Only Aarav Sharma and Ananya Das begin with the letter A; Diya, Kabir, Meera, Rohan, Ishita, and Vivaan do not, so exactly two names come back.

Removing Duplicates with DISTINCT

Sometimes a column repeats the same value across many rows and you only want to know which values appear at all, not how often. "Which events does this school even offer?" doesn't need all eight rows — it needs the unique event names:

SELECT DISTINCT Event
FROM SPORTS_DAY
ORDER BY Event;

Without DISTINCT, this query would return Event once per row — Running four times, Chess and Long Jump twice each, eight lines total, mostly repeats. DISTINCT collapses repeats into a single appearance, so the result is exactly three rows: Chess, Long Jump, Running (alphabetical, because of the ORDER BY).

The Special Case of NULL

Suppose a ninth student registers for Sports Day but hasn't paid the fee yet:

RollNo | Name       | Class | Event | Fee_Paid | Score
9      | Sara Khan  | 8B    | Chess | NULL     | 76

NULL is not the number zero, and it is not an empty piece of text — it represents the absence of a value entirely, a fact that is simply not recorded yet. This distinction matters because a beginner's first instinct is usually to write:

SELECT Name FROM SPORTS_DAY WHERE Fee_Paid = NULL;   -- WRONG

This query runs without an error, but it returns zero rows — not Sara Khan's row, as you might expect. Here is the misconception worth correcting carefully: NULL means "unknown," and in SQL, comparing anything to an unknown value — including comparing an unknown to another unknown with NULL = NULL — does not produce TRUE. It produces a third result, neither true nor false, that SQL treats as a non-match. Asking "is this unknown value equal to NULL?" is itself an unanswerable question, so the row is silently excluded. The correct tool is a dedicated test built for exactly this situation:

SELECT Name FROM SPORTS_DAY WHERE Fee_Paid IS NULL;

This correctly returns Sara Khan — the one row where Fee_Paid genuinely has no value. Its opposite, WHERE Fee_Paid IS NOT NULL, returns the other eight rows, everyone whose fee is recorded as an actual number.

Summarizing Data: Aggregate Functions

So far every query has returned individual rows. Sometimes you want a single summary number instead — a total, an average, a count. SQL provides five core aggregate functions: COUNT, SUM, AVG, MAX, and MIN. Using the full nine-row table (including Sara Khan):

SELECT COUNT(*) AS TotalStudents,
       COUNT(Fee_Paid) AS StudentsWhoPaid,
       SUM(Fee_Paid) AS TotalCollected,
       AVG(Score) AS AverageScore,
       MAX(Score) AS TopScore,
       MIN(Score) AS LowestScore
FROM SPORTS_DAY;

Trace each column of the result separately. COUNT(*) counts rows regardless of content: 9. COUNT(Fee_Paid) counts only rows where that specific column is not NULL: Sara Khan's row is skipped, giving 8 — this is precisely why COUNT(*) and COUNT(column) can disagree, and a common exam trap is assuming they're always the same. SUM(Fee_Paid) adds the eight known fees (100+100+150+50+100+50+150+100), which totals 800, again silently skipping the NULL rather than treating it as zero. AVG(Score) adds all nine scores (78+85+62+91+90+88+70+65+76 = 705) and divides by 9, giving 78.33 (rounded to two places). MAX(Score) and MIN(Score) scan all nine scores and report the extremes: Meera Iyer's 91 and Kabir Singh's 62. The AS keyword just renames the output column to something readable — it doesn't change any calculation.

Grouping Rows: GROUP BY

An overall average is often too coarse — "average score" hides that Chess players score very differently from Long Jump participants. GROUP BY splits the table into buckets by a column's value and runs the aggregate function separately within each bucket:

SELECT Event,
       COUNT(*) AS Participants,
       AVG(Score) AS AvgScore
FROM SPORTS_DAY
GROUP BY Event;

Mentally sort the nine rows into three buckets by Event first. Running: Aarav (78), Diya (85), Rohan (90), Ananya (65) — 4 rows, sum 318, average 79.5. Long Jump: Kabir (62), Vivaan (70) — 2 rows, average 66. Chess: Meera (91), Ishita (88), Sara (76) — 3 rows, sum 255, average 85. The query returns exactly one summary row per distinct Event value:

Event      | Participants | AvgScore
-----------|--------------|---------
Running    | 4            | 79.5
Long Jump  | 2            | 66
Chess      | 3            | 85

The rule to hold onto: every column you list after SELECT in a grouped query must either appear in the GROUP BY list (like Event here) or be wrapped in an aggregate function (like COUNT(*) or AVG(Score)). Asking for Name alongside a GROUP BY Event makes no sense — which of four different Running participants' names would represent the whole group? — and most database systems will refuse the query outright rather than guess.

How SQL Actually Thinks: The Logical Order of Execution

Here is a second misconception worth correcting directly, because it explains behaviour that otherwise looks mysterious. Students naturally assume a query executes in the order it is typed: SELECT first (since it's the first word), then FROM, then WHERE. That is backwards. The database actually evaluates clauses in this logical order: FROM (find the table) → WHERE (filter individual rows) → GROUP BY (bucket the survivors) → aggregate functions (summarize each bucket) → SELECT (pick and rename the final columns) → ORDER BY (sort the finished result).

This is precisely why WHERE cannot filter on the result of an aggregate function — at the moment WHERE runs, grouping and averaging haven't happened yet, so there is nothing yet to compare. Writing WHERE AvgScore > 80 to keep only high-scoring events would fail, because WHERE operates on raw rows, not on grouped summaries; the correct tool for filtering groups after they've been summarized is HAVING, a clause that runs after GROUP BY, precisely because it needs the summary numbers to already exist. It is also why WHERE cannot refer to a column alias you invented in SELECT (like the renamed AvgScore above) — SELECT hasn't run yet when WHERE is evaluated, so that name simply doesn't exist yet as far as the database is concerned. The order you type the clauses in is fixed by SQL's grammar; the order the database actually thinks in is different, and knowing the difference explains errors that otherwise feel arbitrary.

Test Yourself: Query the SPORTS_DAY Table

Using the nine-row table above (including Sara Khan, with her NULL fee), work out the exact result of each query on paper before checking the answer.

1. Write a query that lists every student's Name and Class, sorted alphabetically by name.
2. Write a query that finds all students who did not register for Running (hint: <> or NOT IN).
3. Predict the exact row count returned by SELECT * FROM SPORTS_DAY WHERE Class = '8B' AND Score > 85; and name the matching students.
4. Predict what SELECT COUNT(*) FROM SPORTS_DAY WHERE Fee_Paid IS NULL; returns, and explain in one sentence why WHERE Fee_Paid = NULL would have returned something different.
5. Without running it, predict how many groups SELECT Class, AVG(Score) FROM SPORTS_DAY GROUP BY Class; would produce, and compute the average score for each group by hand.

Answers to check your work: (3) Two rows — Class 8B with Score > 85 matches only Meera Iyer (91) and Ishita Rao (88); Diya Patel (85) is excluded because 85 is not strictly greater than 85. (4) 1 — only Sara Khan; a direct = NULL comparison always returns zero rows, because NULL represents an unknown value and nothing can be proven equal to an unknown, so the comparison is treated as a non-match rather than a match. (5) Two groups — 8A: Aarav (78), Kabir (62), Rohan (90), Vivaan (70), average 75; 8B: Diya (85), Meera (91), Ishita (88), Ananya (65), Sara (76), average 81.

Chapter Summary

A table stores data as rows (records) and columns (fields), identified uniquely by a primary key. SELECT column_list FROM table chooses which columns to see; WHERE condition filters which rows survive, using =, <>, >, <, AND/OR, BETWEEN, IN, and LIKE; ORDER BY sorts the final result, since row order is never guaranteed otherwise; DISTINCT removes duplicate values; NULL means "unknown," not zero or blank, and must be tested with IS NULL/IS NOT NULL rather than =; the five aggregate functions COUNT, SUM, AVG, MAX, and MIN collapse many rows into one summary number, and GROUP BY runs that summary separately per distinct value of a chosen column. Underneath the fixed typing order of SELECT…FROM…WHERE…GROUP BY…ORDER BY, the database actually evaluates FROM first and SELECT nearly last — the reason WHERE can never see an aggregate or an alias that SELECT hasn't produced yet.

Reading the Query Visually

The diagram below shows SELECT Name, Score FROM SPORTS_DAY WHERE Event = 'Running' acting on five rows of the table at once: SELECT keeps two columns (Name, Score), WHERE keeps three rows (the Running rows), and only cells in both a kept column and a kept row end up in the final answer.

Think About It

Think about this: How would you explain sql fundamentals: querying databases to a friend who has never seen a computer? What real-world analogy would you use? Imagine you had to build a system using these concepts — what would be your first step? Try this: before moving on, write down three things you learned and one question you still have.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where sql fundamentals: querying databases is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting sql fundamentals: querying databases to at least 3 other topics you have studied.