SQL and Relational Databases: Structured Data Mastery

The Problem With One Giant List

Picture a school library that tracks every book issue in a single notebook, one row per transaction: student roll number, student name, student class, student city, book title, author, and the date issued. It works fine for the first few entries. But by the fortieth row, something starts to go wrong. Aarav has borrowed six books this term, so his name, class, and city are typed out six separate times. One day the librarian mistypes his city as "Mumbi" instead of "Mumbai" on the fourth entry — now the same student exists as two slightly different people inside the same notebook, and nobody notices until a report says two students share a roll number but live in different cities. If Aarav moves to Pune next year, the librarian has to hunt down and correct all six rows, and if even one gets missed, the data is now silently wrong. And if the library buys a brand-new book that nobody has issued yet, there is no row to write it into at all, because every row in this notebook requires a student.

These three failures have names in database theory: an update anomaly (changing one real-world fact requires editing many rows, and missing even one leaves the data inconsistent), an insertion anomaly (you cannot record a new book, or a new student, until it participates in a transaction), and a deletion anomaly (if Aarav returns his only book and that row is deleted, every fact about Aarav as a person — his class, his city — vanishes with it). All three anomalies exist for the same root cause: unrelated facts (a student's personal details, a book's details, and the act of borrowing) are jammed into one flat list instead of being split into separate, focused tables that reference each other. This is exactly the problem a relational database is built to solve, and it is why real systems — IRCTC storing passengers separately from bookings, a bank storing customers separately from transactions — never use one giant sheet.

What a Relational Database Actually Is

A relational database stores data in tables (formally called relations), where each table holds facts about exactly one kind of thing. A row in a table is called a tuple or record, and represents one specific instance — one student, one transaction. A column is called an attribute or field, and represents one property that every row in that table shares — a name, a date, a score. The number of columns in a table is its degree; the number of rows is its cardinality. These four terms — relation, tuple, attribute, and their counts — are the exact vocabulary CBSE Computer Science and Informatics Practices exams expect you to use precisely, so it is worth fixing them now rather than saying "table" and "row" loosely throughout an answer.

Splitting the library notebook into two tables solves every anomaly from the previous section. A Students table holds each student's personal details exactly once. A separate Marks (or in the library case, an Issues) table holds only the transactions, and instead of repeating the student's name and city in every row, it stores a small reference number that points back to the correct row in Students. We will build this exact pair of tables below and use them for every SQL example in this chapter, so the queries are not abstract — you can trace every single result by hand.

The Primary Key: Every Row Needs a Unique Address

Before two tables can reference each other, every row inside a single table needs a way to be identified without ambiguity. A primary key is a column (or a small set of columns) whose value is guaranteed to be unique for every row and is never allowed to be empty. Using a student's name as a primary key would be a mistake — two different students in the same school can genuinely be named "Aarav," and the moment that happens, "find Aarav's marks" becomes ambiguous. This is why real systems invent a small, meaningless number — a student_id, an Aadhaar-style ID, a PNR on an IRCTC ticket — whose only job is to be unique. It carries no real-world meaning by itself; its entire purpose is to let every row be pointed to unambiguously.

Here is the table we will use for the rest of this chapter, five students, kept deliberately small so every query result can be checked by hand:

Students
student_id  name    class  city
1           Aarav   9      Mumbai
2           Diya    9      Pune
3           Kabir   10     Mumbai
4           Meera   9      Delhi
5           Rohan   10     Pune

student_id is the primary key here — every value from 1 to 5 appears exactly once, and no row is missing it.

Splitting Data Correctly: The Foreign Key

Now we record each student's Math test score in its own table, one row per score, instead of adding a "score" column to Students (which would break the moment a student sits more than one subject). Each row in this new table needs to say which student it belongs to — and it does that by storing that student's primary key value, not their name.

Marks
mark_id  student_id  subject  score
1        1           Math     88
2        2           Math     76
3        3           Math     92
4        4           Math     65
5        5           Math     81

The student_id column inside Marks is called a foreign key: it is the primary key of a different table (Students), copied here purely as a pointer. A foreign key value must always match some existing primary key value in the table it points to — the database refuses to let you insert a Marks row with student_id = 9, because no such student exists in Students. This single rule is what keeps two separate tables trustworthy: it is mathematically impossible to have a "orphan" score belonging to nobody. The diagram below shows exactly how the two tables connect.

Talking to the Database: SQL Basics

SQL (Structured Query Language) is how you create tables, load data into them, and ask questions of them. Its commands fall into three groups that matter for this chapter: DDL (Data Definition Language — commands that build the structure, like CREATE TABLE), DML (Data Manipulation Language — commands that add or change data, like INSERT), and DQL (Data Query Language — commands that retrieve data, essentially SELECT in all its forms). Here is the CREATE TABLE statement for both tables, with the foreign key constraint written explicitly:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(30),
    class      INT,
    city       VARCHAR(20)
);

CREATE TABLE Marks (
    mark_id    INT PRIMARY KEY,
    student_id INT,
    subject    VARCHAR(20),
    score      INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

VARCHAR(30) means "text, up to 30 characters" — the number is a limit, not a fixed length, so "Diya" still only uses 4 characters of storage even though the column allows 30. INT means a whole number. Every column gets a data type because the database needs to know, in advance, how to store and compare values in that column — you cannot run score > 80 on a column that was never told it holds numbers.

Now we load the actual rows with INSERT INTO, one statement per row (real systems batch these, but writing them one at a time makes the mapping from table to statement obvious):

INSERT INTO Students VALUES (1, 'Aarav', 9, 'Mumbai');
INSERT INTO Students VALUES (2, 'Diya', 9, 'Pune');
INSERT INTO Students VALUES (3, 'Kabir', 10, 'Mumbai');
INSERT INTO Students VALUES (4, 'Meera', 9, 'Delhi');
INSERT INTO Students VALUES (5, 'Rohan', 10, 'Pune');

INSERT INTO Marks VALUES (1, 1, 'Math', 88);
INSERT INTO Marks VALUES (2, 2, 'Math', 76);
INSERT INTO Marks VALUES (3, 3, 'Math', 92);
INSERT INTO Marks VALUES (4, 4, 'Math', 65);
INSERT INTO Marks VALUES (5, 5, 'Math', 81);

Text values go inside single quotes; numbers do not. The order of values must match the order of columns declared in CREATE TABLE — the third value in each Students row lands in the class column simply because class was declared third.

Asking Questions: SELECT, WHERE, ORDER BY, DISTINCT

Retrieval always starts with SELECT. To see every column of every row in Students:

SELECT * FROM Students;

The asterisk means "all columns." To filter which rows come back, add WHERE with a condition. To see only class 9 students, sorted alphabetically by name:

SELECT name, city
FROM Students
WHERE class = 9
ORDER BY name;

Trace it by hand: scan all five rows, keep only where class = 9 — that keeps Aarav, Diya, and Meera (Kabir and Rohan are class 10, so they are dropped). Then sort what remains by name: Aarav, Diya, Meera — already alphabetical in this case, so the output is exactly those three names with their cities. Notice SELECT only listed name and city: you can request any subset of columns, you are never forced to pull every column just because WHERE filtered on a different one.

To list the distinct cities students live in, without repeats, use DISTINCT:

SELECT DISTINCT city FROM Students;

Trace it: the raw city values in row order are Mumbai, Pune, Mumbai, Delhi, Pune. DISTINCT collapses repeats, leaving exactly three values: Mumbai, Pune, Delhi. Without DISTINCT, the query would return all five values with Mumbai and Pune each appearing twice.

Counting and Summarizing: Aggregate Functions

Aggregate functions collapse many rows into a single summary number. The four you need at this stage are COUNT, SUM, AVG, MAX, and MIN. Using the Marks table (scores: 88, 76, 92, 65, 81):

SELECT AVG(score) FROM Marks;

Trace it exactly as you would with a calculator: 88 + 76 = 164, +92 = 256, +65 = 321, +81 = 402. Divide by the count of rows, 5: 402 ÷ 5 = 80.4. That is precisely what AVG returns — it is not magic, it is SUM ÷ COUNT computed for you.

SELECT COUNT(*) FROM Marks WHERE score >= 80;

Trace it: scan the five scores against the condition score >= 80 — 88 qualifies, 76 does not, 92 qualifies, 65 does not, 81 qualifies. Three rows pass, so COUNT(*) returns 3. A subtle but important rule: WHERE is applied before the aggregate function runs — the database first throws out rows that fail the condition, and only then counts, sums, or averages whatever survives. MAX(score) on this table is 92, MIN(score) is 65 — both found by simply scanning for the largest and smallest values, exactly as you would by eye on a five-number list.

Reconnecting the Tables: JOIN

Splitting data into Students and Marks solved the redundancy problem, but a real question — "which class 9 or 10 students scored above 80?" — needs facts from both tables at once: the name lives in Students, the score lives in Marks. A JOIN temporarily stitches matching rows from two tables back together, using the primary-key/foreign-key link, without ever duplicating the underlying stored data.

SELECT Students.name, Marks.subject, Marks.score
FROM Students
JOIN Marks ON Students.student_id = Marks.student_id
WHERE Marks.score > 80
ORDER BY Marks.score DESC;

Trace it in three stages, exactly how the database processes it. Stage one, the JOIN: for every row in Marks, find the Students row whose student_id matches, and glue them side by side. Because each student_id in this dataset appears exactly once in each table, this produces five combined rows: Aarav/Math/88, Diya/Math/76, Kabir/Math/92, Meera/Math/65, Rohan/Math/81. Stage two, WHERE Marks.score > 80: keep only rows with a score strictly greater than 80 — that removes Diya (76) and Meera (65), leaving Aarav (88), Kabir (92), and Rohan (81). Stage three, ORDER BY Marks.score DESC: sort what remains from highest score to lowest — Kabir (92), Aarav (88), Rohan (81). That three-row, highest-to-lowest list is exactly what this query returns, and every value in it can be checked against the original two five-row tables.

Two Things Every Beginner Gets Wrong

Misconception 1: SQL runs in the order you type it. The query above is written as SELECT, then FROM, then JOIN/ON, then WHERE, then ORDER BY — but the database does not execute it top to bottom in that order. It logically processes FROM/JOIN first (build the combined rows), then WHERE (filter them), then SELECT (pick which columns to show), and only at the very end ORDER BY (sort the final result). This is why you are allowed to write WHERE Marks.score > 80 using a column that never appears in the final SELECT list — the filtering happens on the full joined data, before the column list is even applied. Reading a query as "gather rows, filter rows, choose columns, sort" — rather than left to right as typed — is the correct mental model and the one CBSE questions on query evaluation actually test.

Misconception 2: an empty field is the same as zero or an empty string. If a sixth student, say a new admission, has not sat the Math test yet, their score is not 0 — a 0 would falsely claim they wrote the test and failed completely. SQL has a special marker, NULL, meaning "this value is unknown or does not exist," which is different from both the number 0 and the empty text ''. This matters for computation: AVG(score) ignores NULL rows entirely rather than treating them as 0 — if that sixth student's row had NULL for score, the average would still be computed over the original 5 real scores (402 ÷ 5 = 80.4), not divided by 6. Comparisons behave unusually too: WHERE score = NULL never matches anything, even a row whose score genuinely is NULL, because "unknown equals unknown" is itself unknown, not true. SQL provides a dedicated test, WHERE score IS NULL, precisely because the ordinary = operator cannot be used for this check.

Test Yourself

Using the exact Students and Marks tables from this chapter, work out each answer before checking it.

1. Write the query that lists every student's name and class for students living in Mumbai. Check: two rows should come back — Aarav (class 9) and Kabir (class 10) — since they are the only two students with city = 'Mumbai'.
2. What does SELECT COUNT(*) FROM Students WHERE class = 10; return, and which two names does it correspond to? Check: 2, corresponding to Kabir and Rohan.
3. A new row is inserted into Marks as (6, 6, 'Math', 70), but no student with student_id = 6 exists in Students. What happens, and why? Check: the database rejects the insert, because student_id in Marks is a foreign key referencing Students.student_id, and foreign keys are not allowed to point at a primary key value that doesn't exist.
4. Would SELECT DISTINCT class FROM Students; return two rows or five? Why? Check: two rows — 9 and 10 — because DISTINCT removes duplicate values, and only two distinct class values exist across the five students even though three students share class 9.

Summary

• A flat, single-table design causes update, insertion, and deletion anomalies because it repeats facts about unrelated things (a person, an event) in the same row.
• A relational database fixes this by storing each kind of fact in its own table (relation), made of rows (tuples) and columns (attributes).
• A primary key uniquely identifies every row in a table and can never be empty or repeated; a foreign key is a copy of another table's primary key, used to link rows across tables without duplicating their other details.
• CREATE TABLE defines structure and data types; INSERT INTO loads rows; SELECT/WHERE/ORDER BY/DISTINCT retrieve, filter, sort, and de-duplicate results.
• COUNT, SUM, AVG, MAX, and MIN collapse many rows into one summary value, applied after any WHERE filter has already run.
• JOIN reunites tables at query time using the primary-key/foreign-key link, evaluated in the order FROM/JOIN, then WHERE, then SELECT, then ORDER BY — not the order the keywords are typed.
• NULL means "unknown," and is never equal to 0, an empty string, or even another NULL under the = operator — it requires the dedicated IS NULL test.

Think About It

Think about this: How would you explain sql and relational databases: structured data mastery 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.