Term
You need to create a clustered index on a view. Which functions can be used inside the view definitions? (Choose all that apply.)
a. AVG()
b. RAND()
c. RAND(1000)
d. GETDATE() |
|
Definition
| a, c. You need to use only deterministic functions. RAND is deterministic only when a seed is specified, and GETDATE is nondeterministic. The reason a. and c. are correct is because AVG( ) and RAND(1000) are deterministic. |
|
|
Term
In Table 7-1 (pg. 78), your users need an easy way to display only the first name, last name, and phone number for customers in the 222 area code. What should you have them do?
a. Use this query on the table: SELECT * FROM customers WHERE phone LIKE '222%'
b. Use this query on the table: SELECT firstname, lastname, phone FROM customers WHERE phone LIKE '222%'
c. Create a view based on the table using this query: SELECT * FROM customers WHERE phone LIKE '222%'; then have users query the view.
d. Create view based on the table using this query: SEELCT firstname, lastname, phone FROM customers WHERE phone LIKE '222%'; then have users query the view |
|
Definition
| d. It is much faster for the users to query a view in this case because there is less code. You do not want to use the SELECT * statement because that returns all fields from the table, and you need only FirstName, LastName, and Phone. |
|
|
Term
You need to create a new view, and you are planning to use this code: CREATE VIEW Contacts_in_222 AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
You may need to index this view later to improve performance. What changes, if any, do you ened to make to this code to be able to index the view later?
a. No changes are necessary.
b. Change the code to this: CREATE VIEW Contacts_in_222 WITH SCHEMABINDING AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
c. Change the code to this: CREATE VIEW Contacts_in_222 WITH INDEXABLE AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
d. Change the code to this: CREATE VIEW Contacts_in_222 WITH TABLEBINDING AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.cardtype as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%' |
|
Definition
| b. To index a view, you must use the SCHEMABINDING option, which prevents the underlying table from being changed, unless the schema bound view is dropped first. Also note, TABLEBINDING and INDEXABLE are not actual options. |
|
|
Term
You need to create a new view, and you are planning to use this code: CREATE VIEW Contacts_in_222 WITH SCHEMABINDING AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.* FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
You may need to index this view later to improve performance. What changes, if any, do you need to make to this code to be able to index the view later?
a. No changes necessary.
b. Change the code to this: CREATE VIEW Contacts_in_222 WITH SCHEMABINDING, SELECTALL AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.*as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
c. Change the code to this: CREATE VIEW Contacts_in_222 WITH SCHEMABINDING AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.[*] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%'
d. Change the code to this: CREATE VIEW Contacts_in_222 WITH SCHEMABINDING AS SELECT c.ContactID, title as [Title], lastname as [Last Name], firstname as [First Name], phone as [Phone Number], c3.CreditCardType as [Card Type] FROM Person.Contact c JOIN Sales.ContactCreditCard c2 on c.ContatcID = c2.ContactID JOIN Sale.CreditCard c3 on c2.CreditCardID = c3.CreditCardID WHERE phone LIKE '222%' |
|
Definition
| d. You can’t index a view that has a wildcard in the SELECT statement; all columns must be called out specifically. Also note, SELECTALL is not an actual option. |
|
|
Term
Using the data from Table 7-2 (pg. 80), you need to create a view that allows users to add new employees. You want them to be able to add all the information except the pay rate. What changes do you need to make to the table to accomplish this?
a. Add a default constraint to the Pay column with a value of 0.00.
b. Change the Pay column so it is not nullable, and add a default constraint with a value of 0.00.
c. Change all the columns to nullable.
d. Do nothing; the table is fine as is. |
|
Definition
| d. To create an updateable view that will allow users to insert new records with everything except the Pay column, you do not need to make any changes because the Pay column is nullable; therefore, your users do not have to insert a value in the Pay column when inserting a new record. |
|
|
Term
Using the data from Table 7-2, you need to create a view that allows users to update the FirstName, LastName, Phone, and Pay columns. The code to create the view looks like this: CREATE VIEW Update_Pay WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay FROM HumanResources.Employees
Users complain they cannot use the new view to add new employees. Why does this fail?
a. Some columns in the table are not nullable, so the view can't be used to insert new records.
b. The EmpID column was not included in the view, so the view can't be used to insert new records.
c. WITH SCHEMABINDING can't be used on an updateable view, so the view can't be used to insert new records.
d. Columns with the money data type, such as the Pay column, can't be used in updateable views, so the view can't be used to insert new records. |
|
Definition
| a. Several nonnullable columns appear in the underlying table, so when users try to insert a new record, SQL Server expects values for all of the nonnullable columns. Since this view does not display all these columns, the view can’t be used to insert new records. It can be used to update existing records, however. |
|
|
Term
You have created a view that your users need to use to update records in one of your tables. The code to create the view looks like this: CREATE VIEW ProductCost WITH SCHEMABINDING AS SELECT ProdID, Cost, Qty, SUM(qty * cost) FROM Products
What do you need to change on this view to make it updateable?
a. Nothing, the view is updateable as is.
b. Change the code to look like this: CREATE VIEW ProductCost AS SELECT ProdID, Cost, Qty, SUM(qty * cost) FROM Products
c. Change the code to look like this: CREATE VIEW ProductCost WITH ALLOWAGREGATES AS SELECT ProdID, Cost, Qty, SUM(qty * cost) FROM PRODUCTS
d. Change the code to look like this: CREATE VIEW ProductCost WITH SCHEMABINDING AS SELECT ProdID, Cost, Qty FROM Products |
|
Definition
| d. Aggregate functions, such as SUM( ) and AVG( ), are not allowed in updateable views, so you have to remove SUM(qty * cost). Also note, ALLOWAGREGATES is not a valid option. |
|
|
Term
You have created a view with the following code: CREATE VIEW Get_Pay WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay, GetDate() FROM HumanResources.Employees
What changes do you need to make to this code to make this view indexable?
a. Change the code to look like this: CREATE VIEW Get_Pay WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay FROM HumanResources.Employees
b. Change the code to look like this: CREATE VIEW Get_Pay AS SELECT FirstName, LastName, Phone, Pay, GetDate() FROM HumanResources.Employees
c. Change the code to look like this: CREATE VIEW Get_Pay WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay, GetDate() FROM HumanResources.dbo.Employees
d. No changes are needed; the view is already indexable |
|
Definition
| a. You need the SCHEMABINDING option on a view that you intend to index, and three-part notation is not allowed. Nondeterministic functions return a different value every time they are called; deterministic functions return the same value every time they are called. Nondeterministic functions such as GETDATE( ) can’t be used in a view you intend to index. |
|
|
Term
You decide to create a view of the OnLineReadOnly database to show the current reseravation status based on passenger name. The view joins tables from across the servers. This is an example of what kind of view?
a. Partitioned view
b. Standard view
c. Indexed view
d. Constrained view |
|
Definition
| a. Only a partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. There is no such thing as a constrained view. |
|
|
Term
Your company stores its business information in a SQL Server database. To facilitate the order-entry process, you want to create a view that presents only those columns in the SALES table that are required by the sales representatives when they take an order or modify customer information. Each sales representative is assigned a separate territory that includes a distinct set of customers. How can you ensure that each sales representative can only modify information about customers who reside in that representative's territory?
a. Create a CHECK constraint on the SALES table and specify the list of permitted territories.
b. Assign each sales representative permissions on the view only for the rows that correspond to that representative's territory.
c. Create a separate view on the SALES table for each territory and specify WITH CHECK OPTION.
d. Assign each sales representative permissions on the SALES table only for the rows that correspond. |
|
Definition
| c. Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed. |
|
|
Term
What is the benefit of using the WITH CHECK OPTION cluase in your view definition?
a. Your view will consist of only those items verified by CONSTRAINT clauses.
b. Your view adheres to criteria set within the SELECT statement.
c. Your view will present only those data confirmed during double data-entry procedures.
d. Your view shows only data in agreement with DEFAULT values. |
|
Definition
| b. The WITH CHECK OPTION assures current data is displayed. |
|
|
