SQL Explained with Queries You Can Follow - Yenra

Start with a small dataset

These fictional library records are deliberately small enough to check by eye. Each book has a distinct book_id. A loan refers to a book by that identifier. Two loans for one book represent two separate loan records.

These examples use SQLite. Its WITH clause lets each exercise name temporary query results built from literal VALUES. They exist for that statement, so the exercises require no setup tables and change no stored data.

Download the complete SQL practice file. Use an SQLite client and run a whole statement, including its WITH section. The official SQLite command-line documentation explains installation and the shell. In that shell, .read sql-practice.sql runs a downloaded file from the current directory. The dot command belongs to the shell, not SQL itself.

Select, filter, and sort

Start with a question: which science books are listed? SELECT chooses output columns, FROM identifies the input, WHERE filters rows, and ORDER BY establishes result order.

WITH books(book_id, title, topic) AS (
  VALUES (1, 'Moon Atlas', 'science'),
         (2, 'Garden Notes', 'nature'),
         (3, 'Star Map', 'science')
)
SELECT title FROM books
WHERE topic = 'science'
ORDER BY title;

Expected result: Moon Atlas, then Star Map. The nature book is excluded by the filter. The output contains one column because the SELECT list contains only title.

Read the question before editing the query. Changing the filter to topic = 'nature' yields Garden Notes. Removing WHERE includes all three titles. Keep ORDER BY when you need predictable order; an engine's incidental row order is not a guarantee. The SQLite SELECT reference explains filtering, joining, grouping, and ordering.

Join related records

Join each loan to its book title by matching identifiers. Paste the same books definition and the loans definition shown below together with SELECT:

WITH books(book_id, title, topic) AS (
  VALUES (1, 'Moon Atlas', 'science'),
         (2, 'Garden Notes', 'nature'),
         (3, 'Star Map', 'science')
), loans(loan_id, book_id) AS (
  VALUES (101, 1), (102, 1), (103, 3)
)
SELECT l.loan_id, b.title
FROM loans AS l
JOIN books AS b ON b.book_id = l.book_id
ORDER BY l.loan_id;

The aliases l and b shorten column references. The ON condition states which records match. Moon Atlas appears twice because there are two matching loans. That repetition reflects the question being asked: one row per loan. Omitting a necessary relationship can multiply rows unexpectedly; check the join condition and the expected number of records.

Count while keeping zeroes

Now ask how many loans each book has, including books with none. LEFT JOIN preserves each book; COUNT of the matched loan identifier counts actual loan records.

WITH books(book_id, title, topic) AS (
  VALUES (1, 'Moon Atlas', 'science'),
         (2, 'Garden Notes', 'nature'),
         (3, 'Star Map', 'science')
), loans(loan_id, book_id) AS (
  VALUES (101, 1), (102, 1), (103, 3)
)
SELECT b.title, COUNT(l.loan_id) AS loan_count
FROM books AS b
LEFT JOIN loans AS l ON l.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY loan_count DESC, b.title;

GROUP BY establishes one group per book. COUNT(l.loan_id) ignores the NULL value introduced for an unmatched loan; COUNT(*) would count the preserved row and give Garden Notes an unwanted 1. The SQLite aggregate-function reference distinguishes these counts.

Self-check: add the fictional loan (104, 2) to the loans VALUES list. Garden Notes should then have one loan. Because the query uses a secondary alphabetical sort, it appears ahead of Star Map when both have one.

SQL dialects differ in functions, date handling, and some syntax. Keep the chosen engine visible when sharing an example. Real applications also need constraints, transactions, permissions, and parameterized inputs; this exercise teaches reading queries. Practice new statements against sample data, and distinguish data-reading SELECT statements from commands that modify records or schema.