← QCE Digital Solutions Unit 2

Efficient SQL queries

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.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 118

Understanding Efficient SQL queries in QCE Digital Solutions Unit 2

What it does: The WHERE clause limits the rows at the database, so only Red house students come back, instead of fetching all rows and filtering later. A single join gets the related data in one trip to the database, rather than repeating a query for each student. Naming only the fields you need means less data is read and sent back. SELECT * fetches every column, even ones you never use. SELECT * returns all fields. An efficient query lists just the two fields needed, such as SELECT firstName, points FROM students. Indexes speed up searches on fields that are often used in WHERE or ON. A field that is searched a lot and has many different values is a good index candidate.

Common mistakes: SELECT * returns every field, including ones the query never uses. An efficient query names only the fields it needs: SELECT name, price. SQL uses a single = to compare values in a WHERE condition. == is not valid SQL. It should be WHERE house = 'Red';.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Efficient SQL queries to a class →

This module's key terms are also in a printable crossword (Modules 337–409) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

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

SELECT name FROM productsSELECT * FROM products

Matches

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.

Applying

Meanings

Term

SELECT name FROM products WHERE stock > 0

Meaning

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.

Practice

SELECT name, price, stock

Testing

Gaps

SELECT name FROM products stock > 0;

Check Answer

Spots

1SELECT *

2FROM products

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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

A. SELECT name FROM products ✓

B. SELECT * FROM products

Naming only the fields you need means less data is read and sent back. SELECT * fetches every column, even ones you never use.

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 ✓

A WHERE clause makes the database do the filtering and return only the matching rows. Fetching everything and filtering in code wastes time and memory.

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

A. False ✓

B. True

SELECT * returns all fields. An efficient query lists just the two fields needed, such as SELECT firstName, points FROM students.

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

Indexes speed up searches on fields that are often used in WHERE or ON. A field that is searched a lot and has many different values is a good index candidate.

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 ✓

A single join lets the database match the rows in one go. Running a repeated query for every order makes many round trips to the database and is much slower.

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.

Key Term

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.

Fill in the Blanks

1. SELECT name FROM products WHERE stock > 0;

Hint: Type the keyword that filters rows so only matching records are returned.

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

Hint: Type the single field the comment says is needed, instead of selecting every field.

3. CREATE INDEX idxLast ON students(lastName);

Hint: Type the keyword that builds a structure to make searching a field faster.

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

Hint: Type the name of the field the comment says is searched often, so it should be indexed.

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

Hint: Type the keyword that combines two tables in one query instead of running repeated queries.

Spot the Error

Question 1

A SELECT *

B FROM products

C WHERE stock > 0;

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

Question 2

A SELECT firstName, house

B FROM students

C WHERE house == 'Red';

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

Question 3

A SELECT name, stock

B FROM products

C WHERE category = 'Tech'

D OR stock > 0;

Line 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.

Question 4

A CREATE INDEX idx_last

B ON students lastName;

C SELECT firstName, lastName

D FROM students

E WHERE lastName = 'Smith';

Line 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.

Question 5

A SELECT name, price

B FROM products, orders

C WHERE category = 'Tech';

Line 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.

Student with a class code? Join your class →