Clause order in a full SQL query

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which clause order is correct in a full SQL query?

A SELECT, WHERE, FROM, ORDER BY

B SELECT, FROM, WHERE, ORDER BY

2. Which statement puts ORDER BY in the right place?

A SELECT name FROM students WHERE points > 100 ORDER BY name;

B SELECT name FROM students ORDER BY name WHERE points > 100;

3. In a full query, what does the database do with WHERE and ORDER BY?

A ORDER BY sorts all the rows, then WHERE is ignored

B WHERE filters the rows, then ORDER BY sorts the rows that are left

4. Where does the semicolon go in a statement with SELECT, FROM, WHERE and ORDER BY?

A At the end of the ORDER BY line, once for the whole statement

B At the end of every line, after each clause

5. Which version is written in the correct style for a Cambridge IGCSE script?

A SELECT name FROM students WHERE house = 'Red' ORDER BY name;

B select name from students order by name where house = 'Red'

Match the Pairs

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

Left column		Right column
1SELECT firstName, points FROM students WHERE points > 100 ORDER BY points DESC;		AThe last clause of a full query. It sorts the rows that are left after WHERE has filtered them.
2ORDER BY points DESC;		BORDER BY is placed before WHERE. The clause order must be SELECT, FROM, WHERE, ORDER BY.
3WHERE comes straight after FROM and filters the rows before they are sorted.		CA semicolon is put after the SELECT line. The semicolon belongs only at the end of the whole statement.
4Keywords are written in capitals, one clause per line, and the semicolon goes only at the end of the whole statement.		DA full query with the clauses in the correct order: SELECT, FROM, WHERE, then ORDER BY, with one semicolon at the very end.
5SELECT firstName, points FROM students ORDER BY points WHERE points > 100;		EFROM students WHERE house = 'Red'
6SELECT name, price; FROM products WHERE price > 5 ORDER BY price;		FSELECT name, price FROM products ORDER BY price;

Definitions

SELECT FROM WHERE ORDER BY

Fill in the Blanks

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

SELECT name, price FROM products WHERE stock > 0 ________ price;

SELECT name, price FROM products _____ stock > 0 ORDER BY price;

SELECT firstName FROM students WHERE yearLevel = 8 ORDER BY points ____;

SELECT name, price ____ products WHERE stock > 0 ORDER BY price;

______ firstName, points FROM students WHERE points > 100 ORDER BY points;

Spot the Error

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

Question 1

A FROM firstName, points

B FROM students

C WHERE points > 100

D ORDER BY points DESC;

Question 2

A SELECT name, price

B FROM products

C WHERE stock > 0

D ORDER BY price

Question 3

A SELECT firstName

B FROM students;

C WHERE house = 'Red'

D ORDER BY firstName;

Question 4

A SELECT studentId, mark

B FROM results

C AND mark >= 50

D ORDER BY mark DESC;

Question 5

A SELECT name, stock;

B FROM products

C WHERE stock < 10

D ORDER BY stock;

Answer Key

Clause order in a full SQL query

Section A: Quick Questions

B — SELECT, FROM, WHERE, ORDER BY

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

B — WHERE filters the rows, then ORDER BY sorts the rows that are left

A — At the end of the ORDER BY line, once for the whole statement

A — SELECT name FROM students WHERE house = 'Red' ORDER BY name;

Section B: Match the Pairs

SELECT firstName, points FROM students WHERE points > 100 ORDER BY points DESC; → A full query with the clauses in the correct order: SELECT, FROM, WHERE, then ORDER BY, with one semicolon at the very end.

ORDER BY points DESC; → The last clause of a full query. It sorts the rows that are left after WHERE has filtered them.

WHERE comes straight after FROM and filters the rows before they are sorted. → FROM students WHERE house = 'Red'

Keywords are written in capitals, one clause per line, and the semicolon goes only at the end of the whole statement. → SELECT name, price FROM products ORDER BY price;

SELECT firstName, points FROM students ORDER BY points WHERE points > 100; → ORDER BY is placed before WHERE. The clause order must be SELECT, FROM, WHERE, ORDER BY.

SELECT name, price; FROM products WHERE price > 5 ORDER BY price; → A semicolon is put after the SELECT line. The semicolon belongs only at the end of the whole statement.

Section C: Definitions

SELECT FROM WHERE ORDER BY — The order of clauses in a full SQL query: SELECT, FROM, WHERE, then ORDER BY. WHERE filters the rows first, then ORDER BY sorts the rows that are left.

Section D: Fill in the Blanks

SELECT name, price FROM products
WHERE stock > 0
ORDER BY price;

SELECT name, price FROM products
WHERE stock > 0
ORDER BY price;

SELECT firstName FROM students
WHERE yearLevel = 8
ORDER BY points DESC;

SELECT name, price FROM products
WHERE stock > 0
ORDER BY price;

SELECT firstName, points FROM students
WHERE points > 100
ORDER BY points;

Section E: Spot the Error

A — A full query starts with SELECT, which lists the fields to show. FROM comes second and names the table. Line 1 should be SELECT firstName, points.

D — The semicolon marks the end of the whole statement, so it goes after the last clause. Line 4 should be ORDER BY price;

B — A semicolon ends the whole statement. Putting one after FROM ends the query too early, before WHERE and ORDER BY. Line 2 should be FROM students with no semicolon.

C — A condition needs the WHERE keyword to start the filter. AND can only join extra conditions to an existing WHERE. Line 3 should be WHERE mark >= 50.

A — The semicolon belongs only at the end of the whole statement. Ending the SELECT line with one stops the query before FROM. Line 1 should be SELECT name, stock.