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 only deleted in SUPPLIER. incorrect All purchase order records tied to that supplier are also deleted. correct The SUPNR of this supplier is deleted in PURCHASE_ORDER. incorrect The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER. 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
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 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 2 and 3 incorrect Statements 1 and 3 incorrect Statements 1 and 4 correct Statements 2 and 4 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: All product numbers except for the 5 highest product numbers. correct All product numbers except for the 5 lowest product numbers. incorrect The 5 highest product numbers. incorrect The 5 lowest product numbers. 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 only deleted in SUPPLIER. incorrect All purchase order records tied to that supplier are also deleted. correct The SUPNR of this supplier is deleted in PURCHASE_ORDER. incorrect The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER. 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 retrieves the product name of each product that has a supplier in New York or Washington. 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 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 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 following query:

SELECT P.PRODNR, P.PRODNAME
FROM PRODUCT P
EXCEPT
SELECT POL.PRODNR
FROM PO_LINE POL

The query retrieves: The number and name of all the products with no outstanding order incorrect The query will not execute because both queries do not select the same columns correct The query will not execute because both queries do not select the same rows incorrect The number and name of all the products that are ordered incorrect MC Given following query:

SELECT P.PRODNR, P.PRODNAME
FROM PRODUCT P
EXCEPT
SELECT POL.PRODNR
FROM PO_LINE POL

The query retrieves: The number and name of all the products that are ordered incorrect The query will not execute because both queries do not select the same rows incorrect The number and name of all the products with no outstanding order incorrect The query will not execute because both queries do not select the same columns correct