Studyledge

Databases and SQL: Question 9

Syllabus 8.3

Structured AS 8 marks

Fernbank Books stores its stock in a single table, BOOK.

BOOK table structure: ISBN (VARCHAR(13), primary key), Title (VARCHAR(40)), Genre (VARCHAR(15)), Author (VARCHAR(30)), Price (REAL), Stock (INTEGER).

BOOK data

ISBN Title Genre Author Price Stock
9780001 The Silent Orbit Sci-Fi J Ramirez 8.99 4
9780002 Garden of Ash Fiction J Ramirez 6.50 0
9780003 Coding for Beginners Non-Fiction A Osei 12.99 10
9780004 The Silent Orbit II Sci-Fi J Ramirez 9.50 2
9780005 Whispering Hills Fiction K Novak 7.25 5
9780006 Data Structures Explained Non-Fiction A Osei 14.00 0
9780007 The Last Harvest Fiction K Novak 6.99 3

Each part below refers back to this original table data, regardless of any earlier part.

(a) Write an SQL statement to display every distinct Genre value held in BOOK. [2]

(b) Write an SQL statement to display the Title and Author of every book whose Author starts with the letter 'J'. [2]

(c) Write an SQL statement to display the Title and Price of every book with a Price between 7.00 and 12.00 inclusive. [2]

(d) Write an SQL statement to count how many books in BOOK are currently out of stock (that is, Stock equal to 0). [2]

Show worked solution Hide worked solution

Worked solution

Part (a): DISTINCT

SELECT DISTINCT Genre
FROM BOOK;

Reading Genre down the table gives: Sci-Fi, Fiction, Non-Fiction, Sci-Fi, Fiction, Non-Fiction, Fiction. Without DISTINCT, this would return all seven values with repeats; DISTINCT collapses repeated values so each genre is returned only once, giving three rows: Sci-Fi, Fiction, Non-Fiction.

[2 marks]: [1] for SELECT ... FROM BOOK targeting the Genre field, [1] for DISTINCT correctly removing repeated genre values.

Part (b): LIKE wildcard

SELECT Title, Author
FROM BOOK
WHERE Author LIKE 'J%';

LIKE 'J%' matches any Author value that starts with the letter J, where % stands for any (including zero) further characters. Checking every row: 9780001, 9780002 and 9780004 all have Author = 'J Ramirez', which starts with J, so they match; 9780003 and 9780006 (A Osei) and 9780005 and 9780007 (K Novak) do not.

TitleAuthor
The Silent OrbitJ Ramirez
Garden of AshJ Ramirez
The Silent Orbit IIJ Ramirez

[2 marks]: [1] for SELECT Title, Author ... WHERE, [1] for the correct LIKE 'J%' pattern (wildcard at the end, matching the start of the string) selecting exactly these three rows.

Part (c): BETWEEN

SELECT Title, Price
FROM BOOK
WHERE Price BETWEEN 7.00 AND 12.00;

BETWEEN 7.00 AND 12.00 is inclusive of both boundary values. Checking every Price: 8.99 (9780001) qualifies; 6.50 (9780002) is below 7.00, excluded; 12.99 (9780003) is above 12.00, excluded; 9.50 (9780004) qualifies; 7.25 (9780005) qualifies; 14.00 (9780006) is above 12.00, excluded; 6.99 (9780007) is below 7.00, excluded.

TitlePrice
The Silent Orbit8.99
The Silent Orbit II9.50
Whispering Hills7.25

[2 marks]: [1] for SELECT Title, Price ... WHERE, [1] for the correct BETWEEN 7.00 AND 12.00 condition selecting exactly these three rows.

Part (d): COUNT with a filter

SELECT COUNT(*) AS OutOfStock
FROM BOOK
WHERE Stock = 0;

The WHERE Stock = 0 condition first filters BOOK down to only the out-of-stock rows: 9780002 (Garden of Ash, Stock 0) and 9780006 (Data Structures Explained, Stock 0). COUNT(*) then counts how many rows remain after filtering, giving 2.

[2 marks]: [1] for WHERE Stock = 0 correctly filtering to the out-of-stock books, [1] for COUNT(*) correctly counting them, giving 2.

Final answers

  • (a) SELECT DISTINCT Genre FROM BOOK; -> Sci-Fi, Fiction, Non-Fiction.
  • (b) SELECT Title, Author FROM BOOK WHERE Author LIKE 'J%'; -> three books by J Ramirez.
  • (c) SELECT Title, Price FROM BOOK WHERE Price BETWEEN 7.00 AND 12.00; -> 8.99, 9.50, 7.25.
  • (d) SELECT COUNT(*) FROM BOOK WHERE Stock = 0; -> 2.