Microsoft 70-461 Exam Practice Test Instant Access

Question 1

You administer a Microsoft Azure SQL Database instance.

You are writing a new stored procedure that uses explicit transactions.

You need to ensure that no other process can affect the objects being used by the stored procedure while it is running.

Which Transact-SQL statement should you run?

ASET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BSET IMPLICIT_TRANSACTIONS ON

CSET CURSOR_CLOSE_ON_COMMIT ON

DSET XACT_ABORT ON

Answer : A

SERIALIZABLE specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

Question 2

You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:

You need to meet the following requirements:

* If a product has a product category value other than ''REGULAR'', the product should have a sub-category with a length of two characters.

* If a product has the product category ''REGULAR'', the product may or may not have a sub-category.

Which Transact-SQL statement should you use?

AOption A

BOption B

COption C

DOption D

Answer : C

Either the Category column is REGULAR, or it is not REGULAR and the length of the column is exactly 2.

Question 3

You create a table that has three columns named StudentCode, SubjectCode, and Marks. The Marks column records grades for students expressed as a percentage. The table has marks obtained by 50 students for various subjects.

You need to retrieve the StudentCode and Marks for students who scored the highest percentage for each subject.

Which Transact-SQL query should you use?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : G

We use PARTION BY SubjectCode to get the Subjects, DESC sorting and RANK =1 to get highest scores.

Question 4

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the following requirements are met:

* Students must be ranked based on their average marks.

* If one or more students have the same average, incremental ranks must be given based on the order they are created.

* Ranks must be sequential without gaps in between.

Which Transact-SQL query should you use?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : C

The ROW_NUMBER numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

References: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

Question 5

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the following requirements are met:

* Students must be ranked based on their average marks.

* If one or more students have the same average, the same rank must be given to these students.

* Ranks must be sequential without gaps in between.

Which Transact-SQL query should you use?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : B

The DENSE_RANK function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

References: https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017

Question 6

You develop a Microsoft SQL Server database that contains a table named Products. The Products table has columns named ProductId, CategoryId, Name, and Price.

Product prices are often updated as follows:

* For a single product by a specific value by using the ProductId column

* For an entire category by a percentage by using the CategoryId column

You need to log the change price to a new table named PriceChange by using the ProductId' ChangedValue, and ChangedDate columns. You also need to ensure that increases in price are recorded as positive values and decreases in price as negative values.

Which Transact-SQL query should you use?

AOption A

BOption B

COption C

DOption D

Answer : D

Question 7

You need to create a query that meets the following requirements:

* The query must return a list of salespeople ranked by amount of sales and organized by postal code.

* The salesperson who has the highest amount of sales must be ranked first.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

A1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS 'Ranking',
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc
More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00

B1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS 'Ranking',
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc
On line 1 add: RowNumber
One line 1 add: PARTITION BY
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00

Answer : B

References:

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Microsoft 70-461 Querying Microsoft SQL Server 2012/2014 Exam Practice Test