Transcript DBC-e03-Appenix-C-PP

DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
Appendix C
SQL Views
Appendix Objectives
• Learn basic SQL statements for
creating views
• Learn basic SQL statements for
using views
• Understand the reasons for using
views
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-2
SQL Views
• A SQL view is a virtual table that is constructed
from other tables or views
• A view has no data of its own, but uses data
stored in tables or other views
• Views are created using SQL SELECT
statements
• Views are used in other SELECT statements just
as if they were a table
• The SQL statements that create the views may
not contain an ORDER BY clause
• If the results of a query using a view need to be
sorted, the sort order must be provided by the
SELECT statement that processes the view
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-3
SQL CREATE VIEW Statement
• The SQL CREATE VIEW statement
is used to create view structures.
CREATE VIEW ViewName AS
{SQL SELECT statement};
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-4
SQL CREATE VIEW Example
CREATE VIEW EmployeePhoneView AS
SELECT FirstName, LastName,
Phone AS EmployeePhone
FROM EMPLOYEE;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-5
Creating a SQL View in
SQL Server 2005
Click the New Query button,
enter the SQL for the view just as
you would enter the SQL for a
query, and then click the
Execute button to create the
query
The “Command(s) completed
successfully” message in the
Messages window indicates that
the view has been created
The new view appears in the
expanded Views folder after it is
created
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-6
Creating a SQL View
MySQL 5.x
Enter the SQL for the view just as
you would enter the SQL for a query,
and then click the Execute button to
create the query
The new view appears in the
expanded wpc schema after it is
created (you may have to refresh the
schema for it to appear)
The “Query returned no resultset”
message indicates that the view has
been created
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-7
Using an SQL CREATE
• Once the view is created, it can be
used in the FROM clause of
SELECT statements just like a table.
SELECT
FROM
ORDER BY
*
EmployeePhoneView
LastName;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-8
Using the EmployeePhoneView in
SQL Server 2005
Click the New Query button,
enter the SQL for the query that
uses the view, and then click the
Execute button to create the
query
The query results appear in the
Results window.
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-9
Using the EmployeePhoneView in
MySQL 5.X
Enter the SQL for the query that uses
the view, and then click the Execute
button to create the query
The query results appear in the
Resultset window.
Use the File | New Resultset
Tab command to open a new
Resultset window
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-10
Some Uses for SQL Views
•
•
•
•
Hide columns or rows
Display results of computations
Hide complicated SQL syntax
Layer built-in functions
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-11
Using SQL Views:
Hide columns or rows I
CREATE VIEW BasicDepartmentDataView AS
SELECT DepartmentName,
Phone AS DepartmentPhone
FROM DEPARTMENT;
SELECT * FROM BasicDepartmentDataView
ORDER BY DepartmentName;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-12
Using SQL Views:
Hide columns or rows II
CREATE VIEW MarkingDepartmentProjectView AS
SELECT ProjectID, Name AS ProjectName,
MaxHours, StartDate, EndDate
FROM
PROJECT
WHERE
Department = 'Marketing';
SELECT * FROM MarkingDepartmentProjectView
ORDER BY ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-13
Using SQL Views:
Display results of computations – SQL Statement
CREATE VIEW ProjectHoursToDateView AS
SELECT PROJECT.ProjectID,
Name AS ProjectName,
MaxHours AS ProjectMaxHours,
SUM(HoursWorked) AS
ProjectHoursWorkedToDate
FROM
PROJECT, ASSIGNMENT
WHERE PROJECT.ProjectID =
ASSIGNMENT.ProjectID
GROUP BY PROJECT.ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-14
Using SQL Views:
Display results of computations - Results
SELECT * FROM ProjectHoursToDateView
ORDER BY PROJECT.ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-15
Using SQL Views:
Hide complicated SQL syntax – SQL Statement
CREATE VIEW EmployeeProjectHoursWorkedView AS
SELECT Name, FirstName, LastName,
HoursWorked
FROM
EMPLOYEE AS E JOIN ASSIGNMENT AS A
ON E.EmployeeNumber =
A.EmployeeNumber
JOIN PROJECT AS P
ON A.ProjectID = P.ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-16
Using SQL Views:
Hide complicated SQL syntax - Results
SELECT * FROM EmployeeProjectHoursWorkedView;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-17
Using SQL Views:
Layering Computations and Built-in Functions
1st SQL Statement
CREATE VIEW ProjectHoursToDateView AS
SELECT PPOJECT.ProjectID,
Name AS ProjectName,
MaxHours AS ProjectMaxHours,
SUM(HoursWorked) AS
ProjectHoursWorkedToDate
FROM
PROJECT, ASSIGNMENT
WHERE PROJECT.ProjectID =
ASSIGNMENT.ProjectID
GROUP BY PROJECT.ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-18
Using SQL Views:
Layering Computations and Built-in Functions
2nd SQL Statement
CREATE VIEW ProjectsOverAllotedMaxHoursView
AS
SELECT ProjectID, ProjectName,
ProjectMaxHours,
ProjectHoursWorkedToDate
FROM
ProjectHoursToDateView
WHERE ProjectHoursWorkedToDate >
ProjectMaxHours;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-19
Using SQL Views:
Layering Computations and Built-in Functions
Results
SELECT
ProjectID, ProjectName, ProjectMaxHours,
ProjectHoursWorkedToDate,
(ProjectHoursWorkedToDate
- ProjectMaxHours)
AS HoursOverMaxAllocated
FROM
ProjectsOverAllotedMaxHoursView
ORDER BY ProjectID;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
C-20
DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
End of Presentation on Appendix C
SQL Views