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)from Northwind.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