Querying Microsoft SQL Server 2012
Question No: 91 DRAG DROP
You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
-> Displays the details of only students from Canada.
-> Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Question No: 92
You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:
You observe that the stored procedure returns results very slowly. You need to improve the execution speed of the stored procedure. Which cursor type should you use?
LOCAL STATIC READ_ONLY FORWARD_ONLY
Answer: B Explanation:
FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.
Question No: 93
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?
Question No: 94
You are maintaining a Microsoft SQL Server database. You run the following query:
You observe performance issues when you run the query. You capture the following query execution plan:
You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?
Add a new index to the ID column of the Person table.
Add a new index to the EndDate column of the History table.
Create a materialized view that is based on joining data from the ActiveEmployee and History tables.
Create a computed column that concatenates the GivenName and SurName columns.
Explanation: Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.
Question No: 95
You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?
GRANT DELETE ON Purchases.Suppliers TO ContosoUser
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = #39;dbo#39;
DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
GRANT SELECT ON Purchases.Suppliers TO ContosoUser
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
Question No: 96
You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement:
You need to ensure that purchase order numbers are used only for a single order. What should you do?
Create a new CLUSTERED constraint on the PurchaseOrderNumber column.
Create a new UNIQUE constraint on the PurchaseOrderNumber column.
Create a new PRIMARY constraint on the PurchaseOrderNumber column.
Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column.
Explanation: You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Reference: UNIQUE Constraints
Question No: 97
You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.
You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?
Perform a file restore.
Perform a transaction log restore.
Perform a restore from a full backup.
Perform a filegroup restore.
Question No: 98 DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.
Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Question No: 99
You create a view based on the following statement:
You grant the Select permission to User1.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view
functions correctly for User1.
Which Transact-SQL statement should you use?
Question No: 100 CORRECT TEXT
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: DECLARE @XML1 XML(Sales.InvoiceSchema)
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|