70-461 Exam
The Only Tip You Need: 70 461 pdf
Exam Code: 70 461 training kit (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Querying Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70 461 exam questions pdf Exam.
Q31. 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
Q32. You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Answer: G
Q33. You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
Answer: A
Q34. 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:
Q35. 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.)
Unless stated above, no columns in the Employee table reference other tables.
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 self-reference foreign key constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: I
Q36. You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.
Which Transact-SQL operator should you use?
A. CROSS JOIN
B. CROSS APPLY
C. PIVOT
D. UNPIVOT
Answer: C
Explanation:
http://technet.microsoft.com/en-us/library/ms177634.aspx
Q37. 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.)
Unless stated above, no columns in the Employee table reference other tables.
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 Primary Key constraint for this table?
A. DateHired
B. Departments
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: C
Q38. 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.
Which Transact-SQL statement should you use?
A. ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse
B. ALTER TABLE Inventory
ADD ItemsInStore - ItemsInWarehouse = TotalItemss
C. ALTER TABLE Inventory
ADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);
Answer: A
Q39. 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
Q40. You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
. Status information must be logged to a status table.
. If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?
A. Scalar user-defined function
B. Inline user-defined function
C. Table-valued user-defined function
D. Stored procedure
Answer: D