Question 30

Display different cities along with the number of Northwind employees in each city.

		SELECT DISTINCT COUNT(City), COUNT(EmployeeID) as 'DifferentCities' FROM Employees GROUP BY City ORDER BY 2
		SELECT COUNT(DISTINCT City) AS NumbeOfCities FROM Employees WHERE EmployeeID IS NOT NULL
		SELECT DISTINCT E.City, COUNT(E.City) AS [Employees Per City] FROM Employees AS E GROUP BY E.City WITH ROLLUP ORDER BY 2
		SELECT DISTINCT City,DENSE_RANK() OVER(ORDER BY City) AS Ranking FROM Employees ORDER BY 2
		Select City, Count(*) AS [EmployeeId] FROM Employees Group By City ORDER BY 2

Question 31

Compare and contrast the following two queries. How do they differ? How are they similar.

-----Query1----- SELECT OrderID, CustomerID, OrderDate, EmployeeID, ShipVia, Freight, ShipName FROM Orders WHERE OrderID in (SELECT MAX(OrderID) FROM Orders GROUP BY CustomerID) ORDER BY OrderID

-----Query2----- SELECT OrderID, o.CustomerID, OrderDate, EmployeeID, ShipVia, Freight, ShipName FROM Orders o JOIN (SELECT MAX(OrderID) as MaxOrder, CustomerID FROM orders GROUP BY CustomerID) m ON o.CustomeriD = m.CustomerID WHERE OrderID = MaxOrder ORDER BY OrderID

Pick all choices that apply and are true.

		Query2 would be a lot harder to maintain if the code needed to be changed.
		The end result of Query1 and Query2 are the same in that both return the same data and number of rows.
		The second method is better, because with larger sets of data it would be a more efficient process.
		Query1 is more efficient than Query2
		Both queries have the same performance level.
		The difference is that the second query displays many more rows than there other one which makes it harder to decipher information.
		They don't actually return the same results and are really two distinct queries.
		Query1 uses a WHERE clause to search for the latest OrderID, while Query2 uses a join to a subquery.

Use Query01 from above.

The 2^rd 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.

True

False

Question 41

Choose the query that best answers the following problem statement.

Display those distinct orders where the quantity of each item on an order is less than10% of the average of quantity of items bought.

		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity) * .1 from [Order Details] where OD.ProductID = ProductID )--End Inner Select
		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity * .1) from [Order Details] where OD.ProductID = ProductID )--End Inner Select
		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity * .1) from [Order Details] where OD.ProductID = OrderID
		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity) * .1 from [Order Details] )--End Inner Select

Question 42

In order for the following query to accept more than one value from the subquery what must be changed?

Select Country , CompanyName , ContactName , ContactTitle , Phone From Customers Where Country = (Select Top 10 country From Customers C Join Orders O on C.CustomerId = O.CustomerID Group By country Order By count(*) )

		The equal sign in the 'Where Country =' phrase must be changed to IN.
		The equal sign in the 'Where Country =' phrase must be changed to ALL.
		The equal sign in the 'Where Country =' phrase must be changed to EXISTS.
		The equal sign in the 'Where Country =' phrase must be changed to ANY.