USA: +1 (725) 712-1730 
Sign in
Question 32
1. Choose the best solution. Write a query the calculates the average unit price for all products in the products table.
| Select Avg(UnitPrice) From Products |
||
| Select UnitPrice From Products Where UnitPrice = (Select Avg([Order Details].UnitPrice) From [Order Details]) |
||
| Select Sum(UnitPrice)/Count(*) From Products |
||
| Select UnitPrice as Average From Products |
||
| Select Avg([Order Details].UnitPrice) From Products, [Order Details] |
Question 33
1. Choose the best solution.
Write a query that returns the count of all the records, the max freight, and employee id for only those freight were the maixmum >= 50. You will need to group on employee id.
Sort on the employee id.
| SELECT EmployeeID , Count(*) As AllRecords , Max(Freight) As MaxFreight FROM Orders GROUP BY EmployeeID HAVING Max(Freight) >= 50 ORDER BY EmployeeID |
||
| SELECT EmployeeID , Count(*) As AllRecords , Max(Freight) As MaxFreight FROM Orders HAVING MaxFreight > 50 GROUP BY EmployeeID |
||
| SELECT Employees.EmployeeID , Count(*) As AllRecords , Max(Orders.Freight) As MaxFreight FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID GROUP BY Employees.EmployeeID HAVING Max(Orders.Freight) >= 50 |
||
| SELECT EmployeeID , Count(*) As AllRecords , Max(Freight) As MaxFreight FROM Orders WHERE Freight >= 50 GROUP BY EmployeeID HAVING Max(Freight) >= 50 ORDER BY EmployeeID |
Question 34
1. Choose the best solution.
Retrieve the number of employees in each city in which there are at least 2 employees.
| SELECT City, COUNT(EmployeeID) AS NumEmployees FROM Employees WHERE EmployeeID > 1 GROUP BY City HAVING COUNT(EmployeeID) > 1 |
||
| SELECT City, COUNT(EmployeeID) AS NumEmployees FROM Employees WHERE EmployeeID > 1 GROUP BY City |
||
| SELECT City, COUNT(EmployeeID) AS NumEmployees FROM Employees GROUP BY City HAVING COUNT(EmployeeID) > 1 |
||
| SELECT COUNT(*) AS NumEmployees FROM Employees HAVING COUNT(EmployeeID) > 1 Question 35 1. _____ functions are used in conjunction with the HAVING clause when you want to limit the grouped rows returned. Question 36 1. The COALESCE(reportsto, 0) statement forces AVG() to count NULL rows as zeroes; thus, forcing every single row to be counted. True False Question 37 1. NULLs are not eliminated by MIN() and MAX() functions. True False Question 38 1. If a GROUP BY is not used in the query, then the aggregate functions are applied to the entire set of rows returned. True False 10 points Question 39 1. When COUNT references a column name, it does not include duplicate nor nulls. True False
Question 40 1. Use Query01 from above. The 2rd inner query is within the DateDiff() funciton — select Max(OrderDate)fromNorthwind.dbo.Orders – returns the most recent date of any purchase. In this case the day was May 6, 2008 (2008-05-06). The difference between that value and the OrderDate is then calculated. Need help ASAP PLEASE! Fast thumbs ups True False |
Expert Answer
Ans:
[32]
SELECT AVG(unitprice)
FROM products;
The above query calculates the total of unit prices and divides it by the number of rows in the products table to give the the average unit price for all products in the products table.
[34]
SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE EmployeeID > 1
GROUP BY City
The above query finds out the number of employees in each city in which there are at least 2 employees. The table is grouped by City.
[35]
Group By
Group By functions are used in conjunction with the HAVING clause when you want to limit the grouped rows returned.
[36]
True
The COALESCE(reportsto, 0) statement forces AVG() to count NULL rows as zeroes; forcing every single row to be counted.We will get the same answer by using the SUM() and COUNT() set functions.
[37]
False
NULLs are eliminated by MIN() and MAX() functions. For the data types char, shorter values are right-padded with ‘pad characters’ before comparing them.
[38]
True
If a GROUP BY is not used in the query, and when an aggregate function is used, the group function displays a single value for the whole table.
