· 6 years ago · Jun 10, 2019, 10:32 PM
1-- -----------------------------------------------------
2-- Schema repairstore
3-- -----------------------------------------------------
4CREATE SCHEMA IF NOT EXISTS `repairstore` DEFAULT CHARACTER SET utf8 ;
5USE `repairstore` ;
6
7-- -----------------------------------------------------
8-- Table `repairstore`.`technicians`
9-- -----------------------------------------------------
10CREATE TABLE IF NOT EXISTS `repairstore`.`technicians` (
11 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
12 `name` VARCHAR(255) NOT NULL,
13 PRIMARY KEY (`id`),
14 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
15ENGINE = InnoDB;
16
17
18-- -----------------------------------------------------
19-- Table `repairstore`.`parts`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `repairstore`.`parts` (
22 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
23 `partno` VARCHAR(255) NOT NULL,
24 `price` DECIMAL(8,2) NOT NULL,
25 PRIMARY KEY (`id`),
26 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `repairstore`.`repairs`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `repairstore`.`repairs` (
34 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
35 `technician_id` INT UNSIGNED NOT NULL,
36 `name` VARCHAR(255) NOT NULL,
37 `device` VARCHAR(255) NOT NULL,
38 `final_report` MEDIUMTEXT NULL,
39 `price` DECIMAL(8,2) NULL DEFAULT 0,
40 `status` ENUM('PENDING', 'IN_PROGRESS', 'DONE') NULL DEFAULT 'PENDING',
41 `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
42 `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
43 PRIMARY KEY (`id`, `technician_id`),
44 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
45 INDEX `fk_repairs_technicians_idx` (`technician_id` ASC) VISIBLE,
46 CONSTRAINT `fk_repairs_technicians`
47 FOREIGN KEY (`technician_id`)
48 REFERENCES `repairstore`.`technicians` (`id`)
49 ON DELETE NO ACTION
50 ON UPDATE NO ACTION)
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `repairstore`.`repair_parts`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `repairstore`.`repair_parts` (
58 `repair_id` INT UNSIGNED NOT NULL,
59 `part_id` INT UNSIGNED NOT NULL,
60 PRIMARY KEY (`repair_id`, `part_id`),
61 INDEX `fk_repair_parts_parts1_idx` (`part_id` ASC) VISIBLE,
62 CONSTRAINT `fk_repair_parts_repairs1`
63 FOREIGN KEY (`repair_id`)
64 REFERENCES `repairstore`.`repairs` (`id`)
65 ON DELETE NO ACTION
66 ON UPDATE NO ACTION,
67 CONSTRAINT `fk_repair_parts_parts1`
68 FOREIGN KEY (`part_id`)
69 REFERENCES `repairstore`.`parts` (`id`)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION)
72ENGINE = InnoDB;
73
74
75SET SQL_MODE=@OLD_SQL_MODE;
76SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
77SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
78
79-- -----------------------------------------------------
80-- Data for table `repairstore`.`technicians`
81-- -----------------------------------------------------
82START TRANSACTION;
83USE `repairstore`;
84INSERT INTO `repairstore`.`technicians` (`id`, `name`) VALUES (1, 'John Doe');
85INSERT INTO `repairstore`.`technicians` (`id`, `name`) VALUES (2, 'Wiz Shannon');
86
87COMMIT;
88
89
90-- -----------------------------------------------------
91-- Data for table `repairstore`.`parts`
92-- -----------------------------------------------------
93START TRANSACTION;
94USE `repairstore`;
95INSERT INTO `repairstore`.`parts` (`id`, `partno`, `price`) VALUES (1, 'CPU1', 199.99);
96INSERT INTO `repairstore`.`parts` (`id`, `partno`, `price`) VALUES (2, 'CPU2', 179.99);
97
98COMMIT;
99
100
101-- -----------------------------------------------------
102-- Data for table `repairstore`.`repairs`
103-- -----------------------------------------------------
104START TRANSACTION;
105USE `repairstore`;
106INSERT INTO `repairstore`.`repairs` (`id`, `technician_id`, `name`, `device`, `final_report`, `price`, `status`, `created_at`, `updated_at`) VALUES (1, 1, 'Jack Sparrow', 'ThinkPad T61', NULL, 199.99, 'IN_PROGRESS', NULL, NULL);
107INSERT INTO `repairstore`.`repairs` (`id`, `technician_id`, `name`, `device`, `final_report`, `price`, `status`, `created_at`, `updated_at`) VALUES (2, 1, 'John Snow', 'SnowMachine2000', 'Doesn\'t work anymore. However, you can buy another one from us.', 19.99, 'DONE', NULL, NULL);
108
109COMMIT;
110
111
112-- -----------------------------------------------------
113-- Data for table `repairstore`.`repair_parts`
114-- -----------------------------------------------------
115START TRANSACTION;
116USE `repairstore`;
117INSERT INTO `repairstore`.`repair_parts` (`repair_id`, `part_id`) VALUES (1, 1);
118
119COMMIT;
120
121/* ----- 2 ----- */
122select *
123from `repairs`
124where `status` = 'DONE';
125
126/* ----- 3 ----- */
127select `technician_id`, COUNT(*) as `repair_count`
128from `repairs`
129group by `technician_id`;
130
131/* ----- 4 ----- */
132select `t`.`name` as `technician`, `r`.`device` as `device`, `p`.`partno` as `partno`
133from `repairs` `r`
134left outer join `technicians` `t` on `t`.`id` = `r`.`technician_id`
135inner join `parts` `p` on `p`.`id` in (select `part_id` from `repair_parts` where `repair_id` = `r`.`id`);
136
137/* ----- 5 ----- */
138select `r`.`device` as `device`, COUNT(*) as `part_count`
139from `repairs` `r`
140join `parts` `p` on `p`.`id` in (select `part_id` from `repair_parts` where `repair_id` = `r`.`id`)
141group by `r`.`id`;