· 9 years ago · Nov 14, 2016, 10:22 PM
1CREATE SCHEMA `kondratenya_laboratory` ;
2CREATE TABLE `kondratenya_laboratory`.`medicines` (
3 `id` INT NOT NULL AUTO_INCREMENT,
4 `name` VARCHAR(45) NOT NULL,
5 `expiration_date` DATE NOT NULL,
6 `description` VARCHAR(200) NULL,
7 `cost` DECIMAL NOT NULL,
8 `storage_id` INT NULL,
9 PRIMARY KEY (`id`));
10
11CREATE TABLE `kondratenya_laboratory`.`storage` (
12 `id` INT NOT NULL AUTO_INCREMENT,
13 PRIMARY KEY (`id`));
14
15ALTER TABLE `kondratenya_laboratory`.`medicines`
16DROP COLUMN `storage_id`;
17
18CREATE TABLE `medicines_storages` (
19 `storage_id` int(11) NOT NULL,
20 `medicines_id` int(11) NOT NULL,
21 KEY `storage_fk_idx` (`storage_id`),
22 KEY `medicines_fk_idx` (`medicines_id`),
23 CONSTRAINT `medicines_fk` FOREIGN KEY (`medicines_id`) REFERENCES `medicines` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
24 CONSTRAINT `storage_fk` FOREIGN KEY (`storage_id`) REFERENCES `storage` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
25) ENGINE=InnoDB DEFAULT CHARSET=utf8;
26
27
28CREATE TABLE `kondratenya_laboratory`.`medicines_analogues` (
29 `medicine_id` INT NOT NULL,
30 `analogue_id` INT NOT NULL,
31 INDEX `medicines_idx` (`medicine_id` ASC),
32 INDEX `analogue_idx` (`analogue_id` ASC),
33 CONSTRAINT `medicines`
34 FOREIGN KEY (`medicine_id`)
35 REFERENCES `kondratenya_laboratory`.`medicines` (`id`)
36 ON DELETE NO ACTION
37 ON UPDATE NO ACTION,
38 CONSTRAINT `analogue`
39 FOREIGN KEY (`analogue_id`)
40 REFERENCES `kondratenya_laboratory`.`medicines` (`id`)
41 ON DELETE NO ACTION
42 ON UPDATE NO ACTION);
43
44
45select m.id, m.name from medicines m
46join medicines_analogues ma on m.id = ma.medicine_id
47join medicines_storages ms on ma.analogue_id = ms.medicines_id
48where ms.storage_id in (select id from storage)
49group by m.id
50having count(distinct ms.storage_id) = (select count(*) from storage)
51
52
53SELECT * FROM medicines m
54left join medicines_analogues ma on m.id = ma.medicine_id
55where ma.analogue_id is null
56
57select m.id, m.name from medicines m
58join medicines_analogues ma on m.id = ma.medicine_id
59join medicines_storages ms on ma.analogue_id = ms.medicines_id
60where ms.storage_id in (select id from storage)
61group by m.id
62having count(distinct ms.storage_id) < (select count(*) from storage)
63
64USE `kondratenya_laboratory`;
65DROP procedure IF EXISTS `replace_storage`;
66
67DELIMITER $$
68USE `kondratenya_laboratory`$$
69CREATE PROCEDURE `replace_storage` (medicine_id INT, storage_id INT)
70BEGIN
71UPDATE medicines_storages ms
72SET
73ms.storage_id = storage_id
74WHERE ms.medicines_id = (select ma.analogue_id
75from medicines_analogues ma
76where ma.medicine_id = medicine_id);
77END$$
78
79
80DELIMITER ;
81
82CALL `kondratenya_laboratory`.`replace_storage`(3, 1);
83
84select * from medicines m
85join medicines_analogues ma on m.id = ma.medicine_id
86join medicines_storages ms on ma.medicine_id = ms.medicines_id
87group by ma.analogue_id,
88(select storage_id from(
89select s.id as storage_id, max(sc.med_count) as medicines_count from storage s join
90(SELECT storage_id, COUNT(*) as med_count FROM medicines_storages GROUP BY storage_id)
91 as sc
92on sc.storage_id = s.id) as max_storage);
93
94select * from medicines m
95join medicines_analogues ma on m.id = ma.medicine_id
96join medicines_storages ms on ma.medicine_id = ms.medicines_id
97group by ma.analogue_id,
98(select * from(
99select max(sc.med_count)from
100(SELECT storage_id, COUNT(*) as med_count FROM medicines_storages GROUP BY storage_id)
101as sc) as max_storage);
102
103
104select mt. name, avg(mt.cost) from (
105select m.name as name, am.cost as cost from medicines m
106join medicines_analogues ma on m.id = ma.medicine_id
107join medicines am on am.id = ma.analogue_id where m.id = 2 ) as mt;
108
109select m.id, m.name, ma.analogue_id, ms.storage_id, max(sc.med_count) as storage_count from medicines m
110join medicines_analogues ma on m.id = ma.medicine_id
111join medicines_storages ms on ma.medicine_id = ms.medicines_id
112join (SELECT storage_id, COUNT(*) as med_count FROM medicines_storages GROUP BY storage_id)
113as sc on sc.storage_id = ms.storage_id
114group by ma.analogue_id