-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathschema.sql
More file actions
324 lines (170 loc) · 57.1 KB
/
Copy pathschema.sql
File metadata and controls
324 lines (170 loc) · 57.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
SET SEARCH_PATH TO public, "$user","public";
-- Lock Database
UPDATE databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = 'DESKTOP-072BN2D (192.168.1.12)', LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;
SET SEARCH_PATH TO public, "$user","public";
SET SEARCH_PATH TO public, "$user","public";
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: src/main/resources/db/changelog/db.changelog-master.xml
-- Ran at: 11.06.2025, 23:27
-- Against: admin@jdbc:postgresql://localhost:5432/space_sql
-- Liquibase version: 4.24.0
-- *********************************************************************
-- Changeset src/main/resources/db/changelog/V1.0__initial_schema.xml::1.0::space-sql
SET SEARCH_PATH TO public, "$user","public";
CREATE TABLE lesson (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, topic VARCHAR(100) NOT NULL, difficulty VARCHAR(50) NOT NULL, order_index INTEGER NOT NULL, CONSTRAINT lesson_pkey PRIMARY KEY (id));
CREATE TABLE task (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, lesson_id BIGINT NOT NULL, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, schema_definition TEXT NOT NULL, solution_query TEXT NOT NULL, order_index INTEGER NOT NULL, CONSTRAINT task_pkey PRIMARY KEY (id), CONSTRAINT fk_task_lesson FOREIGN KEY (lesson_id) REFERENCES lesson(id));
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1.0', 'space-sql', 'src/main/resources/db/changelog/V1.0__initial_schema.xml', NOW(), 8, '9:617a5a5ca3b61504568242ca96dc9b0f', 'createTable tableName=lesson; createTable tableName=task', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.1__initial_data.xml::1::space-sql
SET SEARCH_PATH TO public, "$user","public";
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('1', 'Основы SELECT', 'Научитесь извлекать данные из таблиц с помощью базовых запросов SELECT', 'Основы SQL', 'Легкий', '1');
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('2', 'JOIN и отношения', 'Освойте соединение таблиц и работу с реляционными данными', 'Работа с несколькими таблицами', 'Средний', '2');
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('3', 'Фильтрация данных', 'Научитесь точно фильтровать данные с помощью WHERE и HAVING', 'Фильтрация и сортировка', 'Легкий', '3');
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('4', 'Сортировка и агрегация', 'Используйте ORDER BY и агрегатные функции для анализа данных', 'Фильтрация и сортировка', 'Легкий', '4');
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('5', 'Изменение данных', 'Научитесь добавлять, изменять и удалять данные с помощью INSERT, UPDATE, DELETE', 'Модификация данных', 'Средний', '5');
INSERT INTO lesson (id, title, description, topic, difficulty, order_index) VALUES ('6', 'Подзапросы', 'Освойте мощь подзапросов для сложных выборок данных', 'Продвинутые техники', 'Сложный', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор всех данных', 'Выберите все записи из таблицы users', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT, email VARCHAR(100));', 'SELECT * FROM users;', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор конкретных столбцов', 'Выберите только имена и email пользователей', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT, email VARCHAR(100));', 'SELECT name, email FROM users;', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с условием', 'Выберите пользователей старше 25 лет', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT, email VARCHAR(100));', 'SELECT * FROM users WHERE age > 25;', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор уникальных значений', 'Выберите уникальные города из таблицы пользователей', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));', 'SELECT DISTINCT city FROM users;', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с псевдонимами', 'Выберите имена пользователей с псевдонимом ''Имя''', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT name AS ''Имя'' FROM users;', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с вычислениями', 'Выберите имена и возраст пользователей через 5 лет', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT);', 'SELECT name, age + 5 AS age_in_5_years FROM users;', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с несколькими условиями', 'Выберите пользователей из Москвы старше 20 лет', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT, city VARCHAR(100));', 'SELECT * FROM users WHERE city = ''Москва'' AND age > 20;', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с диапазоном', 'Выберите пользователей в возрасте от 20 до 30 лет', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), age INT);', 'SELECT * FROM users WHERE age BETWEEN 20 AND 30;', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с IN', 'Выберите пользователей из Москвы или Санкт-Петербурга', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));', 'SELECT * FROM users WHERE city IN (''Москва'', ''Санкт-Петербург'');', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('1', 'Выбор с LIKE', 'Выберите пользователей, чьи имена начинаются на ''А''', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT * FROM users WHERE name LIKE ''А%'';', '10');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'Простой INNER JOIN', 'Соедините таблицы users и orders', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'LEFT JOIN', 'Выведите всех пользователей, даже если у них нет заказов', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'RIGHT JOIN', 'Выведите все заказы, даже если нет информации о пользователе', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'FULL JOIN', 'Выведите все записи из обеих таблиц', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, o.total FROM users u FULL JOIN orders o ON u.id = o.user_id;', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'Множественные JOIN', 'Соедините 3 таблицы: users, orders и products', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, product_id INT, quantity INT);', 'SELECT u.name, p.name, o.quantity, p.price * o.quantity AS total FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id;', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'SELF JOIN', 'Найдите пары сотрудников, работающих в одном отделе', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100));', 'SELECT a.name AS employee1, b.name AS employee2, a.department FROM employees a JOIN employees b ON a.department = b.department WHERE a.id < b.id;', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'JOIN с агрегацией', 'Найдите общую сумму заказов для каждого пользователя', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, SUM(o.total) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name;', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'JOIN с фильтрацией', 'Найдите пользователей, у которых сумма заказов превышает 1000', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT u.name, SUM(o.total) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING SUM(o.total) > 1000;', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'JOIN с сортировкой', 'Выведите список пользователей с количеством их заказов, отсортированный по убыванию', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT);', 'SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name ORDER BY order_count DESC;', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('2', 'CROSS JOIN', 'Создайте все возможные комбинации цветов и размеров', 'CREATE TABLE colors(color VARCHAR(100)); CREATE TABLE sizes(size VARCHAR(100));', 'SELECT color, size FROM colors CROSS JOIN sizes;', '10');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Простая фильтрация', 'Выберите продукты дороже 1000 рублей', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));', 'SELECT * FROM products WHERE price > 1000;', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация по дате', 'Выберите заказы, сделанные в 2023 году', 'CREATE TABLE orders(id INT PRIMARY KEY, order_date DATE);', 'SELECT * FROM orders WHERE order_date BETWEEN ''2023-01-01'' AND ''2023-12-31'';', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация по нескольким условиям', 'Выберите активных пользователей из Москвы', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100), is_active BOOLEAN);', 'SELECT * FROM users WHERE city = ''Москва'' AND is_active = true;', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с OR', 'Выберите пользователей из Москвы или Санкт-Петербурга', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));', 'SELECT * FROM users WHERE city = ''Москва'' OR city = ''Санкт-Петербург'';', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с NOT', 'Выберите пользователей не из Москвы', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));', 'SELECT * FROM users WHERE NOT city = ''Москва'';', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с LIKE', 'Выберите продукты, название которых содержит ''Pro''', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT * FROM products WHERE name LIKE ''%Pro%'';', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с IN', 'Выберите пользователей с определенными ID', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT * FROM users WHERE id IN (1, 3, 5);', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с NULL', 'Выберите заказы без указанной даты доставки', 'CREATE TABLE orders(id INT PRIMARY KEY, delivery_date DATE);', 'SELECT * FROM orders WHERE delivery_date IS NULL;', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Фильтрация с HAVING', 'Найдите отделы с более чем 5 сотрудниками', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100));', 'SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('3', 'Комплексная фильтрация', 'Выберите активных пользователей из Москвы или Санкт-Петербурга, зарегистрированных в 2023 году', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100), is_active BOOLEAN, registration_date DATE);', 'SELECT * FROM users WHERE is_active = true AND (city = ''Москва'' OR city = ''Санкт-Петербург'') AND registration_date BETWEEN ''2023-01-01'' AND ''2023-12-31'';', '10');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Простая сортировка', 'Отсортируйте пользователей по имени в алфавитном порядке', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT * FROM users ORDER BY name;', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Сортировка по убыванию', 'Отсортируйте продукты по цене от дорогих к дешевым', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));', 'SELECT * FROM products ORDER BY price DESC;', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Множественная сортировка', 'Отсортируйте сотрудников по отделу, а затем по имени', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100));', 'SELECT * FROM employees ORDER BY department, name;', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Подсчет записей', 'Посчитайте количество пользователей', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'SELECT COUNT(*) AS user_count FROM users;', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Группировка', 'Посчитайте количество сотрудников в каждом отделе', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100));', 'SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Среднее значение', 'Найдите среднюю зарплату по компании', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2));', 'SELECT AVG(salary) AS average_salary FROM employees;', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Сумма', 'Найдите общую сумму всех заказов', 'CREATE TABLE orders(id INT PRIMARY KEY, total DECIMAL(10,2));', 'SELECT SUM(total) AS total_sum FROM orders;', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Минимальное и максимальное значение', 'Найдите минимальную и максимальную цену продуктов', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));', 'SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Группировка с сортировкой', 'Найдите отделы с самой высокой средней зарплатой', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10,2));', 'SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC;', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('4', 'Комплексная агрегация', 'Найдите среднюю зарплату по отделам, где работает более 3 сотрудников', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10,2));', 'SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING COUNT(*) > 3 ORDER BY avg_salary DESC;', '10');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Добавление записи', 'Добавьте нового пользователя', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));', 'INSERT INTO users VALUES (1, ''Иван'', ''ivan@example.com'');', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Добавление нескольких записей', 'Добавьте трех новых пользователей', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));', 'INSERT INTO users VALUES (1, ''Иван'', ''ivan@example.com''), (2, ''Мария'', ''maria@example.com''), (3, ''Алексей'', ''alex@example.com'');', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Обновление записи', 'Обновите email пользователя с id=1', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100)); INSERT INTO users VALUES (1, ''Иван'', ''ivan@example.com'');', 'UPDATE users SET email = ''ivan.new@example.com'' WHERE id = 1;', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Обновление нескольких записей', 'Увеличьте зарплату всем сотрудникам IT отдела на 10%', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10,2));', 'UPDATE employees SET salary = salary * 1.1 WHERE department = ''IT'';', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Удаление записи', 'Удалите пользователя с id=5', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100));', 'DELETE FROM users WHERE id = 5;', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Удаление нескольких записей', 'Удалите всех неактивных пользователей', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), is_active BOOLEAN);', 'DELETE FROM users WHERE is_active = false;', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Обновление с подзапросом', 'Увеличьте зарплату сотрудникам, чья зарплата ниже средней', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2));', 'UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees);', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Удаление с JOIN', 'Удалите все заказы пользователя ''Иван''', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT); INSERT INTO users VALUES (1, ''Иван'');', 'DELETE orders FROM orders JOIN users ON orders.user_id = users.id WHERE users.name = ''Иван'';', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Вставка из другой таблицы', 'Скопируйте всех пользователей из таблицы old_users в new_users', 'CREATE TABLE old_users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE new_users(id INT PRIMARY KEY, name VARCHAR(100));', 'INSERT INTO new_users SELECT * FROM old_users;', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('5', 'Комплексное изменение', 'Удалите все заказы старше 2022 года и обновите статус пользователей, у которых нет заказов', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), status VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, order_date DATE);', 'DELETE FROM orders WHERE order_date < ''2023-01-01''; UPDATE users SET status = ''inactive'' WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);', '10');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Простой подзапрос', 'Найдите сотрудников с зарплатой выше средней', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2));', 'SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);', '1');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос в WHERE', 'Найдите продукты дороже среднего', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));', 'SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);', '2');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос в FROM', 'Найдите среднюю зарплату по отделам, где она выше средней по компании', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10,2));', 'SELECT department, avg_salary FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg WHERE avg_salary > (SELECT AVG(salary) FROM employees);', '3');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос в SELECT', 'Для каждого продукта выведите его цену и разницу со средней ценой', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));', 'SELECT name, price, price - (SELECT AVG(price) FROM products) AS difference FROM products;', '4');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос с IN', 'Найдите заказы пользователей из Москвы', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2));', 'SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = ''Москва'');', '5');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос с EXISTS', 'Найдите пользователей, сделавших хотя бы один заказ', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT);', 'SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);', '6');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос с NOT EXISTS', 'Найдите пользователей без заказов', 'CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders(id INT PRIMARY KEY, user_id INT);', 'SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);', '7');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Коррелированный подзапрос', 'Для каждого продукта выведите количество заказов, где он присутствует', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE order_items(order_id INT, product_id INT, quantity INT);', 'SELECT p.name, (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) AS order_count FROM products p;', '8');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Подзапрос в HAVING', 'Найдите отделы, где средняя зарплата выше средней по компании', 'CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10,2));', 'SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);', '9');
INSERT INTO task (lesson_id, title, description, schema_definition, solution_query, order_index) VALUES ('6', 'Множественные подзапросы', 'Найдите продукты, которые дороже среднего и были заказаны более 5 раз', 'CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2)); CREATE TABLE order_items(order_id INT, product_id INT, quantity INT);', 'SELECT p.name FROM products p WHERE p.price > (SELECT AVG(price) FROM products) AND p.id IN (SELECT product_id FROM order_items GROUP BY product_id HAVING SUM(quantity) > 5);', '10');
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'space-sql', 'src/main/resources/db/changelog/V1.1__initial_data.xml', NOW(), 9, '9:3beecfd8b810c7c5b2d9b9146da072f8', 'insert tableName=lesson; insert tableName=lesson; insert tableName=lesson; insert tableName=lesson; insert tableName=lesson; insert tableName=lesson; insert tableName=task; insert tableName=task; insert tableName=task; insert tableName=task; inser...', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.1__add_users_table.xml::1.1::space-sql
SET SEARCH_PATH TO public, "$user","public";
CREATE TABLE users (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, github_username VARCHAR(255) NOT NULL, avatar_url TEXT, progress INTEGER DEFAULT 0 NOT NULL, CONSTRAINT users_pkey PRIMARY KEY (id), UNIQUE (github_username));
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1.1', 'space-sql', 'src/main/resources/db/changelog/V1.1__add_users_table.xml', NOW(), 10, '9:c16d95db6716464cf0be44b7590bbc3a', 'createTable tableName=users', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.1__add_users_table.xml::1.2::space-sql
SET SEARCH_PATH TO public, "$user","public";
INSERT INTO users (github_username, avatar_url, progress) VALUES ('test_user', 'https://example.com/avatar.png', '0');
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1.2', 'space-sql', 'src/main/resources/db/changelog/V1.1__add_users_table.xml', NOW(), 11, '9:ea51e47f7b4ae53c13e919dbaa55ec03', 'insert tableName=users', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.1__add_users_table.xml::1.3::space-sql
SET SEARCH_PATH TO public, "$user","public";
CREATE INDEX idx_users_github_username ON users(github_username);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1.3', 'space-sql', 'src/main/resources/db/changelog/V1.1__add_users_table.xml', NOW(), 12, '9:32d2dc6467d5b8a1510b76a559ae4bdb', 'createIndex indexName=idx_users_github_username, tableName=users', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.2__create_example_data_table.xml::1::space-sql
SET SEARCH_PATH TO public, "$user","public";
CREATE TABLE task_example_data (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, task_id BIGINT NOT NULL, example_data JSONB NOT NULL, created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL, updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL, CONSTRAINT task_example_data_pkey PRIMARY KEY (id), CONSTRAINT fk_example_data_task FOREIGN KEY (task_id) REFERENCES task(id), UNIQUE (task_id));
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'space-sql', 'src/main/resources/db/changelog/V1.2__create_example_data_table.xml', NOW(), 13, '9:7ed003a683735317db9431bce4c9bfd9', 'createTable tableName=task_example_data', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Changeset src/main/resources/db/changelog/V1.3__insert_example_data.xml::1::space-sql
SET SEARCH_PATH TO public, "$user","public";
INSERT INTO task_example_data (task_id, example_data) VALUES ('1', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "age": 25, "email": "oleg@example.com"}, {"id": 2, "name": "Никита", "age": 30, "email": "nikita@example.com"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('2', '{ "type": "simple", "table": "users", "rows": [ {"name": "Олег", "email": "oleg@example.com"}, {"name": "Никита", "email": "nikita@example.com"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('3', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "age": 28, "email": "oleg@example.com"}, {"id": 2, "name": "Никита", "age": 22, "email": "nikita@example.com"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('4', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург"}, {"id": 3, "name": "Анна", "city": "Москва"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('5', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('6', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "age": 25}, {"id": 2, "name": "Никита", "age": 30} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('7', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "age": 26, "city": "Москва"}, {"id": 2, "name": "Никита", "age": 21, "city": "Санкт-Петербург"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('8', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "age": 22}, {"id": 2, "name": "Никита", "age": 28} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('9', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург"}, {"id": 3, "name": "Анна", "city": "Казань"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('10', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Андрей"}, {"id": 2, "name": "Алексей"}, {"id": 3, "name": "Борис"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('11', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00}, {"id": 2, "user_id": 2, "total": 200.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('12', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('13', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00}, {"id": 2, "user_id": null, "total": 200.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('14', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00}, {"id": 2, "user_id": null, "total": 200.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('15', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "product_id": 1, "quantity": 2}, {"id": 2, "user_id": 2, "product_id": 2, "quantity": 1} ], "products": [ {"id": 1, "name": "Лаптоп", "price": 500.00}, {"id": 2, "name": "Телефон", "price": 300.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('16', '{ "type": "join", "tables": { "employees": [ {"id": 1, "name": "Олег", "department": "IT"}, {"id": 2, "name": "Никита", "department": "IT"}, {"id": 3, "name": "Анна", "department": "HR"} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('17', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00}, {"id": 2, "user_id": 1, "total": 200.00}, {"id": 3, "user_id": 2, "total": 150.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('18', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1, "total": 1000.00}, {"id": 2, "user_id": 1, "total": 200.00}, {"id": 3, "user_id": 2, "total": 150.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('19', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1}, {"id": 2, "user_id": 1}, {"id": 3, "user_id": 2} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('20', '{ "type": "join", "tables": { "colors": [ {"color": "Красный"}, {"color": "Синий"} ], "sizes": [ {"size": "S"}, {"size": "M"} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('21', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('22', '{ "type": "simple", "table": "orders", "rows": [ {"id": 1, "order_date": "2023-05-01"}, {"id": 2, "order_date": "2022-12-15"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('23', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва", "is_active": true}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург", "is_active": false} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('24', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург"}, {"id": 3, "name": "Анна", "city": "Казань"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('25', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург"}, {"id": 3, "name": "Анна", "city": "Казань"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('26', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "ProMax Телефон"}, {"id": 2, "name": "Basic Лаптоп"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('27', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"}, {"id": 3, "name": "Анна"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('28', '{ "type": "simple", "table": "orders", "rows": [ {"id": 1, "delivery_date": null}, {"id": 2, "delivery_date": "2023-05-01"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('29', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT"}, {"id": 2, "name": "Никита", "department": "IT"}, {"id": 3, "name": "Анна", "department": "HR"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('30', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "city": "Москва", "is_active": true, "registration_date": "2023-03-01"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург", "is_active": false, "registration_date": "2022-12-15"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('31', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('32', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('33', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT"}, {"id": 2, "name": "Никита", "department": "HR"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('34', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('35', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT"}, {"id": 2, "name": "Никита", "department": "HR"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('36', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "salary": 1200.00}, {"id": 2, "name": "Никита", "salary": 1500.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('37', '{ "type": "simple", "table": "orders", "rows": [ {"id": 1, "total": 100.00}, {"id": 2, "total": 200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('38', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('39', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT", "salary": 1500.00}, {"id": 2, "name": "Никита", "department": "HR", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('40', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT", "salary": 1500.00}, {"id": 2, "name": "Никита", "department": "HR", "salary": 1200.00}, {"id": 3, "name": "Анна", "department": "IT", "salary": 1600.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('41', '{ "type": "simple", "table": "users", "rows": [] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('42', '{ "type": "simple", "table": "users", "rows": [] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('43', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "email": "oleg@example.com"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('44', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT", "salary": 1500.00}, {"id": 2, "name": "Никита", "department": "IT", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('45', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"}, {"id": 5, "name": "Анна"} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('46', '{ "type": "simple", "table": "users", "rows": [ {"id": 1, "name": "Олег", "is_active": true}, {"id": 2, "name": "Никита", "is_active": false} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('47', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "salary": 1500.00}, {"id": 2, "name": "Никита", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('48', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Иван"} ], "orders": [ {"id": 1, "user_id": 1} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('49', '{ "type": "join", "tables": { "old_users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "new_users": [] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('50', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег", "status": "active"}, {"id": 2, "name": "Никита", "status": "inactive"} ], "orders": [ {"id": 1, "user_id": 1, "order_date": "2023-05-01"} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('51', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "salary": 1500.00}, {"id": 2, "name": "Никита", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('52', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('53', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT", "salary": 1500.00}, {"id": 2, "name": "Никита", "department": "HR", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('54', '{ "type": "simple", "table": "products", "rows": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('55', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег", "city": "Москва"}, {"id": 2, "name": "Никита", "city": "Санкт-Петербург"} ], "orders": [ {"id": 1, "user_id": 1, "total": 100.00} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('56', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('57', '{ "type": "join", "tables": { "users": [ {"id": 1, "name": "Олег"}, {"id": 2, "name": "Никита"} ], "orders": [ {"id": 1, "user_id": 1} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('58', '{ "type": "join", "tables": { "products": [ {"id": 1, "name": "Лаптоп"}, {"id": 2, "name": "Телефон"} ], "order_items": [ {"order_id": 1, "product_id": 1, "quantity": 2}, {"order_id": 2, "product_id": 2, "quantity": 1} ] } }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('59', '{ "type": "simple", "table": "employees", "rows": [ {"id": 1, "name": "Олег", "department": "IT", "salary": 1500.00}, {"id": 2, "name": "Никита", "department": "HR", "salary": 1200.00} ] }');
INSERT INTO task_example_data (task_id, example_data) VALUES ('60', '{ "type": "join", "tables": { "products": [ {"id": 1, "name": "Лаптоп", "price": 1200.00}, {"id": 2, "name": "Телефон", "price": 800.00} ], "order_items": [ {"order_id": 1, "product_id": 1, "quantity": 2}, {"order_id": 2, "product_id": 2, "quantity": 1} ] } }');
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'space-sql', 'src/main/resources/db/changelog/V1.3__insert_example_data.xml', NOW(), 14, '9:f0018b6f9bd3f11657afca446463a969', 'insert tableName=task_example_data; insert tableName=task_example_data; insert tableName=task_example_data; insert tableName=task_example_data; insert tableName=task_example_data; insert tableName=task_example_data; insert tableName=task_example_d...', '', 'EXECUTED', NULL, NULL, '4.24.0', NULL);
-- Release Database Lock
SET SEARCH_PATH TO public, "$user","public";
UPDATE databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
SET SEARCH_PATH TO public, "$user","public";