Page 193 - Informatics_Practices_Fliipbook_Class12
P. 193

Case Based Questions


               1.  Sarfaraz runs a stationary shop. He wants to store the details of stationary so that he can retrieve the desired data as and
                  when required. The database designer has created the following table:
                                                         Table: Stationary


                                SCODE      SNAME               PRICE       QTY       BRAND
                                S001       Gel Pen             55          10        Reynolds
                                S002       Permanent Marker    60          5         Luxor
                                S003       White Board Marker  45          10        Luxor
                                S004       Paper Ream          310         4         Sando

                                S006       Pencil                          15        Natraj
                                S009       Colouring Book      70          3         Artist
                                S101       Eraser              15          5         Natraj
                                S102       Sharpener           6           12        Natraj

                  Sarfaraz wants to retrieve some data from the table. Help him by writing the queries for the following:
                  (i)  To display the structure of the table.
                  (ii)  To display the records whose quantity is less than 5.
                  (iii)  To display the name and price of those stationary items whose names end with 'Marker'.
                  (iv)  To display the price of the most expensive item.
                  (v)  To display the average price of all items.
                  (vi)  To display the brand name and count of items of each brand.
                  (vii)  To display the total number of records in a table.
                  (viii) To display the records in descending order of quantity.
                  (ix)  To display the records whose price is not known.
                  (x)  To display the name of each brand only once.
               2.  Tanmay is very fond of watches, just like his father. His father has a very good collection of watches. As a student of class
                  12, he learnt to create tables and perform queries in a MySQL database. He has created the table, Watches as given below:
                                                          Table: Watches

                  WatchId         WName          Price           Type            Brand          YOP
                  T001            Apple iWatch   56000           Smart watch     Apple          2020-12-12
                  T002            Titan Raga     25673           Designer        Titan          2015-04-15
                  T003            NeoSplash      34908           Sports Watch    Neo Sports     2018-11-09

                  T005            G-Shock        13892           Sports Watch    Casio          2021-03-18
                  T006            Max Pro        15673           Designer        Maxima         2014-06-03

                  Write the queries for the following:
                  (i)  To display the name and price of each watch.
                  (ii)  To change the price of Apple iWatch to 59350.
                  (iii)  To display the records of all Sports watches.
                  (iv)  To display the names of all brands, without repetition.
                  (v)  To display the count of designer watches.
                  (vi)  To display the count of watches of each brand.
                  (vii)  To display the name and brand of watches that have been purchased in the year 2020.

                                                                                        Database Query using SQL  179
   188   189   190   191   192   193   194   195   196   197   198