CREATE VIEW

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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;

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

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

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

A SELECT VIEW house_points;

B SELECT * FROM house_points;

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;

Match the Pairs

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

Left column		Right column
1CREATE VIEW top_students AS SELECT firstName, points FROM students WHERE points > 100;		ASaves the SELECT query under the name top_students so it can be used like a table
2DROP VIEW top_students;		BDeletes the saved view but leaves the original table data untouched
3A view can hold a join, so students can query results from two tables as if they were one table		CThe AS keyword is missing. CREATE VIEW must be followed by the view name, then AS, then the SELECT query.
4Once a view exists, you query it with SELECT FROM the view name, just like a table		DDROP 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.
5CREATE VIEW top_students SELECT firstName, points FROM students;		ESELECT * FROM student_marks WHERE mark > 80;
6DROP TABLE top_students;		FCREATE VIEW student_marks AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Definitions

CREATE VIEW

Fill in the Blanks

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

___________ highMarks AS SELECT studentId, mark FROM results WHERE mark >= 80;

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

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

SELECT firstName, mark ____ studentMarks WHERE mark > 90;

DROP ____ studentMarks;

Spot the Error

Shade in the letter of the line that contains an 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;

Question 2

A CREATE VIEW stock_low

B SELECT name, stock

C FROM products

D WHERE stock < 20;

Question 3

A CREATE VIEW red_house AS

B SELECT * FROM students

C WHERE house = Red;

D SELECT * FROM red_house;

Question 4

A CREATE VIEW stock_low AS

B SELECT name, stock products

C WHERE stock < 20;

D DROP VIEW stock_low;

Question 5

A CREATE VIEW old_orders AS

B SELECT orderId, orderDate

C FROM orders;

D DROP TABLE old_orders;

Answer Key

CREATE VIEW

Section A: Quick Questions

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

B — False: a view stores only the saved SELECT query

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

B — SELECT * FROM house_points;

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

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

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

SELECT firstName, mark FROM studentMarks
WHERE mark > 90;

DROP VIEW studentMarks;

Section E: Spot the Error

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

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

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';.

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

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