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
   191   192   193   194   195   196   197   198   199   200   201