The Reorder List

Story

Every Monday the bookshop manager wants a short reorder list: the recent fiction and science titles that are almost sold out. The catalogue lives in one table and you are the person who knows SQL.

Task

Write one SQL SELECT statement over the table books:

| column | type | meaning | |---|---|---| | id | INTEGER | primary key | | title | TEXT | book title | | genre | TEXT | e.g. Fiction, Science, Travel | | published_year | INTEGER | year of publication | | price_cents | INTEGER | price in cents | | stock | INTEGER | copies on the shelf; NULL means "not counted yet" |

List the books that satisfy all of these:

• genre is Fiction or Science
• published in 2015 or later
• stock is below 5 (a book with 5 copies is fine; books whose stock was never counted are not on the list)

Return the columns title, published_year, stock in that order, sorted by stock ascending, then published_year descending, then title ascending. Return at most 5 rows.

How your answer is graded

Submit only the SQL (one statement, a trailing ; is fine; do not use three double quotes in a row). A hidden Python program creates the books table with different data for each test, runs your statement read-only and prints every row with | between the values. When nothing matches it prints (no rows). That is why the language is Python here, and why you cannot run your SQL directly in Code Lab: use the playground below to experiment.

Example

Playground (paste into Code Lab, change query, run):

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT NOT NULL,
                    published_year INTEGER NOT NULL, price_cents INTEGER NOT NULL, stock INTEGER);
INSERT INTO books VALUES
  (1, 'The Silent Orchard', 'Fiction', 2019, 1599, 3),
  (2, 'Quantum Gardens', 'Science', 2021, 2499, 0),
  (3, 'Atlas of Small Rivers', 'Travel', 2018, 1999, 2),
  (4, 'Salt and Ember', 'Fiction', 2016, 1299, 4),
  (5, 'Letters to a Young Coder', 'Science', 2012, 1799, 1),
  (6, 'The Clockmaker''s Daughter', 'Fiction', 2022, 1499, 5),
  (7, 'Deep Time', 'Science', 2020, 2199, NULL),
  (8, 'Harbor Lights', 'Fiction', 2019, 1399, 3),
  (9, 'Cooking with Fire', 'Cooking', 2021, 2899, 1),
  (10, 'Orbit Notes', 'Science', 2015, 1899, 2),
  (11, 'Paper Cities', 'Fiction', 2023, 1699, 4),
  (12, 'The Last Lighthouse', 'Fiction', 2014, 999, 0);
""")

query = """
SELECT title, published_year, stock
FROM books;
"""
for row in db.execute(query):
    print("|".join(map(str, row)))

The correct statement prints exactly:

Quantum Gardens|2021|0
Orbit Notes|2015|2
Harbor Lights|2019|3
The Silent Orchard|2019|3
Paper Cities|2023|4

("Salt and Ember" also qualifies but is cut off by the 5-row limit.)