Unit 1

The WITH CHECK option of the CREATE VIEW statement

prevents an update from being performed through the view if it causes a row to no longer be included in the view

The WITH ENCRYPTION clause of the CREATE VIEW statement

prevents users from seeing the code that defines the view

The WITH SCHEMABINDING clause of the CREATE VIEW statement

protects the view by binding it to the database schema prevents the tables that the view is based on from being deleted prevents the tables that the view is based on from being modified in a way that affects the view all of the above

Which statement do you use to modify an existing view?

ALTER VIEW

Which statement do you use to delete an existing view?

DROP VIEW

Which of the following should you use to select the columns for a view in the View Designer?

Diagram pane

A view is a/an ________________ statement that's stored as an object in the database.

SELECT

Which of the following should you use to view the code that's generated for a view in the View Designer?

SQL pane

Which of the following can you use to create or modify a view in SQL Server Management Studio?

View Designer

Each of the following is a benefit provided by using views except for one. Which one is it?

You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.

A table that's used to create a view is called

a base table

All of the system objects that define a database are stored in

a system catalog

The SELECT statement for a view

can use the ORDER BY clause if it also uses the TOP clause

One way to examine the system objects that define a database is to use

catalog views

By default,

columns in a view are given the same names as the columns in the base tables

The View Designer allows you to

display the results of a view specify the selection criteria and sort order for a view edit the design of an existing view all of the above

A correlated subquery is one that

is executed once for each row in the outer query

A view

is like a virtual table consists only of the rows and columns specified in its CREATE VIEW statement doesn't store any data itself all of the above

You can code views that

join tables summarize data use subqueries and functions all of the above

A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.

table

The statement CREATE VIEW Example3 AS SELECT * FROM Invoices;

will create an updatable view

The statement CREATE VIEW Example1 AS SELECT VendorName, SUM(InvoiceTotal) AS SumOfInvoices FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName ORDER BY VendorName;

will fail because the ORDER BY clause isn't allowed in this view

The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

will fail because the SELECT statement returns two columns named VendorID

The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices;

will fail because the second column isn't named