← QCE Digital Solutions Unit 2

Views in a relational database

CREATE VIEW makes a view: a stored query shown as a virtual table. It shows chosen data without a second copy, and can restrict access or simplify joins.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 91

Understanding Views in a relational database in QCE Digital Solutions Unit 2

What it does: The view selects only the names, so users given access to it cannot see the other columns in the students table. The join and ON condition are saved inside the view. Users can then run SELECT * FROM MarkList instead of writing the join each time. CREATE VIEW needs the AS keyword between the view name and the SELECT query.

Common mistakes: There is a trailing comma after the last column in the SELECT list, so the stored query is invalid. It should be SELECT firstName, points with no comma before FROM. The alias x is never defined in the FROM clause. students was given the alias s, so it should be s.firstName. A view that simplifies a join still needs correct aliases.

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 Views in a relational database to a class →

This module's key terms are also in a printable crossword (Modules 186–228) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

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

Learning

Duo

A view stores its own separate copy of the data it displays.

True: a view saves a second copy of the rowsFalse: a view stores only the query, and the data stays in the original tables

Matches

CREATE VIEW HouseList SELECT firstName, house FROM students;

↓

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

Applying

Meanings

Term

CREATE VIEW

Meaning

CREATE VIEW makes a view: a stored query shown as a virtual table. It shows chosen data without a second copy, and can restrict access or simplify joins.

Practice

CREATE VIEW studentMarks AS

Testing

Gaps

redStudents AS

Check Answer

Spots

1CREATE VIEW top_students AS

2SELECT firstName, points,

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. A view stores its own separate copy of the data it displays.

A. True: a view saves a second copy of the rows

B. False: a view stores only the query, and the data stays in the original tables ✓

A view is a virtual table. Each time you use it, the stored query runs on the real tables, so there is no second copy of the data.

2. Which statement creates a view of Year 7 students?

A. CREATE VIEW year7 AS SELECT * FROM students WHERE yearLevel = 7; ✓

B. CREATE VIEW year7 SELECT * FROM students WHERE yearLevel = 7;

CREATE VIEW needs the AS keyword between the view name and the SELECT query.

3. A school wants teachers to see student names and houses but not points. Which view helps restrict access?

A. CREATE VIEW houses AS SELECT * FROM students;

B. CREATE VIEW houses AS SELECT firstName, house FROM students; ✓

A view that selects only some columns hides the others, so users given access to the view cannot see the hidden data such as points.

4. A student's points change in the students table. What happens when you next query a view built on that table?

A. The view shows the new points value ✓

B. The view still shows the old points value

A view runs its stored query on the current table data each time, so it always reflects the latest values.

5. Which is a good reason to create a view for a query that joins results, students and subjects?

A. Users can query the view without rewriting the joins each time ✓

B. The view makes the joined data take up less space in the original tables

Views can simplify joins: the join is written once in the view, and users then select from it like a single table. It does not change the original tables.

Match the Pairs

CREATE VIEW HouseList AS SELECT firstName, house FROM students; → Saves a query as a virtual table called HouseList that shows only two columns of students

SELECT * FROM HouseList; → Queries the view as if it were a table, running the stored query to get up-to-date rows

A view can restrict access by showing only some columns, leaving out private data such as points → CREATE VIEW StudentNames AS SELECT firstName, lastName FROM students;

A view can simplify joins by storing the join once so users do not rewrite the ON condition → CREATE VIEW MarkList AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

CREATE VIEW HouseList SELECT firstName, house FROM students; → The AS keyword is missing. CREATE VIEW name must be followed by AS before the SELECT query.

CREATE VIEW Top AS INSERT INTO students VALUES (11, 'Zed'); → A view stores a SELECT query, not an INSERT. The statement after AS must be a SELECT.

Key Term

CREATE VIEW

CREATE VIEW makes a view: a stored query shown as a virtual table. It shows chosen data without a second copy, and can restrict access or simplify joins.

Fill in the Blanks

1. CREATE VIEW redStudents AS SELECT firstName, points FROM students WHERE house = 'Red';

Hint: Type the two-word command that stores a query as a virtual table.

2. CREATE VIEW studentNames AS SELECT firstName, lastName FROM students;

Hint: Type the word for the virtual table being created, which stores a query rather than a second copy of the data.

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

Hint: Type the keyword that combines the second table into the query, so the view hides the join from users.

4. SELECT firstName, points FROM redStudents ORDER BY points DESC;

Hint: Type the keyword that names what is read. A view can be used here like a table.

5. CREATE VIEW year7View AS SELECT firstName, house FROM students WHERE yearLevel = 7;

Hint: Type the short keyword that links the view name to the stored query that follows.

Spot the Error

Question 1

A CREATE VIEW top_students AS

B SELECT firstName, points,

C FROM students

D WHERE points > 100;

Line B — There is a trailing comma after the last column in the SELECT list, so the stored query is invalid. It should be SELECT firstName, points with no comma before FROM.

Question 2

A CREATE VIEW marks_view AS

B SELECT x.firstName, r.mark

C FROM students s JOIN results r

D ON s.studentId = r.studentId;

Line B — The alias x is never defined in the FROM clause. students was given the alias s, so it should be s.firstName. A view that simplifies a join still needs correct aliases.

Question 3

A CREATE VIEW red_house AS

B SELECT firstName, lastName

C FROM students

D WHERE house = Red;

Line D — Text values in a WHERE condition must be in quotes. Without them, SQL treats Red as a column name. It should be WHERE house = 'Red';

Question 4

A CREATE TABLE stock_list AS

B SELECT name, price

C FROM products;

Line A — CREATE TABLE ... AS would save a second copy of the data. A view is only a stored query shown as a virtual table. The line should start with CREATE VIEW.

Question 5

A CREATE VIEW student_names AS

B SELECT firstName, mark

C FROM students;

Line B — The students table has no mark column. That column is in the results table. The view can only select columns from the table in its FROM clause, so it should be lastName.

Student with a class code? Join your class →