54 lines
2.3 KiB
MySQL
54 lines
2.3 KiB
MySQL
|
|
-- Automatisch Cleaningplan erstellen
|
|
INSERT INTO BOOKING VALUES (444, TO_DATE('2023-09-07', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
|
|
|
|
INSERT INTO BOOKINGROOM VALUES (444, 8);
|
|
|
|
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');
|
|
ROLLBACK;
|
|
|
|
-- Automatisch Cleaningplan erstellen und löschen
|
|
INSERT INTO BOOKING VALUES (444, TO_DATE('2023-09-07', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
|
|
|
|
INSERT INTO BOOKINGROOM VALUES (444, 8);
|
|
|
|
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');
|
|
|
|
DELETE FROM BOOKINGROOM WHERE bookingID = 444;
|
|
|
|
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');
|
|
ROLLBACK;
|
|
|
|
-- Cleaningplan kann nicht erstellt werden
|
|
INSERT INTO BOOKING VALUES (555, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
INSERT INTO BOOKING VALUES (556, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
|
|
INSERT INTO BOOKINGROOM VALUES (555, 8);
|
|
INSERT INTO BOOKINGROOM VALUES (556, 8);
|
|
|
|
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-05', 'YYYY-MM-DD');
|
|
ROLLBACK;
|
|
|
|
-- Cleaningplan kann nicht erstellt werden
|
|
INSERT INTO BOOKING VALUES (888, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
|
|
INSERT INTO BOOKINGROOM VALUES (888, 8);
|
|
|
|
INSERT INTO CLEANINGPLAN VALUES (cleaningplan_seq.nextval, TO_DATE('2023-09-04', 'YYYY-MM-DD'), 240, 8);
|
|
ROLLBACK;
|
|
|
|
-- Cleaningplan wird erfolgreich gebucht
|
|
INSERT INTO CLEANINGPLAN VALUES (cleaningplan_seq.nextval, TO_DATE('2023-11-09', 'YYYY-MM-DD'), 240, 8);
|
|
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-11-09', 'YYYY-MM-DD');
|
|
ROLLBACK;
|
|
|
|
-- Gesamtpreise werden angezeigt
|
|
INSERT INTO BOOKING VALUES (234, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
|
|
INSERT INTO BOOKING VALUES (456, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 10, 'No Pension', 0, 15);
|
|
UPDATE BOOKING SET COST = 5 WHERE BOOKINGID = 456;
|
|
INSERT INTO BOOKING VALUES (789, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-09-06', 'YYYY-MM-DD'), 10, 'No Pension', 0, 15);
|
|
DELETE FROM BOOKING WHERE BOOKINGID = 234;
|
|
ROLLBACK;
|