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: All product numbers except for the 5 highest product numbers. correct The 5 highest product numbers. incorrect The 5 lowest product numbers. incorrect All product numbers except for the 5 lowest product numbers. incorrect MC Given the following SQL query:

SELECT SUPNAME, SUPADDRESS, SUPCITY
FROM SUPPLIER R
WHERE NOT EXISTS
  (SELECT *
  FROM PRODUCT P
  WHERE EXISTS
    (SELECT *
    FROM SUPPLIES S
    WHERE R.SUPNR = S.SUPNR
    AND P.PRODNR = S.PRODNR));

This query selects: The supplier name, supplier address and supplier city of all suppliers who cannot supply any products. correct The supplier name, supplier address and supplier city of all suppliers who can at least supply 1 product. incorrect The supplier name, supplier address and supplier city of all suppliers who cannot supply all products. incorrect The supplier name, supplier address and supplier city of all suppliers who can supply all products. incorrect MC Compare the following 2 queries:
  1. SELECT COUNT(DISTINCT SUPNR)
    FROM PURCHASE_ORDER
  2. SELECT COUNT(SUPNR)
    FROM PURCHASE_ORDER
Which of the following statements is correct? Result query 1 is always <= result query 2 because the DISTINCT operator counts only unique SUPNRs. correct Result query 1 is always >= result query 2 because query 1 sums the number of purchase orders per supplier while query 2 sums the number of purchase orders in total. incorrect Result query 1 is sometimes <= and sometimes >= result query 2 because the result depends on the number of suppliers and the number of purchase orders. incorrect Result query 1 is always = result query 2 because PURCHASE_ORDER contains only unique purchase orders. incorrect MC Given following query:

SELECT P1.PRODNR, P1.PRODNAME, S1.SUPNR, S1.PURCHASE_PRICE
FROM PRODUCT P1, SUPPLIES S1
WHERE P1. PRODNR = S1.PRODNR
AND NOT EXISTS
(SELECT *
FROM PRODUCT P2, SUPPLIES S2
WHERE P2.PRODNR = S2.PRODNR
AND P1.PRODNR = P2.PRODNR
AND S1.PURCHASE_PRICE > S2.PURCHASE_PRICE)

Given following statements
  1. For each product the supplier number is retrieved of the supplier who can supply the product for the cheapest prices
  2. For each product, the supplier number is retrieved of the supplier who supplies the product for the highest price.
  3. For each product, exactly one tuple is returned
  4. For each product, more than one tuple can be returned


Which statements are true? Statements 1 and 3 incorrect Statements 2 and 4 incorrect Statements 2 and 3 incorrect Statements 1 and 4 correct 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 that has less than 15 orders. incorrect The query returns the PRODNR and average QUANTITY of each product for which the total ordered quantity is less than 15. correct 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 IS 'WINE' incorrect 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 LIKE "%WINE%" correct 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 = "WINE" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS "%WINE%" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS 'WINE' incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME LIKE "%WINE%" correct MC Given following question: "Retrieve the numbers of all suppliers who can supply product 0832 and 0494"

Which query is CORRECT? SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0832
INTERSECT
SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0494 correct SELECT DISTINC SUPNR
FROM SUPPLIES
WHERE PRODNR IN (0832, 0494) incorrect SELECT UNIQUE SUPNR
FROM SUPPLIES
WHERE PRODNR IN (0832, 0494) incorrect SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0832
UNION ALL
SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0494 incorrect MC Given the following query:

SELECT P.PRODNR, P.PRODNAME, P.AVAILABLE_QUANTITY, SUM(L.QUANTITY) AS ORDERED_QUANTITY
FROM PRODUCT AS P LEFT OUTER JOIN PO_LINE AS L
ON (P.PRODNR=L.PRODNR)
GROUP BY P.PRODNR

Which of the following statements is incorrect? The query retrieves the product number, product name and available quantity of each product thanks to the left outer join. incorrect The query result can never contain NULL values. correct If we remove the GROUP BY statement and P.PRODNR, P.PRODNAME, P.AVAILABLE_QUANTITY from the SELECT statement, the query will result in 1 row containing the total outstanding ordered quantity over all products in column 'ORDERED_QUANTITY'. incorrect The query retrieves for each product the total ordered quantity. 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 = "WINE" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS "%WINE%" incorrect SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME LIKE "%WINE%" correct SELECT SUPNR, SUPNAME
FROM SUPPLIER
WHERE SUPNAME IS 'WINE' incorrect