Microsoft Querying Microsoft SQL Server 2012/2014 - 070-461 Free Exam Questions [2026]

QUESTION NO: 1
You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
* Total sales made by sales people, year, city, and country
* Sub totals only at the city level and country level
* A grand total of the sales amount
Which Transact-SQL query should you use?

A. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

B. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))

C. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

D. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUESTION NO: 2
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use?
(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Discussion 0

Correct Answer:

QUESTION NO: 3
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
* Status information must be logged to a status table.
* If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?

A. Stored procedure

B. Scalar user-defined function

C. Table-valued user-defined function

D. Inline user-defined function

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUESTION NO: 4
You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

A. Smallmoney

B. Money

C. Varchar

D. Numeric

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUESTION NO: 5
You use a Microsoft Azure SQL Database instance. The instance contains a table named Products that has columns named Code, Name, and IsPerishable.
You need to create a view named VwPerishableProducts that:
* returns rows from Products that have a value of True in the IsPerishable column;
* does not allow columns to be altered or dropped in the underlying table.
Which Transact-SQL statement should you run?

A. Option

B. Option

C. Option

D. Option

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUESTION NO: 6
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

B. Grant the sysadmin role on the database to User1.

C. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

D. Grant the db_owner role on the database to User1.

E. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.

Discussion 0

Correct Answer: B,D Vote an answer

QUESTION NO: 7
You develop a SQL Server database for a large multinational company.
You must develop a stored procedure to loop through employee information and return data for one employee at a time.
How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Discussion 0

Correct Answer:

Explanation:
Box 1: CURSOR VARYING OUTPUT
Transact-SQL stored procedures can use the cursor data type only forOUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Box2: FORWARD_ONLY STATIC
Box 3: CLOSE
Box 4: DEALLOCATE
References:https://technet.microsoft.com/en-us/library/ms175498(v=sql.105).aspx

QUESTION NO: 8
You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.
The database contains two tables that have the following definitions:

Global customers place orders from several countries.
You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs
WHERE Rnk = 1

B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1

C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1

D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC

Discussion 0

Correct Answer: B Vote an answer

QUESTION NO: 9
You are a developer for a Microsoft SQL Server database. You need to write a stored procedure that performs several operations in the most efficient way possible.
Which operator or operators should you use? To answer, drag the appropriate operators to the correct operations. Each operator may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Discussion 0

Correct Answer:

Explanation:
Box 1: UNION ALL
UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.
UNION ALL Incorporates all rows into the results. This includes duplicates. If ALL is not specified, duplicate rows are removed.
Box 2: INTERSECT
INTERSECT returns distinct rows that are output by both the left and right input queries operator.
Box 3: INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
Box 4: MERGE
Merge performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
Box 5: FULL OUTER JOIN
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!

QUESTION NO: 11
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.

Discussion 0

Correct Answer:

DECLARE @XML1 XML(Sales.InvoiceSchema)
Reference:
http://msdn.microsoft.com/en-us/library/ms176009.aspx

QUESTION NO: 12
You want to add a new GUID column named ProductGUID to a table named dbo.Product that already contains data.
ProductGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Product.
You need to ensure that the new column is assigned a GUID for existing rows.
Which four Transact-SQL statements should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list of SQL-segments to the answer area and arrange them in the correct order.)

Discussion 0

Correct Answer:

Explanation:
Box 1:
Existing BookGuid can be null.
Box 2:
Box 3: new()
Generate a new global value
Box 4:
Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.

QUESTION NO: 13
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
* Write the results to a disk.
* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
* The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?

A. CREATE TABLE Sales.OrderDetails (
ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

B. CREATE TABLE Sales.OrderDetails (
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUESTION NO: 14
A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?

A. Option C

B. Option B

C. Option A

D. Option D

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

QUALITY AND VALUE

Tested and Approved

Easy to Pass

Try Before Buy

RECENT DISCUSSIONS

Useful Links

Contact Us