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? All purchase order records tied to that supplier are also deleted. correct The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER. incorrect The SUPNR of this supplier is deleted in PURCHASE_ORDER. incorrect The SUPNR of this supplier is only deleted in SUPPLIER. 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
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 We want to retrieve all unique supplier numbers and statuses of suppliers who have at least one outstanding purchase order. Which query is correct? SELECT DISTINCT R.SUPNR, R.SUPSTATUS
FROM SUPPLIER R, PURCHASE_ORDER O incorrect SELECT DISTINCT R.SUPNR, R.SUPSTATUS
FROM SUPPLIER R, PURCHASE_ORDER O
WHERE (R.SUPNR = O.SUPNR) correct SELECT DISTINCT R.SUPNR, R.SUPSTATUS
FROM SUPPLIER R, PURCHASE_ORDER O
WHERE (R.SUPNR = O.PONR) incorrect SELECT R.SUPNR, R.SUPSTATUS
FROM PURCHASE_ORDER R 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 retrieves for each product the total ordered quantity. 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
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 only can be supplied by one supplier incorrect The number and name of all products that cannot be supplied by any supplier incorrect 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 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) >= 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) 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 = PONR
GROUP BY POL2.PONR) 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 cannot supply all products. incorrect 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
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 do not exceed their available 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
MC Given following query:
SELECT CS.CURRENT_STOCK - O.ORDERED AS NEW_STOCK
FROM (SELECT SUM(P.AVAILABLE_QUANTITY) AS CURRENT_STOCK
FROM PRODUCT P) AS CS, (SELECT SUM(POL.QUANTITY) AS ORDERED
FROM PO_LINE POL) AS O
The output of the query represents: A table, summarizing for each product the increase in stock after the ordered products are delivered. incorrect A table, summarizing for each product the decrease in stock after the ordered products are delivered. incorrect A scalar, summarizing the total quantity of products in stock after all the ordered products are delivered. correct A scalar, summarizing the decrease in total available quantity of all products after the ordered products are delivered. incorrect
MC Given following question: "Retrieve the numbers of all suppliers who can supply product 0832 and 0494"
Which query is CORRECT? SELECT DISTINC 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 SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0832
INTERSECT
SELECT SUPNR
FROM SUPPLIES
WHERE PRODNR = 0494 correct SELECT UNIQUE SUPNR
FROM SUPPLIES
WHERE PRODNR IN (0832, 0494) incorrect
MC Given the following view definition and update statement:
CREATE VIEW TOPPRODUCTS(PRODNR,PRODNAME,QUANTITY) AS
SELECT PRODNR, PRODNAME, AVAILABLE_QUANTITY
FROM PRODUCT WHERE AVAILABLE_QUANTITY>100
WITH CHECK OPTION
UPDATE TOPPRODUCTS
SET QUANTITY=80
WHERE PRODNR=0153
What will be the result of this? The update can be successfully made but only the PRODUCT table will be updated. incorrect The update can be successfully made and both the View and PRODUCT table will be updated. incorrect The update will be halted because of the WITH CHECK OPTION. correct The update can be successfully made but only the View will be updated. incorrect
MC Compare the following 2 queries:
- SELECT COUNT(DISTINCT SUPNR)
FROM PURCHASE_ORDER - SELECT COUNT(SUPNR)
FROM PURCHASE_ORDER
Which of the following statements is correct? Result query 1 is always = result query 2 because PURCHASE_ORDER contains only unique purchase orders. incorrect 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
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 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 The query returns the PRODNR and average QUANTITY of each purchase order that has less than 15 orders. 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 that has a supplier in New York or Washington. 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 along with all possible supplier cities. incorrect The query incorrectly combines every product name and supplier city. 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 AND
SUPNR IN
(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
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
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 lowest product numbers. incorrect All product numbers except for the 5 highest product numbers. 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 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 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
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 number and name of all the products that are ordered 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
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 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