Efficient SQL queries

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which query is more efficient for a screen that only shows product names?

A SELECT name FROM products

B SELECT * FROM products

2. Which query filters early so the database returns only the rows needed?

A SELECT name FROM products, then skip stock = 0 rows in Python

B SELECT name FROM products WHERE stock > 0

3. True or false: SELECT * is the most efficient way to write a query when you only need two fields.

A False

B True

4. Which field is the best choice to add an index to, if students are often looked up by it?

A lastName, because it is searched often in WHERE

B house, because it has only a few different values

5. Which approach is more efficient to list each order with the student's first name?

A One query per order to look up its student

B One JOIN query on orders and students

Match the Pairs

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

Left column		Right column
1SELECT name, price FROM products		ASELECT firstName FROM students WHERE house = 'Red'
2CREATE INDEX idx_last ON students(lastName)		BSELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId
3Filter early with WHERE so the database returns only the rows needed		CSELECT * returns every column when only a few are needed. Name the required fields, such as SELECT quantity FROM orders.
4One join is more efficient than running a separate query for every row		DThere is no WHERE clause, so every row comes back even when only in-stock items are needed. Add WHERE stock > 0.
5SELECT * FROM orders WHERE orderId = 4		EAsks only for the two fields needed instead of every column, so less data is read and sent back
6SELECT name FROM products		FBuilds an index on a searched field so the database can find matching records quickly

Definitions

SELECT name FROM products WHERE stock > 0

Fill in the Blanks

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

SELECT name FROM products _____ stock > 0;

-- Only need each year level SELECT _________ FROM students;

CREATE _____ idxLast ON students(lastName);

-- Staff often search by points CREATE INDEX idxPoints ON students(______);

SELECT students.firstName, results.mark FROM students ____ results ON students.studentId = results.studentId;

Spot the Error

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

Question 1

A SELECT *

B FROM products

C WHERE stock > 0;

Question 2

A SELECT firstName, house

B FROM students

C WHERE house == 'Red';

Question 3

A SELECT name, stock

B FROM products

C WHERE category = 'Tech'

D OR stock > 0;

Question 4

A CREATE INDEX idx_last

B ON students lastName;

C SELECT firstName, lastName

D FROM students

E WHERE lastName = 'Smith';

Question 5

A SELECT name, price

B FROM products, orders

C WHERE category = 'Tech';

Answer Key

Efficient SQL queries

Section A: Quick Questions

A — SELECT name FROM products

B — SELECT name FROM products WHERE stock > 0

A — False

A — lastName, because it is searched often in WHERE

B — One JOIN query on orders and students

Section B: Match the Pairs

SELECT name, price FROM products → Asks only for the two fields needed instead of every column, so less data is read and sent back

CREATE INDEX idx_last ON students(lastName) → Builds an index on a searched field so the database can find matching records quickly

Filter early with WHERE so the database returns only the rows needed → SELECT firstName FROM students WHERE house = 'Red'

One join is more efficient than running a separate query for every row → SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId

SELECT * FROM orders WHERE orderId = 4 → SELECT * returns every column when only a few are needed. Name the required fields, such as SELECT quantity FROM orders.

SELECT name FROM products → There is no WHERE clause, so every row comes back even when only in-stock items are needed. Add WHERE stock > 0.

Section C: Definitions

SELECT name FROM products WHERE stock > 0 — An efficient query asks only for the fields and rows it needs: name fields instead of SELECT *, WHERE to filter early, and an index or key to find records fast.

Section D: Fill in the Blanks

SELECT name FROM products WHERE stock > 0;

-- Only need each year level
SELECT yearLevel FROM students;

CREATE INDEX idxLast ON students(lastName);

-- Staff often search by points
CREATE INDEX idxPoints ON students(points);

SELECT students.firstName, results.mark
FROM students JOIN results
ON students.studentId = results.studentId;

Section E: Spot the Error

A — SELECT * returns every field, including ones the query never uses. An efficient query names only the fields it needs: SELECT name, price.

C — SQL uses a single = to compare values in a WHERE condition. == is not valid SQL. It should be WHERE house = 'Red';

D — OR makes the filter wider. It would return every Tech product plus every product in stock. AND keeps only the rows that meet both conditions, so fewer rows are returned.

B — The indexed field must be in brackets after the table name. It should be ON students(lastName); so the index is built on the searched field.

B — Listing an extra table the query never uses pairs every product with every order. That is slow and wasteful. The query only needs FROM products.