STm6icev

1DROP SCHEMA IF EXISTS INSTITUTE CASCADE;
2CREATE SCHEMA INSTITUTE;
3
4CREATE TABLE INSTITUTE.SCIENTIST (
5  SCIENTIST_ID     INT NOT NULL PRIMARY KEY,
6    SCIENTIST_LAB_ID INT NOT NULL,
7  SCIENTIST_FIO    VARCHAR(200),
8  SCIENTIST_POST   VARCHAR(200),
9  SCIENTIST_DEGREE VARCHAR(200)
10);
11
12CREATE TABLE INSTITUTE.LABORATORY (
13  LAB_ID          INT NOT NULL PRIMARY KEY,
14  LAB_HEAD_ID     INT REFERENCES INSTITUTE.SCIENTIST (SCIENTIST_ID),
15  LAB_WORKERS_CNT INT CHECK (LAB_WORKERS_CNT >= 0)
16);
17
18ALTER TABLE INSTITUTE.SCIENTIST
19  ADD CONSTRAINT SCIENTIST_LAB_ID FOREIGN KEY (SCIENTIST_LAB_ID) REFERENCES INSTITUTE.LABORATORY (LAB_ID);
20
21CREATE TABLE INSTITUTE.EQUIPMENT (
22  EQUIPMENT_ID    INT NOT NULL PRIMARY KEY,
23  EQUIPMENT_NM    VARCHAR(200),
24  EQUIPMENT_PRICE INT CHECK (EQUIPMENT_PRICE >= 0)
25);
26
27CREATE TABLE INSTITUTE.EQUIPMENT_X_LAB (
28  EQUIPMENT_ID      INT NOT NULL REFERENCES INSTITUTE.EQUIPMENT (EQUIPMENT_ID),
29  EQUIPMENT_LAB_ID  INT REFERENCES INSTITUTE.LABORATORY (LAB_ID),
30  EQUIPMENT_LAB_CNT INT CHECK (EQUIPMENT_LAB_CNT >= 0)
31);
32
33CREATE TABLE INSTITUTE.RESEARCH (
34  RESEARCH_ID        INT NOT NULL PRIMARY KEY,
35  RESEARCH_NM        VARCHAR(200),
36  RESEARCH_START_DT  DATE,
37  RESEARCH_FINISH_DT DATE,
38  RESEARCH_BUDGET    INT CHECK (RESEARCH_BUDGET >= 0)
39);
40
41CREATE TABLE INSTITUTE.SPONSOR (
42  SPONSOR_ID      INT NOT NULL PRIMARY KEY,
43  SPONSOR_NM      VARCHAR(200),
44  SPONSOR_COUNTRY VARCHAR(200)
45);
46
47CREATE TABLE INSTITUTE.RESEARCH_X_LAB (
48  RESEARCH_ID     INT NOT NULL REFERENCES INSTITUTE.RESEARCH (RESEARCH_ID),
49  RESEARCH_LAB_ID INT REFERENCES INSTITUTE.LABORATORY (LAB_ID),
50    PRIMARY KEY (RESEARCH_ID, RESEARCH_LAB_ID)
51);
52
53CREATE TABLE INSTITUTE.RESEARCH_X_SPONSOR (
54  RESEARCH_ID         INT NOT NULL REFERENCES INSTITUTE.RESEARCH (RESEARCH_ID),
55  RESEARCH_SPONSOR_ID INT REFERENCES INSTITUTE.SPONSOR (SPONSOR_ID),
56    PRIMARY KEY (RESEARCH_ID, RESEARCH_SPONSOR_ID)
57);
58
59INSERT INTO INSTITUTE.LABORATORY (LAB_ID, LAB_WORKERS_CNT) VALUES
60  (13, 4),
61  (17, 6),
62  (19, 5);
63INSERT INTO INSTITUTE.SCIENTIST (SCIENTIST_ID, SCIENTIST_LAB_ID, SCIENTIST_FIO, SCIENTIST_POST, SCIENTIST_DEGREE)
64VALUES
65  (777, 13, 'Соломатин Григорий Алексеевич', 'Младший научный сотрудник', 'Кандидат наук'),
66  (333, 13, 'Васильев Павел Константинович', 'Руководитель', 'Доктор наук'),
67  (666, 13, 'Потапов Иван Олегович', 'Лаборант', NULL),
68  (505, 13, 'Михайлов Михаил Анатольевич', 'Научный сотрудник', 'Кандидат наук'),
69  (234, 17, 'Расторгуев Леонид Ильич', 'Младший научный сотрудник', 'Кандидат наук'),
70  (123, 17, 'Прокофьев Роман Андреевич', 'Научный сотрудник', 'Кандидат наук'),
71  (212, 17, 'Стукалов Степан Аркадбевич', 'Старший научный сотрудник', 'Доктор наук'),
72  (256, 17, 'Молчанов Григорий Иванович', 'Старший научный сотрудник', 'Доктор наук'),
73  (512, 17, 'Ситников Алексей Алексеевич', 'Руководитель', 'Доктор наук'),
74  (128, 17, 'Антонов Борис Иванович', 'Лаборант', NULL),
75  (931, 19, 'Ройтберг Кирилл Савельевич', 'Младший научный сотрудник', 'Кандидат наук'),
76  (890, 19, 'Зотов Иван Игоревич', 'Научный сотрудник', 'Доктор наук'),
77  (591, 19, 'Рыбников Павел Анатольевич', 'Руководитель', 'Доктор наук'),
78  (101, 19, 'Чубаров Евгений Степанович', 'Научный работник', 'Кандидат наук'),
79  (813, 19, 'Красов Федор Иванович', 'Лаборант', NULL);
80UPDATE INSTITUTE.LABORATORY
81SET LAB_HEAD_ID = (SELECT SCIENTIST_ID
82                   FROM INSTITUTE.SCIENTIST
83                   WHERE SCIENTIST_POST = 'Руководитель' AND SCIENTIST_LAB_ID = LAB_ID);
84INSERT INTO INSTITUTE.EQUIPMENT VALUES
85  (1024, 'Спектрограф', 1000),
86  (1100, 'Ленточный смеситель', 1000),
87  (1200, 'V-образный смеситель', 2000),
88  (1300, 'Кристаллизатор V115U', 1500),
89  (1400, 'Кристаллизатор V116U', 2000),
90  (1500, 'Кристаллизатор A110', 1300),
91  (1600, 'Фильтр B770-210', 900),
92  (1700, 'Автоматический реактор', 10000),
93  (1800, 'Фильтр K330-100', 1000),
94  (1900, 'Фильтр P680-230', 1500),
95  (1999, 'Центрифуга', 3000);
96INSERT INTO INSTITUTE.SPONSOR VALUES
97  (9999, 'РАН', 'Россия'),
98  (8888, 'Клуб анонимных химиков', 'Россия'),
99  (7777, 'Chemistry - science of 21th centuary', 'USA'),
100  (6666, 'Chemistry volunteers', 'Germany'),
101  (5555, 'Life is science', 'USA'),
102  (4444, 'Funny chemistry', 'USA');
103INSERT INTO INSTITUTE.EQUIPMENT_X_LAB VALUES
104  (1024, 13, 1),
105  (1024, 17, 1),
106  (1100, 13, 1),
107  (1100, 19, 1),
108  (1200, 17, 1),
109  (1300, 13, 1),
110  (1400, 17, 1),
111  (1500, 19, 1),
112  (1600, 13, 3),
113  (1800, 13, 1),
114  (1800, 19, 2),
115  (1800, 17, 2),
116  (1900, 19, 1),
117  (1999, 17, 1);
118INSERT INTO INSTITUTE.RESEARCH VALUES
119  (1, 'Кинетика реакций с участием благородных газов', '1998-10-03', NULL, 10),
120  (2, 'Влияние галогенов на реакции окисления', '1995-11-16', '2024-03-06', 12),
121  (3, 'Скорость протекания реакций расщепления углеводоров', '2002-12-12', '2019-01-18', 10),
122  (4, 'Коррозия металлов в условиях естественной стреды', '2016-01-23', NULL, 5),
123  (5, 'Методы получения спирта из побочных продуктов реакций брожения', '1993-01-05', NULL, 10000),
124  (6, 'Исследование зависимости прочности материала от его состава', '2002-09-19', '2012-10-23', 15);
125INSERT INTO INSTITUTE.RESEARCH_X_LAB VALUES
126  (1, 13),
127  (2, 13),
128  (3, 19),
129  (4, 17),
130  (5, 13),
131  (5, 17),
132  (5, 19),
133  (6, 19),
134  (6, 17);
135INSERT INTO INSTITUTE.RESEARCH_X_SPONSOR VALUES
136  (1, 9999),
137  (2, 7777),
138  (3, 6666),
139  (4, 4444),
140  (5, 8888),
141  (6, 5555),
142  (6, 9999);
143
144SELECT *
145  FROM INSTITUTE.EQUIPMENT;
146
147SELECT *
148  FROM INSTITUTE.LABORATORY;
149
150SELECT SCIENTIST_FIO
151  FROM INSTITUTE.SCIENTIST
152 WHERE SCIENTIST_POST = 'Лаборант';
153
154SELECT RESEARCH_START_DT, RESEARCH_FINISH_DT
155  FROM INSTITUTE.RESEARCH
156 WHERE RESEARCH_BUDGET >= 10;
157
158UPDATE INSTITUTE.SCIENTIST
159   SET SCIENTIST_POST = 'Лаборант'
160 WHERE SCIENTIST_DEGREE IS NULL;
161
162DELETE
163  FROM INSTITUTE.RESEARCH
164 WHERE RESEARCH_BUDGET = 0;
165
166SELECT RESEARCH_ID
167  FROM INSTITUTE.RESEARCH_X_SPONSOR
168       INNER JOIN INSTITUTE.SPONSOR
169	             ON INSTITUTE.RESEARCH_X_SPONSOR.RESEARCH_SPONSOR_ID = INSTITUTE.SPONSOR.SPONSOR_ID AND INSTITUTE.SPONSOR.SPONSOR_COUNTRY = 'Россия';
170
171SELECT EQUIPMENT_NM
172  FROM INSTITUTE.EQUIPMENT
173 WHERE EQUIPMENT_PRICE <= 1000;
174
175DELETE
176  FROM INSTITUTE.SCIENTIST
177 WHERE SCIENTIST_DEGREE IS NULL;
178
179SELECT LAB_WORKERS_CNT
180FROM INSTITUTE.LABORATORY
181INNER JOIN INSTITUTE.SCIENTIST
182	ON INSTITUTE.LABORATORY.LAB_HEAD_ID = INSTITUTE.SCIENTIST.SCIENTIST_ID AND INSTITUTE.SCIENTIST.SCIENTIST_DEGREE = 'Доктор наук';