Writing an SQL query from a requirement

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Request: "List the names of students who are in the Red house." Which query is correct?

A SELECT house FROM students WHERE firstName = 'Red';

B SELECT firstName, lastName FROM students WHERE house = 'Red';

2. Request: "How many records are in the products table with a price over 10?" Which query is correct?

A SELECT COUNT(*) FROM products WHERE price > 10;

B SELECT SUM(price) FROM products WHERE price > 10;

3. Request: "Find the total of all points for students in year 8." Which query is correct?

A SELECT SUM(points) FROM students WHERE yearLevel = 8;

B SELECT COUNT(points) FROM students WHERE yearLevel = 8;

4. Request: "Show the names of products in order of price, highest first." Which ORDER BY is correct?

A SELECT name FROM products ORDER BY price ASC;

B SELECT name FROM products ORDER BY price DESC;

5. Which is the correct order of the clauses when writing a query for "names of students with more than 100 points, highest points first"?

A SELECT firstName FROM students WHERE points > 100 ORDER BY points DESC;

B SELECT firstName FROM students ORDER BY points DESC WHERE points > 100;

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT COUNT(*) FROM students WHERE house = 'Red';		ASUM cannot add up * (all fields). To count how many records, use COUNT(*); to total, SUM needs a numeric field.
2SELECT firstName, lastName FROM students WHERE points > 100 ORDER BY points DESC;		BLists the names of students with more than 100 points, highest points first
3'The total of' a numeric field for some records needs SUM with a WHERE condition		CSELECT SUM(quantity) FROM orders WHERE productId = 2;
4'In order of ... highest first' is written with ORDER BY and DESC at the end of the query		DAnswers 'how many records have house Red?' by counting the matching students
5SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Blue';		ESELECT name, price FROM products ORDER BY price DESC;
6SELECT SUM(*) FROM products WHERE stock = 0;		FORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.

Definitions

SELECT fields FROM table WHERE condition ORDER BY field DESC;

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT firstName, lastName FROM students WHERE _____ = 'Red';

SELECT _____(*) FROM students WHERE yearLevel = 8;

SELECT ___(quantity) FROM orders WHERE productId = 2;

SELECT firstName, points FROM students ORDER BY points ____;

SELECT name, price FROM ________ WHERE stock = 0;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT firstName, lastName

B FROM student

C WHERE house = 'Red';

Question 2

A SELECT COUNT(*)

B FORM products

C WHERE stock = 0;

Question 3

A SELECT SUM(quantity)

B FROM orders

C WHERE studentId == 1;

Question 4

A SELECT name, price

B FROM price

C WHERE category = 'Tech'

D ORDER BY price DESC;

Question 5

A SELECT firstName points

B FROM students

C WHERE points > 100

D ORDER BY points DESC;

Answer Key

Writing an SQL query from a requirement

Section A: Quick Questions

B — SELECT firstName, lastName FROM students WHERE house = 'Red';

A — SELECT COUNT(*) FROM products WHERE price > 10;

A — SELECT SUM(points) FROM students WHERE yearLevel = 8;

B — SELECT name FROM products ORDER BY price DESC;

A — SELECT firstName FROM students WHERE points > 100 ORDER BY points DESC;

Section B: Match the Pairs

SELECT COUNT(*) FROM students WHERE house = 'Red'; → Answers 'how many records have house Red?' by counting the matching students

SELECT firstName, lastName FROM students WHERE points > 100 ORDER BY points DESC; → Lists the names of students with more than 100 points, highest points first

'The total of' a numeric field for some records needs SUM with a WHERE condition → SELECT SUM(quantity) FROM orders WHERE productId = 2;

'In order of ... highest first' is written with ORDER BY and DESC at the end of the query → SELECT name, price FROM products ORDER BY price DESC;

SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Blue'; → ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.

SELECT SUM(*) FROM products WHERE stock = 0; → SUM cannot add up * (all fields). To count how many records, use COUNT(*); to total, SUM needs a numeric field.

Section C: Definitions

SELECT fields FROM table WHERE condition ORDER BY field DESC; — Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

Section D: Fill in the Blanks

SELECT firstName, lastName FROM students
WHERE house = 'Red';

SELECT COUNT(*) FROM students
WHERE yearLevel = 8;

SELECT SUM(quantity) FROM orders
WHERE productId = 2;

SELECT firstName, points FROM students
ORDER BY points DESC;

SELECT name, price
FROM products
WHERE stock = 0;

Section E: Spot the Error

B — The table is called students, so the FROM line must read FROM students. A table name that does not exist makes the query fail.

B — The keyword is misspelt. The table name must follow FROM, so the line should be FROM products.

C — SQL tests equality with a single =, not ==. The line should be WHERE studentId = 1;

B — FROM must be followed by the table name, not a field name. It should be FROM products.

A — Fields in a SELECT list must be separated by commas. It should be SELECT firstName, points.