Question 30 Display different cities along with the number of Northwind employee

Question

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

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

Explanation / Answer

Hi,

Below is the answer-

Ans 30 -
Select City, Count(*) AS [EmployeeId]
FROM Employees
Group By City
ORDER BY 2

Ans 31 -
Query1 uses a WHERE clause to search for the latest OrderID, while Query2 uses a join to a subquery.
Query1 is more efficient than Query2
The end result of Query1 and Query2 are the same in that both return the same data and number of rows.
Query2 would be a lot harder to maintain if the code needed to be changed.

Ans TRUE

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

Ans 42 -
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 EXISTS

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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