Как получить информацию о структуре БД для документации

Создаём документацию БД и словарь данных информационной системы своими руками. Полезные SQL-скрипты и приемы документирования базы данных

Смотреть статью

DB Fiddle — онлайн-площадка для работы с базами данных SQL

Её можно использовать как своеобразную песочницу для тестирования, отладки и обмена фрагментами SQL. Есть поддержка разных версий MySQL, PostgreSQL и SQLite

Из интересного: к работе над базой можно подключить приятеля — делается это буквально в пару кликов

SQLGlot - это универсальный инструмент для работы с SQL.

Он может использоваться для форматирования SQL-запросов, а также для трансляции между 20 различными диалектами, включая DuckDB, Presto, Spark, Snowflake и BigQuery.

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

Объяснение Запроса
Подзапрос: Здесь используется подзапрос для выбора CustomerID из таблицы Orders, группируя по CustomerID и применяя условие HAVING COUNT(OrderID) > 2. Это выбирает клиентов, сделавших более двух заказов.

Объединение (JOIN): Основной запрос использует JOIN для соединения таблиц Customers и результата подзапроса по CustomerID.

Условие WHERE: Фильтрация происходит по полю City в таблице Customers, чтобы выбрать только тех клиентов, которые находятся в "New York".

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

#вопросы_с_собеседований
Имеет ли значение порядок колонок в составном индексе?

Да, порядок колонок в составном индексе имеет значение.

Рассмотрим пример:

У нас есть таблица orders с колонками order_id, customer_id, order_date, product_id и quantity. Нам необходимо создать составной индекс для ускорения поиска заказов по customer_id и order_date.

Мы можем создать два индекса:

1. Индекс по (customer_id, order_date)
2. Индекс по (order_date, customer_id)

В первом случае, запросы с условием WHERE customer_id = ? AND order_date = ? будут выполняться быстрее, так как эти колонки находятся в порядке, указанном в индексе.

Во втором случае, запросы с условием WHERE order_date = ? AND customer_id = ? будут выполняться медленнее, так как порядок колонок не соответствует порядку в индексе.

Таким образом, порядок колонок в составном индексе имеет значение и должен соответствовать порядку, в котором используются колонки в запросах.

#Вопросы_с_собеседования

❓Опишите различные типы индексов

Есть три типа индексов, а именно:

1. Уникальный индекс (Unique Index): этот индекс не позволяет полю иметь повторяющиеся значения, если столбец индексируется уникально. Если первичный ключ определен, уникальный индекс может быть применен автоматически.
2. Кластеризованный индекс (Clustered Index): этот индекс меняет физический порядок таблицы и выполняет поиск на основе значений ключа. Каждая таблица может иметь только один кластеризованный индекс.
3. Некластеризованный индекс (Non-Clustered Index): не изменяет физический порядок таблицы и поддерживает логический порядок данных. Каждая таблица может иметь много некластеризованных индексов.

#Вопросы_с_собеседования

❓Что такое строковые функции в SQL?

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

• LEN () — возвращает длину значения в текстовом поле.
• LOWER () — преобразует символьные данные в нижний регистр
• UPPER () — преобразует символьные данные в верхний регистр
• SUBSTRING () — извлекает символы из текстового поля.
• LTRIM () — Это удалить все пробелы в начале строки.
• RTRIM () — удалить все пробелы в конце строки.
• CONCAT () — функция Concatenate объединяет несколько символьных строк вместе.
• REPLACE () — для обновления содержимого строки.

#Вопросы_с_собеседования

❓Что такое Self JOIN?

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

Например, следующий SQL-запрос объединяет клиентов из одного города:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

#Вопросы_с_собеседования

❓Выберите из таблицы workers все записи, в которых сумма дня и месяца меньше 10-ти

Ответ на картинке.

#Вопросы_с_собеседования

❓Выберите из таблицы workers все записи, в которых сумма дня и месяца меньше 10-ти

Ответ на картинке.

#Вопросы_с_собеседования

❓При выборке из таблицы workers прибавьте к дате 1 день и отнимите 2 часа, 3 минуты

Ответ на картинке.

Задача

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

Рассмотрение CASE WHEN THEN как оператор if

Одним из применений CASE WHEN THEN является использование ее в качестве замены оператора if в программировании.

Использование конструкции CASE WHEN THEN может значительно упростить код и улучшить читаемость запросов в базе данных.

#вопросы_с_собеседований
Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.

Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id.

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

- Объяснение:

- SELECT: выбираем необходимые столбцы из таблицы page.
- FROM: указываем таблицу page, которая будет являться нашей основной таблицей.
- JOIN: объединяем таблицу sub_category по id подкатегории из таблицы page.
- ON: указываем, что id подкатегории в таблице page должен быть равен id подкатегории в таблице sub_category.
- JOIN: объединяем таблицу category по id категории из таблицы sub_category.
- ON: указываем, что id категории в таблице sub_category должен быть равен id категории в таблице category.
- SELECT: выбираем название подкатегории из таблицы sub_category и название категории из таблицы category.
- AS: переименовываем названия столбцов для удобства чтения.

#Вопросы_с_собеседования
Как SQL-запрос с использованием операторов JOIN может быть оптимизирован для ускорения выполнения, если одна из таблиц содержит миллионы записей, а другая – только несколько сотен? Опишите три различных подхода к оптимизации

Использование Индексов: Создание индексов на столбцах, участвующих в JOIN, может значительно ускорить процесс, особенно на столбцах большой таблицы. Если JOIN выполняется по столбцу, который является первичным ключом или имеет уникальный индекс, это обычно обеспечивает наилучшую производительность.

Оптимизация Порядка JOIN: Порядок, в котором таблицы соединяются в запросе, может влиять на производительность. Лучше начинать с таблицы с наименьшим количеством строк (в данном случае, таблицы с несколькими сотнями записей), что может уменьшить количество обрабатываемых данных на более ранней стадии выполнения запроса.

Использование Подзапросов или CTE (Common Table Expressions): В некоторых случаях, использование подзапросов или CTE для предварительной фильтрации данных перед выполнением JOIN может быть эффективным. Например, можно сначала выделить необходимые данные из меньшей таблицы с помощью подзапроса или CTE, а затем выполнить JOIN с большей таблицей. Это может сократить количество обрабатываемых данных и ускорить выполнение запроса.

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