Page 290 - Information_Practice_Fliipbook_Class11
P. 290

Write the following queries to help Ganesh retrieve the desired records from the table:
              (i)  To display the records in alphabetical order of names of dealers.
              (ii)  To display the name of the dealers along with their turnover.
              (iii)  To display the name and contact number of dealers who are not from Delhi.
              (iv)  To display the records of dealers whose name begins with an ‘R’.
              (v)  To display the average turnover of all dealers.
              (vi)  To display the names of dealers whose contact number is not known.

           2.  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.
           3.  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.

          276  Touchpad Informatics Practices-XI
   285   286   287   288   289   290   291   292   293   294   295