Transcript Views

Advanced SQL: Views
Instructor: Mohamed Eltabakh
[email protected]
1
Views
2
What is a View

An SQL query that we register (store) inside the
database
CREATE VIEW <name> AS <select statement>;
DROP VIEW <name>;

Any query can be a view
ProfNumStudents is a view
with schema (pNumber, CNT)
CREATE VIEW ProfNumStudents AS
SELECT pNumber, count(*) AS CNT
FROM Student
GROUP BY pNumber;
3
Why Need a View

Frequent queries: query is used again and again

Complex queries: query written once and stored in the database

Logical data independence: the base table may change but the the
view is still the same

Hide information (Security): allow users to see the view but not the
original tables
CREATE VIEW StudentBasicInfo AS
SELECT sNumber, sName
FROM Student;
See only sNumber and sName
4
View Schema

You can think of a view as a table, but it gets its data during runtime


Only the definition is stored without data
View Schema

Consists of the columns produced from the select statement
CREATE VIEW ProfNumStudents AS
SELECT pNumber, count(*) AS CNT
FROM Student
GROUP BY pNumber;
CREATE VIEW StudentBasicInfo AS
SELECT sNumber, sName
FROM Student;
ProfNumStudents(pNumber, CNT)
StudentBasicInfo(sNumber, sName)
5
Example
Customers who
have accounts
Customers who
have loans
6
Example (Cont’d)
In this example, we
added an extra
, ‘A’ as type column (constant)
to differentiate
between the two
customer types
, ‘L’ as type
7
Querying a View

Exactly as querying a table
8