-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_db.sql
40 lines (33 loc) · 1.43 KB
/
create_db.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# drop schema if exist
DROP SCHEMA IF EXISTS car_market;
# create and use car_market schema
CREATE SCHEMA car_market;
Use car_market;
# create fuels table
create table car_market.fuels
(
name varchar(100) primary key
);
# create cars table
create table car_market.cars
(
id int not null auto_increment primary key,
title varchar(200) not null,
fuel varchar(100) not null,
price int not null,
is_new boolean not null,
mileage int,
first_reg date,
constraint cars_fuel_fk foreign key (fuel) references car_market.fuels (name)
);
# insert some fuels into fuels table
INSERT INTO car_market.fuels(name) VALUES("gasoline");
INSERT INTO car_market.fuels(name) VALUES("diesel");
# insert some new cars into cars table
INSERT INTO car_market.cars(title, fuel, price, is_new) VALUES("AUDI A4","gasoline",10000,true);
INSERT INTO car_market.cars(title, fuel, price, is_new) VALUES("AUDI A5","gasoline",15000,true);
INSERT INTO car_market.cars(title, fuel, price, is_new) VALUES("AUDI R8","diesel",40000,true);
# insert some used cars into cars table
INSERT INTO car_market.cars(title, fuel, price, is_new, mileage, first_reg) VALUES("BMW Z4","diesel",8000,false, 100000, "2010-01-01");
INSERT INTO car_market.cars(title, fuel, price, is_new, mileage, first_reg) VALUES("BMW X3","diesel",28000,false, 150000, "2008-01-01");
INSERT INTO car_market.cars(title, fuel, price, is_new, mileage, first_reg) VALUES("BMW 520","diesel",22000,false, 80000, "2013-01-01");