Views in a relational database
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A view stores its own separate copy of the data it displays.
2. Which statement creates a view of Year 7 students?
3. A school wants teachers to see student names and houses but not points. Which view helps restrict access?
4. A student's points change in the students table. What happens when you next query a view built on that table?
5. Which is a good reason to create a view for a query that joins results, students and subjects?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1CREATE VIEW HouseList AS SELECT firstName, house FROM students; | ASaves a query as a virtual table called HouseList that shows only two columns of students | |
| 2SELECT * FROM HouseList; | BCREATE VIEW MarkList AS SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId; | |
| 3A view can restrict access by showing only some columns, leaving out private data such as points | CCREATE VIEW StudentNames AS SELECT firstName, lastName FROM students; | |
| 4A view can simplify joins by storing the join once so users do not rewrite the ON condition | DQueries the view as if it were a table, running the stored query to get up-to-date rows | |
| 5CREATE VIEW HouseList SELECT firstName, house FROM students; | EThe AS keyword is missing. CREATE VIEW name must be followed by AS before the SELECT query. | |
| 6CREATE VIEW Top AS INSERT INTO students VALUES (11, 'Zed'); | FA view stores a SELECT query, not an INSERT. The statement after AS must be a SELECT. |
C
Definitions
CREATE VIEW
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
___________ redStudents AS
SELECT firstName, points FROM students
WHERE house = 'Red';
2.
CREATE ____ studentNames AS
SELECT firstName, lastName
FROM students;
3.
CREATE VIEW markList AS
SELECT s.firstName, r.mark FROM students s
____ results r ON s.studentId = r.studentId;
4.
SELECT firstName, points
____ redStudents
ORDER BY points DESC;
5.
CREATE VIEW year7View __
SELECT firstName, house
FROM students WHERE yearLevel = 7;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
CREATE VIEW top_students AS
B
SELECT firstName, points,
C
FROM students
D
WHERE points > 100;
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;
Question 3
A
CREATE VIEW red_house AS
B
SELECT firstName, lastName
C
FROM students
D
WHERE house = Red;
Question 4
A
CREATE TABLE stock_list AS
B
SELECT name, price
C
FROM products;
Question 5
A
CREATE VIEW student_names AS
B
SELECT firstName, mark
C
FROM students;
Answer Key
Views in a relational database
Section A: Quick Questions
Q1
B — False: a view stores only the query, and the data stays in the original tables
Q2
A — CREATE VIEW year7 AS SELECT * FROM students WHERE yearLevel = 7;
Q3
B — CREATE VIEW houses AS SELECT firstName, house FROM students;
Q4
A — The view shows the new points value
Q5
A — Users can query the view without rewriting the joins each time
Section B: Match the Pairs
Q1
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
Q2
SELECT * FROM HouseList; → Queries the view as if it were a table, running the stored query to get up-to-date rows
Q3
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;
Q4
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;
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
CREATE VIEW redStudents AS
SELECT firstName, points FROM students
WHERE house = 'Red';Q2
CREATE VIEW studentNames AS
SELECT firstName, lastName
FROM students;Q3
CREATE VIEW markList AS
SELECT s.firstName, r.mark FROM students s
JOIN results r ON s.studentId = r.studentId;Q4
SELECT firstName, points
FROM redStudents
ORDER BY points DESC;Q5
CREATE VIEW year7View AS
SELECT firstName, house
FROM students WHERE yearLevel = 7;Section E: Spot the Error
Q1
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.
Q2
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.
Q3
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';
Q4
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.
Q5
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.