Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
Answer: B,D
Q2. You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:
You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.
Which Transact-SQL query should you use?
A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice) - - Create Audit Records
B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records
C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice) - - Create Audit Records
D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records
Answer: D
Q3. You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire
transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Answer: A
Q4. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO
F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
Answer: F
Q5. You support a database structure shown in the exhibit. (Click the Exhibit button.)
You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country
Sub totals only at the city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?
A. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), (Country, City), (Country), ())
B. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
C. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
Answer: A
Q6. 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 a create a unique constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: D
Q7. You develop a database for a travel application. You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone
information.
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.
Answer: I
Q8. 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 following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to
these students.
Consecutive ranks must be skipped when the same rank is assigned.
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
Answer: A
Q9. You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?
A. CROSS JOIN
B. UNPIVOT
C. PIVOT
D. CROSS APPLY
Answer: D
Q10. CORRECT TEXT
You have a database 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 Appl. Usp_Customers must meet the following requirements:
NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
...
. Return only the number of rows specified by the @Count parameter. . The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
Q11. You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?
A. Real
B. Small money
C. Money
D. Decimal
Answer: D
Q12. You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a runtime error, the entire
transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored
procedure?
A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. SET XACT_ABORT OFF
C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
D. SET IMPLICIT_TRANSACTIONS ON
E. SET XACT_ABORT ON
F. SET IMPLICIT TRANSACTIONS OFF
Answer: E
Q13. You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.
You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?
A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1
D. EXECUTE AS CALLER
Answer: B
Q14. DRAG DROP
You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust.
Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust.
You need to write a query that meets the following requirements:
The rows in Div1Cust must be combined with the rows in Div2Cust. The result set must have columns named Division and Customer. Duplicates must be retained.
Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q15. You generate a daily report according to the following query:
You need to improve the performance of the query.
What should you do?
A. Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())
B. Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)
C. Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())
D. Rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM
Sales.ufnGetRecentOrders(c.CustomerID, 90))
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime)
RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())
Answer: A
Q16. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?
A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Answer: D
Q17. 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.
Answer: