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 SUPNR
FROM SUPPLIES
WHERE PRODNR = 0832
UNION ALL
SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0494 incorrect SELECT DISTINC SUPNR
FROM SUPPLIES
WHERE PRODNR IN (0832, 0494) incorrect SELECT UNIQUE SUPNR
FROM SUPPLIES
WHERE PRODNR IN (0832, 0494) 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 can at least supply 1 product. incorrect The supplier name, supplier address and supplier city of all suppliers who can supply all products. 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 cannot supply any products. correct 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

For each product the supplier number is retrieved of the supplier who can supply the product for the cheapest prices
For each product, the supplier number is retrieved of the supplier who supplies the product for the highest price.
For each product, exactly one tuple is returned
For each product, more than one tuple can be returned

Which statements are true? Statements 1 and 3 incorrect Statements 1 and 4 correct Statements 2 and 3 incorrect Statements 2 and 4 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) >= 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 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 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) 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 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) >= 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 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) >= 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) >= 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 = PONR
GROUP BY POL2.PONR) incorrect MC We want to retrieve the available quantity of each ordered product of supplier Ad Fundum. Which of the following queries is correct? SELECT PRODNR, AVAILABLE_QUANTITY
FROM PRODUCT
WHERE PRODNR IN
(SELECT PRODNR
FROM PO_LINE
WHERE PONR IN
(SELECT PONR
FROM PURCHASE_ORDER
WHERE SUPNRIN
(SELECT SUPNR
FROM SUPPLIER
WHERE SUPNAME='Ad Fundum'))) correct SELECT PRODNR, AVAILABLE_QUANTITY
FROM PRODUCT
WHERE PRODNR =
(SELECT PRODNR
FROM PO_LINE
WHERE PONR =
(SELECT PONR
FROM PURCHASE_ORDER
WHERE SUPNR=
(SELECT SUPNR
FROM SUPPLIER
WHERE SUPNAME='Ad Fundum'))) incorrect SELECT PRODNR, AVAILABLE_QUANTITY
FROM PRODUCT
WHERE PRODNR IN
(SELECT SUPNR
FROM SUPPLIER
WHERE SUPNAME='Ad Fundum') incorrect SELECT PRODNR, AVAILABLE_QUANTITY
FROM PRODUCT
WHERE PRODNR IN
(SELECT PRODNR
FROM PO_LINE AND
SUPNR IN
(SELECT SUPNR
FROM SUPPLIER
WHERE SUPNAME='Ad Fundum') incorrect MC Given the following query:

SELECT PRODNAME FROM PRODUCT
WHERE PRODNR IN
(SELECT PRODNR FROM SUPPLIES
WHERE SUPNR IN
(SELECT SUPNR FROM SUPPLIER
WHERE SUPCITY = 'New York'))
AND PRODNR IN
(SELECT PRODNR FROM SUPPLIES
WHERE SUPNR IN
(SELECT SUPNR FROM SUPPLIER
WHERE SUPCITY = 'Washington'))

What is the result? The query retrieves the product name of each product along with all possible supplier cities. incorrect The query incorrectly combines every product name and supplier city. incorrect The query retrieves the product name of each product that has both a supplier in New York and a supplier in Washington. correct The query retrieves the product name of each product that has a supplier in New York or Washington. incorrect MC Given the following query:

SELECT *
FROM PRODUCT
WHERE PRODTYPE='red'
ORDER BY AVAILABLE_QUANTITY DESC, PRODNAME

Which of the following answers is correct?

PRODNR	PRODNAME	PRODTYPE	AVAILABLE_QUANTITY
474	Chateau De La Tour, Clos-Vougeot, Grand cru, 2008	red	147
885	Cheateau Margaux, Grand Cru Classé, 1956	red	147
347	Chateau Corbin-Despagne, Saint Emilion, 2005	red	145
832	Conde de Hervias, Rioja, 2004	red	121
...	...	...	...
331	Chateau La Commanderie, Lalande-de-Pomerol, 1998	red	3
219	Marques de Caceres, Rioja Crianza, 2010	red	0

correct

PRODNR	PRODNAME	PRODTYPE	AVAILABLE_QUANTITY
885	Chateau Margaux, Grand Cru Classé, 1956	red	147
474	Chateau De La Tour, Clos-Vougeot, Grand cru, 2008	red	147
347	Chateau Corbin-Despagne, Saint Emilion, 2005	red	145
832	Conde de Hervias, Rioja, 2004	red	121
...	...	...	...
331	Chateau La Commanderie, Lalande-de-Pomerol, 1998	red	3
219	Marques de Caceres, Rioja Crianza, 2010	red	0

incorrect

PRODNR	PRODNAME	PRODTYPE	AVAILABLE_QUANTITY
219	Marques de Caceres, Rioja Crianza, 2010	red	0
331	Chateau La Commanderie, Lalande-de-Pomerol, 1998	red	3
185	Chateau Petrus, 1975	red	5
523	Chateau Andron Blanquet, Saint Estephe, 1979	red	13
...	...	...	...
474	Chateau De La Tour, clos-Vougeot, Grand cru, 2008	red	147
885	Chateau Margaux, Grand Cru Classé, 1956	red	147

incorrect

PRODNR	PRODNAME	PRODTYPE	AVAILABLE_QUANTITY
795	Casa Silva, Los Lingues, Carmenere, 2012	red	105
523	Chateau Andron Blanquet, Saint Estephe, 1979	red	13
977	Chateau Batailley, Grand Cru Classé, 1975	red	21
...	...	...	...
847	Seresin, Merlot, 1999	red	41
345	Vascosassetti, Brunello di Montalcino, 2004	red	64

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