70-461 Exam Questions - Online Test

70-461 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Want to know Testking mcsa 70 461 Exam practice test features? Want to lear more about Microsoft Querying Microsoft SQL Server 2012 certification experience? Study Certified Microsoft exam 70 461 answers to Regenerate exam 70 461 pdf questions at Testking. Gat a success with an absolute guarantee to pass Microsoft exam 70 461 pdf (Querying Microsoft SQL Server 2012) test on your first attempt.

Q61. 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 

Q62. 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 

Q63. 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: 

Q64. 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 Foreign Key constraint that references a different table in the database? 

A. DateHired 

B. Departments 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 

Explanation: 

Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database. 

Q65. CORRECT TEXT 

You have a database that contains the tables as shown below: 

You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: 

The code must NOT use aliases. 

The code must NOT use object delimiters. 

The objects called in Procedure1 must be able to be resolved by all users. 

OrderDate must be compared to Parameter1 after the data type of Parameter1 is 

changed to datetime. 

Which SELECT statement should you use? 

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

Answer: 

Q66. 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 Customerld 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, Crderld, 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: G 

Q67. You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. 

You need to assign a PartitionNumber for each record in the Customers table.You also need to ensure that the PartitionNumber satisfies the following conditions: 

. Always starts with 1. 

. Starts again from 1 after it reaches 100. 

Which Transact-SQL statement should you use? 

A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence 

D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

Answer: B 

Q68. You develop 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 not include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId 

B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER 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 JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

Answer: D 

Q69. 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 

Q70. 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