Database Fundamentals: SQL for Data-Driven Applications

A Notebook That Cannot Keep Up

Picture the register your school library uses to track borrowed books. Every time a student borrows a book, the librarian writes a line: the student's name, the book title, the date issued, the date it's due back. It works fine for the first few weeks. Then problems start creeping in. One entry says "Aarav Sharma" and another, for the same boy, says "Aarav S." — are these the same person, or two different students who both happen to be named Aarav? If the librarian wants to know which books are overdue today, there is no shortcut: she has to read every single line in the register, by eye, checking each due date against today's date. If two students return books at the same counter at the same time, only one person can physically write in the notebook at once. And if the register is ever lost, every record it held is gone with it.

None of these problems are about handwriting or laziness. They are structural problems with how the data is organized — as free-form sentences in a notebook rather than as consistently shaped, searchable records. This chapter is about the fix: organizing data into tables with a strict, repeatable shape, and using a language called SQL (Structured Query Language) to create, search, update, and remove that data reliably. This combination — structured tables plus a query language — is what powers almost every real "data-driven application" you use: a library system, a school's result portal, a train-booking system, a UPI payment history screen. They are all, underneath, tables and queries.

From Notebook to Table: What a Database Really Is

The first fix is to stop writing free-form sentences and instead force every entry into the same fixed set of slots. Instead of "Aarav Sharma borrowed Wings of Fire on 1 July, due back 15 July," we write one row with fixed columns: a student ID number, a book title, an issue date, a due date. Every row has exactly the same columns, in the same order, with the same meaning. This structure is called a table.

A table has precise vocabulary worth learning exactly, because CBSE Computer Science and every real database course uses these words constantly:

• Table — a grid of data about one kind of thing (all students, or all book issues).
• Field (or column) — one category of information every row has, such as Name or DueDate. Each field has a fixed data type — a rule for what kind of value it may hold.
• Record (or row) — one complete entry: all the field values for one student, or one book issue.
• Primary key — one field (or a small combination of fields) whose value is guaranteed to be different in every single row, so it can be used to point at that row unambiguously. A student's name is a poor primary key, because two students can share a name. A StudentID number, assigned once and never reused, is a good primary key.

Here is our running example, a Students table for a school library system:

StudentID | Name          | ClassSection
----------+---------------+-------------
1         | Aarav Sharma  | 9A
2         | Diya Patel    | 9B
3         | Kabir Singh   | 9A
4         | Meera Iyer    | 9B
5         | Rohan Gupta   | 9A

StudentID is the primary key here — even if two students were both named "Aarav Sharma," their StudentID values (say, 1 and 17) would still be different, so the row would never be mistaken for another. Notice this also fixes the original register problem: instead of typing a name inconsistently every time, the system stores one ID number per student, once, and everything else refers back to it.

The diagram below shows this idea alongside a second table, BookIssues, which records every book a student has taken out. Watch how the two tables are connected: BookIssues doesn't repeat the student's name and section on every row — it just stores the StudentID number, and that number points back into the Students table.

SQL: The Language for Talking to a Database

Organizing data into tables solves the structure problem, but you still need a way to ask questions of that data and to change it. That is what SQL is for. It is worth being precise about three different things students often blur together: a database is the organized collection of tables; a DBMS (Database Management System — software like MySQL, PostgreSQL, or SQLite) is the program that actually stores the tables on disk and enforces their rules; and SQL is the language you type to tell the DBMS what to do. SQL is not itself a database, and it is not itself software you install — it is a set of commands, a bit like how English is a language you use to give someone instructions, not the instructions' outcome. Every time you check seat availability on IRCTC or scroll your last five UPI transactions in a banking app, a program is sending SQL commands to a DBMS holding tables shaped very much like the ones in this chapter.

SQL commands read almost like restricted English sentences. A few conventions to fix firmly before writing any: keywords such as SELECT, FROM, and WHERE are not case-sensitive to the DBMS, but it is standard practice to type them in UPPERCASE so they stand out from table and column names, which you choose yourself. Every complete SQL statement ends with a semicolon ;. Text values are wrapped in single quotes, like 'Aarav Sharma'; number values are written plainly, with no quotes, like 5. Mixing this up — quoting a number, or forgetting to quote text — is a very common first error, and the DBMS will either reject the statement or, worse, silently compare against the wrong type.

Building the Table: CREATE TABLE and Data Types

Before any data can go in, the table's shape must be declared: how many columns, what each is called, and what type of value each may hold. Three data types cover almost everything in this chapter: INT for whole numbers, VARCHAR(n) for text up to n characters, and DATE for calendar dates, always written in the unambiguous international order YYYY-MM-DD — this matters for an Indian student because it is the opposite order from the DD/MM/YYYY we write by hand, and mixing the two orders is another very common bug.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(30),
    ClassSection VARCHAR(5)
);

CREATE TABLE BookIssues (
    IssueID INT PRIMARY KEY,
    StudentID INT,
    BookTitle VARCHAR(50),
    IssueDate DATE,
    DueDate DATE,
    Returned VARCHAR(3),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

Two things to notice. First, PRIMARY KEY written after a column is a rule, not just a label — the DBMS will now refuse to let two rows share the same StudentID, and will refuse a blank (NULL) StudentID too, because a primary key must always identify exactly one row. Second, the last line of BookIssues, FOREIGN KEY (StudentID) REFERENCES Students(StudentID), is what makes StudentID in this table a foreign key: it tells the DBMS that any value placed in this column must already exist as a StudentID in the Students table. Try to issue a book to StudentID 99 when no such student exists, and the DBMS will reject it. This single line is what keeps the two tables honest with each other as the data-driven application grows.

Filling It In: INSERT INTO

A freshly created table is an empty shape — five named, typed columns and zero rows. INSERT INTO adds rows, one statement at a time or several at once.

INSERT INTO Students (StudentID, Name, ClassSection) VALUES (1, 'Aarav Sharma', '9A');
INSERT INTO Students (StudentID, Name, ClassSection) VALUES (2, 'Diya Patel', '9B');
INSERT INTO Students (StudentID, Name, ClassSection) VALUES (3, 'Kabir Singh', '9A');
INSERT INTO Students (StudentID, Name, ClassSection) VALUES (4, 'Meera Iyer', '9B');
INSERT INTO Students (StudentID, Name, ClassSection) VALUES (5, 'Rohan Gupta', '9A');

Trace what happens: the first statement adds exactly one row, (1, 'Aarav Sharma', '9A'), to an otherwise empty table. Each following statement adds one more row underneath. After five statements, Students holds five rows, in no guaranteed order — a table is a set of rows, not a list, so the DBMS is free to store them however it likes internally. Once several rows exist, listing them one INSERT at a time gets tedious, so SQL also allows a single statement carrying many rows, separated by commas:

INSERT INTO BookIssues (IssueID, StudentID, BookTitle, IssueDate, DueDate, Returned) VALUES
    (101, 1, 'Wings of Fire', '2026-07-01', '2026-07-15', 'No'),
    (102, 3, 'A Brief History of Time', '2026-07-03', '2026-07-17', 'Yes'),
    (103, 1, 'The Discovery of India', '2026-07-10', '2026-07-29', 'No'),
    (104, 2, 'Malgudi Days', '2026-07-12', '2026-07-22', 'No'),
    (105, 5, 'The Alchemist', '2026-07-15', '2026-07-26', 'No');

This one statement produces exactly the same end result as five separate INSERT statements would — five new rows in BookIssues — it is purely a more compact way to write it.

Asking Questions: SELECT and WHERE

Reading data back out is the operation you will use most often, and it starts with SELECT. SELECT * FROM Students; asks for every column of every row — the * means "all columns." Usually you want fewer columns and fewer rows, which is what WHERE is for: it keeps only the rows that satisfy a condition, checked one row at a time.

SELECT Name FROM Students WHERE ClassSection = '9A';

To find the output, the DBMS walks the table row by row and tests the condition against each: Aarav Sharma, ClassSection '9A' — matches, keep the Name. Diya Patel, '9B' — does not match, discard. Kabir Singh, '9A' — matches, keep. Meera Iyer, '9B' — discard. Rohan Gupta, '9A' — matches, keep. The result is three rows: Aarav Sharma, Kabir Singh, Rohan Gupta.

Conditions can be combined with AND and OR, and can use <, >, <=, >=, =, and !=. Suppose the librarian wants exactly the query a real library application would run every morning: which books are still out and already overdue, assuming today is 20 July 2026.

SELECT BookTitle FROM BookIssues
WHERE Returned = 'No' AND DueDate < '2026-07-20';

Trace all five rows against both conditions together. IssueID 101: Returned is 'No' (passes condition one) and DueDate '2026-07-15' is before '2026-07-20' (passes condition two) — both true, so AND keeps it. IssueID 102: Returned is 'Yes', so condition one already fails; the row is dropped, and its due date is never even relevant. IssueID 103: Returned is 'No', but DueDate '2026-07-29' is not before '2026-07-20' — condition two fails, dropped. IssueID 104: Returned is 'No', DueDate '2026-07-22' is not before '2026-07-20' — dropped. IssueID 105: Returned is 'No', DueDate '2026-07-26' is not before '2026-07-20' — dropped. Only one row survives both conditions: 'Wings of Fire'. That single query — a comparison on a date column combined with a comparison on a status column — is exactly the logic behind the "overdue" notification a real library app would show.

Common Misconception: WHERE Column = NULL Never Matches Anything

Suppose a new student joins and hasn't been assigned a section yet, so the DBMS records that field as absent — written NULL, meaning "unknown or not entered," never confuse this with zero or an empty string, which are actual values that just happen to be small or blank.

INSERT INTO Students (StudentID, Name, ClassSection) VALUES (6, 'Ishaan Verma', NULL);

Now suppose someone tries to find this student with the equals sign, the way you would for any other value:

SELECT Name FROM Students WHERE ClassSection = NULL;

This returns zero rows — even though Ishaan Verma's row plainly exists. This surprises almost everyone the first time. The reason is that SQL treats NULL as "unknown," and the comparison "is this unknown value equal to NULL?" is itself unknown, not true — and WHERE only keeps rows where the condition evaluates to true, so an unknown result is discarded, exactly like a false one. This holds even for a row compared against itself: NULL is never considered equal to NULL. The correct way to test for a missing value uses a dedicated keyword, not the equals sign:

SELECT Name FROM Students WHERE ClassSection IS NULL;

This one correctly returns Ishaan Verma. The rule to keep permanently: test for NULL only with IS NULL or IS NOT NULL, never with = or !=.

Sorting and Summarizing: ORDER BY and Aggregate Functions

A result set arrives in whatever order the DBMS happened to store the rows, which is not necessarily useful. ORDER BY fixes that:

SELECT BookTitle, DueDate FROM BookIssues
WHERE Returned = 'No'
ORDER BY DueDate ASC;

The four not-yet-returned rows have due dates 07-15 (101), 07-29 (103), 07-22 (104), and 07-26 (105) — notice these are not already in that order by IssueID. Sorted ascending by DueDate, the output is: Wings of Fire (07-15), Malgudi Days (07-22), The Alchemist (07-26), The Discovery of India (07-29). ASC (ascending, the default) sorts smallest/earliest first; DESC reverses it.

Beyond listing rows, SQL can compute a single summary number over many rows at once, using aggregate functions: COUNT(*) counts rows, SUM() totals a numeric column, AVG() averages it, MAX() and MIN() find extremes.

SELECT COUNT(*) FROM BookIssues WHERE Returned = 'No';

Trace: four rows (101, 103, 104, 105) satisfy Returned = 'No', so the answer is a single value, 4 — not four rows of data, one row holding the number four.

When you want that kind of count separately for each student rather than one grand total, GROUP BY splits the rows into buckets first, then applies the aggregate function inside each bucket:

SELECT StudentID, COUNT(*) AS BooksIssued
FROM BookIssues
GROUP BY StudentID;

Group the five BookIssues rows by StudentID: StudentID 1 gets rows 101 and 103, a bucket of two, so COUNT(*) is 2. StudentID 2 gets only row 104, count 1. StudentID 3 gets only row 102, count 1. StudentID 5 gets only row 105, count 1. The output is four summary rows: (1, 2), (2, 1), (3, 1), (5, 1) — one line per distinct StudentID, each carrying that student's own book count, not the overall total.

Connecting Tables: Primary Keys, Foreign Keys, and JOIN

BookIssues only stores a bare StudentID number — useful for the DBMS, but not something a librarian wants printed on a notice. To turn that number back into a name, SQL uses JOIN, which lines up rows from two tables wherever their key values match, exactly along the red links drawn in the earlier diagram.

SELECT Students.Name, BookIssues.BookTitle, BookIssues.DueDate
FROM Students
JOIN BookIssues ON Students.StudentID = BookIssues.StudentID
WHERE BookIssues.Returned = 'No'
ORDER BY BookIssues.DueDate;

Trace this in two stages, the way the DBMS effectively does. Stage one, matching: for every BookIssues row, find the Students row whose StudentID is equal. Row 101 (StudentID 1) pairs with Aarav Sharma. Row 102 (StudentID 3) pairs with Kabir Singh. Row 103 (StudentID 1) pairs with Aarav Sharma again — the same student can appear more than once, once per book. Row 104 (StudentID 2) pairs with Diya Patel. Row 105 (StudentID 5) pairs with Rohan Gupta. Stage two, filtering and sorting: keep only pairs where Returned = 'No' (this drops row 102, since Kabir's copy was already returned), then sort what remains by DueDate. The final output, four rows: Aarav Sharma — Wings of Fire — 2026-07-15; Diya Patel — Malgudi Days — 2026-07-22; Rohan Gupta — The Alchemist — 2026-07-26; Aarav Sharma — The Discovery of India — 2026-07-29. This is precisely the report a school library application would generate to send overdue reminders — readable names, not raw ID numbers — and it only works because BookIssues.StudentID and Students.StudentID are guaranteed, by the FOREIGN KEY rule set up earlier, to line up correctly.

Changing and Removing Data: UPDATE and DELETE

Data-driven applications don't just grow — records change and get removed. UPDATE changes existing values; DELETE removes whole rows. Both are almost always used with WHERE, and that WHERE clause is not optional the way it can feel with SELECT — leaving it off is one of the most damaging mistakes possible in SQL.

UPDATE BookIssues SET Returned = 'Yes' WHERE IssueID = 101;

This finds the one row where IssueID equals 101 and changes only its Returned field from 'No' to 'Yes'; every other column in that row, and every other row in the table, is untouched. Contrast that precision with what happens if the WHERE clause is dropped: UPDATE BookIssues SET Returned = 'Yes'; would set Returned to 'Yes' on all five rows at once, silently marking books as returned that are still sitting on students' shelves. The same danger applies to DELETE:

DELETE FROM BookIssues WHERE IssueID = 105;

This removes exactly one row, IssueID 105 (The Alchemist, Rohan Gupta). Written without the WHERE clause, DELETE FROM BookIssues; empties the entire table — every issue record for every student, gone in one statement, with no undo built into SQL itself. The rule worth memorizing: before running an UPDATE or a DELETE, read the WHERE clause first and check, in your head, exactly which rows it will touch — ideally by running the same condition as a SELECT first, to see the affected rows before changing or removing them.

After the UPDATE and DELETE above, BookIssues holds four rows: 101 (now Returned = 'Yes'), 102 (Returned = 'Yes'), 103 (Returned = 'No'), 104 (Returned = 'No'). Running the earlier overdue query again — WHERE Returned = 'No' AND DueDate < '2026-07-20' — now returns zero rows, since row 101, the only one that had matched, no longer has Returned = 'No'. And SELECT COUNT(*) FROM BookIssues WHERE Returned = 'No'; now evaluates to 2, not 4, because two of the four originally-unreturned rows changed state. This is the essential nature of a database: query results are not fixed answers, they are computed fresh from whatever the tables currently hold.

Check Your Understanding

Work these out using the tables exactly as they stand after the UPDATE and DELETE above: Students unchanged (six rows, including Ishaan Verma with a NULL ClassSection); BookIssues holding IssueID 101 (StudentID 1, Wings of Fire, Returned 'Yes'), 102 (StudentID 3, A Brief History of Time, Returned 'Yes'), 103 (StudentID 1, The Discovery of India, DueDate 2026-07-29, Returned 'No'), and 104 (StudentID 2, Malgudi Days, DueDate 2026-07-22, Returned 'No').

1. Write a SQL query that lists the names of all students in section 9B. (Answer: SELECT Name FROM Students WHERE ClassSection = '9B'; — returns Diya Patel and Meera Iyer.)
2. What does SELECT COUNT(*) FROM BookIssues WHERE Returned = 'No'; evaluate to right now? (Answer: 2 — rows 103 and 104 are the only ones with Returned = 'No'.)
3. A classmate writes DELETE FROM BookIssues WHERE StudentID = 1; intending to remove Aarav's currently-overdue book (103). What actually happens, and why is this risky? (Answer: it deletes both row 101 and row 103, because both have StudentID = 1 — including the already-returned Wings of Fire record, which had nothing wrong with it. The condition was broader than intended; IssueID = 103 would have been the precise, safe choice.)
4. Using JOIN, write a query listing the borrower's name and book title for every book not yet returned, sorted by due date, and state its output. (Answer: SELECT Students.Name, BookIssues.BookTitle, BookIssues.DueDate FROM Students JOIN BookIssues ON Students.StudentID = BookIssues.StudentID WHERE BookIssues.Returned = 'No' ORDER BY BookIssues.DueDate; — output: Diya Patel, Malgudi Days, 2026-07-22; then Aarav Sharma, The Discovery of India, 2026-07-29.)
5. Why does SELECT Name FROM Students WHERE ClassSection = NULL; return zero rows even though Ishaan Verma's row has ClassSection set to NULL? What is the correct query? (Answer: NULL represents an unknown value, and = NULL always evaluates to unknown rather than true, so no row is ever kept by an equals comparison against NULL — even a row that genuinely holds NULL. The correct form is WHERE ClassSection IS NULL;, which does return Ishaan Verma.)
6. A classmate says "SQL is a database." Correct this statement precisely. (Answer: SQL is a query language used to create, read, update, and delete data. The database is the organized collection of tables themselves. A DBMS — such as MySQL or PostgreSQL — is the software that stores those tables and carries out the SQL commands issued against them. The three are related but distinct.)

Summary

• A table organizes data into fixed-shape records (rows) made of typed fields (columns), replacing free-form notes with consistently structured entries that can be searched reliably.
• A primary key is a field guaranteed unique per row, used to identify that row without ambiguity; a foreign key stores another table's primary key value, linking related tables together.
• SQL is the language used to talk to a DBMS: CREATE TABLE defines a table's columns and types (INT, VARCHAR(n), DATE); INSERT INTO adds rows; SELECT ... WHERE retrieves and filters rows; ORDER BY sorts results; aggregate functions (COUNT, SUM, AVG, MAX, MIN), optionally with GROUP BY, compute summaries; UPDATE ... SET ... WHERE changes existing values; DELETE FROM ... WHERE removes rows; JOIN ... ON combines matching rows across two tables via their key columns.
• NULL means "unknown or missing," not zero or empty text, and must be tested with IS NULL / IS NOT NULL, never = or !=.
• UPDATE and DELETE apply to every row that matches their WHERE clause — omit WHERE, and the statement applies to the entire table at once, which is almost never what you actually want.

Think About It

Think about this: How would you explain database fundamentals: sql for data-driven applications 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.