Exam 70-761 topic 1 question 159 discussion

Actual exam question from Microsoft's 70-761

Question #: 159
Topic #: 1

SIMULATION -
You have a database that contains a table named Products in the Sales schema. The table was created by running the following Transact-SQL statement:

The table includes the data shown below:

You are developing a report that displays the following values and column headers in the order listed below:
✑ average price of a product named Average
✑ the smallest number of products in stock named LowestNumber
✑ the highest product price named HighestPrice
You need to write a query to return the results for the report. The query must meet the following requirements:
✑ Use built-in, aggregate and mathematical functions.
✑ Use two-part names and tables.
✑ Use the table alias to qualify column names.
✑ Define the alias for all fields by using the AS keyword.
✑ Use the first letter of the table name as the table alias.
✑ Do not use the ROW_NUMBER function.
✑ Do not surround object names with square brackets.
✑ Do not use variables.
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.

Show Suggested Answer

Suggested Answer: See explanation below
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/

by chaoxes at July 22, 2020, 5:19 a.m.

Comments

Submit Cancel

chaoxes

Highly Voted 4 years, 9 months ago

Group by is not necessary. Answer is correct, you can test yourself: CREATE TABLE Prod ( ProductID bigint NOT NULL PRIMARY KEY ,ProductName nvarchar(100) NOT NULL ,ProductPrice decimal(18,2) NOT NULL ,ProductsInStock int NOT NULL ,ProductsOnOrder int NOT NULL ) INSERT INTO Prod VALUES (1, 'ProductA', 10.00, 10, 15) ,(2, 'ProductB', 30.00, 20, 0) ,(3, 'ProductC', 15.00, 5, 20) SELECT AVG(p.ProductPrice) AS Average, MIN(P.ProductsInStock) AS LowestNumber, MAX(P.ProductPrice) AS HighestPrice FROM Prod AS p

upvoted 5 times

Andy7622

4 years, 4 months ago

use two part names for tables . this is the Sales schema. => FROM Sales.Product

upvoted 1 times

...

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 159 discussion

Comments

chaoxes

Andy7622

SY0-701