SQL Basics — Asking the Database
ReadingRoom stores its books, members and loans in a relational database. Whether you later use Dapper or Entity Framework Core, SQL is what actually reaches the server. When a page is slow or a number looks wrong, reading the SQL is how you find out why.
The rule
Say what you want — which rows, which columns, in what order — not how.
A relational database keeps data in tables: rows of the same shape, with named, typed columns. ReadingRoom has three:
| Books | Members | Loans |
|---|---|---|
| Id, Title, Author, Year | Id, Name, Email | Id, BookId, MemberId, LoanedOn, DueDate, ReturnedAt |
Four statements do nearly all the everyday work:
SELECT Title, Year FROM Books WHERE Year < 1970 ORDER BY Year;
INSERT INTO Books (Title, Author, Year) VALUES ('Dune', 'Frank Herbert', 1965);
UPDATE Loans SET ReturnedAt = '2025-03-02' WHERE Id = 40;
DELETE FROM Members WHERE Id = 12;A JOIN combines tables through matching columns. Loans.BookId holds a book's Id, so:
SELECT b.Title, m.Name, l.DueDate
FROM Loans l
JOIN Books b ON b.Id = l.BookId
JOIN Members m ON m.Id = l.MemberId
WHERE l.ReturnedAt IS NULL;That lists every book currently out and who has it. Aggregates summarise: SELECT MemberId, COUNT(*) FROM Loans GROUP BY MemberId counts loans per member.
NULL means "no value". It is never equal to anything, not even another NULL, so test it with IS NULL, never = NULL.
The shortcut
Build a query in the order the database thinks, not the order you type: FROM → WHERE → SELECT → ORDER BY. First the table, then which rows, then which columns, then the order.
FROM
Which table holds the answer?
FROM Books.
The trap: an UPDATE or DELETE without WHERE. It changes every row in the table.
Drills
Answer fast — trust the rule.
Which condition finds loans that have not been returned?
What does DELETE FROM Loans; do?
How do you pair each loan with its book's title?
Which query counts loans per member?
Remember
- Say what you want — which rows, which columns, in what order — not how.
- Think FROM → WHERE → SELECT → ORDER BY.
IS NULL, never= NULL; never UPDATE or DELETE without WHERE.