← QCE Digital Solutions Unit 2

CREATE VIEW

Saves a SELECT query under a name so you can query it like a table. A view stores the query, not a copy of the data, so it always shows current rows. Remove it with DROP VIEW.

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

Understanding CREATE VIEW in QCE Digital Solutions Unit 2

What it does: The SELECT inside the view contains a JOIN with an ON condition. Querying student_marks then needs no join code. The view name goes in the FROM clause. The query runs against the current data in the underlying tables, so it always shows up-to-date rows. A view saves typing and keeps the query consistent. It does not store a one-off result.

Common mistakes: A view over a join still needs a correct ON condition. The foreign key results.studentId must equal the primary key students.studentId, so it should be ON s.studentId=r.studentId;. CREATE VIEW needs the keyword AS before the SELECT query it saves. The line should be CREATE VIEW stock_low AS.

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 CREATE VIEW to a class →

This module's key terms are also in a printable crossword (Modules 305–336) 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 statement correctly saves a query as a view?

CREATE VIEW top_students SELECT firstName, points FROM students WHERE points > 100;CREATE VIEW top_students AS SELECT firstName, points FROM students WHERE points > 100;

Matches

CREATE VIEW top_students SELECT firstName, points FROM students;

↓

The AS keyword is missing. CREATE VIEW must be followed by the view name, then AS, then the SELECT query.

Applying

Meanings

Term

CREATE VIEW

Meaning

Saves a SELECT query under a name so you can query it like a table. A view stores the query, not a copy of the data, so it always shows current rows. Remove it with DROP VIEW.

Practice

CREATE VIEW student_marks AS

Testing

Gaps

highMarks AS

Check Answer

Spots

3FROM students s JOIN results r

4ON s.studentId=r.subjectId;

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 statement correctly saves a query as a view?

A. CREATE VIEW top_students AS SELECT firstName, points FROM students WHERE points > 100; ✓

B. CREATE VIEW top_students SELECT firstName, points FROM students WHERE points > 100;

A view needs the keyword AS before the SELECT. The pattern is CREATE VIEW name AS SELECT ...

2. True or false: a view stores its own separate copy of the data from the tables it is built on.

A. True: a view keeps a copy of the rows

B. False: a view stores only the saved SELECT query ✓

A view saves the query, not the data. Each time you use it, the query runs on the real tables.

3. A view called high_marks was created from the results table. Later a new row is added to results with a mark of 99. What happens when you query the view?

A. The new row appears, because the view runs its query on the current data ✓

B. The new row is missing, because the view kept an old copy of the data

Views always show current rows because the saved query is run again each time.

4. Which query reads rows from a view named house_points?

A. SELECT VIEW house_points;

B. SELECT * FROM house_points; ✓

Once a view exists, you query it with SELECT ... FROM, the same as a table.

5. Which view definition is built over a join of two tables?

A. CREATE VIEW student_marks AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId; ✓

B. CREATE VIEW student_marks AS SELECT s.firstName, r.mark FROM students s JOIN results r;

A view over a join still needs the ON condition (foreign key equals primary key). Without it, every row is paired with every other row.

Match the Pairs

CREATE VIEW top_students AS SELECT firstName, points FROM students WHERE points > 100; → Saves the SELECT query under the name top_students so it can be used like a table

DROP VIEW top_students; → Deletes the saved view but leaves the original table data untouched

A view can hold a join, so students can query results from two tables as if they were one table → CREATE VIEW student_marks AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Once a view exists, you query it with SELECT FROM the view name, just like a table → SELECT * FROM student_marks WHERE mark > 80;

CREATE VIEW top_students SELECT firstName, points FROM students; → The AS keyword is missing. CREATE VIEW must be followed by the view name, then AS, then the SELECT query.

DROP TABLE top_students; → DROP TABLE is used to remove a view. A view must be removed with DROP VIEW, otherwise the command fails because top_students is not a table.

Key Term

CREATE VIEW

Saves a SELECT query under a name so you can query it like a table. A view stores the query, not a copy of the data, so it always shows current rows. Remove it with DROP VIEW.

Fill in the Blanks

1. CREATE VIEW highMarks AS SELECT studentId, mark FROM results WHERE mark >= 80;

Hint: Type the two-word command that saves a query under a name.

2. CREATE VIEW year7 AS SELECT firstName, house FROM students WHERE yearLevel = 7;

Hint: Type the keyword that links the view name to the SELECT query it saves.

3. CREATE VIEW studentMarks AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Hint: Type the keyword that introduces the matching condition for the join inside the view's query.

4. SELECT firstName, mark FROM studentMarks WHERE mark > 90;

Hint: Type the keyword that names the source of the rows. Here the source is a view, used just like a table.

5. DROP VIEW studentMarks;

Hint: Type the kind of object being removed; the tables and their data stay untouched.

Spot the Error

Question 1

A CREATE VIEW mark_list AS

B SELECT firstName, mark

C FROM students s JOIN results r

D ON s.studentId=r.subjectId;

Line D — A view over a join still needs a correct ON condition. The foreign key results.studentId must equal the primary key students.studentId, so it should be ON s.studentId=r.studentId;.

Question 2

A CREATE VIEW stock_low

B SELECT name, stock

C FROM products

D WHERE stock < 20;

Line A — CREATE VIEW needs the keyword AS before the SELECT query it saves. The line should be CREATE VIEW stock_low AS.

Question 3

A CREATE VIEW red_house AS

B SELECT * FROM students

C WHERE house = Red;

D SELECT * FROM red_house;

Line C — Text values in a WHERE condition must be in quotes. Without them SQL looks for a column called Red. It should be WHERE house = 'Red';.

Question 4

A CREATE VIEW stock_low AS

B SELECT name, stock products

C WHERE stock < 20;

D DROP VIEW stock_low;

Line B — The saved query is missing the FROM keyword before the table name. It should be SELECT name, stock FROM products.

Question 5

A CREATE VIEW old_orders AS

B SELECT orderId, orderDate

C FROM orders;

D DROP TABLE old_orders;

Line D — old_orders is a view, not a table, so DROP TABLE is the wrong command. A view is removed with DROP VIEW old_orders;.

Student with a class code? Join your class →