Page 346 - Information_Practice_Fliipbook_Class11
P. 346

Program 11: Consider the table Schedule given below:

        Table: Schedule

                          +-----------+---------------------------------+----------------+---------------+-------------+
                          | EventId | EName                                 | EDate           | ETime         |Etype        |
                          +-----------+----------------------------------+----------------+---------------+--------------+
                          |     1001 | Appointment with Maple | 2023-11-10 | 11 AM         | Official     |
                          |     1002 | Birthday Rashmi                 | 2023-10-01 | 7 AM           | Personal  l
                          |     1003 | Play - The Hamlet               | 2023-09-30 | 7 PM           | Personal  l
                          |     1004 | Board Meeting                    | 2023-11-18 | 2 PM           | Official    |
                          |     1005 | Flight - Mumbai                  | 2023-07-11 | 10:30 AM   | Official    |
                          |     1006 | Submit Tender                    | 2023-08-03 | 12 AM         | Official    |
                          +-----------+-----------------------------------+-----------------+----------------+-------------+

        Write SQL statements to perform the following tasks:
          (i)  Display the records in ascending order of event date.

         Ans.  mysql> SELECT * FROM Schedule
                  -> ORDER BY EDate;
          (ii)  Display name, date and time of personal events.

         Ans.  mysql> SELECT EName, EDate, ETime
                  -> FROM Schedule
                  -> WHERE Etype = 'Personal';
          (iii)  Change the time of event named 'Birthday Rashmi' to 12 AM

         Ans.  mysql> UPDATE Schedule
                  -> SET ETime = '12 AM'

                  -> WHERE EName = 'Birthday Rashmi';
          (iv)  Add another attribute in the table with following specifications:

               Attribute Name : Remarks
               Data Type : varchar(50)

         Ans.  ALTER TABLE Schedule
              ADD Remarks varchar(50);
          (v)  Add the value of Remark as 'Collect Tickets' for the event id 1005.
         Ans.  UPDATE Schedule

                  -> SET Remarks = 'Collect Tickets'
                  -> WHERE EventId = 1005;
          (vi)  Delete the events that are scheduled before November 2023.

         Ans.  mysql> DELETE FROM Schedule
                  -> WHERE EDate < '2023-11-01';
          (vii)  Remove the attribute Remarks from the table.

         Ans.  ALTER TABLE Schedule
              DROP Remarks;




          332  Touchpad Informatics Practices-XI
   341   342   343   344   345   346   347   348   349   350   351