SQL DZ SolorLab

# Создаем таблицу1
CREATE TABLE competition(Id INTEGER PRIMARY KEY, competition_id INTEGER, competition_name text, world_record INTEGER, sportsman text);
# Вставляем данные
INSERT INTO competition VALUES(1, 1, 'бег на 100 метров', 30, 'Aaaa name');
INSERT INTO competition VALUES(2, 1, 'бег на 800 метров', 240, 'Bbbb name');
INSERT INTO competition VALUES(3, 1, 'бег на 5000 метров', 1500, 'Cccc name');
INSERT INTO competition VALUES(4, 2, 'прыжки в высоту', 3, 'Dddd name');
INSERT INTO competition VALUES(5, 2, 'прыжок с шестом', 5, 'Gggg name');
INSERT INTO competition VALUES(6, 1, 'метание диска', 50, 'Hhhh name');
INSERT INTO competition VALUES(7, 1, 'метание молота', 40, 'Www name');
INSERT INTO competition VALUES(8, 3, 'толкание ядра', 20, 'Qqqq name');
INSERT INTO competition VALUES(9, 3, 'плавание на спине', 50, 'Rrrr name');
INSERT INTO competition VALUES(10, 3, 'самый быстрый гол (футбол)', 2, 'Pppp name');
# Обновляем
UPDATE  competition SET world_record = '31'  WHERE world_record = 30;
# Другое
SELECT MIN(world_record) FROM competition;
SELECT MAX(world_record) FROM competition;
SELECT COUNT(*) FROM competition WHERE competition_id!='';
SELECT * FROM competition WHERE competition_id='1' ORDER BY competition_name DESC;
# Удаляем лишнее
DELETE FROM competition WHERE competition_name = 'бег на 100 метров';

# Создаем таблицу2
CREATE TABLE competition_details(Id INTEGER PRIMARY KEY, competition_id INTEGER, competition_name text, city text, set_date text);

# Вставляем строки в т2
INSERT INTO competition_details VALUES(1, 1, 'бег на 100 метров', 'Lon', '01.02.2000');
INSERT INTO competition_details VALUES(2, 1, 'бег на 800 метров', 'Mos', '02.02.2000');
INSERT INTO competition_details VALUES(3, 1, 'бег на 5000 метров', 'Ast', '03.02.2000');
INSERT INTO competition_details VALUES(4, 2, 'прыжки в высоту', 'Am', '04.02.2000');
INSERT INTO competition_details VALUES(5, 2, 'прыжок с шестом', 'Br', '05.02.2000');
INSERT INTO competition_details VALUES(6, 1, 'метание диска', 'Kan', '06.02.2000');
INSERT INTO competition_details VALUES(7, 1, 'метание молота', 'Chi', '07.02.2000');
INSERT INTO competition_details VALUES(8, 3, 'толкание ядра', 'Rio', '08.02.2000');
INSERT INTO competition_details VALUES(9, 3, 'плавание на спине', 'Bar', '09.02.2000');
INSERT INTO competition_details VALUES(10, 3, 'самый быстрый гол (футбол)', 'Kar', '10.02.2000');

# Объединение таблиц
SELECT competition.competition_name, competition.sportsman, competition_details.city, competition_details.set_date
FROM competition
INNER JOIN competition_details
ON competition.id = competition_details.id;