nQM3V0r7

1create table if not exists categories
2(
3id int(3) auto_increment primary key,
4category varchar(50),
5daily_rate int(3), 
6weekly_rate int(3),
7monthly_rate int(3),
8weekend_rate int(3)
9);
10
11insert into categories values 
12(1, 'alabala', 3,5,3,5),
13(2, 'blabla', 3,4,1,6),
14(3, 'ldkfj', 2,9,2,8);
15
16
17create table if not exists cars(
18id int(3) auto_increment primary key,
19plate_number int(10), 
20make varchar(20),
21model varchar(20), 
22car_year year, 
23category_id int(3), 
24doors int(1), 
25picture blob, 
26`condition` varchar(20), 
27available bool
28);
29
30alter table cars
31add CONSTRAINT  fk_cars_category foreign key (category_id) references categories(id);
32
33insert into cars values 
34(1, 21093, 'asjd', 'aljsdasdl;', '1999', 3,3,null, 'good', false),
35(2, 5432423, 'gdhgsdd', 'hfdsda;', '2000', 2,5,null, 'good', false),
36(3, 12345, 'hgfsd', 'ksdhhsdsd;', '2001', 1,5,null, 'good', false);
37
38create table if not exists employees
39(
40id int(3) auto_increment primary key,
41first_name varchar(20), 
42last_name varchar(20), 
43title varchar(20), 
44notes blob
45);
46
47insert into employees values 
48(1, 'kjashd', 'gfkaslj', 'aljsdasdl;', null),
49(2, 'lkasdgh', 'asdgfd', 'hfdsda;', null),
50(3, 'fglkfdjlkfd', 'hgfsd', 'ksdhhsdsd;', null);
51
52create table if not exists customers
53(
54id int(3) auto_increment primary key, 
55driver_licence_number int(10), 
56full_name varchar(20), 
57address varchar(50), 
58city varchar(20), 
59`zip-code` int (10), 
60notes blob
61);
62
63insert into customers values 
64(1, 32094832, 'lkjda', 'aslkjdlksjd', 'rewsd', 1230, null),
65(2, 2133128, 'gfdsl;k', 'wqeytro', 'gfdas', 5040, null),
66(3, 4980912, 'mbdsad', 'jdfs;dlk', 'asdgd', 2354, null);
67
68create table if not exists rental_orders
69(
70id int(3) auto_increment primary key, 
71employee_id int(3), 
72customer_id int(3), 
73car_id int(3), 
74car_condition varchar(20), 
75tank_level int (3), 
76kilometrage_start int(6), 
77kilometrage_end int(6), 
78total_kilometrage int(6),
79start_date date, 
80end_date date, 
81total_days int (5), 
82rate_applied varchar(20), 
83tax_rate int(5), 
84order_status varchar(20), 
85notes blob
86);
87
88alter table rental_orders
89add CONSTRAINT  fk_rental_employee foreign key (employee_id) references employees(id);
90
91alter table rental_orders
92add CONSTRAINT  fk_rental_customer foreign key (customer_id) references customers(id);
93
94alter table rental_orders
95add CONSTRAINT  fk_rental_car foreign key (car_id) references cars(id);
96
97insert into rental_orders values 
98(1, 1,2,1, 'dsfkj', 23,1231,12432,3218,'1999-05-20', '2000-04-21', 328,'sadasda', 2198, 'asldkj',null),
99(2, 2,3,1, 'gfdass', 54,4523,9832,7643,'2000-05-20', '2000-04-21', 328,'sadasda', 2198, 'asldkj',null),
100(3, 3,2,1, 'bddas', 85,4342,6432,65432,'2001-05-20', '2000-04-21', 328,'sadasda', 2198, 'asldkj',null);