Question 25

Write a query that returns the employee ID, last name, and the employees region ID and region description.

Choose the best query solution.

		SELECT Distinct Employees.EmployeeID , Employees.LastName , Region.RegionID , Region.RegionDescription FROM Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID INNER JOIN Region ON Territories.RegionID = Region.RegionID ORDER BY Employees.LastName
		SELECT [Employees].EmployeeID, [Employees].Region, Region.RegionDescription FROM Employees, Region
		SELECT Employees.EmployeeID, Employees.LastName, Region.RegionID, Region.RegionDescription FROM Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN Region ON Territories.RegionID = Region.RegionID ORDER BY Employees.LastName
		SELECT Employees.EmployeeID , Employees.LastName , Region.RegionID , Region.RegionDescription FROM Employees CROSS JOIN Region

Question 26

Write query that returns the count of rows where the region is null from the suppliers table.

		SELECT Count(Region) FROM Suppliers Having Count(*) Is Null
		SELECT Count(*) FROM Suppliers Where Region Is Null
		SELECT Count(Region) FROM Suppliers Where Region Is Null
		SELECT Count(Region) FROM Suppliers Having Count(Region) Is Null
		SELECT Region FROM Suppliers Where Region Is Null

Question 27

Write a query that shows the max units on order and the max units in stock from the products table, grouped by category id, having a max units in stock >60.

		SELECT Categories.CategoryID , MAX (UnitsOnOrder) AS 'Units on Order' , MAX (UnitsInStock) AS 'Units In Stock' FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE ReorderLevel >20 GROUP BY Categories.CategoryID HAVING MAX (UnitsInStock)>60
		SELECT CategoryID, MAX(UnitsOnOrder) AS MaxOnOrder, MAX(UnitsInStock) AS MaxInStock FROM Products WHERE UnitsInStock > 20 GROUP BY CategoryID HAVING MAX(UnitsInStock) > 60
		SELECT CategoryID, MAX(UnitsOnOrder) AS MaxOnOrder, MAX(UnitsInStock) AS MaxInStock FROM Products WHERE ReorderLevel > 20 GROUP BY CategoryID HAVING MAX(UnitsInStock) > 60;
		SELECT CategoryID, MAX(UnitsOnOrder) AS MaxOnOrder, max(UnitsOnOrder) AS MaxInStock From Products group by CategoryID having MAX(UnitsInStock) > 60 and max(reorderlevel) >20
		Select CategoryID, MAX(UnitsOnOrder) AS MaxOnOrder, Max(UnitsInStock) AS MaxInStock From products Where UnitsInStock>60 and reorderlevel>20 Group by categoryid

Question 28

Write a query that finds the Earliest and Latest Dates of Hire for the employees. No grouping here.

Which query is the least efficient i.e. has the highest Query Cost relative to the batch. You will need to look at the queries together and use the Display Estimated Execution Plan button.

		SELECT TOP 1 HireDate As [Earliest Hire Date], (Select Top 1 HireDate From Employees Order BY Hiredate DESC) AS [Latest Hire Date] FROM Employees Order By HireDate
		SELECT 'Oldest' AS Definition, Min(HireDate) AS HireDate FROM employees UNION SELECT 'MostRecent' AS Definition, MAX(HireDate) AS HireDate FROM employees
		SELECT CONVERT(char(10),MIN(HireDate),121) AS EarliestHireDate, CONVERT(char(10), MAX(Hiredate),121) AS LatestHireDate FROM Employees
		SELECT MIN(HireDate) AS EarliestHireDate,MAX(HireDate) AS LatestHireDate FROM Employees

Question 29

Retrieve the number of employees in each city in which there are at least 2 employees.

		SELECT City, COUNT(*) as EmployeesInCity FROM Employees GROUP BY City HAVING COUNT(City) >=2
		Select City, COUNT(*) as EmployeesInCity From Employees Where employeeid<=2 Group by employeeId
		Select City, COUNT(*) as EmployeesInCity From Employees Group by City Having MIN(City)>2
		SELECT City, COUNT(*) as EmployeesInCity FROM [Employees] Group By City Having Count(EmployeeID) > 2
		SELECT City, COUNT(*) as EmployeesInCity FROM Employees Group by City HAVING SUM(NumEmp) > 2
		Select City, COUNT(*) as EmployeesInCity FROM Employees Group By City Having Count(*) <= 2
		SELECT City, COUNT(*) as EmployeesInCity FROM Employees HAVING COUNT(EmployeeID > = 2)

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. Need help pls! Fast thumbs up! TY