Skip to content

Instantly share code, notes, and snippets.

@mfd

mfd/sql.md

Last active March 11, 2023 17:39
Show Gist options
  • Save mfd/7b409255eed4b2526988897819ef1aa7 to your computer and use it in GitHub Desktop.
Save mfd/7b409255eed4b2526988897819ef1aa7 to your computer and use it in GitHub Desktop.
Download ZIP
SQL course
Raw
sql.md

Курс «Основы структурированного языка запросов SQL»

Решение практических задач

Lesson 1 - Выбор данных с использованием оператора SELECT

/*
1. Выбор данных с использованием оператора SELECT
    a.	Выбрать все месторождения таблица DW_OILFIELD, все скважины таблица DW_WELLS; отобразить названия столбцов на русском языке.
    b.	Найти все различные коды месторождений OILFIELD_ID по которым есть скважины (таблица DW_WELLS);
    c.	Вывести информацию о МЭР (DW_MTH_OP_RAP) отобразив любые две колонки, например нефть, воду и их сумму в третьей отдельной колонке.
*/
--1.a
select * from KIN_UNG.dw_oilfield;
select * from KIN_UNG.dw_wells;
select oilfield_name as "Название месторождения" from KIN_UNG.dw_oilfield;

-- 1.b
select distinct oilfield_id from kin_ung.dw_wells;

-- 1.c.
select well_id, OIL_M3, WAT_LIQ_INJ_m3, OIL_M3 + WAT_LIQ_INJ_M3 as "Сумма в м3" from kin_ung.DW_MTH_OP_RAP where OIL_T> 0;

Lesson 2 - Ограничение и сортировка данных

/*
2. Ограничение и сортировка данных
    a.	Найти последний день, в который были ошибки в журнале загрузки данных в РН-КИН UT_SA2DW_LOG;
    b.	Найти таблицы в схеме имеющие комментарии, содержащие слово «проект» (all_tab_comments);
    c.	Найти столбцы имеющие комментарии, содержащие фрагмент слов «коорд» или «X» в названии (all_col_comments);
    d.	Определить последний патч, примененный на регионе UT_DB_PATCH_VERSION.
*/

--2.a
select *
from KIN_UNG.UT_SA2DW_LOG
where MSG_TYPE LIKE 'E%'
order by created_on desc;

-- 2.b
select * from all_tab_comments where owner = 'KIN_UNG' and table_name like 'DW%' and comments like '%проект%';

-- 2.c
select * from all_tab_comments where comments like '%коорд%' or comments  like '%X%';
select * from all_tab_comments where (comments like '%коорд%' or comments  like '%X%') and owner = 'KIN_UNG';
-- скобки используются для порядка выполнения, сначала  ищем KIN_UNG, потом остальное
-- см. еще правила приоритета

-- 2.d
select * from KIN_UNG.ut_db_patch_version order by 1 desc;
select * from KIN_UNG.ut_db_patch_version where order by 1 desc;

Lesson 3 - Использование строковых функций, и преобразований

/*
3. Использование строковых функций, и преобразований
    a.	Вывести названия месторождений в верхнем регистре;
    b.	Показать дату ошибки из UT_SA2DW_LOG или иную дату с точностью до секунд; 
    c.	Вывести все строки, содержащие информацию об ошибках за этот день (используя синтаксис вида to_date('01-02-2021', 'DD-MM-YYYY'));
*/

-- 3a
select UPPER(OILFIELD_NAME_FULL) as "Месторождение", INITCAP(OILFIELD_NAME_FULL) as "Месторождение" from KIN_UNG.dw_oilfield;

-- 3.b
    -- L псевдоним таблицы
select msg_type, to_char(created_on, 'DD-MM-YYYY HH24:MI:SS') as "Дата ошибки", L.* from KIN_UNG.UT_SA2DW_LOG L where msg_type = 'ERR' order by created_on DESC;
select * from KIN_UNG.UT_SA2DW_LOG where MSG_TYPE = 'ERR';

-- 3.c
select msg_type, to_char(created_on, 'DD-MM-YYYY HH24:MI:SS'), L.* from KIN_UNG.UT_SA2DW_LOG L
where trunc(L.created_on, 'DD') = to_date('01-02-2021', 'DD-MM-YYYY');

Lesson 4 - Групповые агрегатные функции

/*
4. Групповые агрегатные функции
    a.	Отобразить сумму добычи в тоннах по одному из объектов DW_MTH_OP_RAP;
    b.	Посчитать количество записей РИГИС по скважине или объекту DW_GIS.
    c.	Округлить среднюю добычу в тоннах по объекту до 1 знака до запятой и после DW_MTH_OP_RAP.
*/

-- 4.a
    -- быстрее
select sum(oil_t) from kin_ung.dw_mth_op_rap where object_id = 48;
    -- дольше
select sum(oil_t), object_id from kin_ung.dw_mth_op_rap
group by object_id;

-- 4.b
select count(*) from KIN_UNG.DW_GIS where object_id = 22;
select well_id, count(*) from kin_ung.dw_gis
group by well_id;

-- 4.c
select * from KIN_UNG.DW_MTH_OP_RAP;
select * from KIN_UNG.DW_GIS;

select 
  -- до запятой
  object_id, round(avg(oil_t), -1), 
  -- после запятой
  round(avg(oil_t), 1)  
from KIN_UNG.DW_MTH_OP_RAP
group by object_id;

Lesson 5 - Отображение данных из нескольких таблиц

/*
5. Отображение данных из нескольких таблиц
    a.	Отобразить координаты устья скважины с указанием названия месторождения и имени скважины (DW_OILFIELD, DW_WELLS, DW_WELL_HEAD_COORDS);
    b.	Посчитать количество скважин по всем месторождениям региона;
    c.	Посчитать количество скважин по месторождению, на которые есть Карты (DW_GRID).
*/

-- 5.a 
select o.oilfield_name, w.well_name, c.x0, c.y0
from kin_ung.dw_oilfield o 
join kin_ung.dw_wells w
on (o.oilfield_id = w.oilfield_id)
join kin_ung.dw_well_head_coords c
on (w.well_id = c.well_id) order by 1;

-- для 3 таблиц должно быть 2 соединения (join/where), для 4табл -> 3, для 5табл -> 4

select o.oilfield_name, w.well_name, o.oilfield_id, m.oil_t, m.dt
from kin_ung.dw_oilfield o, kin_ung.dw_wells w, kin_ung.dw_mth_op_rap m
where o.oilfield_id = w.oilfield_id and w.well_id = m.well_id
and m.oil_t > 0
and o.oilfield_name = 'МАМОНТОВСКОЕ';

-- 5.b
select oilfield_id, count(*) from KIN_UNG.dw_wells
group by oilfield_id;

select f.oilfield_name, count(*) from KIN_UNG.dw_wells w, KIN_UNG.dw_oilfield f 
where w.oilfield_id = f.oilfield_id
group by f.oilfield_name;

-- 5.c
-- возвращаем к какому месторождению привязаны карты
select f.oilfield_id, f.oilfield_name, o.object_name, g.caption from kin_ung.dw_oilfield f , kin_ung.dw_object o, kin_ung.dw_grid g
where f.oilfield_id = o.oilfield_id and o.object_id = g.object_id;

-- ищем те месторождения, где есть хоть одна карта (частный случай) 
select distinct f.oilfield_id from kin_ung.dw_oilfield f , kin_ung.dw_object o, kin_ung.dw_grid g
where f.oilfield_id = o.oilfield_id and o.object_id = g.object_id;
-- // Результат: 209, 588, 141, 229, 249, 572, 201, 215, 203, 259, 531

-- объединяем из 5.b (где считали кол-во скважин) и наш предыдущий запрос
select f.oilfield_name, count(*) from KIN_UNG.dw_wells w, kin_ung.dw_oilfield f
where w.oilfield_id = f.oilfield_id
and f.oilfield_id in (select distinct f.oilfield_id from kin_ung.dw_oilfield f , kin_ung.dw_object o, kin_ung.dw_grid g
where f.oilfield_id = o.oilfield_id and o.object_id = g.object_id)
group by f.oilfield_name;

Lesson 6 - Подзапросы и их классификация

/*
6. Подзапросы и их классификация
    a.	Вывести все названия типов контуров, загруженных в БД (DW_LINE, DW_DICT_LINE_KIND).
    b.	Найти все скважины с добычей больше среднего по региону.
*/

select 
(select oilfield_name f from kin_ung.dw_oilfield f where f.oilfield_id = w.oilfield_id) "Имя мест",
w.* from kin_ung.dw_wells w;

select 
w.*
from kin_ung.dw_wells w, (select max (oilfield_id) oilfield_id from kin_ung.dw_oilfield) subq
where w.oilfield_id = subq.oilfield_id;


-- 6.a.
select
(select d.value_full
from KIN_UNG.dw_dict_line_kind d
where d.id_code = l.line_kind) "Тип", l.*
from kin_ung.dw_line l;

    -- 6.a var2
select (
    select d.value_full
    from KIN_UNG.dw_dict_line_kind d
    where d.id_code = l.line_kind
    ) "Тип", l.*
from (select distinct line_kind from kin_ung.dw_line) l;

    -- 6.a var3 самый быстрый ч\з join
select *
from kin_ung.dw_dict_line_kind d
join
    (select distinct line_kind from kin_ung.dw_line) l 
on d.id_code = l.line_kind;    

    -- 6.a var4
select *
from KIN_UNG.dw_dict_line_kind
where id_code in (select distinct line_kind from kin_ung.dw_line);

-- 6.b
select avgs.well_id, round(avgs.avg_w, 2) rounded_avg_rate from
(select R.well_id, AVG(oil_t) avg_w
FROM kin_ung.dw_mth_op_rap R
GROUP BY R.well_id) avgs
WHERE avgs.avg_w > (SELECT AVG(R.oil_t) FROM kin_ung.dw_mth_op_rap R);

    -- 6.b var 2
select * from kin_ung.dw_mth_op_rap mq where mq.oil_t > 
(SELECT AVG(R.oil_t) FROM kin_ung.dw_mth_op_rap R);

Lesson 7 - Объединения множеств

/*
7. Объединения множеств
    a.	Найти два одинаковых и различающихся справочника в различных регионах;
    b.	Найти пересечение двух различающихся справочников DW_DICT_METHOD разных регионов.
*/

-- 7.a
-- берем любые таблицы - DW_DICT%

SELECT * from KIN_UNG.DW_DICT_CONTRACTOR
MINUS
SELECT * from KIN_BN_ARL.DW_DICT_CONTRACTOR;

SELECT id_code, value_short, value_full from KIN_BN_ARL.DW_DICT_GRID_KIND
MINUS
(SELECT id_code, value_short, value_full from KIN_BN_ARL.DW_DICT_GRID_KIND
INTERSECT
SELECT id_code, value_short, value_full  from KIN_UNG.DW_DICT_GRID_KIND);

-- 7.b
SELECT lower(id_code), lower(value_short), lower(value_full) FROM KIN_BN_ARL.dw_dict_method
INTERSECT
SELECT lower(id_code), lower(value_short), lower(value_full) FROM KIN_UNG.dw_dict_method;

Lesson 8 - Язык манипулирования данными (DML)

/*
8. Язык манипулирования данными (DML)
    a.	Добавить вручную новую скважину, переименовать ее и удалить;
    b.	Изменить название карты DW_GRID.
*/

-- 8.a
INSERT INTO "KIN_BN_ARL"."DW_WELLS" (OILFIELD_ID, WELL_ID, WELL_NAME) VALUES ('223', '3434', 'Тестовая скважина');
UPDATE "KIN_BN_ARL"."DW_WELLS" SET WELL_NAME = 'Тесто' WHERE WELL_ID = 3434;
DELETE FROM "KIN_BN_ARL"."DW_WELLS" WHERE WELL_ID = 3434;
-- 8.b
UPDATE "KIN_BN_ARL"."DW_GRID" SET CAPTION = 'Карта начальных н-н толщиан, м' WHERE ROWID = 'AAAZKqAAJAAAK0DAAB' AND ORA_ROWSCN = '32883536332970'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment