Question & Answer: Write query that returns the count of rows where the region is null from the suppliers table……

Question 26

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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. Select all correct Need help ASAP please! Fast thumbs up

Expert Answer

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

Ans:
SELECT Count(*)
FROM Suppliers
Where Region Is Null

Question 27
1.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.
Ans:

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

Question 28
1. 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.

Ans:
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 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

Question 29
1. Retrieve the number of employees in each city in which there are at least 2 employees.
Ans:

SELECT City, COUNT(*) as EmployeesInCity
FROM [Employees]
Group By City
Having Count(EmployeeID) > 2

Still stressed from student homework?

Get quality assistance from academic writers!

Order now