🖥 SQL для начинающих: 10 правил построения «точных» запросов

«Точный» SQL-запрос возвращает «чистые» данные в необходимом и достаточном количестве, при этом потребляет как можно меньше памяти и справляется за минимальное время. Скорость работы с базой влияет на производительность. Потребление памяти может негативно сказаться даже на безопасности. Всё это прямо и косвенно влияет на прибыль компании. В статье разберёмся, как не допускать ошибок.

Для наших целей понадобятся тестовые данные. Будем работать с базой данных Oracle Database. Примеры в статье будут приводиться на языке SQL, PL/SQL. Нам важен подход, который можно адаптировать под другую реляционную систему управления базами данных — РСУБД.

▪ Часть 1
▪ Часть 2

@sqlhub

🖥 Как расширенные объединения и оконные функции могут вывести ваши SQL-запросы на новый уровень

В этой статье мы рассмотрим четыре распространённых типа соединений, а также различные типы доступных оконных функций.

К концу этой статьи вы будете обладать знаниями, необходимыми для того, чтобы вывести ваши SQL-запросы на новый уровень и раскрыть истинную мощь ваших данных.

▪ Читать

@sqlhub

🖥 NoSQLMap — Автоматизированный инструмент для баз данных NoSQL, предназначенный для аудита, а также для автоматизации атак путем внедрения и использования слабых сторон конфигурации по умолчанию в базах данных NoSQL и веб-приложениях, использующих NoSQL, для раскрытия или клонирования данных из базы данных.

⚠️ Информация предоставлена исключительно с целью ознакомления. И побуждает обратить внимание на проблемы в безопасности.

🖥 GitHub

@sqlhub

🖥 Работа с PostgreSQL в Python на русском

▪ Подключение к БД
▪ Создание таблиц
▪ Вставка и извлечение данных
▪ Обновление таблиц
▪ Удаление строк

#doc #python #postgresql #russian

@sqlhub

🖥 13 упражнений по SQL с решениями

В этом наборе упражнений мы поработаем с SQL и T-SQL. С помощью этих упражнений мы будем создавать различные запросы SQL и T-SQL, чтобы отточить навыки работы с запросами.

Независимо от того, являетесь ли вы новичком или опытным разработчиком, эти упражнения помогут укрепить знания и подготовиться к реальным собеседованиям. Так что давайте погрузимся в работу и начнём решать задачи!

▪Читать

@sqlhub

🖥 Выбор максимума из одного поля и значения другого поля по максимуму третьего поля

Дана таблица - table, с тремя полями field1, field2, field3.
Задача получить в одном запросе два значения:

Максимум поля field1
Значение поля field2 в записи, где поле field3 максимально.
Значения поля field3 уникальны, так что оба ответа однозначны.

Решить эти две задачи двумя запросами не проблема.

1) SELECT MAX(field1) FROM table;
2)SELECT field2 FROM table
ORDER BY field3 DESC
LIMIT 1;

или используем для второй задачи вариант с WHERE:

SELECT field2 FROM table
WHERE field3 = (SELECT MAX(field3) FROM table);

Как получить оба в одном запросе

Есть два хорошо оптимизированных запроса (при условии что соответствующие индексы в наличии). У которых между собой ничего общего, кроме таблицы-источника, нет. Причём каждый запрос возвращает строго одну запись. В таких условиях решение по объединению очевидно. CROSS JOIN.

SELECT *
FROM (
SELECT MAX(field1) field1
FROM table
) t1
CROSS JOIN (
SELECT field2
FROM table
ORDER BY field3 DESC LIMIT 1
) t2
;

Второй вариант

select
(select max(a) from tbl) as a,
b,
c
from tbl
order by c desc
limit 1

Решение с CROSS JOIN работает на 10% быстрее.

👉 Пишите ваше решение в комментариях👇

@sqlhub

🖥 Задача SQL

Есть кинотеатр, в нем идут фильмы. У фильма есть название, длительность (пусть для простоты будет 60, 90 или 120 минут), цена билета (в разное время и дни может быть разная), время начала сеанса (один фильм может быть показан несколько раз в разное время за разную цену). Также, есть информация о купленных билетах (номер билета, на какой сеанс).

Задания:

▪составь грамотную нормализованную схему хранения этих данных в БД. Внеси в нее 4-5 фильмов, расписание на один день и несколько проданных билетов.

Напишите запросы, считающие и выводящие в понятном виде:

▪ошибки в расписании (фильмы накладываются друг на друга), отсортированные по возрастанию времени.
Выводить необходимо колонки «фильм 1», «время начала», «длительность», «фильм 2», «время начала», «длительность».

▪перерывы больше или равные 30 минут между фильмами, выводятся по уменьшению длительности перерыва. Выводить надо колонки «фильм 1», «время начала», «длительность», «время начала второго фильма», «длительность перерыва».

▪список фильмов, для каждого указано общее число посетителей за все время, среднее число зрителей за сеанс и общая сумма сбора по каждому, отсортированные по убыванию прибыли. Внизу таблицы должна быть строчка «итого», содержащая данные по всем фильмам сразу.

▪число посетителей и кассовые сборы, сгруппированные по времени начала фильма: с 9 до 15, с 15 до 18, с 18 до 21, с 21 до 00:00. (то есть сколько посетителей пришло с 9 до 15 часов, сколько с 15 до 18 и т.д.).

@sqlhub

🖥 Освоение SQL: Использование данных для решения сложных задач

Я решил подробно рассказать о том, как я подхожу к использованию SQL для запроса баз данных. Я принял участие в еженедельном конкурсе Danny’s SQL challenge, чтобы начать тематическое исследование по этой теме. Вся необходимая вам информация об этом испытании доступна здесь.

▪Читать

@sqlhub

🖥 Руководство по наиболее востребованным базовым командам SQL

Продемонстрируем применение основных команд SQL на примерах.

▪ Читать

@sqlhub

🖥 Вводный курс в SQL

SQL – это важнейший инструмент для управления данными в современной технологической отрасли. Знакомство с базовыми концепциями SQL необходимо для работы с базами данных, а приобретение знаний в области передовых концепций может вывести ваши навыки на новый уровень.

При правильном обучении и практике любой желающий может овладеть SQL и эффективно управлять данными.

▪ Читать

@sqlhub

🖥 Сложная задача про календарь

Давай возьмемся за действительно сложную задачу. Напиши SQL-код, выводящий календарь на текущий месяц в виде:

Пн Вт Ср Чт Пт Сб Вс

| | | | | 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

Подсказка: ты можешь делать запросы без таблиц, например SELECT 2 + 3, 'Hello'
Подсказка: здесь не надо использовать циклы или процедуры

Подсказка: функции работы с датой и временем ты можешь найти тут http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html (англ.)
Подсказка: для сокращения объема кода ты можешь использовать переменные (создаются командой SET)

Пишите свое решение в комментариях👇

@sqlhub

🖥 Разбор ошибок. SQL запрос кросс-джойн двух таблиц без явного указания оператора JOIN

SELECT *
FROM orders, customers
WHERE orders.customer_id = customers.customer_id
AND customers.first_name = 'John'

Этот запрос выполняет кросс-джойн двух таблиц без явного указания оператора JOIN, что может привести к неожиданным результатам.

Кроме того, он не использует оператор WHERE для фильтрации строк, что означает, что он возвращает все строки, удовлетворяющие условию соединения. Это может привести к запросу к большому объему данных, из-за которых запрос будет выполняться медленно.

Наконец, условие фильтрации для идентификации клиента по имени не учитывает возможность наличия нескольких клиентов с одинаковыми именами в таблице customers.

@sqlhub

🖥 Как транспонировать в SQL?

Для транспонирования таблицы в SQL необходимо использовать оператор PIVOT или функцию MAX или CASE в сочетании с оператором GROUP BY.

Оператор PIVOT позволяет преобразовать строки в столбцы, а столбцы - в строки, используя значения одного столбца в качестве заголовков новых столбцов.

Синтаксис оператора PIVOT выглядит следующим образом:

SELECT *
FROM table_name
PIVOT
(
aggregate_function(column_to_aggregate)
FOR column_to_pivot IN (list_of_pivot_values)
) AS alias_name;

Здесь table_name - это имя таблицы, которую нужно транспонировать aggregate_function - это агрегатная функция, которую нужно применить к столбцу, column_to_aggregate - это имя столбца, который нужно агрегировать, column_to_pivot - это имя столбца, который нужно использовать для создания новых столбцов, list_of_pivot_values - это список значений столбца column_to_pivot, для которых нужно создать новые столбцы, alias_name - это имя для результирующей таблицы

Пример использования оператора PIVOT:

SELECT *
FROM (
SELECT product_id, year, sales
FROM sales_table
) AS source_table
PIVOT
(
SUM(sales)
FOR year IN (2023, 2022, 2021)
) AS pivot_table;

В этом примере мы выбираем данные из таблицы sales_table и используем оператор PIVOT, чтобы преобразовать строки в столбцы, используя годы продаж как заголовки новых столбцов.

Если оператор PIVOT недоступен в вашей версии SQL, вы можете использовать функцию MAX или CASE в сочетании с оператором GROUP BY.

Синтаксис функции MAX для транспонирования таблицы выглядит следующим образом:

SELECT column_to_group_by,
MAX(CASE column_to_pivot WHEN pivot_value_1 THEN value_to_show ELSE NULL END) AS pivot_value_1,
MAX(CASE column_to_pivot WHEN pivot_value_2 THEN value_to_show ELSE NULL END) AS pivot_value_2,
...
FROM table_name
GROUP BY column_to_group_by;

Здесь column_to_group_by - это имя столбца, по которому нужно группировать данные, column_to_pivot - это имя столбца, который нужно использовать для создания новых столбцов, pivot_value_1, pivot_value_2 - это значения столбца column_to_pivot, для которых нужно создать новые столбцы, value_to_show - это значение, которое нужно показать в новом столбце

Задача:

Дана таблица T:

id value
2 a
3 a
4 b
5 c

Необходимо сформировать запрос, который бы вернул следующий набор:

a b c
2 1 1

Пишите свое решение в комментариях👇

@sqlhub

🖥 Как написать SQL скрипт

SQL-скрипт - это файл с расширением .sql, содержащий набор инструкций SQL, которые могут выполняться на базе данных.

Для написания SQL-скрипта необходимо выполнить следующие шаги:

Определить цель скрипта - что он должен делать и какие данные необходимо извлечь или изменить.

Открыть текстовый редактор, такой как блокнот или специализированный редактор SQL-скриптов.

Написать инструкции SQL, используя правильный синтаксис и ключевые слова. Например, для создания таблицы в базе данных можно использовать следующий код:

CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE
);

Сохранить файл с расширением .sql. В некоторых редакторах можно также выполнить скрипт непосредственно из редактора.

Запустить скрипт на базе данных. Это можно сделать с помощью интерфейса базы данных или с помощью командной строки, используя утилиты командной строки, такие как sqlcmd или mysql.

Убедиться, что скрипт выполнен успешно и не было ошибок.

Пример SQL-скрипта, который добавляет новую запись в таблицу employees:

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (1, 'John', 'Doe', 'johndoe@email.com', '2022-01-01');

Этот скрипт добавляет нового сотрудника в таблицу employees с идентификатором 1, именем John, фамилией Doe, электронной почтой johndoe@email.com и датой найма 1 января 2022 года.

Задание:

Написать SQL скрипт, который создаст базу данных с иерархией таблиц (нормализированную) для такой сущности:
Человек: имя, фамилия, дата рождения, номера телефонов, домашний адрес, адрес работы, название фирмы, в которой работает.
Скрипт должен быть написан так, чтоб его можно было выполнить несколько раз без ошибок и дублирования (идемпотентность).

Пишите свое решение в комментариях👇

@sqlhub

🖥 Как распарсить строку в SQL


Для того чтобы распарсить строку в SQL, можно использовать различные функции и операторы, в зависимости от конкретных требований и формата строки.

Допустим, если строка содержит разделители, такие как запятые или пробелы, то можно использовать функцию SUBSTRING_INDEX() для извлечения определенного фрагмента строки.
Например, следующий запрос извлекает первое слово из строки Привет, мир:

SELECT SUBSTRING_INDEX('Привет, мир', ' ', 1);

Результатом запроса будет строка Привет.

Если же требуется извлечь определенный набор символов из строки, то можно использовать функцию SUBSTRING().
Например, следующий запрос извлекает первые три символа из строки Привет:

SELECT SUBSTRING('Привет', 1, 3);

Результатом запроса будет строка При.

Также можно использовать функцию REGEXP_SUBSTR() для извлечения определенного фрагмента строки, используя регулярные выражения.
Например, следующий запрос извлекает первое слово из строки Привет, мир:

SELECT REGEXP_SUBSTR('Привет, мир', '^[^ ]+');

Результатом запроса будет строка Привет.

@sqlhub

🖥 6 простых способов использовать передовые методы SQL

SQL является основой анализа и преобразования данных, но в некоторых случаях требуется более сложный подход. Вот тут-то и пригодятся передовые методы SQL.

▪ Читать

@sqlhub

🖥 Как заменить null на 0 в SQL

Чтобы заменить значение NULL на 0 в SQL, можно использовать функцию COALESCE. Эта функция принимает несколько аргументов и возвращает первый не NULL аргумент.

Если все аргументы NULL, функция вернет NULL. Вот пример использования COALESCE для замены значений NULL на 0:

SELECT COALESCE(column_name, 0) FROM table_name;

В этом запросе column_name - имя столбца, значения которого нужно заменить, а table_name - имя таблицы, в которой находится столбец. Функция COALESCE заменит все значения NULL в столбце на 0. Если значение столбца не NULL, то функция вернет его без изменений.

Также можно использовать оператор IS NULL для проверки на NULL и замены его на 0. Вот пример:

SELECT CASE WHEN column_name IS NULL THEN 0 ELSE column_name END FROM table_name;

Этот запрос также заменит значения NULL на 0. Если значение столбца не NULL, то запрос вернет его без изменений.

@sqlhub