Microsoft Querying Microsoft SQL Server 2012

Question - 35 Maximum mark : - 66

Home
All Exams
Microsoft Querying Microsoft SQL Server 2012 Online Practice Exams
Microsoft Querying Microsoft SQL Server 2012

You administer a Microsoft SQL Server database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role. ✘ Include the Sales schema as an owned schema for the db_denydatawriterrole. Add the users to the db_denydatawriter role. ✘ Deny Delete permissions on each table in the Sales schema for each user. ✘ Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You administer a Microsoft SQL Server database. The database contains a Product table created by using the following definition: You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

Convert all indexes to Column Store indexes. ✘ Implement Unicode Compression. ✘ Implement row-level compression. ✘ Implement page-level compression. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You generate a daily report according to the following query: You need to improve the performance of the query. What should you do?

Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.Salesorder s ON c.CustomerID = c.CustomerID
GROUP BY s.CustomerID
HAVING MAX(s.OrderDate) < DATEADD(DAY, -90, GETDATE())

✘

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

✘

Rewrite the report query as follows:SELECT c.CustomerName
FROM Sales.Customer c
WHERE ( SELECT OrderDate
FROM Sales.ufnGeTLastOrderDate(c.CustomerID)) < DATEADD(DAY, -90, GETDATE() ) Rewrite the UDF as follows: CREATE FUNCTION Sales.ufnGeTLastOrderDate(@CustomerID int) RETURNS @OrderTable TABLE (OrderDate datetime) AS BEGIN INSERT @OrderTable SELECT MAX(s.OrderDate) As OrderDate FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID RETURN END

✘

Rewrite the report query as follows:SELECT c.CustomerName
FROM Sales.Customer c
WHERE (SELECT OrderDate FROM Sales.ufnGetOrderDate (c.CustomerID)) < DATEADD(DAY, -90, GETDATE())
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetOrderDate (@CustomerID int)
RETURNS TABLE AS RETURN (
SELECT TOP 1 s.OrderDate
FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID
)

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You use a Microsoft SQL Server database that contains a table named BlogEntry that has the following columns: Id is the Primary Key. You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

UPDATE TOP(10) BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0)

✘

UPDATE BlogEntrySET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry
ORDER BY EntryDateTime DESC)

✘

UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (
SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID

✘

UPDATE BlogEntrySET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry
ORDER BY EntryDateTime DESC)

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You use Microsoft SQL Server to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = @@ERROR,
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8)) + N',
Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO

✘

DECLARE @ErrorVar INT;DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = ERROR_STATE(),
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(ERROR_STATE() AS NVARCHAR(8)) + N',
Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO

✘

EXEC DeleteJobCandidateIF (ERROR_STATE() != 0)
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N',
Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO

✘

EXEC DeleteJobCandidatePRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N',
Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You use Microsoft SQL Server to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

IF @@TRANCOUNT = 0

✘

IF (XACT_STATE ( ) ) = 0

✘

IF (XACT_STATE ( ) ) = 1

✘

IF @@TRANCOUNT = l

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

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?

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

✘

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

✘

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

✘

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

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You use Microsoft SQL Server 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?

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

✘

TRY(SELECT CONVERT (DECIMAL(36,9), @var)
SELECT 'True' As BadCast )
CATCH( SELECT 'False' As BadCast)

✘

SELECTCASE WHEN CONVERT (DECIMAL(36,9), @var) IS NULL
THEN 'True' ELSE 'False' END AS BadCast

✘

SELECTIIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' ) AS BadCast

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You develop a Microsoft SQL Server 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?

Synonyms ✘ Common table expressions ✘ Views ✘ Temporary tables ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You develop a Microsoft SQL Server 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?

ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse ✘ ALTER TABLE InventoryADD TotalItems ASItemsInStore + ItemsInWarehouse PERSISTED ✘ ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED ✘ ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft Querying Microsoft SQL Server 2012

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner