Copie el siguiente SQL en el archivo hotel.sql.
CREATE COLUMN TABLE HOTEL.HOTEL(
hno INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(40) NOT NULL,
city VARCHAR(30) NOT NULL,
state CHAR(2) NOT NULL,
zip CHAR(6)
);
CREATE COLUMN TABLE HOTEL.ROOM(
hno INTEGER,
type CHAR(6),
free NUMERIC(3),
price NUMERIC(6, 2),
PRIMARY KEY (hno, type)
);
CREATE COLUMN TABLE HOTEL.CUSTOMER(
cno INTEGER PRIMARY KEY,
title CHAR(7),
firstname VARCHAR(20),
name VARCHAR(40) NOT NULL,
address VARCHAR(40) NOT NULL,
zip CHAR(6)
);
CREATE COLUMN TABLE HOTEL.RESERVATION(
resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
rno INTEGER NOT NULL,
cno INTEGER,
hno INTEGER,
type CHAR(6),
arrival DATE NOT NULL,
departure DATE NOT NULL,
PRIMARY KEY (
"RESNO", "ARRIVAL"
)
);
CREATE COLUMN TABLE HOTEL.MAINTENANCE(
mno INTEGER PRIMARY KEY,
hno INTEGER,
desc VARCHAR(100),
date_performed DATE,
performed_by VARCHAR(40)
);
CREATE OR REPLACE PROCEDURE HOTEL.SHOW_RESERVATIONS(
IN IN_HNO INTEGER, IN IN_ARRIVAL DATE)
SQL SECURITY INVOKER
READS SQL DATA
AS BEGIN
SELECT
R.RESNO,
R.ARRIVAL,
DAYS_BETWEEN (R.ARRIVAL, R.DEPARTURE) as "Nights",
H.NAME,
CUS.TITLE,
CUS.FIRSTNAME AS "FIRST NAME",
CUS.NAME AS "LAST NAME"
FROM
HOTEL.RESERVATION AS R
LEFT OUTER JOIN
HOTEL.HOTEL AS H
ON H.HNO = R.HNO
LEFT OUTER JOIN
HOTEL.CUSTOMER AS CUS
ON CUS.CNO = R.CNO
WHERE R.ARRIVAL = :IN_ARRIVAL AND
H.HNO = :IN_HNO
ORDER BY
H.NAME ASC,
R.ARRIVAL DESC;
END;
INSERT INTO HOTEL.HOTEL VALUES(10, 'Congress', '155 Beechwood St.', 'Seattle', 'WA', '20005');
INSERT INTO HOTEL.HOTEL VALUES(11, 'Regency', '477 17th Avenue', 'Seattle', 'WA', '20037');
INSERT INTO HOTEL.HOTEL VALUES(12, 'Long Island', '1499 Grove Street', 'Long Island', 'NY', '11788');
INSERT INTO HOTEL.HOTEL VALUES(13, 'Empire State', '65 Yellowstone Dr.', 'Albany', 'NY', '12203');
INSERT INTO HOTEL.HOTEL VALUES(14, 'Midtown', '12 Barnard St.', 'New York', 'NY', '10019');
INSERT INTO HOTEL.HOTEL VALUES(15, 'Eighth Avenue', '112 8th Avenue', 'New York', 'NY', '10019');
INSERT INTO HOTEL.HOTEL VALUES(16, 'Lake Michigan', '354 OAK Terrace', 'Chicago', 'IL', '60601');
INSERT INTO HOTEL.HOTEL VALUES(17, 'Airport', '650 C Parkway', 'Rosemont', 'IL', '60018');
INSERT INTO HOTEL.HOTEL VALUES(18, 'Sunshine', '200 Yellowstone Dr.', 'Clearwater', 'FL', '33575');
INSERT INTO HOTEL.HOTEL VALUES(19, 'Beach', '1980 34th St.', 'Daytona Beach', 'FL', '32018');
INSERT INTO HOTEL.HOTEL VALUES(20, 'Atlantic', '111 78th St.', 'Deerfield Beach', 'FL', '33441');
INSERT INTO HOTEL.HOTEL VALUES(21, 'Long Beach', '35 Broadway', 'Long Beach', 'CA', '90804');
INSERT INTO HOTEL.HOTEL VALUES(22, 'Indian Horse', '16 MAIN STREET', 'Palm Springs', 'CA', '92262');
INSERT INTO HOTEL.HOTEL VALUES(23, 'Star', '13 Beechwood Place', 'Hollywood', 'CA', '90029');
INSERT INTO HOTEL.HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '70112');
INSERT INTO HOTEL.HOTEL VALUES(25, 'Ocean Star', '45 Pacific Avenue', 'Atlantic City', 'NJ', '08401');
INSERT INTO HOTEL.HOTEL VALUES(26, 'Bella Ciente', '1407 Marshall Ave', 'Longview', 'TX', '75601');
INSERT INTO HOTEL.ROOM VALUES(10, 'single', 20, 135.00);
INSERT INTO HOTEL.ROOM VALUES(10, 'double', 45, 200.00);
INSERT INTO HOTEL.ROOM VALUES(12, 'single', 10, 70.00);
INSERT INTO HOTEL.ROOM VALUES(12, 'double', 13, 100.00);
INSERT INTO HOTEL.ROOM VALUES(13, 'single', 12, 45.00);
INSERT INTO HOTEL.ROOM VALUES(13, 'double', 15, 80.00);
INSERT INTO HOTEL.ROOM VALUES(14, 'single', 20, 85.00);
INSERT INTO HOTEL.ROOM VALUES(14, 'double', 35, 140.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'single', 50, 105.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'double', 230, 180.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'suite', 12, 500.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'single', 10, 120.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'double', 39, 200.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'suite', 20, 500.00);
INSERT INTO HOTEL.ROOM VALUES(17, 'single', 4, 115.00);
INSERT INTO HOTEL.ROOM VALUES(17, 'double', 11, 180.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'single', 15, 90.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'double', 19, 150.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'suite', 5, 400.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'single', 45, 90.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'double', 145, 150.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'suite', 60, 300.00);
INSERT INTO HOTEL.ROOM VALUES(20, 'single', 11, 60.00);
INSERT INTO HOTEL.ROOM VALUES(20, 'double', 24, 100.00);
INSERT INTO HOTEL.ROOM VALUES(21, 'single', 2, 70.00);
INSERT INTO HOTEL.ROOM VALUES(21, 'double', 10, 130.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'single', 34, 80.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'double', 78, 140.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'suite', 55, 350.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'single', 44, 100.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'double', 115, 190.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'suite', 6, 450.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'single', 89, 160.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'double', 300, 270.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'suite', 100, 700.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'single', 10, 125.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'double', 9, 200.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'suite', 78, 600.00);
INSERT INTO HOTEL.CUSTOMER VALUES(1000, 'Mrs', 'Jenny', 'Porter', '1340 N. Ash Street, #3', '10580');
INSERT INTO HOTEL.CUSTOMER VALUES(1001, 'Mr', 'Peter', 'Brown', '1001 34th St., APT.3', '48226');
INSERT INTO HOTEL.CUSTOMER VALUES(1002, 'Company', NULL, 'Datasoft', '486 Maple St.', '90018');
INSERT INTO HOTEL.CUSTOMER VALUES(1003, 'Mrs', 'Rose', 'Brian', '500 Yellowstone Drive, #2', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1004, 'Mrs', 'Mary', 'Griffith', '3401 Elder Lane', '20005');
INSERT INTO HOTEL.CUSTOMER VALUES(1005, 'Mr', 'Martin', 'Randolph', '340 MAIN STREET, #7', '60615');
INSERT INTO HOTEL.CUSTOMER VALUES(1006, 'Mrs', 'Sally', 'Smith', '250 Curtis Street', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1007, 'Mr', 'Mike', 'Jackson', '133 BROADWAY APT. 1', '45211');
INSERT INTO HOTEL.CUSTOMER VALUES(1008, 'Mrs', 'Rita', 'Doe', '2000 Humboldt St., #6', '97213');
INSERT INTO HOTEL.CUSTOMER VALUES(1009, 'Mr', 'George', 'Howe', '111 B Parkway, #23', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1010, 'Mr', 'Frank', 'Miller', '27 5th St., 76', '95054');
INSERT INTO HOTEL.CUSTOMER VALUES(1011, 'Mrs', 'Susan', 'Baker', '200 MAIN STREET, #94', '90018');
INSERT INTO HOTEL.CUSTOMER VALUES(1012, 'Mr', 'Joseph', 'Peters', '700 S. Ash St., APT.12', '92714');
INSERT INTO HOTEL.CUSTOMER VALUES(1013, 'Company', NULL, 'TOOLware', '410 Mariposa St., #10', '20019');
INSERT INTO HOTEL.CUSTOMER VALUES(1014, 'Mr', 'Antony', 'Jenkins', '55 A Parkway, #15', '20903');
INSERT INTO HOTEL.RESERVATION VALUES(1, 100, 1000, 11, 'single', '2020-12-24', '2020-12-27');
INSERT INTO HOTEL.RESERVATION VALUES(2, 110, 1001, 11, 'double', '2020-12-24', '2021-01-03');
INSERT INTO HOTEL.RESERVATION VALUES(3, 120, 1002, 15, 'suite', '2004-11-14', '2004-11-18');
INSERT INTO HOTEL.RESERVATION VALUES(4, 130, 1009, 21, 'single', '2019-02-01', '2019-02-03');
INSERT INTO HOTEL.RESERVATION VALUES(5, 150, 1006, 17, 'double', '2019-03-14', '2019-03-24');
INSERT INTO HOTEL.RESERVATION VALUES(6, 140, 1013, 20, 'double', '2004-04-12', '2004-04-30');
INSERT INTO HOTEL.RESERVATION VALUES(7, 160, 1011, 17, 'single', '2004-04-12', '2004-04-15');
INSERT INTO HOTEL.RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2004-09-01', '2004-09-03');
INSERT INTO HOTEL.RESERVATION VALUES(9, 180, 1001, 22, 'double', '2004-12-23', '2005-01-08');
INSERT INTO HOTEL.RESERVATION VALUES(10, 190, 1013, 24, 'double', '2004-11-14', '2004-11-17');
INSERT INTO HOTEL.MAINTENANCE VALUES(10, 24, 'Replace pool liner and pump', '2019-03-21', 'Discount Pool Supplies');
INSERT INTO HOTEL.MAINTENANCE VALUES(11, 25, 'Renovate the bar area. Replace TV and speakers', '2020-11-29', 'TV and Audio Superstore');
INSERT INTO HOTEL.MAINTENANCE VALUES(12, 26, 'Roof repair due to storm', null, null);
