Microsoft Querying Data with Transact-SQL - 70-761 Exam Practice Test
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You create a table by running the following Transact-SQL statement:
You are developing a report that displays customer information. The report must contain a grand total column.
You need to write a query that returns the data for the report.
Which Transact-SQL statement should you run?
You create a table by running the following Transact-SQL statement:
You are developing a report that displays customer information. The report must contain a grand total column.
You need to write a query that returns the data for the report.
Which Transact-SQL statement should you run?
Correct Answer: H
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
All the sales data is stored in a table named table1. You have a table named table2 that contains city names.
You need to create a query that lists only the cities that have no sales.
Which statement clause should you add to the query?
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
All the sales data is stored in a table named table1. You have a table named table2 that contains city names.
You need to create a query that lists only the cities that have no sales.
Which statement clause should you add to the query?
Correct Answer: E
Vote an answer
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A database has two tables as shown in the following database diagram:
You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A database has two tables as shown in the following database diagram:
You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have a database named DB1 that contains two tables named Sales.Customers and Sales.CustomerTransaction. Sales.CustomerTransactions has a foreign key relationship to column named CustomerID in Sales.Customers.
You need to recommend a query that returns the number of customers who never completed a transaction.
Which query should you recommend?
You need to recommend a query that returns the number of customers who never completed a transaction.
Which query should you recommend?
Correct Answer: B
Vote an answer
You have the following Transact-SQL query:
What type of functions are used in the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
What type of functions are used in the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation
Box 1: Scalar
The return value of a function can either be a scalar (single) value or a table.
Box 2: Table-Valued
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
References:
https://msdn.microsoft.com/en-us/library/ms186755.aspx
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
You need to create a table named MiscellaneousPayment that meets the following requirements:
Which Transact-SQL statement should you run?
Which Transact-SQL statement should you run?
Correct Answer: B
Vote an answer
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a table that was created by running the following Transact-SQL statement:
The Products table includes the data shown in the following table:
TotalUnitPrice is calculated by using the following formula:
TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)
You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a table that was created by running the following Transact-SQL statement:
The Products table includes the data shown in the following table:
TotalUnitPrice is calculated by using the following formula:
TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)
You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have a table named HumanResources.Department that was created with the query shown in the exhibit.
(Click the Exhibit button.)
You need to query temporal data in the table.
In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data.
NOTE: Make only one selection in each column.
(Click the Exhibit button.)
You need to query temporal data in the table.
In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data.
NOTE: Make only one selection in each column.
Correct Answer:
Explanation
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.
You work for an organization that monitors seismic activity around volcanos. You have a table named GroundSensors. The table stored data collected from seismic sensors. It includes the columns describes in the following table:
The database also contains a scalar value function named NearestMountain that returns the name of the mountain that is nearest to the sensor.
You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The query must meet the following requirements:
- Include the average normalized readings and nearest mountain name.
- Exclude sensors for which no normalized reading exists.
- Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
- Use one part names to reference tables, columns and functions.
- Do not use parentheses unless required.
- Do not use aliases for column names and table names.
- Do not surround object names with square brackets.
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 the code that has been provided as well as below it.
1. SELECT
2. FROM Sales.Products AS P
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.
The database also contains a scalar value function named NearestMountain that returns the name of the mountain that is nearest to the sensor.
You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The query must meet the following requirements:
- Include the average normalized readings and nearest mountain name.
- Exclude sensors for which no normalized reading exists.
- Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
- Use one part names to reference tables, columns and functions.
- Do not use parentheses unless required.
- Do not use aliases for column names and table names.
- Do not surround object names with square brackets.
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 the code that has been provided as well as below it.
1. SELECT
2. FROM Sales.Products AS P
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.
Correct Answer:
See explanation below
Explanation
1. SELECT avg(P.ProductPrice) AS Average, min(P.ProductsInStock) AS LowestNumber, max(P.ProductPrice) AS HighestPrice
2. FROM Sales.Products AS P
Make the additions to line 1.
References: https://www.mssqltips.com/sqlservertip/4424/max-min-and-avg-sql-server-functions/
Explanation
1. SELECT avg(P.ProductPrice) AS Average, min(P.ProductsInStock) AS LowestNumber, max(P.ProductPrice) AS HighestPrice
2. FROM Sales.Products AS P
Make the additions to line 1.
References: https://www.mssqltips.com/sqlservertip/4424/max-min-and-avg-sql-server-functions/
You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement:
You create the following table valued function to generate lists of people:
You need to build a report that shows meetings with at least two people only.
What should you use?
You create the following table valued function to generate lists of people:
You need to build a report that shows meetings with at least two people only.
What should you use?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A database has two tables as shown in the following database diagram:
You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A database has two tables as shown in the following database diagram:
You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You work for an organization that monitors seismic activity around volcanos. You have a table named GroundSensors. The table stored data collected from seismic sensors. It includes the columns describes in the following table:
The database also contains a scalar value function named NearestMountain that returns the name of the mountain that is nearest to the sensor.
You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The query must meet the following requirements:
* Include the average normalized readings and nearest mountain name.
* Exclude sensors for which no normalized reading exists.
* Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
* Use one part names to reference tables, columns and functions.
* Do not use parentheses unless required.
* Do not use aliases for column names and table names.
* Do not surround object names with square brackets.
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 the 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.
SELECT SensorID, NearestMountain(Location)
FROM GroundSensors
WHERE TREMOR <> 0 AND NormalizedReading IS NOT NULL
GROUP BY SensorID, NearestMountain(Location)
The database also contains a scalar value function named NearestMountain that returns the name of the mountain that is nearest to the sensor.
You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The query must meet the following requirements:
* Include the average normalized readings and nearest mountain name.
* Exclude sensors for which no normalized reading exists.
* Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
* Use one part names to reference tables, columns and functions.
* Do not use parentheses unless required.
* Do not use aliases for column names and table names.
* Do not surround object names with square brackets.
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 the 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.
SELECT SensorID, NearestMountain(Location)
FROM GroundSensors
WHERE TREMOR <> 0 AND NormalizedReading IS NOT NULL
GROUP BY SensorID, NearestMountain(Location)
Correct Answer:
Explanation
GROUP BY is a SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.
SELECT SensorID, NearestMountain(Location)
FROM GroundSensors
WHERE TREMOR <> 0 AND NormalizedReading IS NOT NULL
GROUP BY SensorID, NearestMountain(Location)
References: https://msdn.microsoft.com/en-us/library/ms177673.aspx
GROUP BY is a SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.
SELECT SensorID, NearestMountain(Location)
FROM GroundSensors
WHERE TREMOR <> 0 AND NormalizedReading IS NOT NULL
GROUP BY SensorID, NearestMountain(Location)
References: https://msdn.microsoft.com/en-us/library/ms177673.aspx
You are performing a code review of stored procedures. Code at line SP03 fails to run (Line numbers are included for reference only.)
You need to ensure that transactions are rolled back when an error occurs.
Which Transact-SQL segment should you insert at line SP07?
You need to ensure that transactions are rolled back when an error occurs.
Which Transact-SQL segment should you insert at line SP07?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database named DB1 that contains two tables named Sales.Customers and Sales.Orders.
Sales.Customers has a foreign key relationship to a column named CustomerID in SalesOrders.
You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2016.
Solution: You recommend the following query:
Does this meet the goal?
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database named DB1 that contains two tables named Sales.Customers and Sales.Orders.
Sales.Customers has a foreign key relationship to a column named CustomerID in SalesOrders.
You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2016.
Solution: You recommend the following query:
Does this meet the goal?
Correct Answer: A
Vote an answer