Page 297 - Information_Practice_Fliipbook_Class11
P. 297

20.  Consider the table  MOBILE that stores information about the company manufacturing a mobile, its names, price, and
                  launch date. Thereafter answer the questions that follow:
                                                          Table: MOBILE
                                         +-------+-----------+---------+---------+-------------+
                                         | M_Id  | M_Company | M_Name  | M_Price | Launch_Date |
                                         +-------+-----------+---------+---------+-------------+
                                         | MB001 | Samsung   | Galaxy  |    4500 | 2013-02-12  |
                                         | MB003 | Nokia     | N1100   |    2250 | 2011-04-15  |
                                         | MB004 | Micromax  | Unite3  |    4500 | 2016-10-17  |
                                         | MB005 | Sony      | XperiaM |    7500 | 2017-11-20  |
                                         | MB006 | Oppo      | SefieEx  |    8500 | 2010-08-21  |
                                         +-------+-----------+---------+---------+-------------+
                  (i)  Write SQL queries for the following:
                     a.  To insert a new tuple in the MOBILE table whose M_Price is not yet known.
                     b.  Display the mobile name and name of the company of the mobile phones whose price is greater than 5000.
                     c.  List the name of the mobile phones along with their price that were launched in the year 2017.
                     d.  Display M_Company, M_Name and M_Price in descending order of their launch date.
                     e.  List the details of a mobile whose name starts with "S" or ends with "a".
                     f.  Display the names of the mobile companies having prices between 3000 and 5000
                  (ii)  What will be the output produced on the execution of the following SQL queries:
                     a. SELECT MAX(Launch_Date), MIN(Launch_Date) FROM MOBILE;
                     b. SELECT AVG(M_Price) FROM MOBILE;
                     c. SELECT M_Name, Launch_Date FROM MOBILE
                       WHERE M_Company=’Nokia’ OR M_Price IS NULL;
              21.  Consider the table TRAINS and answer the questions that follow:
                                                          Table: TRAINS
                          +-------+----------------------+--------------------+--------------------+--------------+
                          | TNO   | TName                | Start              | End                | Journey_Time |
                          +-------+----------------------+--------------------+--------------------+--------------+
                          |  1651 | Pune Hbj Special     | Pune Junction      | Habibganj          |            6 |
                          | 11096 | Ahimsa Express       | Pune Junction      | Ahmedabad Junction |           12 |
                          | 12002 | Bhopal Shatabdi      | New Delhi          | Habibganj          |           28 |
                          | 12015 | Ajmer Shatabdi       | New Delhi          | Ajmer Junction     |            4 |
                          | 12030 | Swarna Shatabdi      | Amritsar Junction  | New Delhi          |            6 |
                          | 12314 | Sealdah Rajdhani     | New Delhi          | Sealdah            |           37 |
                          | 12417 | Prayag Raj Express   | Allahabad Junction | New Delhi          |           14 |
                          | 12451 | Shram Shakti Express | Kanpur Central     | New Delhi          |            5 |
                          | 12498 | Shan-e-Punjab        | Amritsar Junction  | New Delhi          |            6 |
                          | 13005 | Amritsar Mail        | Howrah Junction    | Amritsar Junction  |           40 |
                          | 14673 | Shaheed Express      | Jaynagar           | Amritsar Junction  |           36 |
                          +-------+----------------------+--------------------+--------------------+--------------+
                  (i)  Write SQL queries for the following:
                     a.  To display details of all trains which start from Pune Junction.
                     b.  To display details of trains that have duration of more than 15 hours.
                     c.  To display the names of trains that end with the word "Shatabdi".
                     d.  To delete the record of trains that start from Jaynagar.
                     e.  To change the duration of Swarna Shatabdi to 7 hours.
                     f.  To display the count of trains that end at New Delhi.
                  (ii)  Write the output for the following SQL queries:
                     a.  SELECT START, COUNT(*) FROM TRAINS

                       GROUP BY START
                       HAVING COUNT(*)>1;


                                                                Database Concepts and the Structured Query Language  283
   292   293   294   295   296   297   298   299   300   301   302