Page 199 - Informatics_Practices_Fliipbook_Class12
P. 199
Ans. (i) SELECT UPPER(breed) FROM mypet;
(ii) SELECT SUM(price) FROM mypet;
(iii) SELECT AVG(lifespan) FROM mypet;
18. Write the names of SQL functions to perform the following operations: [2022]
(i) Display name of the Month from your date of birth.
(ii) Convert email-id to lowercase.
(iii) Count the number of characters in your name.
Ans. (i) MONTHNAME()
(ii) LOWER()/LCASE()
(iii) LENGTH()
19. Consider the following table PRODUCT: [2022]
Table: Product
PID PNAME PRICE QUANTITY
P1001 Eraser 10.50 5
P1002 Ball Pen 15.00 2
P1003 Gel Pen 25.10 3
P1004 Ruler 5.00 1
Find the output of the following SQL queries
(i) SELECT 10+MOD(QUANTITY,3) FROM PRODUCT WHERE PNAME = "Eraser";
(ii) SELECT ROUND(PRICE,2)*QUANTITY FROM PRODUCT WHERE QUANTITY > 2;
(iii) SELECT UCASE (RIGHT(PNAME,2)) FROM PRODUCT;
Ans. (i) 1
(ii) 52.50
75.30
(iii) ER
EN
EN
ER
20. Consider the table: ITEM [2022]
SNo Itemname Type Price Stockdate
1 Chaises Living 11500.58 2020-02-19
2 Accent Chairs Living 31000.67 2021-02-15
3 Baker Racks Kitchen 25000.623 2019-01-15
4 Sofa Living 7000.3 2020-10-18
5 Nightstand Bedroom NULL 2021-07-23
Write SQL queries for the following:
(i) Display all the records in descending order of Stockdate.
(ii) Display the Type and total number of items of each Type.
(iii) Display the least Price.
(iv) Display the Itemname with their price rounded to 1 decimal place.
Ans. (i) SELECT * FROM item ORDER BY stockdate DESC;
(ii) SELECT type, COUNT(*) FROM item GROUP BY type;
(iii) SELECT MIN(price) FROM item;
(iv) SELECT itemname, ROUND(price,1) FROM item;
Database Query using SQL 185

