Q1. 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.
Answer: B
Q2. You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:
You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?
A. ALTER TABLE Orders
ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES
Customer (CustomerId)
B. ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID)
REFERENCES
Orders (CustomerId)
C. ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
D. ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES
Orders
(CrderlD);
E. ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms189049.aspx
Q3. You develop a Microsoft SQL Server 2012 server database that supports an application.
The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.
The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?
A. ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse
B. ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
C. ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
D. ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
Answer: B
Q4. 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.
Answer: F
Q5. 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.)
Answer:
Q6. 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
Q7. 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
Q8. DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.
Which three 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.)
Answer:
Q9. 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
Q10. 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?
A. Perform a file restore.
B. Perform a transaction log restore.
C. Perform a restore from a full backup.
D. Perform a filegroup restore.
Answer: A