Describing what a query does in plain English

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. In SELECT firstName, points FROM students, what does the SELECT part tell us?

A Which fields are shown: firstName and points

B Which records are kept: only students named firstName

2. Which clause of a query decides which RECORDS (rows) are returned?

A WHERE

B SELECT

3. SELECT name FROM products WHERE price > 10. Which is the best plain-English description?

A Show the price of every product named 10

B Show the name of every product that costs more than 10

4. What does ORDER BY points DESC do in a query?

A Picks only the records that have the highest points

B Puts the results in order from highest points to lowest

5. SELECT firstName FROM students WHERE house = 'Red' ORDER BY firstName. Which description is correct?

A Show first names of Red house students, sorted A to Z

B Show all students from every house, sorted by house

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 house = 'Red' ORDER BY points DESC;		AShows the name and price of products that have no stock left.
2SELECT name, price FROM products WHERE stock = 0;		BShows the first name and points of students in Red house, with the highest points first.
3The SELECT part tells you which fields (columns) appear in the results.		CSELECT firstName FROM students ORDER BY firstName ASC;
4The ORDER BY part tells you the order the records appear in, such as A to Z.		DSELECT name shows only the name field, not all fields. WHERE price > 10 picks the records, but the SELECT part limits the fields.
5Plain English: SELECT name FROM products WHERE price > 10 means 'show all fields of products costing over 10'.		ESELECT lastName, yearLevel FROM students;
6Plain English: SELECT firstName FROM students WHERE house = 'Blue' means 'show Blue house students sorted by name'.		FThere is no ORDER BY clause, so the query does not sort the results. WHERE only picks the Blue house records.

Definitions

SELECT WHERE ORDER BY

Fill in the Blanks

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

______ firstName, points FROM students ORDER BY points DESC;

SELECT firstName FROM students _____ house = 'Red';

SELECT name, price FROM products ________ price DESC;

SELECT firstName, points FROM students WHERE points _ 100;

SELECT firstName FROM students ORDER BY points ____;

Spot the Error

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

Question 1

A -- Red house student names

B SELECT firstName, lastName

C FROM students

D WHERE house = Red;

Question 2

A -- Cheapest products first

B SELECT name, price

C FORM products

D ORDER BY price ASC;

Question 3

A SELECT firstName points

B FROM students

C WHERE points > 100

D ORDER BY points DESC;

Question 4

A SELECT name, stock

B FROM products;

C WHERE stock = 0;

Question 5

A SELECT firstName, house

B FROM students

C WHERE yearLevel = 7

D ORDER firstName ASC;

Answer Key

Describing what a query does in plain English

Section A: Quick Questions

A — Which fields are shown: firstName and points

A — WHERE

B — Show the name of every product that costs more than 10

B — Puts the results in order from highest points to lowest

A — Show first names of Red house students, sorted A to Z

Section B: Match the Pairs

SELECT firstName, points FROM students WHERE house = 'Red' ORDER BY points DESC; → Shows the first name and points of students in Red house, with the highest points first.

SELECT name, price FROM products WHERE stock = 0; → Shows the name and price of products that have no stock left.

The SELECT part tells you which fields (columns) appear in the results. → SELECT lastName, yearLevel FROM students;

The ORDER BY part tells you the order the records appear in, such as A to Z. → SELECT firstName FROM students ORDER BY firstName ASC;

Plain English: SELECT name FROM products WHERE price > 10 means 'show all fields of products costing over 10'. → SELECT name shows only the name field, not all fields. WHERE price > 10 picks the records, but the SELECT part limits the fields.

Plain English: SELECT firstName FROM students WHERE house = 'Blue' means 'show Blue house students sorted by name'. → There is no ORDER BY clause, so the query does not sort the results. WHERE only picks the Blue house records.

Section C: Definitions

SELECT WHERE ORDER BY — Read a query one clause at a time: SELECT names which fields are shown, WHERE picks which records, and ORDER BY sets the order the results appear in.

Section D: Fill in the Blanks

SELECT firstName, points
FROM students
ORDER BY points DESC;

SELECT firstName
FROM students
WHERE house = 'Red';

SELECT name, price
FROM products
ORDER BY price DESC;

SELECT firstName, points
FROM students
WHERE points > 100;

SELECT firstName
FROM students
ORDER BY points DESC;

Section E: Spot the Error

D — WHERE picks the records. Text values must be in quotes, so it should be WHERE house = 'Red';

C — FROM names the table the records come from. It is spelled FROM, not FORM.

A — SELECT lists the fields to show, and the fields must be separated by commas. It should be SELECT firstName, points

B — The semicolon ends the query too early, so the WHERE line is cut off. Remove it: FROM products

D — ORDER BY sets the order of the results. The keyword is two words, so it should be ORDER BY firstName ASC;