Q1. CORRECT TEXT 

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.) 

You have an application named Appl. You have a parameter named @Count that uses the 

int data type. App1 is configured to pass @Count to a stored procedure. 

You need to create a stored procedure named usp_Customers for App1 that returns only 

the number of rows specified by the @Count parameter. 

The solution must NOT use BEGIN and END statements. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the 

complete code. 

View Answer

Q2. You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. 

The view has the following definition: 

You need to ensure that users can update only the phone numbers and email addresses by using this view. 

What should you do? 

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Create an INSTEAD OF UPDATE trigger on the view. 

C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

D. Create an AFTER UPDATE trigger on the view. 

View Answer

Q3. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. 

You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. 

Which approach should you use? 

A. A recursive stored procedure 

B. Trigger 

C. An UPDATE statement that includes CASE 

D. Cursor 

E. The foreach SQLCLR statement 

View Answer

Q4. Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. 

Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. 

You need to ensure that your query executes in the minimum possible time. 

Which query should you use? 

A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p 

B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p 

C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders 

D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders 

View Answer

Q5. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

View Answer

Q6. You develop a Microsoft SQL Server 2012 database. 

You need to create a batch process that meets the following requirements: 

. Returns a result set based on supplied parameters. 

. Enables the returned result set to perform a join with a table. 

Which object should you use? 

A. Inline user-defined function 

B. Stored procedure 

C. Table-valued user-defined function 

D. Scalar user-defined function 

View Answer

Q7. You are a database developer of a Microsoft SQL Server 2012 database. 

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. 

A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. 

Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer 

(SourceID int NOT NULL IDENTITY, 

CustomerID int NOT NULL IDENTITY, 

CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

View Answer

Q8. You develop a database for a travel application. You need to design tables and other database objects. 

You need to store media files in several tables. 

Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q9. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

B. SELECT Id, Name, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM StudentMarks 

C. SELECT StudentCode as Code, DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

D. SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

View Answer

Q10. CORRECT TEXT 

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) 

You have the following query: 

You need to recreate the query to meet the following requirements: 

Reference columns by using one-part names only. 

Sort aggregates by SalesTerritoryID, and then by ProductID. 

Order the results in descending order from SalesTerritoryID to ProductID. 

The solution must use the existing SELECT clause and FROM clause. 

.... 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

View Answer