sql workshopdshook/sqlworkshop/slides/p9.pdf · sql workshop views doug shook. 2 ... create view...

SQL Workshop

Views

Doug Shook

2

Views A view consists of a SELECT statement that is stored as an object in the DB

– Can be referred to like a table• SELECT, INSERT, UPDATE, DELETE

These views are optimized by SQL for performance– Views do not store any data!

3

ViewsA CREATE VIEW statement CREATE VIEW VendorsMin AS SELECT VendorName, VendorState, VendorPhone FROM Vendors;

The virtual table that’s represented by the view

4

ViewsA SELECT statement that uses the view SELECT * FROM VendorsMin WHERE VendorState = 'CA' ORDER BY VendorName;

The result set returned by the SELECT statement

(75 rows)

5

Benefits Design independence

– What if the DB that a view is based on changes?

Security– Provide limited access to sensitive information

Simplified queries– Abstract complicated queries into the view

6

CREATE VIEW Guidelines:

– The name of the view must not conflict with tables– Joins, Unions and Subqueries are allowed– ORDER BY can only be used with TOP– WITH ENCRYPTION obscures the code used to

create the view– WITH SCHEMABINDING prevents users from

dropping tables that the view needs

7

CREATE VIEWA view that uses a join

CREATE VIEW VendorInvoices AS SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID;

A view that uses TOP and ORDER BY clauses CREATE VIEW TopVendors AS SELECT TOP 5 PERCENT VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC;

8

CREATE VIEWA view that names all of its columns in the CREATE VIEW clause CREATE VIEW OutstandingInvoices (InvoiceNumber, InvoiceDate, InvoiceTotal, BalanceDue) AS SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

9

CREATE VIEWA view that names just the calculated column in its SELECT clause CREATE VIEW OutstandingInvoices AS SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

10

CREATE VIEWA view that summarizes invoices by vendor

CREATE VIEW InvoiceSummary AS SELECT VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceSum FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName;

11

CREATE VIEWA view that uses the WITH SCHEMABINDING option CREATE VIEW VendorsDue WITH SCHEMABINDING AS SELECT InvoiceDate AS Date, VendorName AS Name, VendorContactFName + ' ' + VendorContactLName AS Contact, InvoiceNumber AS Invoice, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM dbo.Vendors JOIN dbo.Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

12

Updatable Views Requirements for updatable views are more restrictive than read only views

– Cannot include DISTINCT or TOP– Cannot include an aggregate– Cannot include calculated values in SELECT– Cannot include GROUP BY or HAVING– Cannot include UNION

13

Updatable ViewsAn updatable view

CREATE VIEW InvoiceCredit AS SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

An UPDATE statement that updates the view UPDATE InvoiceCredit SET CreditTotal = CreditTotal + 200 WHERE InvoiceTotal – PaymentTotal – CreditTotal >= 200;

14

Updatable ViewsA read-only view CREATE VIEW OutstandingInvoices AS SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

15

DROP and ALTERA statement that creates a view

CREATE VIEW Vendors_SW AS SELECT * FROM Vendors WHERE VendorState IN ('CA','AZ','NV','NM');

A statement that modifies the view ALTER VIEW Vendors_SW AS SELECT * FROM Vendors WHERE VendorState IN ('CA','AZ','NV','NM','UT','CO');

A statement that deletes the view DROP VIEW Vendors_SW;

16

WITH CHECK OPTIONA statement that creates an updatable view CREATE VIEW VendorPayment AS SELECT VendorName, InvoiceNumber, InvoiceDate, PaymentDate, InvoiceTotal, CreditTotal, PaymentTotal FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

The data retrieved by the view before the update

17

WITH CHECK OPTIONA statement that updates the Invoices table through the view UPDATE VendorPayment SET PaymentTotal = 19351.18, PaymentDate = '2012-04-02' WHERE VendorName = 'Malloy Lithographing Inc' AND InvoiceNumber = 'P-0608';

The updated Invoices table

The data retrieved by the view after the update

18

WITH CHECK OPTION Because this view did not use WITH CHECK OPTION, when the update completed the affected row was no longer included in the view

WITH CHECK OPTION prevents such UPDATEs from taking place

19

INSERT and DELETE Think about INSERTing a row into a view

– View contains half of the columns from one table

What problems might arise?

Think about DELETEing a row from a view– What if the view is based on two tables?– What if the view is based on a table with foreign

key constraints?

20

INSERT and DELETEA statement that creates an updatable view CREATE VIEW IBM_Invoices AS SELECT InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE VendorID = (SELECT VendorID FROM Vendors WHERE VendorName = 'IBM');

The contents of the view

21

INSERT and DELETEAn INSERT statement that fails due to columns with null values INSERT INTO IBM_Invoices (InvoiceNumber, InvoiceDate, InvoiceTotal) VALUES ('RA23988', '2012-05-04', 417.34);

The response from the system Cannot insert the value NULL into column 'VendorID', table 'AP.dbo.Invoices'; column does not allow nulls. INSERT fails. The statement has been terminated.

22

INSERT and DELETE What can we do instead?

A DELETE statement that fails due to a foreign key constraint DELETE FROM IBM_Invoices WHERE InvoiceNumber = 'Q545443';

The response from the system The DELETE statement conflicted with the REFERENCE constraint "FK_InvoiceLineItems_Invoices". The conflict occurred in database "AP_AllObjects", table "dbo.InvoiceLineItems", column 'InvoiceID'. The statement has been terminated.

23

INSERT and DELETETwo DELETE statements that succeed DELETE FROM InvoiceLineItems WHERE InvoiceID = (SELECT InvoiceID FROM Invoices WHERE InvoiceNumber = 'Q545443'); DELETE FROM IBM_Invoices WHERE InvoiceNumber = 'Q545443';

The response from the system (1 row(s) affected)

24

Catalog Views SQL Server maintains a system catalog

– Tables that list all objects in a database

It can be useful to examine this information– But dangerous to query the catalog directly

Catalog views are set up to provide access to the system catalog

25

Catalog ViewsSome of the SQL Server catalog views sys.schemas

sys.sequences

sys.tables

sys.views

sys.columns

sys.key_constraints

sys.foreign_keys

sys.foreign_key_columns

sys.objects

26

Catalog ViewsRetrieve the name and schema of each table SELECT sys.tables.name AS TableName, sys.schemas.name AS SchemaName FROM sys.tables INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id;

The result set

27

Exercises Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the VendorName is N, O, or P.

28

Exercises Create a view named Top10PaidInvoices that returns three columns for each vendor: VendorName, LastInvoice (the most recent invoice date), and SumOfInvoices(the sum of the InvoiceTotal column). Return only the 10 vendors with the largest SumOfInvoices and include only paid invoices.

29

Exercises Create an updatable view named VendorAddress that returns the VendorID, both address columns, and the city, state, and zip code columns for each vendor. Then, write a SELECT query to examine the result set where VendorID=4. Next, write an UPDATE statement that changes the address so that the suite number (Ste 260) is stored in VendorAddress2 rather than in VendorAddress1. To verify the change, rerun your SELECT query.