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

