API tools faq
paste
Advertisement
IhorD

Hotel Db

IhorD
Dec 14th, 2023
237
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 6.12 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE Room;
  2. DROP TABLE Guest;
  3. DROP TABLE Category;
  4. DROP TABLE Reservation;
  5.  
  6. CREATE TABLE Guest (IdGuest INTEGER PRIMARY KEY, Name VARCHAR2(20) NOT NULL, Surname VARCHAR2(30) NOT NULL, Discount INTEGER NULL);
  7. CREATE TABLE Category (IdCategory INTEGER PRIMARY KEY, Name VARCHAR2(20) NOT NULL, Price NUMERIC(8,2) NOT NULL);
  8. CREATE TABLE Room (RoomNumber INTEGER PRIMARY KEY, IdCategory INTEGER NOT NULL REFERENCES Category ON DELETE CASCADE, Capacity INTEGER NOT NULL);
  9. CREATE TABLE Reservation (IdReservation INTEGER PRIMARY KEY, DateStart DATE NOT NULL, DateEnd DATE NOT NULL, IdGuest INTEGER NOT NULL REFERENCES Guest ON DELETE CASCADE, RoomNumber INTEGER NOT NULL REFERENCES Room ON DELETE CASCADE, Paid INTEGER NOT NULL);
  10.  
  11. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (1, 'Anna', 'White', 20);
  12. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (2, 'John', 'Black', NULL);
  13. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (3, 'Mike', 'Green', 10);
  14. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (4, 'Ivy', 'Blue', 30);
  15. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (5, 'Monica', 'Gray', NULL);
  16. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (6, 'Jack', 'Blue', 5);
  17. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (7, 'Nick', 'Yellow', 15);
  18. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (8, 'Edward', 'Purple', 20);
  19. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (9, 'Helena', 'Pink', NULL);
  20. INSERT INTO Guest (IdGuest, Name, Surname, Discount) VALUES (10, 'Sarah', 'Red', 20);
  21.  
  22. INSERT INTO Category (IdCategory, Name, Price) VALUES (1, 'Turistic', 30);
  23. INSERT INTO Category (IdCategory, Name, Price) VALUES (2, 'Standard', 60);
  24. INSERT INTO Category (IdCategory, Name, Price) VALUES (3, 'Luxurious', 120);
  25.  
  26. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (101, 1, 2);
  27. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (102, 1, 2);
  28. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (103, 1, 3);
  29. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (104, 2, 2);
  30. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (105, 2, 2);
  31. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (201, 1, 2);
  32. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (202, 3, 4);
  33. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (203, 3, 2);
  34. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (204, 3, 2);
  35. INSERT INTO Room (RoomNumber, IdCategory, Capacity) VALUES (205, 2, 3);
  36.  
  37. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (1, TO_DATE('2009-07-01','yyyy-mm-dd'), TO_DATE('2009-07-05','yyyy-mm-dd'), 1, 101, 1);
  38. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (2, TO_DATE('2008-01-03','yyyy-mm-dd'), TO_DATE('2008-01-15','yyyy-mm-dd'), 1, 102, 1);
  39. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (3, TO_DATE('2009-07-15','yyyy-mm-dd'), TO_DATE('2009-08-02','yyyy-mm-dd'), 2, 101, 1);
  40. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (4, TO_DATE('2008-12-12','yyyy-mm-dd'), TO_DATE('2008-12-14','yyyy-mm-dd'), 3, 103, 1);
  41. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (5, TO_DATE('2009-05-01','yyyy-mm-dd'), TO_DATE('2009-05-05','yyyy-mm-dd'), 3, 201, 1);
  42. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (6, TO_DATE('2009-04-01','yyyy-mm-dd'), TO_DATE('2009-05-01','yyyy-mm-dd'), 4, 201, 0);
  43. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (7, TO_DATE('2008-11-15','yyyy-mm-dd'), TO_DATE('2008-11-20','yyyy-mm-dd'), 4, 105, 0);
  44. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (8, TO_DATE('2009-10-01','yyyy-mm-dd'), TO_DATE('2009-10-07','yyyy-mm-dd'), 10, 104, 1);
  45. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (9, TO_DATE('2009-07-03','yyyy-mm-dd'), TO_DATE('2009-07-20','yyyy-mm-dd'), 10, 204, 1);
  46. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (10, TO_DATE('2009-03-12','yyyy-mm-dd'), TO_DATE('2009-03-20','yyyy-mm-dd'), 9, 201, 1);
  47. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (11, TO_DATE('2009-12-01','yyyy-mm-dd'), TO_DATE('2009-12-02','yyyy-mm-dd'), 9, 202, 1);
  48. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (12, TO_DATE('2009-12-01','yyyy-mm-dd'), TO_DATE('2009-12-10','yyyy-mm-dd'), 8, 202, 1);
  49. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (13, TO_DATE('2008-03-03','yyyy-mm-dd'), TO_DATE('2008-03-12','yyyy-mm-dd'), 1, 203, 0);
  50. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (14, TO_DATE('2009-08-25','yyyy-mm-dd'), TO_DATE('2009-09-01','yyyy-mm-dd'), 6, 205, 1);
  51. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (15, TO_DATE('2009-04-16','yyyy-mm-dd'), TO_DATE('2009-04-21','yyyy-mm-dd'), 6, 101, 0);
  52. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (16, TO_DATE('2009-04-17','yyyy-mm-dd'), TO_DATE('2009-04-20','yyyy-mm-dd'), 6, 105, 1);
  53. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (17, TO_DATE('2009-02-12','yyyy-mm-dd'), TO_DATE('2009-02-14','yyyy-mm-dd'), 5, 104, 1);
  54. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (18, TO_DATE('2008-11-10','yyyy-mm-dd'), TO_DATE('2008-11-20','yyyy-mm-dd'), 1, 103, 1);
  55. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (19, TO_DATE('2009-06-07','yyyy-mm-dd'), TO_DATE('2009-06-15','yyyy-mm-dd'), 2, 101, 1);
  56. INSERT INTO Reservation (IdReservation, DateStart, DateEnd, IdGuest, RoomNumber, Paid) VALUES (20, TO_DATE('2009-07-01','yyyy-mm-dd'), TO_DATE('2009-07-05','yyyy-mm-dd'), 3, 101, 1);
  57.  
  58. COMMIT
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!