Question 25 Write a query that returns the employee ID, last name, and the emplo
ID: 3873971 • Letter: Q
Question
Question 25
Write a query that returns the employee ID, last name, and the employee’s 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
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
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.LastNameSELECT [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
Explanation / Answer
Question 25)
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
Question 26)
SELECT Count(*)
FROM Suppliers
Where Region Is Null
Question 27)
SELECT CategoryID, MAX(UnitsOnOrder) AS MaxOnOrder, MAX(UnitsInStock) AS MaxInStock
FROM Products
WHERE ReorderLevel > 20
GROUP BY CategoryID
HAVING MAX(UnitsInStock) > 60;
Question 28)
SELECT MIN(HireDate) AS EarliestHireDate,MAX(HireDate) AS LatestHireDate
FROM Employees
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.