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 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 supply all products. incorrect 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 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 The following table with purchase orders is created:

CREATE TABEL PURCHASE_ORDER (
  PONR CHAR(7) NOT NULL PRIMARY KEY,
  PODATE DATE,
  SUPNR CHAR(4) NOT NULL,
  FOREIGN KEY (SUPNR) REFERENCES SUPPLIER SUPNR
  ON DELETE CASCADE ON UPDATE CASCADE);

What happens upon deletion of a supplier? The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER. incorrect All purchase order records tied to that supplier are also deleted. correct The SUPNR of this supplier is only deleted in SUPPLIER. incorrect The SUPNR of this supplier is deleted in PURCHASE_ORDER. incorrect 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 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 The number and name of all products that can be supplied by more than one supplier correct MC Given following query:

SELECT R1.SUPNAME, R1.SUPNR, COUNT(*)
FROM PURCHASE_ORDER PO1, SUPPLIER R1
WHERE PO1.SUPNR = R1.SUPNR
GROUP BY R1.SUPNR
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM PURCHASE_ORDER PO2, SUPPLIER R2
WHERE PO2.SUPNR = R2.SUPNR
GROUP BY R2.SUPNR)

The query retrieves: The name, number and total outstanding orders of the supplier with the most outstanding orders correct The name, number and total outstanding orders of the supplier with the least outstanding orders incorrect The name, number and total outstanding orders of all suppliers that have outstanding orders incorrect The name, number and total outstanding orders of all suppliers that have outstanding orders, except for the supplier(s) with the least outstanding orders 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 name and total number of outstanding orders of all suppliers that have at least one outstanding order 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 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 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 name and total number of outstanding orders of all suppliers that have at least one outstanding order incorrect 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 supplier name and order date of each of his/her outstanding orders incorrect The name and total number of outstanding orders of all suppliers correct 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
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

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