Lab: 1. Write an SQL statement to get the average, maximum, and minimum quantity
ID: 675097 • Letter: L
Question
Lab:
1. Write an SQL statement to get the average, maximum, and minimum quantity per order stored in table “Sales.SalesOrderDetail”, column “SalesOrderID”, for order numbers 43660, 43670, and 43672. This query should be written as a single SQL statement.
Fill out the following table:
Order number
Average
Maximum
Minimum
43660
43670
43672
Submit the SQL statement used to accomplish this task.
How many rows were affected?
Provide a screenshot of the result set.
2. When working in a normalized environment, chances are one will have to combine tables and get a result set into a table. To accomplish this task, the clause JOIN is used. Depending on what result is needed, different forms of this clause are used. They are:
INNER JOIN
OUTER JOIN (both LEFT and RIGHT)
FULL JOIN
CROSS JOIN
What all types of JOIN have in common is that they, based on a condition, match one record from one table to one or more records in another table. The result will be records that combine the data from both tables.
INNER JOINs are the most used type of JOIN. They return only the data for which matches were found.
SELECT *
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
Using the example above, write an SQL query that returns all the information for all contacts stored in the Person.BusinessEntity table and only the jobTitle from the HumanResources.Employee table. Note that multiple rows may be returned.
Submit the SQL statement used to accomplish this task.
How many rows were affected?
Provide a screenshot of the result set.
Order number
Average
Maximum
Minimum
43660
43670
43672
Explanation / Answer
For the Q1, for each orderid, we need to collect records with the same orderid and in the collected records , we need to find the minimum,
maximum and average quantity in that order id. To collect all rows together for a given order id, we use group by clause and then apply the
functions avg , min , and max . We rename the result using "AS" in the select clause. Also since we want to filter out the results for only the
3 order ids mentioned in question, we will use where clause to filter out the results.
SELECT SalesOrderId AS OrderNumber, AVG(quantity) AS Average , MIN(quantity) AS Minimum , MAX(quantity) AS Maximum
FROM Sales.SalesOrderDetail
WHERE SalesOrderId IN (43660, 43670, and 43672)
GROUP BY SalesOrderId
Note: If SalesOrderId is not numeric in the table, you will have to use quotes around the values ex: "43660", "43670"
For Q2, We need to get all rows from BusinessEntity table with and additional column JobTitle from Employee . The value of jobtitle should be corresponding
to the same BusinessEntityID in both tables.
SELECT *, JobTile
FROM Person.BusinessEntity
INNER JOIN HumanResources.Employee
ON Person.BusinessEntity.BusinessEntityID = HumanResources.Employee.BusinessEntityID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.