Search This Blog

Lets go..

Tuesday

SQL exam 70-461 questions and answers part 3


Free Phone

MS-SQL exam 70-461 questions and answers dumps
QUESTION 21
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.

Correct Answer: Below

QUESTION 22
You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats. 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.

Correct Answer: Below
QUESTION 23
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.

Correct Answer: I

QUESTION 24
You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. 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.

Correct Answer: Below


QUESTION 25
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Ÿ Future modifications to the table definition will not affect the applications' ability to access data.
Ÿ The new object can accommodate data retrieval and data modification.
Ÿ You need to achieve this goal by using the minimum amount of changes to the applications.

What should you create for each application?

A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables

Correct Answer: Below




QUESTION 26
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 during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. 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.

Correct Answer: Below

QUESTION 27
You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. 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.

Correct Answer: Below

QUESTION 28
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.

Correct Answer: Below


QUESTION 29
You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: One column in its WHERE clause contained in a non-clustered index. Four additional columns One COUNT (*) column based on a grouping of the four additional columns. You need to optimize the statement.

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.

Correct Answer: Below


QUESTION 30
You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a non-clustered index. The query returns the following six columns: One column in its WHERE clause contained in a non-clustered index. Four additional columns One COUNT (*) column based on a grouping of the four additional columns. You need to optimize the statement.

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.

Correct Answer: Below



Correct Answer:

21
22
23
24
25
26
27
28
29
30
F
C
I
E
C
M
K
G
F
C

Monday

SQL exam 70-461 questions and answers part 2



MS - SQL exam 70-461 questions and answers dumps
QUESTION 11
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

 

QUESTION 12

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



QUESTION 13
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit
FROM Profits

B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit
FROM Profits
C. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit
FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit
FROM Profits



QUESTION 14

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data
to an NVARCHAR(MAX) variable named @var.
You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use? select

A. BEGIN TRY
SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast
END TRY
BEGIN CATCH
SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast
END CATCH
B. TRY(
SELECT convert(decimal(36,9), @var)
SELECT 'True' AS BadCast
)
CATCH(
SELECT 'False' AS BadCast
)
C. SELECT
CASE
WHEN convert(decimal(36,9), @var) IS NULL
THEN 'True'
ELSE 'False'
END
AS BadCast
D. SELECT
IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')
AS BadCast



QUESTION 15
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



QUESTION 16
You are a database developer at an independent software vendor. You create stored procedures
that contain proprietary code. You need to protect the code from being viewed by your customers.
Which stored procedure option should you use?

A. ENCRYPTBYKEY
B. ENCRYPTION
C. ENCRYPTBYPASSPHRASE
D. ENCRYPTBYCERT

 

QUESTION 17
You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO
B. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO

 

QUESTION 18
You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:

INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK



QUESTION 19
Your database contains a table named Purchases. The table includes a DATETIME column named
PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index
on the PurchaseTime column. The business team wants a report that displays the total number of
purchases made on the current day. You need to write a query that will return the correct results
in the most efficient manner. Which Transact-SQL query should you use?

A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))



QUESTION 20
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

Correct Answer:

11
12
13
14
15
16
17
18
19
10
A
E
B
D
A
B
A
E
D
D