Microsoft 70-461 Exam Practice Test Instant Access

Question 1

The following is a series of questions in which you are required to input one or more lines of code.

To input your response

Type your response into the text entry field in the Answer Area. You may input one or more lines of code. More than one solution may be correct. You will receive credit if your solution matches any of the correct solutions.

To validate code syntax

After entering your code, click the Check Syntax button. This validates code syntax (such as SQL commands) and values (such as table names and variable names) used in your solution. If there are any errors, they will appear in the window next to the Check Syntax button. You may change your code and re-validate the syntax as many times as you want.

Note that Check Syntax does NOT validate whether you have answered the question correctly. It simply validates the accuracy of your syntax.

To view available command keywords

Click the Keywords button to view a list of command keywords. This is a general list provided for reference and is not limited to commands used in the question.

The Sales schema of a database contains the tables shown in the exhibit.

You need to create a stored procedure named RecentOrders that meets the following requirements:

* Declares an input variable named @EndDate of type varchar(255).

* Compare the value of @EndDate with the OrderDate for records from the Sales.Orders table.

* Return only the OrderID column for rows where the OrderDate is more recent than @EndDate.

Construct the Transact-SQL for the stored procedure by using the following guidelines:

* Do not use aliases.

* Do not use object delimiters.

* Ensure that any objects called in RecentOrders can be resolved by all users.

* Convert @endDate to a datetime data type in the WHERE clause of the SELECT statement before comparing the value to an OrderDate.

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

ALine 1: CREATE PROCECURE RecentOrders @EndDate varchar(255)
Line 2: AS
Line 3: SELECT OrderID
Line 4: FROM Sales.Orders
Line 5: WHERE Sales.Orders

BLine 1: CREATE PROCECURE RecentOrders @EndDate varchar(255)
Line 2: AS
Line 3: SELECT OrderID
Line 4: FROM Sales.Orders
Line 5: WHERE Sales.Orders.OrderDate > @Endate
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

Answer : B

Question 2

You are designing a Microsoft SQL Server database named Orders.

You create a table by running the following Transact-SQL statement:

You create a stored procedure to be used by an ASP.NET application that runs the following statement:

You need to ensure that the query runs as efficiently as possible.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.

Example: To create a primary key in an existing table

The following example creates a primary key on the column TransactionID in the AdventureWorks database.

ALTER TABLE Production.TransactionHistoryArchive

ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

References:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys

Question 3

You develop a Microsoft SQL Server database that contains a two tables named OrdersHistorical and CompletedOrders.

Both tables have the same schema that has the following definition:

You write the following Transact-SQL statement:

You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.

Which table hint should you use?

AROWLOCK

BHOLDLOCK

CXLOCK

DTABLOCK

EUPDLOCK

Answer : E

UPDLOCK s

pecifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.

Incorrect Answres:

Not A: ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken.

B: HOLDLOCK is equivalent to SERIALIZABLE. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in.

Note: SERIALIZABLE is equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not

D: TABLOCK specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level.

When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Question 4

You have a Microsoft SQL Server database that contains two tables named SalesTax and SalesTerritory.

You need to write a query that will return all possible combinations of SalesTax and SalesTerritory, regardless of whether two particular records are related.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

Answer : C

We want to do a CROSS JOIN, which returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?

Question 5

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.

The table contains a unique constraint on the combination of ProductName and CreatedDateTime.

You need to modify the Products table to meet the following requirements:

* Remove all duplicates of the Products table based on the ProductName column.

* Retain only the oldest Products row.

Which Transact-SQL query should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

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