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
   194   195   196   197   198   199   200   201   202   203   204