Itcs285 sql quiz

University of Bahrain
College of Information Technology
Department of Computer Science
ITCS285: Database Management Systems
Practical Quiz
Student Name
Student ID
Section
Musherah Moqbel ALi
202002276
3
GRADE
Install the Customer/Orders database by select SQL Work Shop => Utilities =>
Sample Dataset. Write SQL statements to the following queries.
1. Update the UNIT_PRICE to be decreased by 20% for PRODUCT_ID 9.
2. Retrieve customer FULL_NAME which full name (starts with uppercase letter N
and end with lowercase letter i) or (starts with uppercase letter K and end with
lowercase letter d) in ascending order of FULL_NAME.
3. Retrieve the PRODUCT_ID and PRODUCT_NAME of all the products with
LINE_ITEM_ID 1 and unit price 14.34 or 10.33 (use join).
4. Retrieve the PRODUCT_NAME that product UNIT_PRICE less than the 10
(use subquery).
5. Retrieve PRODUCT_NAME for the second maximum product UNIT_PRICE
(use subquery).
1. SQL code.
UPDATE PRODUCTS
SET UNIT_PRICE = UNIT_PRICE-(UNIT_PRICE*0.2)
WHERE PRODUCT_ID = 9;
Capture SQL code and result.
2. SQL code.
SELECT FULL_NAME FROM CUSTOMERS
WHERE FULL_NAME LIKE ‘N%i’ OR FULL_NAME LIKE ‘K%d’
ORDER BY FULL_NAME ASC;
Capture SQL code and result.
3. SQL code.
SELECT S.PRODUCT_ID , S.PRODUCT_NAME
FROM PRODUCTS S, ORDER_ITEMS R
WHERE S.PRODUCT_ID = R.PRODUCT_ID
AND R.LINE_ITEM_ID = 1 AND (S.UNIT_PRICE = 14.34 OR S.UNIT_PRICE =
10.33);
Capture SQL code and result.
4. SQL code.
SELECT PRODUCT_NAME
FROM PRODUCTS
WHERE UNIT_PRICE = ANY (SELECT UNIT_PRICE
FROM PRODUCTS
WHERE UNIT_PRICE