98 lines
4.8 KiB
Plaintext
98 lines
4.8 KiB
Plaintext
# Task 1
|
|
> source ./example_bookings.sql
|
|
> select * from `Bookings` where `BookingID` between 11 and 14
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| BookingID | Reserved | TableNumber | CustomerID | EmployeeID |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
| 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 |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
|
|
# Task 2
|
|
> delimiter $
|
|
> source ./table_is_free.sql
|
|
> source ./check_booking.sql
|
|
> delimiter ;
|
|
> call CheckBooking("2022-11-12 18:15:00", 3, @IsFree);
|
|
+---------------------------+
|
|
| BookingStatus |
|
|
+---------------------------+
|
|
| Table 3 is already booked |
|
|
+---------------------------+
|
|
> select @IsFree;
|
|
+---------+
|
|
| @IsFree |
|
|
+---------+
|
|
| 0 |
|
|
+---------+
|
|
> call CheckBooking("2022-11-12 17:15:00", 3, @IsFree);
|
|
+-----------------+
|
|
| BookingStatus |
|
|
+-----------------+
|
|
| Table 3 is free |
|
|
+-----------------+
|
|
> select @IsFree;
|
|
+---------+
|
|
| @IsFree |
|
|
+---------+
|
|
| 1 |
|
|
+---------+
|
|
|
|
# Task 3
|
|
> delimiter $
|
|
> source ./add_valid_booking.sql
|
|
> delimiter ;
|
|
> call AddValidBooking("2022-11-12 18:15:00", 3, 4, 1);
|
|
+-----------------------------------------------+
|
|
| BookingStatus |
|
|
+-----------------------------------------------+
|
|
| Table 3 is already booked - booking 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 |
|
|
+-----------+---------------------+-------------+------------+------------+
|
|
> call AddValidBooking("2022-11-12 17:15:00", 3, 4, 1);
|
|
+----------------------------------------------------+
|
|
| BookingStatus |
|
|
+----------------------------------------------------+
|
|
| Table 3 was free - booking accepted (BookingID=15) |
|
|
+----------------------------------------------------+
|
|
> 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 |
|
|
+-----------+---------------------+-------------+------------+------------+
|