Transcript Chapter 2 - Faculty.frostburg

Views
Objectives
 Create views
 Modify/Drop Views
 Insert/Delete/Update to/from views
 Retrieve data from views
Facts
 Views as logical representation of data
 Views are queried like tables
 Views are objects similar to tables
 Views are stored queries
 Every time a view is uses the query is run (Views are
not stored database)
3
Advantages of Views
 Restrict database access
 Represent data from different tables
 Make complex query easy
 Calculate values of formula
 Represent different views of the same data
 Views can be created from other views
4
Creating Views
CREATE [OR REPLACE] VIEW
name
AS
sub-query
[WITH READ ONLY]
5
Creating Views
 Example:
CREATE OR REPLACE VIEW salesEmpl
AS
SELECT
*
FROM
testdata.Employee
WHERE
dept=‘Sales’;
6
Check the Structure of Views
 DESC salesEmpl;
Name
F_Name
L_Name
SSN
Dept
B_Date
Null?
Type
VARCHAR2(20)
VARCHAR2(20)
NUMBER(9)
VARCHAR2(20)
DATE
7
Check the Content of Views
SELECT
FROM
*
SalesEmpl;
Retrieve data from Views
SELECT
FROM
WHERE
L_Name, F_Name, SSN
SalesEmpl
TO_CHAR(B_Date, ‘YYYY’)=1973;
8
Column Aliases:
CREATE VIEW GoodStudents AS
SELECT
Name, SSN ID, Major Area, Email Contact
FROM
Students
Where
GPA>3;
Alternative Renaming:
CREATE OR REPLACE VIEW GoodStudents
(Name, ID, Area, Contact)
AS
SELECT
Name, SSN, Major, Email
FROM
Students
WHERE
GPA>3;
9
Use of Functions:
 CREATE VIEW EmployeeData
(Dept, MinSal, MaxSal, AvgSal, CountEmp)
AS
SELECT
FROM
Dept, MIN(PayRate), Max(PayRate),
AVG (PayRate), Count(*)
Employee;
10
Use of Functions:
SELECT
FROM
*
EmployeeData;
 How to modify Views?
11
SELECT only Views:
CREATE View EmployeeData
AS
SELECT
L_Name, F_Name, Dept
FROM
Employee
WITH READ ONLY;
Why do we need a read only view?
12
Delete Views:
DROP VIEW Name)
DROP VIEW EmployeeData;
13
Check the list of views
SELECT
FROM
*
USER_VIEWS;
14
INSERT Data into View
INSERT INTO GoodStudent
(ID, Name, Area, Contact)
VALUES (211202111, ‘John Smith’, ‘COSC’,
‘[email protected]’);
15
Insert with NULL value:
INSERT INTO GoodStudent
(ID, Name, Area, Contact)
VALUES (211202111, ‘John Smith’, NULL,
‘[email protected]’);
16
Delete data from views:
DELETE
WHERE
GoodStudent
ID=211202111;
Update Views:
UPDATE
SET
WHERE
GoodStudent
Area=‘MATH’
ID=211202111;
17
Create Views from Multiple table
 CREATE OR REPLACE VIEW SalesInfo
(Name, ID, Item, Cost)
AS
SELECT
F_Name || ‘ ‘ || L_Name, ID,
HowMany, Price
FROM
Employee, Sales
WHERE
ID=E_ID;
18