MC Given following query:
SELECT DISTINCT P1.PRODNR, P1.PRODNAME
FROM PRODUCT P1, SUPPLIES S1
WHERE P1.PRODNR=S1.PRODNR AND
1 <= (SELECT COUNT(*) FROM SUPPLIES S2
WHERE S1.SUPNR <> S2.SUPNR AND P1.PRODNR=S2.PRODNR)
ORDER BY PRODNR
The query retrieves: The number and name of all products that can be supplied by more than one supplier correct The number and name of all products that cannot be supplied by any supplier incorrect The number and name of all products that only can be supplied by one supplier incorrect The number and name of all products that can be supplied by all suppliers incorrect
MC Which of the following queries selects the name of the supplier, corresponding order number and total ordered quantity of the order that has the maximum total quantity ordered. SELECT R1.SUPNAME, POL1.PONR, SUM(POL1.QUANTITY)
FROM SUPPLIER R1, PURCHASE_ORDER PO1, PO_LINE POL1
WHERE R1.SUPNR = PO1. SUPNR AND PO1. PONR = POL1.PONR
GROUP BY POL1. PONR
HAVING SUM(POL1.QUANTITY) >= ALL
(SELECT SUM(POL2.QUANTITY)
FROM SUPPLIER R2, PURCHASE_ORDER PO2, PO_LINE POL2
WHERE R2.SUPNR = PO2.SUPNR AND PO2.PONR = PONR
GROUP BY POL2.PONR) correct SELECT R1.SUPNAME, POL1.PONR, SUM(POL1.QUANTITY)
FROM SUPPLIER R1, PURCHASE_ORDER PO1, PO_LINE POL1
WHERE R1.SUPNR = PO1. SUPNR AND PO1. PONR = POL1.PONR
GROUP BY POL1. PONR
HAVING SUM(POL1.QUANTITY) <= ALL
(SELECT SUM(POL2.QUANTITY)
FROM SUPPLIER R2, PURCHASE_ORDER PO2, PO_LINE POL2
WHERE R2.SUPNR = PO2.SUPNR AND PO2.PONR = PONR
GROUP BY POL2.PONR) incorrect SELECT R1.SUPNAME, POL1.PONR, SUM(POL1.QUANTITY)
FROM SUPPLIER R1, PURCHASE_ORDER PO1, PO_LINE POL1
WHERE R1.SUPNR = PO1. SUPNR AND PO1. PONR = POL1.PONR
GROUP BY POL1. PONR
HAVING SUM(POL1.QUANTITY) >= ANY
(SELECT SUM(POL2.QUANTITY
FROM SUPPLIER R2, PURCHASE_ORDER PO2, PO_LINE POL2
WHERE R2.SUPNR = PO2.SUPNR AND PO2.PONR = POL2.PONR
GROUP BY POL2.PONR) incorrect SELECT R1.SUPNAME, POL1.PONR, SUM(POL1.QUANTITY)
FROM SUPPLIER R1, PURCHASE_ORDER PO1, PO_LINE POL1
WHERE R1.SUPNR = PO1. SUPNR AND PO1. PONR = POL1.PONR
GROUP BY POL1. PONR
HAVING SUM(POL1.QUANTITY) <= ANY
(SELECT SUM(POL2.QUANTITY)
FROM SUPPLIER R2, PURCHASE_ORDER PO2, PO_LINE POL2
WHERE R2.SUPNR = PO2.SUPNR AND PO2.PONR = PONR
GROUP BY POL2.PONR) incorrect
MC Given the following SQL query:
SELECT P1.PRODNR
FROM PRODUCT P1
WHERE 5 <=
(SELECT COUNT(*)
FROM PRODUCT P2
WHERE P1.PRODNR < P2.PRODNR)
This query selects: The 5 highest product numbers. incorrect The 5 lowest product numbers. incorrect All product numbers except for the 5 highest product numbers. correct All product numbers except for the 5 lowest product numbers. incorrect
MC Given following query:
SELECT R.SUPNAME, (SELECT COUNT(PO.PODATE)
FROM PURCHASE_ORDER PO
WHERE R.SUPNR = PO.SUPNR) AS SUMMARY
FROM SUPPLIER R
The query selects: The supplier name and order date of each of his/her outstanding orders. If a supplier does not have an outstanding order, s/he will be included in the output with a null value for the "SUMMARY" column. incorrect The name and total number of outstanding orders of all suppliers correct The supplier name and order date of each of his/her outstanding orders incorrect The name and total number of outstanding orders of all suppliers that have at least one outstanding order incorrect
MC Given the following query:
SELECT PRODNR, AVG(QUANTITY) AS AVG_QUANTITY
FROM PO_LINE
GROUP BY PRODNR
HAVING SUM(QUANTITY < 15)
What is the result? The query returns the PRODNR and average QUANTITY of each purchase order that has less than 15 orders. incorrect The query returns the PRODNR and average QUANTITY of each purchase order that has less than 15 purchase order lines. incorrect The query returns the PRODNR and average QUANTITY of each product for which the total ordered quantity is less than 15. correct The query returns the PRODNR and average QUANTITY of each product that has less than 15 orders. incorrect
MC We're interested in wine stores. Therefore, we want to retrieve the SUPNR and SUPNAME of each store which contains 'wine' in its store name. Which of the following queries can we use? SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME LIKE "%WINE%" correct SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS "%WINE%" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME = "WINE" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS 'WINE' incorrect
MC Given the following query:
SELECT P.PRODNR, P.PRODNAME
FROM PRODUCT P
WHERE EXISTS
(SELECT *
FROM PO_LINE_POL
WHERE P.PRODNR = POL.PRODNR
GROUP BY POL.PRODNR
HAVING SUM(POL.QUANTITY) > P.AVAILABLE_QUANTITY)
The query retrieves: The name and number of the product with the highest ordered quantity. incorrect The name and number of all products that are ordered and exceed their available quantity. correct The name and number of the product with the lowest ordered quantity. incorrect The name and number of all products that are ordered and do not exceed their available quantity. incorrect
MC Compare the following 2 queries: