Question 32 1. Choose the best solution. Write a query the calculates the averag

Question

Question 32

1.       Choose the best solution. Write a query the calculates the average unit price for all products in the products table.

Select Avg(UnitPrice)
From Products

Select UnitPrice
From Products
Where UnitPrice = (Select Avg([Order Details].UnitPrice) From [Order Details])

Select Sum(UnitPrice)/Count(*)
From Products

Select UnitPrice as Average
From Products

Select Avg([Order Details].UnitPrice)
From Products, [Order Details]

Question 33

1.       Choose the best solution.

Write a query that returns the count of all the records, the max freight, and employee id for only those freight were the maixmum >= 50. You will need to group on employee id.

Sort on the employee id.

SELECT EmployeeID
    , Count(*) As AllRecords
    , Max(Freight) As MaxFreight
FROM Orders
GROUP BY EmployeeID
HAVING Max(Freight) >= 50
ORDER BY EmployeeID

SELECT EmployeeID
    , Count(*) As AllRecords
    , Max(Freight) As MaxFreight
FROM Orders
HAVING MaxFreight > 50
GROUP BY EmployeeID

SELECT Employees.EmployeeID
    , Count(*) As AllRecords
    , Max(Orders.Freight) As MaxFreight
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.EmployeeID
HAVING Max(Orders.Freight) >= 50

SELECT EmployeeID
    , Count(*) As AllRecords
    , Max(Freight) As MaxFreight
FROM Orders
WHERE Freight >= 50
GROUP BY EmployeeID
HAVING Max(Freight) >= 50
ORDER BY EmployeeID

Question 34

1.       Choose the best solution.

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

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE EmployeeID > 1
GROUP BY City
HAVING COUNT(EmployeeID) > 1

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE EmployeeID > 1
GROUP BY City

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1

SELECT COUNT(*) AS NumEmployees
FROM Employees
HAVING COUNT(EmployeeID) > 1

Question 35

1.       _____ functions are used in conjunction with the HAVING clause when you want to limit the grouped rows returned.

Question 36

1.      The COALESCE(reportsto, 0) statement forces AVG() to count NULL rows as zeroes; thus, forcing every single row to be counted.

True

False

Question 37

1.       NULLs are not eliminated by MIN() and MAX() functions.

True

False

Question 38

1.      If a GROUP BY is not used in the query, then the aggregate functions are applied to the entire set of rows returned.

True

False

10 points   

Question 39

1.      When COUNT references a column name, it does not include duplicate nor nulls.

True

False

  

Question 40

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

Need help ASAP PLEASE! Fast thumbs ups

True

False

Select Avg(UnitPrice)
From Products

Select UnitPrice
From Products
Where UnitPrice = (Select Avg([Order Details].UnitPrice) From [Order Details])

Select Sum(UnitPrice)/Count(*)
From Products

Select UnitPrice as Average
From Products

Select Avg([Order Details].UnitPrice)
From Products, [Order Details]

Explanation / Answer

Ans:

[32]   

SELECT AVG(unitprice)

FROM products;

The above query calculates the total of unit prices and divides it by the number of rows in the products table to give the the average unit price for all products in the products table.

[34]

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE EmployeeID > 1
GROUP BY City

The above query finds out the number of employees in each city in which there are at least 2 employees. The table is grouped by City.

[35]

Group By

Group By functions are used in conjunction with the HAVING clause when you want to limit the grouped rows returned.

[36]

True

The COALESCE(reportsto, 0) statement forces AVG() to count NULL rows as zeroes; forcing every single row to be counted.We will get the same answer by using the SUM() and COUNT() set functions.

[37]

False

NULLs are eliminated by MIN() and MAX() functions. For the data types char, shorter values are right-padded with 'pad characters' before comparing them.

[38]

True

If a GROUP BY is not used in the query, and when an aggregate function is used, the group function displays a single value for the whole table.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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