Question 26 1. Write query that returns the count of rows where the region is nu

Question

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

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

Explanation / 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

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.