· 8 years ago · Apr 06, 2018, 10:22 AM
1/* KURSOVA RABOTA BD */
2
3
4/****** CREATE TABLE ******/
5
6
7DROP DATABASE IF EXISTS cars;
8CREATE DATABASE cars;
9USE cars;
10
11CREATE TABLE countries (
12 id INT NOT NULL AUTO_INCREMENT,
13 country_name VARCHAR(84) NOT NULL UNIQUE,
14 PRIMARY KEY( id )
15);
16
17CREATE TABLE brands (
18 id INT NOT NULL AUTO_INCREMENT,
19 brand_name VARCHAR(255) NOT NULL UNIQUE,
20 country_id INT NOT NULL,
21 CONSTRAINT FOREIGN KEY ( country_id ) REFERENCES countries(id),
22 PRIMARY KEY ( id )
23);
24
25CREATE TABLE models (
26 id INT NOT NULL AUTO_INCREMENT,
27 brand_id INT NOT NULL,
28 model_name VARCHAR(255) NOT NULL,
29 engine_code VARCHAR(50) NOT NULL,
30 horsepower SMALLINT UNSIGNED NOT NULL,
31 fuel_type ENUM('gas','diesel','electric','hybrid') NOT NULL,
32 UNIQUE KEY( model_name, engine_code ),
33 CONSTRAINT FOREIGN KEY ( brand_id ) REFERENCES brands(id),
34 PRIMARY KEY ( id )
35);
36
37CREATE TABLE cars (
38 id INT NOT NULL AUTO_INCREMENT,
39 vin CHAR(17) NOT NULL UNIQUE,
40 license_plate VARCHAR(10) NOT NULL UNIQUE,
41 miles MEDIUMINT UNSIGNED NOT NULL,
42 made SMALLINT UNSIGNED NOT NULL,
43 active BOOLEAN NOT NULL DEFAULT 1,
44 model_id INT NOT NULL,
45 CONSTRAINT FOREIGN KEY ( model_id ) REFERENCES models(id),
46 PRIMARY KEY ( id )
47);
48
49CREATE TABLE people (
50 id INT NOT NULL AUTO_INCREMENT,
51 egn VARCHAR(20) NOT NULL UNIQUE,
52 first_name VARCHAR(50) NOT NULL,
53 surname VARCHAR(50) NOT NULL,
54 last_name VARCHAR(50) NOT NULL,
55 address VARCHAR(255) NOT NULL,
56 PRIMARY KEY ( id )
57);
58
59CREATE TABLE cars_owners (
60 car_id INT NOT NULL,
61 owner_id INT NOT NULL,
62 active_owner BOOLEAN NOT NULL DEFAULT 1,
63 PRIMARY KEY( car_id, owner_id ),
64 CONSTRAINT FOREIGN KEY ( car_id ) REFERENCES cars(id),
65 CONSTRAINT FOREIGN KEY ( owner_id ) REFERENCES people(id)
66);
67
68CREATE TABLE repair_shops (
69 id INT NOT NULL AUTO_INCREMENT,
70 company_name VARCHAR(255) NOT NULL,
71 address VARCHAR(255) NOT NULL UNIQUE,
72 bulstat CHAR(9) NOT NULL UNIQUE,
73 PRIMARY KEY ( id )
74);
75
76CREATE TABLE car_repairs (
77 repair_id INT NOT NULL AUTO_INCREMENT,
78 car_id INT NOT NULL,
79 shop_id INT NOT NULL,
80 repair_date DATE NOT NULL,
81 repair_description VARCHAR(255),
82 repair_cost DECIMAL(15,2) NOT NULL,
83 CONSTRAINT FOREIGN KEY ( car_id ) REFERENCES cars(id),
84 CONSTRAINT FOREIGN KEY ( shop_id ) REFERENCES repair_shops(id),
85 UNIQUE KEY ( car_id, repair_date ),
86 PRIMARY KEY ( repair_id )
87);
88
89
90/****** END CREATE TABLE ******/
91
92/****** INSERT DATA ******/
93
94
95INSERT INTO countries ( country_name )
96VALUES ( 'Germany' ), ( 'Japan') , ( 'USA' ), ( 'Italy' ), ( 'France' ), ( 'Romania' ), ( 'Russian Federation' ), ( 'UK' );
97
98INSERT INTO brands ( country_id, brand_name )
99VALUES ( 1, 'VW' ), ( 1, 'BMW' ), ( 1, 'Mercedes-Benz' ), ( 2, 'Honda' ), ( 2, 'Toyota' ), ( 3, 'Ford' ), ( 6, 'Dacia' );
100
101INSERT INTO models ( brand_id, model_name , engine_code , horsepower , fuel_type )
102VALUES ( 1, 'GOLF TDI' , 'ARL' , 150, 'diesel' ),
103 ( 1, 'GOLF 1.8T' , 'AGU' , 150, 'gas' ),
104 ( 2, 'M3' , 'S65' , 414, 'gas' ),
105 ( 5, 'Prius' , '1NZ-FXE' , 90, 'hybrid' ),
106 ( 7, 'Sandero' , 'H4Bt' , 80, 'gas' );
107
108INSERT INTO cars ( vin , license_plate , miles, made , model_id )
109VALUES ( '1FAFP58S11A177991', 'PK6666AM' , 10000, 2010, 3 ),
110 ( 'JH4DB1641NS802336', 'CO1010BH' , 100000, 2004, 1 ),
111 ( '1G4GD5EDXBF330171', 'PB3089BA' , 60000, 2009, 4 ),
112 ( 'KL5VM52L54B110914', 'C000000' , 300000, 1999, 5 ),
113 ( 'WDBHM36E3VF540750', 'PK9999AH' , 50000, 2003, 1 );
114
115INSERT INTO people ( egn, first_name, surname, last_name, address )
116VALUES ( '9703194444' , 'Georgi' , 'Georgiev' , 'Georgiev', 'Bulgaria,Kostinbrod,Lomsko Shose,5' ),
117 ( '6312194443' , 'James' , 'Daniel' , 'May', 'UK,London,Hammersmith,10' ),
118 ( '9603193333' , 'Valentin' , 'Georgiev' , 'Aleksandrov', 'Bulgaria,Sofia,Tsar Simeon,8' ),
119 ( '9703190000' , 'Ivan' , 'Ivanov' , 'Ivanov', 'Germany,Berlin,Karl-Marx-Allee,45' ),
120 ( '9703191111' , 'Petur' , 'Petrov' , 'Petrov', 'Bulgaria,Plovdiv,Ivan Asen II,17' );
121
122INSERT INTO cars_owners ( car_id, owner_id )
123VAlUES ( 4, 2 ),
124 ( 1, 1),
125 ( 3, 4),
126 ( 3, 5),
127 ( 2, 1),
128 ( 5, 2);
129
130UPDATE cars_owners
131SET active_owner = 0
132WHERE (car_id = 5) OR (car_id = 3 AND owner_id = 4);
133
134INSERT INTO repair_shops ( company_name, address, bulstat )
135VALUES ( 'Ortatsi EOOD', 'Bulgaria,Peturch,Pliska,1', '130255999'),
136 ( 'BMW Serviz Sofia Iztok', 'Bulgaria,Sofia,Mladost,10', '150690420');
137
138INSERT INTO car_repairs ( car_id, shop_id, repair_date, repair_description, repair_cost)
139VALUES ( 2, 1, '2018-04-01', 'Blown Head Gasket repair', 500.00 ),
140 ( 1, 2, '2018-04-03', 'Oil Change', 50.00);
141
142/****** END INSERT DATA ******/
143
144SELECT brand_name AS german_brands
145FROM brands
146WHERE brands.country_id IN( SELECT c.id
147 FROM countries AS c
148 WHERE c.country_name = 'Germany'
149)
150ORDER BY brand_name; /*** SELECT WITH WHERE CLAUSE ***/
151
152SELECT shop_id, SUM(repair_cost) AS total_cost
153FROM car_repairs
154WHERE repair_date BETWEEN '2018-04-01' AND '2018-04-30'
155GROUP BY shop_id
156ORDER BY total_cost DESC; /*** SELECT WITH AGGREGATE FUNCTION AND GROUP BY ***/
157
158SELECT b.brand_name, m.model_name
159FROM brands AS b
160JOIN models AS m
161ON b.id = m.brand_id
162ORDER BY b.brand_name; /*** INNER JOIN ***/
163
164SELECT p.first_name, p.last_name, c.license_plate, c.model_id
165FROM people AS p
166LEFT JOIN cars AS c
167ON c.id IN ( SELECT co.car_id
168 FROM cars_owners AS co
169 WHERE p.id = co.owner_id
170 AND co.active_owner != 0 /*** OUTER JOIN ***/
171);
172
173SELECT p.id, p.first_name, p.last_name, COUNT(c.id) AS total_owner_cars
174FROM people AS p
175JOIN cars AS c
176ON c.id IN ( SELECT co.car_id
177 FROM cars_owners AS co
178 WHERE p.id = co.owner_id
179)
180GROUP BY p.id; /*** JOIN WITH AGGREGATE FUNCTION ***/