Page 329 - Informatics_Practices_Fliipbook_Class12
P. 329

SQL


            Program 23: 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.  SELECT * FROM Schedule


                     ORDER BY EDate;
                 (ii)  Display name, date and time of personal events.
                Ans.  SELECT EName, EDate, ETime


                     FROM Schedule
                     WHERE Etype = 'Personal';
                 (iii)  Change the time of event named 'Birthday Rashmi' to 12 AM

                Ans.  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.  DELETE FROM Schedule


                     WHERE EDate < '2023-11-01';

                                                                                                      Practical  315
   324   325   326   327   328   329   330   331   332   333   334