8Se1Za74

1create database if not exists `components_shop`;
2use `components_shop`;
3
4create table if not exists `users`(
5	id int auto_increment primary key,
6    username varchar(50) not null,     
7    password varchar(15) not null,
8    type enum('user', 'repairman')
9);
10
11create table if not exists `devices_repair` (
12	id int auto_increment primary key,
13    owner_id int not null,
14    repairman_id int not null,
15    price double not null,
16	accepted timestamp not null,
17	status boolean not null,
18	constraint foreign key (owner_id) references users(id) on delete cascade on update cascade,
19    constraint foreign key (repairman_id) references users(id) on delete cascade on update cascade
20);
21
22create table if not exists `spare_parts` (
23	id int auto_increment primary key,
24    name varchar(40) not null,
25	price double not null
26);
27
28create table if not exists `device_parts` (
29    id int auto_increment primary key,
30    spare_part_id int not null,
31    device_repair_id int not null,
32    constraint foreign key (spare_part_id) references spare_parts(id) on delete cascade on update cascade,
33    constraint foreign key (device_repair_id) references devices_repair(id) on delete cascade on update cascade
34);
35
36#populating database
37INSERT INTO `users` (`username`, `password`, `type`) VALUES ('SkullMoster', 'p7drwlkqqg', 'user');
38INSERT INTO `users` (`username`, `password`, `type`) VALUES ('asmongold', 'fdkylozlo7', 'user');
39INSERT INTO `users` (`username`, `password`, `type`) VALUES ('ofaStrOl', 'wsyxu2dpkj', 'repairman');
40INSERT INTO `users` (`username`, `password`, `type`) VALUES ('AlCrEntI', 'wsyxu2dpkj', 'repairman');
41INSERT INTO `users` (`username`, `password`, `type`) VALUES ('IumPtion', 'y999dd1851', 'repairman');
42
43INSERT INTO `devices_repair` (`owner_id`, `repairman_id`, `price`, `accepted`, `status`) VALUES (1, 4, 400, current_timestamp(), 1);
44INSERT INTO `devices_repair` (`owner_id`, `repairman_id`, `price`, `accepted`, `status`) VALUES (2, 3, 1000, current_timestamp(), 0);
45INSERT INTO `devices_repair` (`owner_id`, `repairman_id`, `price`, `accepted`, `status`) VALUES (1, 5, 100, current_timestamp(), 0);
46
47INSERT INTO `spare_parts` (`name`, `price`) VALUES ('Iphone 12 pro display', 200.00);
48INSERT INTO `spare_parts` (`name`, `price`) VALUES ('Iphone 8 motherboard', 500.00);
49INSERT INTO `spare_parts` (`name`, `price`) VALUES ('Iphone 8 microphone', 100.00);
50INSERT INTO `spare_parts` (`name`, `price`) VALUES ('Samsung S20 charging port', 250.00);
51INSERT INTO `spare_parts` (`name`, `price`) VALUES ('Samsung S20 display', 400.00);
52
53INSERT INTO `device_parts` (`spare_part_id`, `device_repair_id`) VALUES (2, 1);
54INSERT INTO `device_parts` (`spare_part_id`, `device_repair_id`) VALUES (1, 1);
55INSERT INTO `device_parts` (`spare_part_id`, `device_repair_id`) VALUES (3, 1);
56INSERT INTO `device_parts` (`spare_part_id`, `device_repair_id`) VALUES (3, 3);
57INSERT INTO `device_parts` (`spare_part_id`, `device_repair_id`) VALUES (4, 3);
58
59#Group By and Join
60SELECT users.*
61FROM `devices_repair` 
62JOIN users
63ON `devices_repair`.owner_id = users.id
64GROUP BY users.id;
65
66#Aggregate function most expensive repair part 
67SELECT MAX(spare_parts.price) as 'max_part_price' 
68FROM `spare_parts`;
69
70#Inner Join
71SELECT * FROM `devices_repair`
72JOIN users
73ON `devices_repair`.owner_id = users.id;
74
75#Outer Join - righ join
76SELECT * FROM `devices_repair`
77RIGHT JOIN users
78ON `devices_repair`.owner_id = users.id;
79
80#Aggrate function and Join
81SELECT MAX(parts.price)
82FROM `device_parts` 
83JOIN `spare_parts` as parts
84ON `device_parts`.spare_part_id = parts.id
85GROUP BY `device_parts`.`device_repair_id`;
86
87