NpSVqNcu

1DROP database if exists hotels;
2CREATE database hotels;
3USE hotels;
4
5
6CREATE TABLE rooms(
7numOfRoom INT NOT NULL PRIMARY KEY UNIQUE,
8category VARCHAR (50) NOT NULL,
9numOfBeds INT NOT NULL,
10viewOfRoom VARCHAR (50) NOT NULL
11);
12
13CREATE TABLE clients(
14id INT NOT NULL auto_increment PRIMARY KEY,
15nameOfClient VARCHAR (255) NOT NULL,
16egn VARCHAR (20) NOT NULL,
17dateOfArrival DATE NOT NULL,
18daysOfStaying INT NOT NULL,
19dateOfLeaving DATE NOT NULL,
20room_id int,
21constraint foreign key (room_id) references rooms (numOfRoom)
22);
23
24
25CREATE TABLE rating(
26id INT NOT NULL auto_increment PRIMARY KEY,
27rate ENUM ('1', '2', '3', '4', '5'),
28clients_id INT,
29CONSTRAINT FOREIGN KEY (clients_id) REFERENCES clients (id));
30
31
32INSERT INTO rooms(
33numOfRoom, category,numOfBeds, viewOfRoom) VALUES (101, 'Double room', 2, 'North view');
34INSERT INTO rooms(
35numOfRoom, category,numOfBeds, viewOfRoom) VALUES (207, 'Single room', 1, 'South view');
36INSERT INTO rooms(
37numOfRoom, category,numOfBeds, viewOfRoom) VALUES (306, 'Apartment', 4, 'West view');
38INSERT INTO rooms(
39numOfRoom, category,numOfBeds, viewOfRoom) VALUES (505, 'Studio', 3, 'East view');
40INSERT INTO rooms(
41numOfRoom, category,numOfBeds, viewOfRoom) VALUES (603, 'Apartment', 4, 'East view');
42
43INSERT INTO clients(
44nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
45VALUES ('Ivan Georgiev Dimitrov', 6405120033, "2016-05-11", 5, "2016-05-16", 101);
46INSERT INTO clients(
47nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
48VALUES ('Amy Steven Johnson', 263585946212, "2017-02-12", 3, "2017-02-15", 207);
49INSERT INTO clients(
50nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
51VALUES ('Sammy Elizabeth Smith', 10254585963, "2018-03-16", 10, "2018-03-26", 101);
52INSERT INTO clients(
53nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
54VALUES ('Maria Stefanova Angelova', 8501160563, "2018-01-15", 3, "2018-01-18", 505);
55
56INSERT INTO rating(
57rate, clients_id) VALUES ('5', 1);
58INSERT INTO rating(
59rate, clients_id) VALUES ('2', 2);
60INSERT INTO rating(
61rate, clients_id) VALUES ('5', 3);
62INSERT INTO rating(
63rate, clients_id) VALUES ('3', 4);