Transcript Ch5_SQL_4

Database Management Systems
Chapter 5
SQL
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
R1
Example Instances


We will use these
instances of the
Sailors and
Reserves relations
in our examples.
If the key for the
Reserves relation
contained only the
attributes sid and
bid, how would the
semantics differ?
sid b
id
d
ay
22 1
0
1 1
0
/1
0
/9
6
58 1
0
3 1
1
/1
2
/9
6
S1
sid
22
31
58
snam
e rating
dustin 7
lubber 8
rusty
10
age
45.0
55.5
35.0
S2
sid
28
31
44
58
snam
e rating
yuppy 9
lubber 8
guppy 5
rusty
10
age
35.0
55.5
35.0
35.0
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
View
• A database view is a virtual table or logical
table which is defined as a SQL Select Query.
• A database view is dynamic because it is not
related to the physical schema. When the
data of the tables changes, the view reflects
that changes as well.
• Most database management systems,
including MySQL, allow you to update
data in the underlying tables through the
database view with some prerequisites.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Advantage of Views
•
•
•
•
•
Simplify complex queries and hide the complexity of
underlying tables to the end-users and external
applications.
Limit data access to specific users - expose only nonsensitive data.
Provides extra security layer for a database
management system - read-only view.
Enable computed columns.
Enables backward compatibility - create database
views with the same schema as the legacy tables.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Disadvantage
• Performance: querying data from a database
view can be slow especially if the view is
created based on other views.
• Tables dependency: you create view based
on underlying tables of the a database.
Whenever you change the structure of those
tables that view associates with, you have to
change the view as well.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Create View Statement
CREATE VIEW [database_name].[view_name]
AS [SELECT statement]
CREATE VIEW ReservationPerSailor AS
SELECT S.sid, S.sname, COUNT( *) as RCount
FROM Reservation R, Sailor S
WHERE R.sid = S.sid
GROUP by S.sid
ORDER BY S.sid DESC;
•
Within a database, a view and a table cannot
have the same name
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Create View with Subquery
• The SELECT statement can contain a
subquery in a WHERE clause, but not in
FROM clause.
CREATE VIEW aboveAvgSailor AS
SELECT S2.sid, S2.sname, S2.rating
FROM Sailor S2
WHERE
S2.rating > (SELECT AVG(S1.rating) FROM Sailor S1)
ORDER BY S2.rating DESC;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Query Against View
• You can query a view as if it is a table.
• You can create a view based on other views.
• Queries against the views are executed in
two ways:
•
•
A temporary table is created based on the view
definition statement and executes the incoming
query on this temporary table.
The incoming query with the query defined the
view are combined into one query and executes
the combined query.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Show View Definition
Create view AboveAvgStudents as
Select S2.sid, concat (S2.fname,' ', S2.lname) as StudentName,
S2.points/S2.credits as GPA
From Students S2
Where S2.points/S2.credits > (select AVG(S1.points/S1.credits) from Students S1)
order by S2.sid;
Show create view AboveAvgStudents;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Modify an Existing View
ALTER VIEW [database_name]. [view_name] AS
[SELECT statement]
Alter view AboveAvgStudents as
Select S2.sid, concat (S2.fname,' ', S2.lname) as StudentName,
TimeStampDiff(YEAR, S2.DOB, CurDate()) as Age,
S2.points/S2.credits as GPA
From Students S2
Where S2.points/S2.credits > (select AVG(S1.points/S1.credits) from Students S1)
order by S2.sid;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Modified View
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Create or Replace View
•
•
If a view already exists, MySQL simply
modifies the view.
If the view does not exist, MySQL create a
new view.
CREATE OR REPLACE VIEW [database_name]. [view_name] AS
[SELECT statement]
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Drop a View
DROP VIEW [IF EXISTS] [database_name].[view_name]
• IF EXISTS clause allows you to check
whether the view exists or not.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Create an Updatable View
• Views are not only query-able but
also updatable.
•
•
Using INSERT or UPDATE statement to insert
or update rows of the base table through the
updatable view.
Using DELETE statement to remove rows of the
underlying table through the view.
• To be updatable, there must be a one-to-one
relationship between the rows in the view
and the rows in the underlying table.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Create view Sub_Students as
Select fname as FirstName, lname as LastName, credits as Credits, points as
Points
From Students;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Update Sub_Students
SET Credits = Credits + 1 WHERE Points > 340;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Insertable View
• An updatable view is insertable if it also
satisfies additional requirements
•
•
•
No duplicate view column names;
The view must contain all columns in the
underlying table that do not have a default value.
The view columns must be simple column
references. They must not be expressions or
composite expression.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
INSERT INTO Sub_Students values ('Lisa', 'Li', 100, 300);
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Non-Updatable View
•
A view is not updatable if it contains any of the
following
• Aggregate functions such as MIN, MAX, SUM, AVG,
COUNT, etc.
• DISTINCT, GROUP BY, HAVING clause.
• UNION or UNION ALL clause.
• Subquery in the SELECT clause or in the WHERE clause that
refers to the table appeared in the FROM clause.
• Reference to non-updatable view in the FROM clause.
• Reference only to literal values.
• Multiple references to any column of the base table.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Update Mutli-table View
• Only one single underlying table can be
updated in a multi-table view definition.
•
SET Clause must name only columns from one of
the tables in the view.
Create View StudentsEnrollment As
Select S1.fname as FirstName, S1.lname as LastName,
S2.course_title as CourseTitle, E.grade as Grade
From Students S1, Sessions S2, Enrolled E
Where S1.sid = E.sid AND S2.call_num = E.call_num;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Update StudentsEnrollment
SET LastName = concat(LastName,’A’) Where Grade = ‘A’;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Removing Rows Through View
DELETE from Sub_Students
WHERE Credits = 104;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
WITH CHECK OPTION
• When updatable view shows only partial
data of the underlying table, it is possible
to update data which is not visible through
the view.
• To ensure data consistency of the view,
WITH CHECK OPTION clause is used when
you create the view.
CREATE OR REPLACE VIEW view_name
AS select_statement WITH CHECK OPTION;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
CREATE OR REPLACE VIEW vps AS
SELECT
employeeNumber,
lastname,
firstname,
jobtitle,
extension,
email,
officeCode,
reportsTo
FROM
employees
WHERE
jobTitle LIKE '%VP%';
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Select employeeNumber, lastname, firstname, jobtitle From vps;
INSERT INTO
vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reports
To)
values(1703,'Lily','Bush','IT Manager','x9111','[email protected]',1,1002);
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
SELECT
* FROM
employees
ORDER BY employeeNumber DESC;
This is not what we want to do through the view of “vps’
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
CREATE OR REPLACE VIEW vps AS
SELECT
employeeNumber,
lastname,
firstname,
jobtitle,
extension,
email,
officeCode,
reportsTo
FROM
employees
WHERE
jobTitle LIKE '%VP%'
WITH CHECK OPTION;
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke
INSERT INTO
vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reports
To)
values(1703,'Lily','Bush','IT Manager','x9111','[email protected]',1,1002);
1 Error Code: 1369. CHECK OPTION failed 'classicmodels.vps'
INSERT INTO
vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reports
To)
VALUES(1704,'John','Smith','SVP
Marketing','x9112','[email protected]',1,1076);
It works as expected.
atabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke