Основные понятия SQL

Все темы Systems Analyst

Приходилось ли вам писать SQL-запросы? Для чего?

Приведите реальный пример SQL-запроса и его задачи: извлечение, проверка, изменение или анализ данных. Не заявляйте о практике, которой не было.

Зачем нужны индексы в таблицах БД?

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

1. Ускорение доступа к данным: Индексы позволяют быстрее находить строки в таблице по значению индексируемого столбца. Это особенно полезно при выполнении SELECT запросов с условиями WHERE, которые используют индексированные столбцы.

2. Улучшение производительности запросов: Запросы, которые используют индексы, выполняются быстрее, так как база данных может применять оптимизированный доступ к данным.

3. Повышение эффективности соединений (JOIN): Индексы могут улучшить производительность запросов, которые соединяют несколько таблиц.

4. Поддержание уникальности данных: Уникальные индексы гарантируют, что значения в столбце (или группе столбцов) уникальны, что особенно важно для первичных ключей.

5. Поддержка внешних ключей: Индексы могут использоваться для ускорения операций проверки ограничений внешних ключей.

6. Оптимизация сортировки: Индексы ускоряют операции сортировки результатов запросов.

Знакомы ли вы с нормализацией баз данных?

Да, я знаком с нормализацией баз данных. Нормализация представляет собой процесс организации данных в реляционной базе данных для уменьшения избыточности и повышения их структурной целостности. Основные формы нормализации включают:

1. Первая нормальная форма ( 1NF ): Все значения в таблице должны быть атомарными (неделимыми). Это значит, что каждая ячейка должна содержать только одно значение, а не списки значений или структуры данных.

2. 2NF: отношение находится в 1NF, и каждый атрибут, не входящий ни в один кандидатный ключ, полностью зависит от каждого кандидатного ключа, без зависимости от его части.

3. Третья нормальная форма ( 3NF ): Таблица должна быть в 2NF, и все неключевые столбцы должны зависеть только от первичного ключа, а не от других неключевых столбцов.

4. BCNF: для каждой нетривиальной функциональной зависимости X → Y набор X должен быть суперключом.

5. 4NF: для каждой нетривиальной многозначной зависимости X →→ Y набор X должен быть суперключом.

6. 5NF: каждая нетривиальная зависимость соединения должна следовать из кандидатных ключей. Она касается декомпозиции с восстановлением соединением без потерь.

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

Задача на нормализацию таблиц базы данных. Дают две таблицы с некоторыми полями. Что в них не так и почему? Как исправить?

В задаче на нормализацию таблицы, проблемы могут включать:

1. Дублирование данных: Если в обеих таблицах содержатся одни и те же данные (например, повторяющиеся столбцы или строки), это может привести к избыточности и потере целостности данных.

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

Для исправления можно предложить следующее:

1. Разделение таблиц: Разделите данные на более мелкие, нормализованные таблицы, чтобы устранить избыточность данных и соответствовать требованиям нормальных форм.

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

3. Удаление повторяющихся данных: Избавьтесь от дублирования данных, чтобы упростить обслуживание и минимизировать риск ошибок.

Даются следующие три операции SQL. Какой будет результат? TRUNCATE TABLE; ROLLBACK; SELECT * FROM TABLE;

Эти три операции SQL выполняют следующее:

1. TRUNCATE TABLE имя_таблицы очищает таблицу. Возможность отката зависит от СУБД: в PostgreSQL TRUNCATE можно откатить внутри незавершённой транзакции; в MySQL он вызывает неявный COMMIT. В условии не указаны имя таблицы, СУБД и начало транзакции, поэтому однозначного результата нет.

2. ROLLBACK; - Отменяет изменения, сделанные в текущей транзакции. Если транзакция еще не была зафиксирована (committed), все изменения возвращаются к состоянию до начала транзакции.

3. SELECT * FROM TABLE; - Выполняет выборку всех записей из таблицы и возвращает результат в виде набора строк.

Каждая операция имеет свое специфическое назначение в управлении данными и транзакциями в SQL.

Чем TRUNCATE отличается от DELETE?

TRUNCATE и DELETE - это две разные операции для удаления данных в SQL:

1. TRUNCATE :

   • Очищает все данные из таблицы.

   • Обычно освобождает данные целиком, без построчного удаления. Операция всё равно журналируется в объёме, необходимом конкретной СУБД для восстановления.

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

   • Не вызывает DELETE-триггеры. В PostgreSQL существуют отдельные ON TRUNCATE-триггеры.

2. DELETE :

   • Удаляет определенные строки из таблицы в соответствии с заданными условиями с использованием WHERE.

   • Удаленные строки сохраняются в журнале транзакций, что позволяет откатить транзакцию (если она не зафиксирована).

   • Можно использовать с условиями WHERE для выборочного удаления.

   • Вызывает триггеры, если они определены на удаление.

Что такое транзакция?

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

Какими свойствами должна обладать транзакция? (ACID)

Транзакция должна обладать следующими свойствами ACID:

1. Атомарность ( Atomicity ): Все операции транзакции выполняются либо все, либо ни одна из них. Нет промежуточных состояний.

2. Согласованность ( Consistency ): Транзакция должна переводить базу данных из одного согласованного состояния в другое согласованное состояние. Все правила целостности должны быть соблюдены.

3. Изолированность: параллельные транзакции взаимодействуют согласно выбранному уровню изоляции. При SERIALIZABLE результат эквивалентен некоторому последовательному выполнению; более слабые уровни допускают определённые аномалии.

4. Долговечность ( Durability ): Результаты успешно завершенной транзакции должны быть постоянно сохранены в базе данных даже в случае сбоя системы или перезагрузки.

Чем отличается UNION от UNION ALL?

Основное различие между операторами UNION и UNION ALL в SQL заключается в том, как они обрабатывают дублирующиеся строки при объединении результатов запросов:

1. UNION: Оператор UNION удаляет дублирующиеся строки из результатов объединения. Если два запроса объединяются оператором UNION, он вернет только уникальные строки из обоих запросов.

2. UNION ALL: Оператор UNION ALL возвращает все строки из обоих запросов, включая дублирующиеся строки. Он не производит удаление дубликатов и просто объединяет все строки.

Можете назвать три первые формы нормализации?

1. Первая нормальная форма ( 1NF ): В этой форме все атрибуты таблицы должны быть атомарными, то есть не должны содержать повторяющихся или составных значений. Каждая ячейка в таблице должна содержать только одно значение.

2. 2NF: отношение находится в 1NF, и каждый атрибут, не входящий ни в один кандидатный ключ, полностью зависит от каждого кандидатного ключа, без зависимости от его части.

3. Третья нормальная форма ( 3NF ): Таблица находится в третьей нормальной форме, если она находится во второй нормальной форме и все неключевые атрибуты являются функционально зависимыми только от первичного ключа, а не от других неключевых атрибутов.

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

Что такое первичный ключ? Каким свойством обладает первичный ключ? Что такое внешний ключ?

Определения:

1. Первичный ключ (Primary Key): Первичный ключ в базе данных уникально идентифицирует каждую запись в таблице. Он гарантирует уникальность данных в столбце или комбинации столбцов и обеспечивает быстрый доступ к записям. Первичный ключ не может содержать пустых значений (NULL).

2. Свойства первичного ключа:

   • Уникальность: Каждое значение первичного ключа должно быть уникальным в пределах таблицы.

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

   • Не может быть NULL: Первичный ключ не может содержать пустые значения.

3. Внешний ключ связывает столбцы со столбцами уникального ключа другой или той же таблицы и обеспечивает ссылочную целостность. Целью может быть PRIMARY KEY или подходящее ограничение UNIQUE. NULL допустим, если его не запрещает отдельное ограничение.

   Пример использования внешнего ключа: если в таблице “Заказы” есть столбец “КлиентID”, который является внешним ключом, он ссылается на столбец “ID” в таблице “Клиенты”, обеспечивая связь между заказами и клиентами.

Что такое поисковые пути в базах данных?

Термин требует контекста. В PostgreSQL search_path задаёт порядок схем для поиска объектов, указанных без имени схемы. Пути доступа к данным в плане запроса, например последовательное или индексное сканирование, являются другим понятием.

Какие бывают представления в БД?

Обычное представление сохраняет определение запроса и вычисляет данные при обращении; само по себе оно не гарантирует ускорения. Материализованное представление хранит результат и требует обновления, чтобы отразить изменения исходных данных. Поддержка обновляемых представлений и правила доступа зависят от СУБД.

Для чего используется HAVING в SQL?

Клауза HAVING в SQL используется для фильтрации данных в результирующем наборе запроса, который включает операции группировки (GROUP BY). Она применяется для задания условий, которым должны удовлетворять агрегированные значения, чтобы быть включенными в результат. Это позволяет делать выборки данных на основе агрегатных функций (например, SUM, COUNT, AVG) после их вычисления в запросе с использованием GROUP BY.

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

Из требований выписывают бизнес-сущности, их атрибуты и устойчивые идентификаторы. Затем для каждой пары сущностей определяют смысл связи, кардинальность и обязательность, включая допустимость отсутствующего значения. Бизнес-правила переводят в ограничения: уникальность, допустимые значения, составные ключи и зависимости атрибутов. Модель нормализуют, чтобы один факт хранился в одном месте и обновление не порождало противоречий, но не добавляют физические детали вроде индексов и партиций. Результат проверяют на реальных сценариях создания, изменения и удаления данных, а также на граничных примерах вместе с владельцами требований.

Чем отличаются уровни модели данных?

Концептуальная модель отвечает, какие бизнес-понятия существуют и как они связаны. Она понятна заказчику и обычно не содержит атрибутов, ключей и особенностей СУБД. Логическая модель уточняет сущности, атрибуты, идентификаторы, кардинальности, обязательность и правила целостности, оставаясь независимой от конкретного хранилища. Физическая модель описывает реализацию в выбранной СУБД: таблицы и столбцы, типы, индексы, ограничения, партиционирование и денормализацию. Переход между уровнями не механический: один логический объект может превратиться в несколько физических, и наоборот.

Какие ограничения и действия задают для внешнего ключа?

Внешний ключ требует, чтобы непустое значение ссылалось на существующий уникальный или первичный ключ совместимого типа. Допустимость NULL задаётся отдельно и означает отсутствие обязательной связи. Для обновления и удаления родительской строки выбирают действие по бизнес-правилу: RESTRICT или NO ACTION запрещает операцию при зависимостях, CASCADE переносит изменение или удаление, SET NULL разрывает необязательную связь. Поддержка и точная семантика действий зависят от СУБД. CASCADE опасно выбирать по умолчанию: оно может удалить большую цепочку данных, поэтому последствия проверяют явно.

Когда выбрать графовую БД, а когда key-value?

Графовую БД выбирают, когда главные запросы обходят связи переменной глубины: маршруты, зависимости, рекомендации, поиск сообществ. Она хранит вершины и рёбра как основные элементы и удобна, когда форма обхода важнее доступа к одной записи. Key-value подходит для быстрого чтения и записи значения по известному ключу: сессий, кеша, профилей или счётчиков. Обычно он не даёт богатых запросов по содержимому значения и связям. Решение проверяют на реальных запросах, требованиях к транзакциям, объёме и эксплуатации. Категории пересекаются, поэтому название продукта не заменяет проверку его конкретных возможностей.