← QCE Digital Solutions Unit 2

SQL CREATE VIEW

Saves a SELECT query as a named virtual table. It shows chosen columns and rows, can hide others, and stores no copy of the data, so you query it like a table.

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

Understanding SQL CREATE VIEW in QCE Digital Solutions Unit 2

What it does: The view lists only studentId and firstName, so columns such as lastName and points are hidden from anyone who queries the view. DROP VIEW deletes only the saved query. The students table and all of its rows remain, because the view never held its own copy of the data. The syntax is CREATE VIEW name AS followed by a SELECT query. The word AS joins the view name to the query. A view is queried like a table, so you put its name after FROM. Views limit what users see, which helps protect sensitive columns. They do not copy or back up data.

Common mistakes: The AS keyword is missing. A view is created with CREATE VIEW name AS followed by the SELECT query, so line 1 should be CREATE VIEW year7 AS. Text values in a WHERE condition need quotes. Without them SQL reads Red as a column name. 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 SQL CREATE VIEW 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

Which statement saves a query as a view called house_points?

CREATE VIEW house_points AS SELECT house, points FROM students;CREATE house_points VIEW SELECT house, points FROM students;

Matches

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

↓

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

Applying

Meanings

Term

CREATE VIEW

Meaning

Saves a SELECT query as a named virtual table. It shows chosen columns and rows, can hide others, and stores no copy of the data, so you query it like a table.

Practice

CREATE VIEW HouseList AS

Testing

Gaps

CREATE redHouse AS

Check Answer

Spots

1CREATE VIEW year7

2SELECT firstName, lastName

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 saves a query as a view called house_points?

A. CREATE VIEW house_points AS SELECT house, points FROM students; ✓

B. CREATE house_points VIEW SELECT house, points FROM students;

The syntax is CREATE VIEW name AS followed by a SELECT query. The word AS joins the view name to the query.

2. A view called year7 has been created. How do you query it?

A. SELECT * FROM year7; ✓

B. SELECT VIEW year7;

A view is queried like a table, so you put its name after FROM.

3. Does a view store its own separate copy of the data?

A. Yes. It copies the rows into a new table when you create it.

B. No. It runs the saved query on the original table each time. ✓

A view is a virtual table. The data stays in the original table and the view only shows it.

4. A school wants teachers to see student names but NOT their points. Which view does this?

A. CREATE VIEW names AS SELECT firstName, lastName FROM students; ✓

B. CREATE VIEW names AS SELECT * FROM students;

A view hides columns by leaving them out of its SELECT. SELECT * would show every column, including points.

5. A student's points are changed in the students table. What does the view house_points show next time it is queried?

A. The old points, because the view kept a copy from when it was made

B. The new points, because the view reads the current table data ✓

A view stores no data, so it always shows the current contents of the table underneath.

Match the Pairs

CREATE VIEW houseRed AS SELECT firstName, points FROM students WHERE house = 'Red'; → Saves a query as a virtual table named houseRed that shows only the first name and points of Red house students.

SELECT * FROM houseRed; → Queries the saved view houseRed just like a table, running its stored SELECT each time to give the current rows.

A view can hide sensitive columns by leaving them out of its SELECT list. → CREATE VIEW studentNames AS SELECT studentId, firstName FROM students;

A view is not a table: it stores a query, not a copy of the data, so removing the view leaves the original table untouched. → DROP VIEW studentNames;

CREATE VIEW topStudents SELECT firstName, points FROM students WHERE points > 100; → The AS keyword is missing. CREATE VIEW name must be followed by AS before the SELECT query.

CREATE TABLE topStudents AS SELECT firstName, points FROM students; → CREATE TABLE makes a real table with its own copy of the data, not a view. A view needs CREATE VIEW.

Key Term

CREATE VIEW

Saves a SELECT query as a named virtual table. It shows chosen columns and rows, can hide others, and stores no copy of the data, so you query it like a table.

Fill in the Blanks

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

Hint: Type the keyword that makes a saved, named virtual table instead of a real stored table.

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

Hint: Type the short keyword that links the view's name to the query it saves.

3. -- view: topStudents SELECT * FROM topStudents WHERE points > 100;

Hint: Type the name of the view being queried, as shown in the comment. You query it just like a table.

4. CREATE VIEW names AS SELECT firstName, lastName FROM students;

Hint: Type the keyword that starts the saved query. Listing only some columns hides the others from view users.

5. CREATE VIEW blueHouse AS SELECT firstName, points FROM students WHERE house = 'Blue';

Hint: Type the clause keyword that limits which rows the view shows.

Spot the Error

Question 1

A CREATE VIEW year7

B SELECT firstName, lastName

C FROM students

D WHERE yearLevel = 7;

Line A — The AS keyword is missing. A view is created with CREATE VIEW name AS followed by the SELECT query, so line 1 should be CREATE VIEW year7 AS.

Question 2

A CREATE VIEW red_house AS

B SELECT firstName, points

C FROM students

D WHERE house = Red;

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

Question 3

A CREATE VIEW stock_list AS

B SELECT name, price, stock,

C FROM products;

D SELECT * FROM stock_list;

Line B — There is a stray comma after the last column. A comma may only separate columns, so the line should be SELECT name, price, stock with no trailing comma.

Question 4

A CREATE VIEW student_info IS

B SELECT firstName, house

C FROM students;

D SELECT * FROM student_info;

Line A — The keyword that links the view name to its query is AS, not IS. The line should be CREATE VIEW student_info AS.

Question 5

A CREATE VIEW top_marks AS

B SELECT studentId, marks

C FROM results

D WHERE mark >= 90;

Line B — The results table has a column called mark, not marks. A view can only select columns that exist in its source table, so it should be SELECT studentId, mark.

Student with a class code? Join your class →