102 lines
5.3 KiB
Plaintext
102 lines
5.3 KiB
Plaintext
# Task 1
|
|
> delimiter $
|
|
> source ./add_booking.sql
|
|
> delimiter ;
|
|
> call AddBooking(20, "2024-11-19 19:00:00", 3, 1, 2)
|
|
+-------------------+
|
|
| Confirmation |
|
|
+-------------------+
|
|
| New booking added |
|
|
+-------------------+
|
|
> select * from `Bookings` where `BookingID` = 20
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| 20 | 2024-11-19 19:00:00 | 3 | 1 | 2 |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
|
|
# Task 2
|
|
> delimiter $
|
|
> source ./update_booking.sql
|
|
> delimiter ;
|
|
> call UpdateBooking(20, date_sub((select Reserved from `Bookings` where `BookingID` = 20), interval 1 day))
|
|
+--------------------+
|
|
| Confirmation |
|
|
+--------------------+
|
|
| Booking 20 updated |
|
|
+--------------------+
|
|
> select * from `Bookings` where `BookingID` = 20;
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| 20 | 2024-11-18 19:00:00 | 3 | 1 | 2 |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
> call UpdateBooking(21, "2024-11-18 15:00:00")
|
|
+---------------------------------------------+
|
|
| Confirmation |
|
|
+---------------------------------------------+
|
|
| Booking 21 wasn't updated - missing booking |
|
|
+---------------------------------------------+
|
|
> select * from `Bookings` where `BookingID` = 21
|
|
+-----------+----------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+----------+-------------+------------+------------+
|
|
+-----------+----------+-------------+------------+------------+
|
|
|
|
# Task 3
|
|
> delimiter $
|
|
> source ./cancel_booking.sql
|
|
> delimiter ;
|
|
> call CancelBooking(20)
|
|
+----------------------+
|
|
| Confirmation |
|
|
+----------------------+
|
|
| Booking 20 cancelled |
|
|
+----------------------+
|
|
> select * from `Bookings`
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| 1 | 2024-11-14 18:00:00 | 1 | 3 | 1 |
|
|
| 2 | 2024-11-14 19:30:00 | 2 | 2 | 1 |
|
|
| 3 | 2024-11-14 18:45:00 | 3 | 4 | 1 |
|
|
| 4 | 2024-11-14 19:15:00 | 4 | 1 | 2 |
|
|
| 5 | 2024-11-14 18:30:00 | 5 | 6 | 2 |
|
|
| 6 | 2024-11-14 19:00:00 | 6 | 5 | 2 |
|
|
| 7 | 2024-11-15 18:15:00 | 1 | 7 | 5 |
|
|
| 8 | 2024-11-15 19:45:00 | 2 | 8 | 5 |
|
|
| 9 | 2024-11-15 18:30:00 | 3 | 9 | 2 |
|
|
| 10 | 2024-11-15 19:00:00 | 4 | 10 | 5 |
|
|
| 11 | 2022-10-14 18:00:00 | 5 | 1 | 1 |
|
|
| 12 | 2022-11-12 19:30:00 | 3 | 3 | 1 |
|
|
| 13 | 2022-10-11 18:45:00 | 2 | 2 | 1 |
|
|
| 14 | 2022-10-13 19:15:00 | 2 | 1 | 1 |
|
|
| 15 | 2022-11-12 17:15:00 | 3 | 4 | 1 |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
> call CancelBooking(21)
|
|
+--------------------------------------------+
|
|
| Confirmation |
|
|
+--------------------------------------------+
|
|
| Booking 21 not cancelled - missing booking |
|
|
+--------------------------------------------+
|
|
> select * from `Bookings`
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| 1 | 2024-11-14 18:00:00 | 1 | 3 | 1 |
|
|
| 2 | 2024-11-14 19:30:00 | 2 | 2 | 1 |
|
|
| 3 | 2024-11-14 18:45:00 | 3 | 4 | 1 |
|
|
| 4 | 2024-11-14 19:15:00 | 4 | 1 | 2 |
|
|
| 5 | 2024-11-14 18:30:00 | 5 | 6 | 2 |
|
|
| 6 | 2024-11-14 19:00:00 | 6 | 5 | 2 |
|
|
| 7 | 2024-11-15 18:15:00 | 1 | 7 | 5 |
|
|
| 8 | 2024-11-15 19:45:00 | 2 | 8 | 5 |
|
|
| 9 | 2024-11-15 18:30:00 | 3 | 9 | 2 |
|
|
| 10 | 2024-11-15 19:00:00 | 4 | 10 | 5 |
|
|
| 11 | 2022-10-14 18:00:00 | 5 | 1 | 1 |
|
|
| 12 | 2022-11-12 19:30:00 | 3 | 3 | 1 |
|
|
| 13 | 2022-10-11 18:45:00 | 2 | 2 | 1 |
|
|
| 14 | 2022-10-13 19:15:00 | 2 | 1 | 1 |
|
|
| 15 | 2022-11-12 17:15:00 | 3 | 4 | 1 |
|
|
+-----------+---------------------+-------------+------------+------------+
|