mirror of
https://github.com/datahub-project/datahub.git
synced 2025-07-08 09:41:19 +00:00
161 lines
8.2 KiB
SQL
161 lines
8.2 KiB
SQL
-- Sample data is taken from https://developers.sap.com/tutorials/hana-clients-hdbsql.html
|
|
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 VARCHAR(2) NOT NULL,
|
|
zip VARCHAR(6)
|
|
);
|
|
CREATE COLUMN TABLE HOTEL.ROOM(
|
|
hno INTEGER,
|
|
type VARCHAR(6),
|
|
free NUMERIC(3),
|
|
price NUMERIC(6, 2),
|
|
PRIMARY KEY (hno, type),
|
|
FOREIGN KEY (hno) REFERENCES HOTEL.HOTEL
|
|
);
|
|
CREATE COLUMN TABLE HOTEL.CUSTOMER(
|
|
cno INTEGER PRIMARY KEY,
|
|
title VARCHAR(7),
|
|
firstname VARCHAR(20),
|
|
name VARCHAR(40) NOT NULL,
|
|
address VARCHAR(40) NOT NULL,
|
|
zip VARCHAR(6)
|
|
);
|
|
CREATE COLUMN TABLE HOTEL.RESERVATION(
|
|
resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
|
|
rno INTEGER NOT NULL,
|
|
cno INTEGER,
|
|
hno INTEGER,
|
|
type VARCHAR(6),
|
|
arrival DATE NOT NULL,
|
|
departure DATE NOT NULL,
|
|
PRIMARY KEY (
|
|
"RESNO", "ARRIVAL"
|
|
),
|
|
FOREIGN KEY(hno) REFERENCES HOTEL.HOTEL,
|
|
FOREIGN KEY(cno) REFERENCES HOTEL.CUSTOMER
|
|
);
|
|
CREATE COLUMN TABLE HOTEL.MAINTENANCE(
|
|
mno INTEGER PRIMARY KEY,
|
|
hno INTEGER,
|
|
description 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(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.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.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', '2020-11-14', '2020-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', '2020-04-12', '2020-04-30');
|
|
INSERT INTO HOTEL.RESERVATION VALUES(7, 160, 1011, 17, 'single', '2020-04-12', '2020-04-15');
|
|
INSERT INTO HOTEL.RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2020-09-01', '2020-09-03');
|
|
INSERT INTO HOTEL.RESERVATION VALUES(9, 180, 1001, 22, 'double', '2020-12-23', '2021-01-08');
|
|
INSERT INTO HOTEL.RESERVATION VALUES(10, 190, 1013, 24, 'double', '2020-11-14', '2020-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); |