SQL: запросы и базы данных

Все темы Python Developer

Подтемы:

• Основные концепции SQL
• Фильтрация строк
• JOIN Операторы и операции с таблицами
• PIVOT Таблицы
• PostgreSQL
• План выполнения запроса
• Реляционные и нереляционные БД

Для чего используется LIKE?

Оператор LIKE в SQL используется для выполнения поиска в текстовых данных с использованием шаблонов. Он позволяет выбирать строки, которые соответствуют определенному шаблону, который может содержать специальные символы, такие как % (заменяющий любое количество символов) и _ (заменяющий один символ). Это полезно для поиска строк с определенными паттернами, например, имена, которые начинаются или заканчиваются определенной последовательностью символов, или содержат определенные символы в середине строки.

Что такое обобщенное табличное выражение (CTE) и как оно используется?

CTE представляет именованный результат запроса, заданный через WITH и доступный внутри одной SQL-команды. Он помогает разбить сложный запрос на части и поддерживает рекурсивные вычисления. CTE не гарантирует ускорение или однократное вычисление: материализация и встраивание в основной запрос зависят от СУБД и плана.

Что представляют собой значения null и NaN, и в чем заключается их различие? Каким образом следует обрабатывать данные с такими типами?

Значения NULL и NaN используются для представления отсутствующих или неопределенных данных.

NULL:

• В SQL, NULL используется для обозначения отсутствия значения.

• Он не является нулевым значением, а скорее указывает на отсутствие какого-либо значения.

• NULL не равен ни одному другому значению, даже самому себе.

NaN (Not a Number):

• NaN используется в числовых вычислениях, чтобы указать на неопределенный результат или ошибку.

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

Различие между NULL и NaN заключается в их контексте использования: NULL применяется в SQL для обозначения отсутствия значения, в то время как NaN используется в числовых вычислениях для обозначения неопределенного результата.

В чем разница query и key?

В контексте SQL, термины "query" и "key" относятся к разным понятиям:

1. Query (Запрос): Это инструкция или набор инструкций, написанных на языке SQL, которые позволяют выполнить операции с данными в базе данных. Запросы могут включать выборку данных, их вставку, обновление, удаление и множество других операций. Например, SELECT * FROM users; — это запрос на выборку всех данных из таблицы пользователей.

2. Key (Ключ): Это атрибут или набор атрибутов в таблице, который помогает SQL-системе быстро и эффективно организовать, доступить и поддерживать целостность данных. Существуют различные типы ключей:

   • Primary Key (Первичный ключ): Уникально идентифицирует каждую запись в таблице.

   • Foreign Key (Внешний ключ): Обеспечивает ссылочную целостность между двумя таблицами.

   • Unique Key (Уникальный ключ): Гарантирует, что все значения в столбце уникальны.

Таким образом, "query" это команды для работы с данными, а "key" — это элементы структуры базы данных, обеспечивающие управление и целостность данных.

Чем отличаются WHERE и HAVING?

WHERE и HAVING - это два разных оператора в SQL для фильтрации данных, применяемых в разных контекстах:

WHERE:

• WHERE используется для фильтрации строк до группировки или агрегации данных.

• Он применяется к отдельным строкам и определяет, какие строки будут включены в результат запроса.

• WHERE применяется к строкам до выполнения агрегации.

HAVING:

• HAVING используется для фильтрации результатов агрегации данных после выполнения группировки.

• Он применяется к группам строк, сгруппированным по определенным критериям, и определяет, какие группы будут включены в результат запроса.

• HAVING применяется к результатам агрегации после выполнения группировки.

Перечислите типы JOINов, дайте характеристику каждому.

В SQL существует несколько типов операторов JOIN:

1. INNER JOIN:

   • Возвращает только те строки, которые имеют совпадения в обоих таблицах по указанным условиям.

   • Если в обеих таблицах нет совпадений, строки не возвращаются.

2. LEFT JOIN (или LEFT OUTER JOIN):

   • Возвращает все строки из левой таблицы (первой в запросе), а также строки из правой таблицы, которые имеют совпадения с условием JOIN.

   • Если в правой таблице нет совпадений, возвращается NULL для столбцов правой таблицы.

3. RIGHT JOIN (или RIGHT OUTER JOIN):

   • Возвращает все строки из правой таблицы (второй в запросе), а также строки из левой таблицы, которые имеют совпадения с условием JOIN.

   • Если в левой таблице нет совпадений, возвращается NULL для столбцов левой таблицы.

4. FULL JOIN (или FULL OUTER JOIN):

   • Возвращает все строки из обеих таблиц, совпадающие и несовпадающие по условию JOIN.

   • Если нет совпадений, возвращаются NULL значения для недостающих столбцов.

5. CROSS JOIN:

   • Возвращает декартово произведение всех строк из обеих таблиц, то есть каждая строка из одной таблицы объединяется со всеми строками из другой таблицы.

В одной таблице 2 строчки, в другой 3, сколько минимум строк будет при иннер и лефт джойне? А сколько максимум вне зависимости от джойна?

При выполнении INNER JOIN минимальное количество строк равно 0, если нет совпадений между строками обеих таблиц.

При выполнении LEFT JOIN минимальное количество строк равно количеству строк в левой таблице, т.е. 2 строки, так как левые таблицы строки всегда включаются, даже если нет совпадений.

Максимум строк (вне зависимости от типа JOIN): 2*3=6.

Расскажите про PIVOT таблицы.

PIVOT в SQL - это операция, которая позволяет преобразовывать строки данных в столбцы. Обычно это используется для трансформации результатов запроса, где значения из одного столбца (обычно строковые значения) становятся заголовками столбцов. Это позволяет легче анализировать данные, особенно в случае агрегированных результатов. PIVOT в SQL использует агрегирующую функцию, такую как SUM или MAX, чтобы сгруппировать данные по определенным критериям. Пример использования PIVOT может выглядеть так:

SELECT *
FROM (
    SELECT category, year, sales
    FROM sales_table
) AS src
PIVOT (
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS piv;

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

Указана база PosgreSQL. Вы ее самостоятельно разворачивали, в контейнере?

Опишите свой реальный опыт развёртывания PostgreSQL. Если использовали Docker, расскажите об образе, настройках подключения, постоянном томе для данных и запуске через Docker CLI или Compose. Если самостоятельно базу не разворачивали, укажите это прямо.

Как получить план выполнения запроса (EXPLAIN/EXPLAIN ANALYZE)?

Чтобы получить план выполнения запроса в PostgreSQL, вы можете использовать команду EXPLAIN перед вашим SQL-запросом. Если вы хотите выполнить запрос и получить его план выполнения, используйте команду EXPLAIN ANALYZE.

В чем отличия между нереляционными (NoSQL) и реляционными (SQL) базами данных, и для каких целей они обычно применяются?

Нереляционные (NoSQL) и реляционные (SQL) базы данных отличаются по структуре данных, модели запросов и способу обработки информации:

Реляционные базы данных (SQL):

• Организованы в виде таблиц с жесткими схемами и строгими правилами отношений между таблицами.

• Для доступа и обработки данных используется язык структурированных запросов (SQL).

• Подходят для приложений с жесткими требованиями к структуре данных и целостности, таких как финансовые системы, системы управления заказами и CRM.

Нереляционные базы данных (NoSQL):

• Основаны на различных моделях данных, таких как документы, ключ-значение, столбцы или графы.

• Обычно не имеют жестких схем и позволяют гибко хранить разнородные данные.

• Предоставляют горизонтальное масштабирование и высокую производительность при работе с большими объемами данных.

• Часто используются для хранения и обработки полуструктурированных или неструктурированных данных, таких как веб-логи, социальные сети, аналитика больших данных и системы интернета вещей (IoT).