SWfpmXkG

1DROP DATABASE IF EXISTS ski_slopes_system;
2CREATE DATABASE ski_slopes_system;
3USE ski_slopes_system;
4
5CREATE TABLE lifts(
6id INT AUTO_INCREMENT PRIMARY KEY,
7name VARCHAR(255) NOT NULL UNIQUE,
8liftType ENUM('кабинка', 'седалка', 'влек'),
9length INT NOT NULL,
10price DOUBLE NOT NULL,
11status ENUM('отворен', 'затворен', 'частично отворен'),
12capacity VARCHAR(255)
13);
14
15CREATE TABLE slopes(
16id INT AUTO_INCREMENT PRIMARY KEY,
17name VARCHAR(255) NOT NULL UNIQUE,
18length INT NOT NULL,
19thickness INT NOT NULL,
20altitude INT NOT NULL,
21level ENUM('начинаещи', 'лека', 'средна', 'трудна'),
22lift_id INT NOT NULL,
23CONSTRAINT FOREIGN KEY (lift_id) REFERENCES  lifts(id)
24);
25
26CREATE TABLE cards(
27id INT AUTO_INCREMENT PRIMARY KEY,
28rideType ENUM('сутрешно', 'следобедно', 'нощно'),
29price DOUBLE NOT NULL,
30cardType ENUM('детска', 'студентска', 'нормална'),
31lifts VARCHAR(255) NOT NULL
32);
33
34CREATE TABLE cards_lifts(
35card_id INT NOT NULL,
36lift_id INT NOT NULL,
37CONSTRAINT FOREIGN KEY (card_id) REFERENCES cards(id),
38CONSTRAINT FOREIGN KEY (lift_id) REFERENCES lifts(id)
39);
40
41INSERT INTO lifts
42VALUES 	(NULL, 'Ястребец', 'кабинка', '4827', 12.5, 'отворен', '6 човека'),
43		(NULL, 'Ситняково експрес', 'седалка', '1876', '5', 'отворен', '4 човека'),
44        (NULL, 'Ястребец експрес', 'седалка', '2050', '6', 'затворен', '4 човека'),
45        (NULL, 'Маркуджик 2', 'седалка', '950', '3', 'отворен', '4 човека'),
46        (NULL, 'Мартинови Бараки Експрес', 'седалка', '1209', '4', 'отворен', '6 човека'),
47        (NULL, 'Ситняково', 'влек', '226', '2', 'отворен', '1 човек'),
48        (NULL, 'Маркуджик 0', 'влек', '500', '2', 'отворен', '1 човек'),
49        (NULL, 'Маркуджик 1', 'влек', '903', '2', 'отворен', '1 човек'),
50        (NULL, 'Маркуджик 3', 'влек', '1031', '3', 'отворен', '1 човек');
51        
52SELECT 	id AS Номер, 
53		name AS Лифт , 
54		liftType AS Тип, 
55        length AS 'Дължина в метри', 
56        price AS Цена, 
57        status AS Статус, 
58        capacity AS Капацитет FROM lifts;	
59        
60INSERT INTO slopes
61VALUES 	(NULL, 'Ситняково 1', '4585', '100', '1900', 'лека', '2'),
62		(NULL, 'Ситняково 2', '3575', '110', '1970', 'средна', '2'),
63        (NULL, 'Ситняково 3', '4221', '110', '1800', 'средна', '2'),
64        (NULL, 'Червено знаме', '800', '100', '1900', 'трудна', '2'),
65        (NULL, 'Мартинови бараки 1', '6520', '110', '2300', 'лека', '5'),
66        (NULL, 'Мартинови бараки 2', '5520', '110', '2340', 'средна', '5'),
67        (NULL, 'Мартинови бараки 3', '4256', '110', '2156', 'средна', '5'),
68        (NULL, 'Мартинови бараки 4', '1200', '100', '1999', 'трудна', '5'),
69        (NULL, 'Ястребец 1', '3450', '110', '2340', 'средна', '3'),
70        (NULL, 'Ястребец 3', '3600', '110', '2410', 'средна', '3'),
71        (NULL, 'Попангелов', '900', '110', '1890', 'средна', '3'), 
72        (NULL, 'Маркуджик 2-А', '700', '130', '2450', 'трудна', '7'),
73        (NULL, 'Маркуджик 2-Б', '650', '130', '2600', 'средна', '9');
74        
75SELECT 	id AS Номер,
76		name AS Писта,
77        length AS 'Дължина в метри',
78        thickness AS 'Снежна покривка в см',
79        altitude AS 'Надморска височина',
80        level AS Ниво,
81        lift_id AS Лифт FROM slopes;
82        
83INSERT INTO cards
84VALUES 	(NULL, 'сутрешно', '38', 'нормална', 'всички'),
85		(NULL, 'сутрешно', '26', 'студентска', 'всички'),
86        (NULL, 'сутрешно', '12', 'детска', 'всички'),
87        (NULL, 'следобедно', '32', 'нормална', 'всички'),
88        (NULL, 'следобедно', '20', 'студентска', 'всички'),
89        (NULL, 'следобедно', '8', 'детска', 'всички'),
90        (NULL, 'нощно', '28', 'нормална', '1|2|3'),
91        (NULL, 'нощно', '16', 'студентска', '1|2|3'),
92        (NULL, 'нощно', '1', 'детска', '1|2|3'),
93        (NULL, 'сутрешно', '32', 'нормална', '1|2|3|5|7'),
94        (NULL, 'сутрешно', '20', 'студентска', '2|3|5|7'),
95        (NULL, 'следобедно', '28', 'нормална', '2|3|5'),
96        (NULL, 'следобедно', '18', 'студентска', '1|2|3|5'),
97        (NULL, 'сутрешно', '8', 'детска', '1|2|3|5|7'),
98        (NULL, 'следобедно', '6', 'детска', '2|3|5');
99        
100SELECT 	id AS Номер,
101		rideType AS "Вид каране",
102        price AS Цена,
103        cardType AS "Вид карта",
104        lifts AS Лифтове FROM cards;
105        
106INSERT INTO cards_lifts
107VALUE 	(1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7), (1,8), (1,9),
108		(2,1), (2,2), (2,3), (2,4), (2,5), (2,6), (2,7), (2,8), (2,9),
109        (3,1), (3,2), (3,3), (3,4), (3,5), (3,6), (3,7), (3,8), (3,9),
110        (4,1), (4,2), (4,3), (4,4), (4,5), (4,6), (4,7), (4,8), (4,9),
111        (5,1), (5,2), (5,3), (5,4), (5,5), (5,6), (5,7), (5,8), (5,9),
112        (6,1), (6,2), (6,3), (6,4), (6,5), (6,6), (6,7), (6,8), (6,9),
113        (7,1), (7,2), (7,3),
114        (8,1), (8,2), (8,3),
115        (9,1), (9,2), (9,3),
116        (10,1), (10,2), (10,3), (10,5), (10,7),
117        (11,2), (11,3), (11,5), (11,7),
118        (12,2), (12,3), (12,5),
119        (13,1), (13,2), (13,3), (13,5),
120        (14,1), (14,2), (14,3), (14,5), (14,7),
121        (15,2), (15,3), (15,5);
122        
123SELECT 	cards.rideType as "Вид каране", cards.price as "Цена", cards.cardType as "Вид карта" ,
124		lifts. name as "Лифт"
125        FROM cards JOIN lifts
126        ON cards.id IN(
127						SELECT card_id
128                        FROM cards_lifts
129                        WHERE cards_lifts.lift_id = lifts.id)
130                        ORDER BY cards.id;
131                        
132
133delimiter |
134create procedure checkPrice(in lift_name varchar(255), in lift_capacity varchar(255))
135begin
136declare result varchar(255);
137set result  = 'Невалидни данни';
138	if(	(select lifts.price
139			from lifts
140			where lifts.name = lift_name
141			and lifts.capacity = lift_capacity) is not null)
142	then 
143			(select lifts.price as 'Цена' from lifts where lifts.name = lift_name);
144	else 
145			select result;
146	end if;
147end
148|
149delimiter ;
150
151call checkPrice('Ястребец', '6 човека');