Chapter Nine Data Manipulation Language DML Views

1
Chapter Nine Data Manipulation Language
(DML)Views

• Objectives
• Definition
• Creating views
• Retrieve data from a view
• Drop a view

2

• VIEWS
• DATABASE OBJECTS

1-TABLE 2-VIEW -What is a view? -Why using a
view? Restrict database access. Make complex
queries easy. Represent data from different
tables Represent different Views of the same
data.
3
VIEWS DATABASE OBJECTS

• CREATE VIEW COSCStudent
• AS
• SELECT ID, Name, GPA
• FROM Student
• WHERE MajorCOSC
• DESCRIBE COSCStudent

4
Practice

• Create a view called Customer_v with the
  attributes
• Customer number
• Customer last name
• Customer Street
• Customer City
• Customer State
• -Customer zip code

5
Practice

• Create a view for customer sales person called
  Cust_Sale_v with the attributes
• Customer number
• Customer last name
• Sales Rep Number
• Sales Person First Name
• Sales Person Last Name

6
VIEWS DATABASE OBJECTS

• Aliases Column Name
• CREATE VIEW COSCStudent
• AS
• SELECT ID COSCid,
• name COSCName,
• GPA
• FROM Student
• WHERE MajorCOSC

7
VIEWS DATABASE OBJECTS

• Retrieving Data from View
• SELECT
• FROM COSCStudent
• SELECT COSCid, COSCname
• FROM COSCStudent

8
Practice

• Display all the fields in Cust_Sale_v

9
VIEWS DATABASE OBJECTS

• Example
• CREATE VIEW COSCData
• (minsal, maxsal, avesal)
• AS
• SELECT MIN(salary), MAX (salary),
• AVG (salary)
• FREOM faculty
• WHERE dept COSC

10
Practice

• Create a view called Cust_v with the attributes
• Customer number
• Customer name (Last, First)
• Customer Address (Street, city, state, zip)
• -Customer balance
• -Customer credit limit
• -Sales Rep Number

11
VIEWSDATABASE OBJECTS

• Removing a View
• DROP VIEW majors

12
Practice

• Remove the view Cust_v

13
Practice

• Create a view using a sub-query.