Page 196 - Informatics_Practices_Fliipbook_Class12
P. 196
(ii) To convert your email id 'ABC@XYZ.com' to lowercase.
(iii) To remove leading spaces from a string ' my country'.
(iv) To display current date.
6
(v) To display the value of 10 .
OR
Consider a table PRODUCT with the following data :
Table: PRODUCT
SNo Itemname Company Stockdate Price Discount
1 Monitor HP 2021-12-20 15499.739 15
2 Webcam Logitech 2020-02-03 4890.90 5
3 Keyboard Logitech 2022-08-19 1878.985 30
4 Mouse HCL 2021-05-16 1200.00 7
5 Speakers iBall 2021-10-19 NULL 25
Write SQL queries using SQL functions to perform the following operations:
(i) Display the first 3 characters of all Itemnames.
(ii) Display the names of all items whose Stockday is "Monday".
(iii) Display the total price of all the products.
(iv) Display the maximum Price.
(v) Display the average Price of all the products by the company named 'Logitech'.
Ans. (i) SELECT YEAR('2023-05-15');
(ii) SELECT LOWER('ABC@XYZ.COM'); or SELECT LCASE('ABC@XYZ.COM');
(iii) SELECT LTRIM(' my country');
(iv) SELECT DATE(NOW());
(v) SELECT POW(10,6);
OR
(i) SELECT LEFT(itemname,3) FROM product;
(ii) SELECT name FROM product WHERE DAYNAME(stockdate) = 'Monday';
(iii) SELECT SUM(price) FROM product;
(iv) SELECT MAX(price) FROM product;
(v) SELECT AVG(price) FROM product WHERE company = 'Logitech';
12. Consider the following table Schooldata: [2023]
Table: SchoolData
Admno Name Grade Club Marks Gender
20150001 Sargam Singh 12 STEM 86 Male
20140212 Alok Kumar 10 SPACE 75 Male
20090234 Mohit Gaur 11 SPACE 84 Male
20130216 Romil Malik 10 READER 91 Male
20190227 Tanvi Batra 11 STEM 70 Female
20120200 Nomita Ranjan 12 STEM 64 Female
Write SQL queries for the following:
(i) Display average Marks secured by each gender.
(ii) Display the minimum Marks secured by the students of Grade 10.
182 Touchpad Informatics Practices-XII

