SQL для начинающих тестировщиков
@KlimenkovDM для каналаSQL для начинающих тестировщиков (1/17)
Привет всем, меня зовут Даниил!
В тестировании уже несколько лет, долгое время SQL мне был не нужен. Однако, потом я попал на проект, где SQL был основным инструментом для тестирования - пришлось изучать 🤷🏻♂️
Текущий уровень знаний оцениваю как выше среднего (среди тестировщиков). Понятное дело, что есть ребята, которые меня многому научат.
Хотелось бы рассказать о граблях и лайфаках, которые я постиг за это время.
Материал будет полезен тем, кто мало работал с SQL, но сейчас самостоятельно его изучает - как вспомогательный инструмент.
В теории может быть полезен для разработчиков, если они помогают тестировщикам писать скрипты.
Планирую описать
1. А для чего SQL тестировщику?
2. Чем скрипт тестировщика отличается от скрипта разработчика?
3. С чего начать изучать SQL?
4. Select * from - плохая привычка, правильная select distinct * from
5. NVL - вторая правильная привычка тестировщика
6. Так left или inner?
7. Избегаем конструкции вида select * from (select * from (select * from t2) t1) t
8. With as - великое изобретение для работы тестировщика
9. MINUS - второе великое изобретение для работы тестировщика
10. Работа с датами
11. Алиасы таблиц - правильный тон
12. Параметризация скриптов
Примечание
▫️ Я не планирую писать учебник или ссылки. Многое гуглится, еще больше можно просто узнать читая скрипты опытных разработчиков, ну и личное общение с ними - никто не отменял.
▫️Все ошибки, приведенные в постах, реальны (либо мои, либо чужие).
▫️Писать я буду про select. Модификация данных - отдельная тема.
▫️В постах будет рассматриваться только Oracle.
Автор: @KlimenkovDM
#sqlдлятестировщиков #тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
Чем скрипт тестировщика отличается от скрипта разработчика? (2/17)
Самое главное, cкрипт тестировщика МОЖЕТ быть не оптимальным, но он ДОЛЖЕН возвращать верный результат.
Т.е. часто лучше пойти каким-то более длинным путем написания скрипта, нежели разработчик.
Да, скрипт может выполняться очень долго, но, если он дает такой же результат, что и скрипт разработчика, значится с 99% уверенностью можно считать, что все работает корректно.
Поэтому не нужно стремиться писать оптимально и красиво. Нужно стремиться писать понятно и прямолинейно. Т.е. каждая следующая строчка скрипта явно должна быть очевидна вам и вашему коллеге.
⚠️ скрипт только для дев/тест сред.
Звучит непонятно?
Надеюсь, следующие посты внесут ясность 😉
Обсудить в чатике (https://t.me/+DUhhHFWbPA5kZjFi) 💬
Автор: @KlimenkovDM
#sqlдлятестировщиков #тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
Ч.1 С чего начать изучать SQL? (3/17)
Если вы не изучали SQL, то первым делом нужно понять как он работает.
В этом как раз и заключается вся сложность.
Нужно понимать последовательность выполнения операций.
Буду объяснять на примере стандартной схемы HR (https://t.me/oracle_dbd/225) и таблички employees.
Например:
select department_id, last_name, sum(salary)
from employees
where first_name = 'John'
group by department_id, last_name
having sum(salary)>1000
order by department_id, last_name
Хоть скрипт начинается с select, но выполняться он будет по-другому.
Перепишем его в той последовательности, как он будет выполняться:
from employees e
where e.first_name = 'John'
group by department_id, last_name
having sum(salary)>1000
select department_id, last_name, sum(salary)
order by department_id, last_name;
Да это вроде как все знают, но по личному опыту скажу, что без понимание этого писать запросы проблематично.
Часто начинающие пишут что-то типа такого:
select department_id, last_name, sum(salary) as slr
from employees
where first_name = 'John'
group by department_id, last_name
having slr > 1000
order by department_id, last_name
т.е. в select присваивают псевдоним полю (slr), а потом ниже пытаются к нему обратиться (having).
Естественно, СУБД выкидывает ошибку (неактуально для ORACLE версии 23 (https://t.me/oracle_dbd/482))
Нужно запомнить:
Первым всегда выполнятся часть от From до Where, когда это понимание приходит - уже проще соединять таблицы 😊
Автор: @KlimenkovDM
#sqlдлятестировщиков #тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
Ч.2. С чего начать изучать SQL? (4/17)
У нас есть таблицы (employees, departments), в которых хранится: название отдела, фио, должность, зп в месяц
Давайте их соединим:
select d.department_name, e.first_name, e.last_name, sum(e.salary)
from employees e
join departments d on e.department_id = d.department_id
where e.first_name = 'John'
group by d.department_name, e.first_name, e.last_name
having sum(e.salary) > 1000
order by d.department_name, e.first_name;
И здесь ключевой момент:
Для тестировщика можно оперировать таким допущением.
После FROM идет сборка "огромной таблицы" из всех перечисленных таблиц.
Вот собралась одна такая таблица - и дальше уже начинают ее фильтровать и из нее делать select.
Почему я заострил на этом внимание?
Гуглеж или чтение учебников с ходу не дают эту информацию. Нужно прочитать 100500 страниц текста, чтобы это понять.
А учебники, вообще, мало кто читает...
После понимания этого нужно переходить:
- К соединению таблиц.
- Затем уже изучение агрегирующих функций.
- Всякие полезности разработки, типа оконных функций - можно вначале не изучать, они постигаются по мере усложнения задач. Часто не нужны тестировщику.
- План запроса - это магия. Подозреваю, что для многих разработчиков тоже (шутка, хотя...)
Обсудить в чатике (https://t.me/+DUhhHFWbPA5kZjFi) 💬
Автор: @KlimenkovDM
#sqlдлятестировщиков #тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
4. Select * from - плохая привычка, правильная select distinct * from
С помощью этого лайфхака я отловил кучу ошибок у разработчиков.
distinct - это убирание дублей.
Разработчики очень часто его не пишут, так как это дорогая операция.
Поэтому тестировщик ОБЯЗАН везде писать distinct
Пример:
Вам нужно получить список клиентов, а потом посчитать сумму заказов по ним.
Список клиентов - все те клиенты, которых обслуживают менеджеры с должностью содержащей слово "менеджер"
Напишем простейшую заготовку
with clients as (select 1 id, 'Рога' name from dual
union all
select 2, 'Копыта' from dual),
managers as (select 1 id, 1 id_cl, 'Иванов' FIO, 'Клиентский менеджер' position from dual
union all
select 2, 1, 'Петров', 'Старший менеджер' from dual
union all
select 3, 2, 'Сидоров', 'Клиентский менеджер' from dual
union all
select 4, 2, 'Боширов', 'Начальник' from dual),
orders as (select 1 id, 1 id_cl, 100 amount from dual
union all
select 2, 2, 50 from dual)
Допустим разработчик написал выборку такую:
select c.name, sum(o.amount)
from clients c
join managers m on m.id_cl = c.id
join orders o on o.id_cl = c.id
where m.position like '%менеджер%'
group by c.name;
А как бы написал я.
,list_clients as (
select distinct c.id, c.name
from clients c
join managers m on m.id_cl = c.id
where m.position like '%менеджер%')
select c.name, sum(o.amount)
from list_clients c
join orders o on o.id_cl = c.id
group by c.name;
Так вот при получении списка клиентов, у разработчика произошло дублирование клиента "Рога", т.к. ее обслуживают два менеджера с должностью содержащей слово "менеджер".
Естественно, при дальнейшем join он уже получил не один, а два заказа с суммой по 100, и как итог, общая сумма заказов у него удвоилась.
Мой же скрипт отдельно собрал список клиентов, используя distinct. Получил уникальную одну запись "Рога" и правильно собрал итоговую выборку.
Ничего страшного нету в таком скрипте.
Некрасиво? Да.
Возможно более затратно для выполнения? Да.
Но тестировщику главное, что?
Правильно работающий скрипт.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
5. NVL - вторая правильная привычка тестировщика.
Также полезная привычка оборачивать в nvl числовые поля: nvl(field,0)
Большинство знает как работает ORACLE с null, например:
select sum(field) from table
в данном случае если в field будет null, то оракл его "пропустит" и сумму по столбцу посчитает корректно.
select field_1+field_2 from table
а вот тут будут нюансы. Если в одной записи в поле field_1 будет null и в этой же записи в field_2 будет число, то результат будет null.
Поэтому правильней писать
select nvl(field_1,0) + nvl(field_2,0) from table
ну и соотвественно привычка формируется и для
select sum(nvl(field,0)) from table
Также эта привычка помогла быстро отловить и понять причину реальной ошибки на проекте.
Пример, нужно вывести ТОП фирм по продажам в порядке убывания.
Пускай, у нас после выборки будет такая картина:
with t_top as (select 100 rub, 'Рога' name from dual
union all
select null, 'Копыта' from dual
union all
select 200, 'Ромашка' from dual)
Далее разработчик применял оконую функцию
select row_number() over (order by rub desc) rn, rub, name from t_top
Итог - результат ранжирования некорректный :(
Честно, я уж не помню, как я писал запрос, но подозреваю, что получая промежуточный запрос, я явно прописывал nvl(field,0) в нужном поле.
Т.е. я писал что-то типа
select sum(nvl(field,0)) from table
Ничего страшного нету в такой записи.
Некрасиво? Да.
Возможно более затратно для выполнения? Да.
Но тестировщику главное, что?
Правильно работающий скрипт.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
6. Так LEFT или INNER?
Ну это вообще отдельная тема. Но кратко можно сформулировать так.
Если ты не уверен, что INNER 100% правильный, то используй LEFT.
Иными словами, если я понимаю, что при соединении двух таблиц INNER точно будет правильным, а LEFT избыточным - я напишу INNER.
Если я в первый раз вижу таблицы, то я буду использовать LEFT.
Дальше смотреть, что я получаю на выходе и думать, нужен ли мне LEFT или можно использовать INNER.
Но есть нюанс - на тестовых данных часто это сложно понять. А если есть доступ к приближенным данным прода - то на них и можно производить анализ.
Полезное знание:
если вы использовали LEFT, то при последущем соедении этой таблицы нужно использовать LEFT
Select * from table_1 t1
left join table_2 t2 on t1.id = t2.id
left join table_3 t3 on t2.id = t3.id
т.е. мы берем table_1 к ней лефтим table_2, тогда если мы хотим к table_2 присоеденить table_3, то нужно уже лефтить.
Я по началу мог написать:
Select * from table_1 t1
left join table_2 t2 on t1.id = t2.id
inner join table_3 t3 on t2.id = t3.id
Но мне подсказали умные люди, что так делать бессмысленно. Т.к. если будут NULL-записи то inner не отработает.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
7. Избегаем конструкции вида select * from (select * from (select * from t2) t1) t
Почему данная конструкция неудобна?
На самом деле, дело привычки, но я не люблю это конструкциюю. Почему? Просто неудобно потом смотреть промежуточный результат select * from t1
Проще использовать with
with t1 as (select * from t2) ,
t as (select * from t1)
select * from t
При таком написании, если мне нужно проверить, что в промежуточных результах - то проще вычленить его :)
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
8. With as - великое изобретение для работы тестировщика.
Чем хороша эта конструкция?
▫️Первое. Она позволяет собирать промежуточные запросы и всегда получить быстро их результаты. А при условии, что мы постоянно используем distinct, то поиск ошибок резко упрощается.
Поэтому все что можно я всегда пихаю в эту конструкцию. С опытом работы на конкретном проекте приходит понимание, какие нужны промежуточные запросы и какое их количество и что в них должно быть.
Для начала можно очень сильно дробить на мелкие промежуточные запросы, постепенно укрупняя их.
▫️Второе. Допустим у вас на тестировании очень большая выборка (например, вы тестируете VEIW) и полный запрос выполняется долго (например, по 2-3 минуты).
И, например, у вас нету клиента в выборке, а у разработчика есть.
Добавляя нужную фильтрацию только по этому клиенту, а также проверяя результаты промежуточных запросов, вы можете раскрутить в каком именно подзапросе вы его "потеряли".
А дальше уже думать, это у вас ошибка или у разработчика.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
9. MINUS - второе великое изобретение для работы тестировщика
Этот оператор АРХИВАЖЕН если нужно проверить, например, VIEW.
Как я тестировал VIEW? Писал по требованиям свой Select. А дальше сравнивал результат работы своей выборки с результатом VIEW разработчика.
Как раз для этого нужен оператор MINUS.
При правильном использовании - он показывает разницу по обеим таблицам.
Я помню потратил два дня гуглежа, чтобы правильно написать минусовку.
Поэтому просто оставлю его тут.
Причем все скобки важны, насколько я помню :)
with my_table as (select 1 from dual),
testing_table as (select 1 from dual),
results as ((
select *
from my_table
minus
select *
from testing_table
)
union (
select *
from testing_table
minus
select *
from my_table
))
select *
from results
Если запрос вернул пусто - значится обе выборки my_table и testing_table полностью совпадают.
Если запрос вернул не пусто, то обычно видно в каком значении расхождение, а дальше уже нужно думать и анализировать: я ошибся или разработчик.
Кстати, лайфхак.
Если вам нужно протестировать доработанную VIEW, то лучше взять текущую версию с прода, дописать ее под новые требования и протестировать.
Это явно проще чем писать новую выборку.
Также обычно версия прода уже протестирована и оптимизирована.
Тогда как старый скрипт тестировщика может быть не оптимальным.
Плюс его вообще может не быть, ну или написано так, что ничего непонятно.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
10. Работа с датами.
Тут достаточно всё просто. Если вы хотите использовать конкретную дату, рекомендую всегда оборачивать строку в to_date,
select to_date('31.12.2023','dd.mm.yyyy') dt from dual
Почему?
Потому что часто запрос вида:
select * from table1 where dt = '31.12.2023'
может работать некорректно или вообще выдавать ошибку. Я как понимаю, зависит от вашей среды разработки и конкретных ее настроек.
А так как ваши скрипты могут быть полезны другим тестировщикам, то проще оборачивать дату в to_date.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
11. Алиасы таблиц - правильный тон
Есть ли ошибка в этом скрипте?
with t1 as (select 1 F1, 2 F2 from dual),
t2 as (select 3 F3, 4 F4 from dual)
select * from t1
where F1 in (select F2 from t2)
Есть.
Причем ORACLE вам спокойно выполнит этот скрипт и вернет результат. (впрочем, возможно зависит от среды разработки, вроде некоторые разругаются)
А ошибка в том, что в t2 нету поля F2
Это реальный случай такой ошибки у разработчика. Отловили ее чисто случайно. Но искать ее пришлось пару часов.
Возникла она из-за того, что:
Во-первых нету алиаса таблицы.
Во-вторых, обращение к полю идет не через название таблицы.
А так как названия таблиц чаще всего длинные, то хороший и правильный тон задавать алиас таблицы, а потом к ней обращаться через алиас
select * from table_1 t1
join table_2 t2 on t1.id = t2.code
where t1.field = 123
Ну и сюда же напишу, что если в одной БД несколько схем, то хорошая привычка у тестировщика явно прописывать название схемы.
Это просто удобно для последующего использования скриптов.
У разработчиков же это обычно вызывает жуткие споры - прописывать схему или нет :)
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
12. Параметризация скриптов
Параметры удобны. Например, у вас скрипт на подразделение. А вам нужно проверить кучу подразделений.
Чтобы не крутить ролик на мышке и не править во всех местах код подразделения лучше использовать параметр
select * from department
where dep_id = :id_dep
Но пару лайфхаков я вам дам.
Не называйте параметр так же как поле. Лучше чутка переименовать его. А то мало ли по запарке удалите двоеточие и будет потом какая-то ерунда.
Я помню случай, когда на тесте периодически кто-то удалял данные. Как раз там было что-то типа
delete from department
where dep_id = dep_id
Также я не использую в параметрах дату. Дату удобней объявить в начале в одтельном CTE, а потом обращаться к нему
with my_dates as
(select to_date('31.12.2023','dd.mm.yyyy') date_1
,to_date('31.01.2022','dd.mm.yyyy') date_2
from dual)
select * from table_1
where dt_r = (select date_1 from my_dates)
and r_dt = (select date_2 from my_dates)
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)
Заключение
Да, серия постов получилась сумбурной. :)
И по ней явно не научишься писать SQL запросы.
Однако, если вы сейчас изучаете SQL, то надеюсь эти лайфхаки вам помогут в работе.
Если зайдет, можно отдельно подумать над серией постов по модификации данных и подготовке тестовых данных, т.к. там не всё так однозначно на первый взгляд.
Автор: @KlimenkovDM
#sqlдлятестировщиков
#тестирование
Специально для Oracle Developer (https://t.me/oracle_dbd)