Page 195 - Informatics_Practices_Fliipbook_Class12
P. 195

9.  Consider the table Patient given below and write SQL commands.                              [2023]
                                                          Table: Patient
                     Patientid       Name           City         Phone        Dateofadm      Department
                  1000001        Ritvik Garg   Delhi          68476213      2021-12-10    Surgery
                  1000002        Rahil Arora   Mumbai         36546321      2022-01-08    Medicine
                  1000003        Mahek Bhatt   Delhi          68421879      2022-02-02    Cardiology
                  1000004        Soumik Rao    Delhi          26543266      2022-01-11    Medicine
                  1000005        Suresh Sood   Bangalore      65432442      2021-03-09    Surgery
                  (i)   Display the details of all patients who were admitted in January.
                  (ii)   Count the total number of patients from Delhi.
                  (iii)  Display the last 2 digits of the Patientid of all patients from Surgery Department.
             Ans.  (i)  SELECT * FROM patient WHERE MONTHNAME(dateofadm) = 'January';
                  (ii)   SELECT COUNT(*) FROM patient WHERE city = 'Delhi'; or SELECT city, count(*) FROM
                     patient
                     GROUP BY city HAVING city='Delhi';
                  (iii)   SELECT RIGHT(patientid,2) FROM patient WHERE department = 'Surgery'; or SELECT
                     MID(patientid,-2) FROM patient WHERE department = 'Surgery';
              10.  Write the output (i-iii) for the following SQL commands.                                    [2023]
                                           Table: FASHION
                          ID          Product        Price          Qty
                         F01      Kajal_              970            10
                         F02      Foundation         2100            15
                         F03      Night Cream        1700            20
                         F04      Day Cream          1400            10
                         F05      Shampoo            1200            25
                         F06      Lipstick            850            32

                  (i)   SELECT COUNT(Product) FROM FASHION;
                  (ii)   SELECT SUM (Price*Qty) FROM FASHION WHERE Product="Night Cream";
                  (iii)  SELECT LEFT (Product, 4) FROM FASHION WHERE Price>1500;
             Ans.  (i)  COUNT(Product)
                     6
                  (ii)  SUM(price*qty)
                     34000
                  (iii)  LEFT(PRODUCT,4)
                     Foun
                     Nigh
                                                               OR

                  (i)  SUBSTR("CLIMATE CHANGE", 4,4)
                     MATE
                  (ii)  UCASE(RIGHT("Pollution" , 3))
                     ION
                  (iii)  SELECT LENGTH ("HAPPY")+3
                     8
              11.  Write the SQL queries which will perform the following operations:                          [2023]
                  (i)  To display the year from your Date of Admission which is '2023-05-15'.


                                                                                        Database Query using SQL  181
   190   191   192   193   194   195   196   197   198   199   200