SQL CREATE VIEW

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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;

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

A SELECT * FROM year7;

B SELECT VIEW year7;

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.

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;

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

Match the Pairs

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

Left column		Right column
1CREATE VIEW houseRed AS SELECT firstName, points FROM students WHERE house = 'Red';		ACREATE TABLE makes a real table with its own copy of the data, not a view. A view needs CREATE VIEW.
2SELECT * FROM houseRed;		BCREATE VIEW studentNames AS SELECT studentId, firstName FROM students;
3A view can hide sensitive columns by leaving them out of its SELECT list.		CThe AS keyword is missing. CREATE VIEW name must be followed by AS before the SELECT query.
4A view is not a table: it stores a query, not a copy of the data, so removing the view leaves the original table untouched.		DQueries the saved view houseRed just like a table, running its stored SELECT each time to give the current rows.
5CREATE VIEW topStudents SELECT firstName, points FROM students WHERE points > 100;		EDROP VIEW studentNames;
6CREATE TABLE topStudents AS SELECT firstName, points FROM students;		FSaves a query as a virtual table named houseRed that shows only the first name and points of Red house students.

Definitions

CREATE VIEW

Fill in the Blanks

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

CREATE ____ redHouse AS SELECT firstName, points FROM students WHERE house = 'Red';

CREATE VIEW year7 __ SELECT firstName, lastName FROM students WHERE yearLevel = 7;

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

CREATE VIEW names AS ______ firstName, lastName FROM students;

CREATE VIEW blueHouse AS SELECT firstName, points FROM students _____ house = 'Blue';

Spot the Error

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

Question 1

A CREATE VIEW year7

B SELECT firstName, lastName

C FROM students

D WHERE yearLevel = 7;

Question 2

A CREATE VIEW red_house AS

B SELECT firstName, points

C FROM students

D WHERE house = Red;

Question 3

A CREATE VIEW stock_list AS

B SELECT name, price, stock,

C FROM products;

D SELECT * FROM stock_list;

Question 4

A CREATE VIEW student_info IS

B SELECT firstName, house

C FROM students;

D SELECT * FROM student_info;

Question 5

A CREATE VIEW top_marks AS

B SELECT studentId, marks

C FROM results

D WHERE mark >= 90;

Answer Key

SQL CREATE VIEW

Section A: Quick Questions

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

A — SELECT * FROM year7;

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

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

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

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

CREATE VIEW names AS
SELECT firstName, lastName
FROM students;

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

Section E: Spot the Error

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.

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

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.

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

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.