t7w6tFNA

1CREATE DATABASE IF NOT EXISTS `hotel`; 
2USE `hotel`;
3
4CREATE TABLE departments (
5	id INT PRIMARY KEY AUTO_INCREMENT,
6	name VARCHAR(50)
7);
8
9INSERT INTO departments(name) VALUES('Front Office'), ('Support'), ('Kitchen'), ('Other');
10
11CREATE TABLE employees (
12	id INT PRIMARY KEY AUTO_INCREMENT,
13	first_name VARCHAR(50) NOT NULL,
14	last_name VARCHAR(50) NOT NULL,
15	job_title VARCHAR(50) NOT NULL,
16	department_id INT NOT NULL,
17	salary DOUBLE NOT NULL,
18	CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)
19);
20
21INSERT INTO `employees` (`first_name`,`last_name`, `job_title`,`department_id`,`salary`) VALUES
22	('John', 'Smith', 'Manager',1, 900.00),
23	('John', 'Johnson', 'Customer Service',2, 880.00),
24	('Smith', 'Johnson', 'Porter', 4, 1100.00),
25	('Peter', 'Petrov', 'Front Desk Clerk', 1, 1100.00),
26	('Peter', 'Ivanov', 'Sales', 2, 1500.23),
27	('Ivan' ,'Petrov', 'Waiter', 3, 990.00),
28	('Jack', 'Jackson', 'Executive Chef', 3, 1800.00),
29	('Pedro', 'Petrov', 'Front Desk Supervisor', 1, 2100.00),
30	('Nikolay', 'Ivanov', 'Housekeeping', 4, 1600.00);
31	
32
33	
34CREATE TABLE rooms (
35	id INT PRIMARY KEY AUTO_INCREMENT,
36	`type` VARCHAR(30)
37);
38
39INSERT INTO rooms(`type`) VALUES('apartment'), ('single room');
40
41CREATE TABLE clients (
42	id INT PRIMARY KEY AUTO_INCREMENT,
43	first_name VARCHAR(50),
44	last_name VARCHAR(50),
45	room_id INT NOT NULL,
46    CONSTRAINT fk_clients_rooms
47    FOREIGN KEY (room_id)
48    REFERENCES rooms(id)
49);
50
51INSERT INTO clients(`first_name`,`last_name`,`room_id`) 
52VALUES('Pesho','Petrov', 1),('Gosho','Georgiev', 2),
53('Mariya','Marieva', 2), ('Katya','Katerinova', 1), ('Nikolay','Nikolaev', 2);