6SMuCqCq

1CREATE DATABASE cjms;
2
3USE cjms;
4
5#1
6CREATE TABLE planets (
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(30) NOT NULL
9);
10
11CREATE TABLE spaceports (
12	id INT PRIMARY KEY AUTO_INCREMENT,
13    name VARCHAR(50) NOT NULL,
14    planet_id INT,
15    CONSTRAINT fk_spaceports_planet
16    FOREIGN KEy (planet_id) REFERENCES planets(id)
17);
18
19CREATE TABLE spaceships (
20	id INT PRIMARY KEY AUTO_INCREMENT,
21    name VARCHAR(50) NOT NULL,
22	manufacturer VARCHAR(30) NOT NULL,
23    light_speed_rate INT DEFAULT 0
24);
25
26CREATE TABLE colonists (
27	id INT PRIMARY KEY AUTO_INCREMENT,
28    first_name VARCHAR(20) NOT NULL,
29    last_name VARCHAR(20) NOT NULL,
30    ucn CHAR(10) NOT NULL UNIQUE,
31    birth_date DATE NOT NULL
32);
33
34CREATE TABLE journeys (
35	id INT PRIMARY KEY AUTO_INCREMENT,
36	journey_start DATETIME NOT NULL,
37    journey_end DATETIME NOT NULL,
38    purpose ENUM('Medical','Technical','Educational', 'Military') NOT NULL,
39    destination_spaceport_id INT,
40    spaceship_id INT,
41    CONSTRAINT fk_journeys_spaceports
42    FOREIGN KEY (destination_spaceport_id) REFERENCES spaceports(id),
43    CONSTRAINT fk_journeys_spaceships
44    FOREIGN KEY (spaceship_id) REFERENCES spaceships(id)
45);
46
47CREATE TABLE travel_cards (
48	id INT PRIMARY KEY AUTO_INCREMENT,
49	card_number CHAR(10) NOT NULL UNIQUE,
50    job_during_journey ENUM('Pilot','Engineer','Trooper', 'Cleaner','Cook') NOT NULL,
51    colonist_id INT,
52    journey_id INT,
53    CONSTRAINT fk_travelCards_colonists
54    FOREIGN KEY (colonist_id) REFERENCES colonists(id),
55    CONSTRAINT fk_travelCards_journeys
56    FOREIGN KEY (journey_id) REFERENCES journeys(id)
57);
58
59#2
60INSERT INTO travel_cards(card_number, job_during_journey, colonist_id, journey_id)
61SELECT	(
62			CASE
63				WHEN birth_date > '1980-01-01' 
64					THEN CONCAT_WS('', YEAR(birth_date),DAY(birth_date),LEFT(ucn,4)) 
65				ELSE CONCAT_WS('', YEAR(birth_date),MONTH(birth_date),RIGHT(ucn,4))
66			END
67		) AS card_number,
68        (
69			CASE
70				WHEN id % 2 = 0 THEN 'Pilot'
71                WHEN id % 3 = 0 THEN 'Cook'
72                ELSE 'Engineer'
73			END 
74        ) AS job_during_journey,
75        id AS colonist_id,
76        LEFT(ucn,1) AS journey_id
77FROM colonists
78WHERE id BETWEEN 96 AND 100;
79
80#3
81UPDATE journeys SET purpose = (
82	CASE
83		WHEN id % 2 = 0 THEN 'Medical'
84        WHEN id % 3 = 0 THEN 'Technical'
85        WHEN id % 5 = 0 THEN 'Educational'
86        WHEN id % 7 = 0 THEN 'Military'
87        ELSE purpose
88	END
89);
90
91#3
92DELETE FROM colonists
93WHERE id NOT IN (SELECT colonist_id FROM travel_cards);
94
95#4
96SELECT id, journey_start, journey_end FROM journeys
97WHERE purpose = 'Military'
98ORDER BY journey_start;
99
100#5
101SELECT ship.name AS spaceship_name, port.name AS spaceport_name
102FROM spaceships AS ship
103JOIN journeys AS j
104ON ship.id = j.spaceship_id
105JOIN spaceports AS port
106ON j.destination_spaceport_id = port.id
107ORDER BY light_speed_rate DESC LIMIT 1;
108
109#6
110SELECT ship.name, ship.manufacturer
111FROM spaceships AS ship
112JOIN journeys AS j
113ON ship.id = j.spaceship_id
114JOIN travel_cards AS tc
115ON j.id = tc.journey_id
116JOIN colonists AS c
117ON tc.colonist_id = c.id
118WHERE tc.job_during_journey = 'Pilot' AND
119YEAR(birth_date) > YEAR('1989-01-01')
120ORDER BY ship.name;
121
122#7
123SELECT p.name AS planet_name, port.name AS spaceport_name
124FROM spaceports AS port
125JOIN planets AS p
126ON port.planet_id = p.id
127JOIN journeys AS j
128ON port.id = j.destination_spaceport_id
129WHERE j.purpose = 'Educational'
130ORDER BY port.name DESC;
131
132#8
133SELECT p.name AS planet_name, COUNT(j.id) AS journeys_count
134FROM planets AS p
135JOIN spaceports AS port
136ON p.id = port.planet_id
137JOIN journeys AS j
138ON port.id = j.destination_spaceport_id
139GROUP BY p.name
140ORDER BY journeys_count DESC, p.name;
141
142#9
143SELECT tc.job_during_journey AS job_name
144FROM travel_cards AS tc
145WHERE tc.journey_id = (
146	SELECT j.id FROM journeys AS j
147    ORDER BY DATEDIFF(j.journey_end,j.journey_start) DESC LIMIT 1
148)
149GROUP BY job_name 
150ORDER BY COUNT(tc.job_during_journey) LIMIT 1;
151
152
153#10
154DELIMITER $$
155CREATE FUNCTION udf_count_colonists_by_destination_planet(planet_name VARCHAR(30))
156RETURNS INT
157BEGIN
158		DECLARE c_count INT;
159        SET c_count := (
160			SELECT COUNT(c.id) FROM colonists AS c
161            JOIN travel_cards AS tc
162            ON c.id = tc.colonist_id
163			JOIN journeys AS j
164            ON tc.journey_id = j.id
165            JOIN spaceports AS ports
166            ON j.destination_spaceport_id = ports.id
167            JOIN planets AS p
168            ON ports.planet_id = p.id
169            WHERE p.name = planet_name
170		);
171                        
172		RETURN c_count;
173END; $$
174
175SELECT udf_count_colonists_by_destination_planet('Otroyphus');
176
177
178#11
179DELIMITER $$
180CREATE PROCEDURE udp_modify_spaceship_light_speed_rate
181						(spaceship_name VARCHAR(50), light_speed_rate_increse INT(11))
182BEGIN
183	START TRANSACTION;
184	IF spaceship_name NOT IN (SELECT name FROM spaceships) 
185        THEN
186			SIGNAL SQLSTATE '45000'
187            SET MESSAGE_TEXT = 'Spaceship you are trying to modify does not exists.';
188			ROLLBACK;
189	ELSE 
190		UPDATE spaceships 
191			SET light_speed_rate = light_speed_rate +  light_speed_rate_increse
192		WHERE name = spaceship_name;
193	END IF;
194END; $$
195
196CALL udp_modify_spaceship_light_speed_rate ('Na Pesho koraba', 1914);
197SELECT name, light_speed_rate FROM spacheships WHERE name = 'Na Pesho koraba'
198
199CALL udp_modify_spaceship_light_speed_rate('USS Templar', 5);
200SELECT name, light_speed_rate FROM spaceships WHERE name = 'USS Templar'