Question

Which query lists all products with a total quantity sold less than the average quantity sold?

Select one:

a.
SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) < AVG(LINE_UNITS);

b.
SELECT P_CODE, AVG(LINE_UNITS) AS TOTALUNITS
FROM LINE
GROUP BY P_CODE
HAVING AVG(LINE_UNITS) < (SELECT AVG(LINE_UNITS) FROM LINE);

c.
SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) < (SELECT AVG(LINE_UNITS) FROM LINE);

d.
SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT SUM(LINE_UNITS) FROM LINE);

Which query returns the number of different vendors in the PRODUCT table?

Select one:

a.
SELECT DISTINCT COUNT(V_CODE)

FROM PRODUCT;

b.
SELECT COUNT(V_CODE)

FROM PRODUCT;

c.
SELECT COUNT(DISTINCT V_CODE)

FROM PRODUCT;

d.
SELECT DISTINCT(V_CODE)

FROM PRODUCT;

Which query returns the number of products in the inventory supplied by each vendor and limit the listing to products whose prices average greater than equal $100?

Select one:

a.
SELECT V_CODE, COUNT(P_CODE) AS NUMPRODS, AVG(P_PRICE) AS AVGPRICE
FROM PRODUCT
WHERE AVG(P_PRICE) >= 100
GROUP BY V_CODE
ORDER BY V_CODE;

b.
SELECT V_CODE, COUNT(P_CODE) AS NUMPRODS
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE) >= 100
ORDER BY V_CODE;

c.
SELECT V_CODE, COUNT(P_CODE) AS NUMPRODS
FROM PRODUCT
WHERE AVG(P_PRICE) >= 100
ORDER BY V_CODE;

d.
SELECT V_CODE, COUNT(P_CODE) AS NUMPRODS, AVG(P_PRICE) AS AVGPRICE
FROM PRODUCT
GROUP BY V_CODE
WHERE AVG(P_PRICE) >= 100
ORDER BY V_CODE;

Aggregate functions ignore nulls when performing calculations, but the GROUP BY clause includes nulls and considers all of the nulls to be the same when forming collections.

Select one:

True

False

Unlike ORDER BY clause, the order of attributes in GROUP BY clause does not matter.

Select one:

True

False

in SQL language

Answer 1

1. OPTION (C)

Explanation: We need to select pcode whose total quanity sold is less than average of quantiy sold. So, first we find average of quantity sold SELECT AVG(LINE_QTY) FROM LINE and then compare it with sum of quantity sold by each pcode using groupby command.

2. OPTION (C)

Explanation: This is proper syntax of using DISTINCT with COUNT().

3. OPTION (B)

Explanation: firstly compute average for each product using GROUP BY and then you need to set limit . Instinct says to use WHERE clause but remember GROUP BY is mostly used with HAVING clause.

4. True

Explanation: All Aggregate functions except COUNT(*) and GROUPBY ignore nulls when performing calculations and groupby considers all of the nulls to be the same when forming collections. which means group by will also return group where all entity is null.

5. True

Explanation: order doesn't matter in GROUP BY CLAUSE.