SQL Flashcards
1
Q
Какая разница между DROP и TRUNCATE?
A
DROP - удаляет всю таблицу
TRUNCATE - удаляет только данные таблицы
2
Q
Что такое case function?
A
Возвращение значений в зависимости от условия
CASE проверяет истинность набора условий и в зависимости от результата проверки может возвращать тот или иной результат.
Имеет два формата (оба поддерживают дополнительный аргумент ELSE):
- простое выражение CASE для определения результата сравнивает выражение с набором простых выражений
- поисковое выражение CASE для определения результата вычисляет набор логических выражений
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
Пример:
SELECT
column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS new_column_name
FROM table_name;
3
Q
Что такое collation?
A
Collation - параметр указывает SQL серверу, как нужно сортировать и сравнивать строки. Например, разные или нет строки “Apple” и “apple” зависит от указанного Collation.
4
Q
Типы репликации в SQL Server?
A
Репликация- набор технологий копирования и распространения данных и объектов БД между БД, а также синхронизации БД для поддержания согласованности.
- Репликация транзакций - изменения на издателе доставляются подписчику по мере их появления (почти в реальном времени). Изменения данных применяются на подписчике в том же порядке и в тех же рамках транзакций, в которых они выполнялись у издателя.
- Репликация слиянием - данные можно изменять как на издателе, так и на подписчике, а также отслеживать с помощью триггеров. Подписчик синхронизируется с издателем при подключении к сети и обменивается с ним всеми строками, которые изменились со времени последней синхронизации издателя и подписчика.
- Репликация моментальных снимков - моментальный снимок издателя применяется к подписчику. Данные распространяются точно в том виде, в котором они были представлены в определенный момент времени. Обновление данных не отслеживается. Во время синхронизации формируется моментальный снимок и отсылается подписчикам целиком.
- Одноранговая репликация - основанна на репликации транзакций, распространяет согласованные на уровне транзакций изменения между несколькими экземплярами сервера почти в реальном времени.
- Двунаправленная репликация - представляет собой особую топологию репликации транзакций, которая позволяет двум серверам обмениваться изменениями друг с другом: каждый сервер публикует данные, после чего подписывается на публикацию с теми же данными от другого сервера.
- Обновляемые подписки - основаны на репликации транзакций. Когда данные для обновляемой подписки обновляются на подписчике, они сначала распространяются на издателя, а затем на других подписчиков.
5
Q
Self Join
A
SELF JOIN - используется для объединения таблицы с ней самой таким образом, будто это две разные таблицы, временно переименовывая одну из них. Нужен тогда, когда у разных полей одной таблицы могут быть одинаковые значения. Например, один и тот же участник музыкальной группы может быть и вокалистом, и, например, клавишником.
6
Q
Отличия БД от СУБД:
A
- База данных (БД) — это собственно хранилище данных, которое может быть структурировано разными способами.
- Система управления базами данных (СУБД) — это программное обеспечение, которое помогает управлять и манипулировать данными в базе данных
7
Q
Типы БД:
A
- Иерархические БД организованы как дерево, где связи устанавливаются в виде “родитель-потомок”.
- Сетевые БД имеют более сложную структуру с множественными связями между записями.
- Реляционные БД (SQL) основаны на таблицах с данными, связанными через общие ключи. На курсе мы будем работать с ними.
- Нереляционные БД (известные как NoSQL) структурируют данные не по табличной схеме, а используют форматы, такие как ключ-значение, документы, колонки или графы.
8
Q
Типы СУБД:
A
-
Реляционные СУБД:
- MySQL: Открытая система, широко используемая в веб-разработке. На курсе мы будем работать с ней.
- Oracle Database: Коммерческая СУБД, широко используемая в корпоративных решениях.
- Microsoft SQL Server: Предназначена для интеграции с другими продуктами Microsoft.
- PostgreSQL: Открытая СУБД, поддерживает большие базы данных и сложные запросы.
-
Нереляционные (NoSQL) СУБД:
- MongoDB: Документо-ориентированная СУБД, хранит данные в формате похожем на JSON.
- Cassandra: Ориентирована на работу с большими объемами данных.
- Redis: СУБД на основе ключ-значение, используется для кэширования данных и реализации очередей сообщений.
9
Q
Типы отношений в SQL
A
- 1 к 1 (Один к одному): Каждая запись в одной таблице соответствует одной записи в другой таблице.
- 1 ко многим (Один ко многим): Одна запись в первой таблице может быть связана с многими записями во второй таблице.
- Многие к 1 (Многие к одному): Множество записей в одной таблице связаны с одной записью в другой таблице.
- Многие ко многим (Многие ко многим): Записи в одной таблице могут быть связаны с множеством записей в другой таблице.
10
Q
Нормализация
A
это процесс организации данных для снижения избыточности и улучшения целостности.
11
Q
Избыточность
SQL
A
относится к повторению данных в разных таблицах, что может привести к несоответствию и ошибкам при обновлении. Нормализация помогает уменьшить эту избыточность, распределяя данные так, чтобы каждый элемент информации был сохранен только в одном месте. Это обеспечивает более чистую и эффективную структуру базы данных.
12
Q
Целостность
SQL
A
относится к поддержанию точности и надежности информации. Это включает обеспечение того, чтобы данные оставались последовательными и верными во всех таблицах базы данных. Целостность обеспечивается правилами, которые предотвращают ввод некорректных данных и поддерживают взаимосвязь между различными частями данных.
13
Q
Нормальная форма
A
это уровень, на котором организованы данные, чтобы минимизировать избыточность и улучшить целостность. Каждая нормальная форма определяет набор правил для структурирования данных, и существует несколько уровней нормальных форм. Переход от одной нормальной формы к другой помогает сделать базу данных более организованной, уменьшая повторение и улучшая управление данными.
14
Q
0NF (Нулевая нормальная форма):
A
нет структуры; данные могут быть дублированы и неструктурированы.
15
Q
1NF (Первая нормальная форма):
A
каждая запись уникальна, и каждый атрибут содержит только атомарные значения.
16
Q
2NF (Вторая нормальная форма):
A
Таблица находится во второй нормальной форме, если она уже в 1NF и все неключевые атрибуты полностью зависят от первичного ключа, а не от части его.
17
Q
Денормализация
A
это процесс, используемый для оптимизации производительности базы данных путем добавления избыточности.
Это может включать объединение таблиц, чтобы уменьшить количество соединений (joins), или добавление избыточных копий данных в одной таблице для ускорения чтения за счет потенциально замедленной записи и увеличения использования пространства. Это противоположный нормализации подход, который часто используется в больших базах данных, где скорость чтения критически важна.
18
Q
INTEGER (INT):
Типы данных SQL
A
- Пример:
age INT - Этот тип данных используется для хранения целых чисел, например, возраста.
19
Q
FLOAT и DOUBLE:
Типы данных SQL
A
- Пример:
price FLOATилиaverage_score DOUBLE - Эти типы данных используются для хранения чисел с плавающей точкой, таких как цены или средние оценки.
20
Q
VARCHAR
Типы данных SQL
A
Пример: first_name VARCHAR(50)
Этот тип данных предназначен для хранения текстовых строк переменной длины, таких как имена.
21
Q
CHAR:
Типы данных SQL
A
- Пример:
gender CHAR(1) - CHAR также используется для хранения строк, но фиксированной длины. В этом примере, CHAR(1) будет использован для хранения одного символа (например, “M” или “F” для пола).
22
Q
DATE:
Типы данных SQL
A
- Пример:
birth_date DATE - Этот тип данных предназначен для хранения даты (год, месяц, день).
23
Q
ENUM:
Типы данных SQL
A
Пример: status ENUM(‘active’, ‘inactive’, ‘pending’)
ENUM представляет собой перечисление значений и используется, когда столбец может иметь только одно из ограниченного множества значений.
23
Q
BOOLEAN:
Типы данных SQL
A
Пример: is_active BOOLEAN
BOOLEAN предназначен для хранения логических значений, таких как true или false.
23
TIME:
| Типы данных SQL
Пример: appointment_time TIME
TIME используется для хранения времени (часы, минуты, секунды).
24
DATETIME и TIMESTAMP:
| Типы данных SQL
Пример: created_at DATETIME или updated_at TIMESTAMP
Эти типы данных используются для хранения даты и времени. DATETIME сохраняет фиксированную дату и время, в то время как TIMESTAMP может использоваться для отслеживания времени создания и обновления записей.
25
BLOB и TEXT:
| Типы данных SQL
- Пример: `profile_picture BLOB` или `comments TEXT`
- BLOB (Binary Large Object) и TEXT используются для хранения больших объемов данных, таких как изображения (BLOB) и длинные тексты (TEXT).
26
NULL
| Типы данных SQL
Это специальное значение в MySQL, которое указывает на отсутствие данных или неопределенное значение в столбце. Когда столбец имеет тип данных, который допускает NULL (например, VARCHAR, INT, DECIMAL, и т.д.) и в него не внесено конкретное значение, то это значение считается NULL. Это означает, что в ячейке
27
Пустое значение (Empty Value):
| Типы данных SQL
Это может относиться к строкам VARCHAR или TEXT, где внутри столбца есть строка, но она пуста, т.е., не содержит символов. Это не то же самое, что NULL. В этом случае, столбец не содержит NULL, но содержит пустую строку.
28
Получение списка всех доступных баз данных.
| Работа с базами данных
`SHOW DATABASES;`
29
SQL. Создание новой базы данных mydb
| Работа с базами данных
`CREATE DATABASE mydb;`
30
Удаление существующей базы данных и всех её данных.
| Работа с базами данных
`DROP DATABASE mydb;`
31
Активировать базу данных с которой будешь работать
| Работа с базами данных
- Выбор базы данных, с которой вы хотите работать (активировать).
- Пример:
`USE mydb;`
32
CREATE TABLE:
| Работа с таблицами
- Создание новой таблицы.
- Пример:
`CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, birthdate DATE );`
33
SQL. Удаление таблицы users и всех её данных.
| Работа с таблицами
`DROP TABLE users;`
34
DESCRIBE (или DESC):
| Работа с таблицами
- Получение информации о структуре таблицы.
- Пример:
`DESCRIBE users;`
35
INSERT INTO:
| Работа с таблицами
- Вставка новых записей в таблицу.
- Пример:
`INSERT INTO users (username, email, birthdate) VALUES ('john_doe', 'john@example.com', '1990-05-15');`
36
SQL. Удаление записей из таблицы.
| Работа с таблицами
`DELETE FROM users WHERE id = 1;`
37
UPDATE:
| Работа с таблицами
- Обновление данных в таблице.
- Пример:
`UPDATE users SET email = 'new_email@example.com' WHERE id = 2;`
38
Изменение структуры существующей таблицы.
- Примеры:
- Добавление нового столбца:
`ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);`
- Изменение типа столбца:
`ALTER TABLE users MODIFY COLUMN phone_number INT;`
- Удаление столбца:
`ALTER TABLE users DROP COLUMN birthdate;`
39
Как получить все данные из таблицы?
| SQL
`SELECT * FROM employees;`
40
Как получить уникальные значения столбца?
| SQL
- `DISTINCT`
- Пример:
`SELECT DISTINCT department FROM employees;`
41
Как получить первые несколько записей из таблицы?
| SQL
- `SELECT * FROM LIMIT`
- Пример: `SELECT * FROM employees LIMIT 3;`
42
Как переименовать столбец в результате запроса?
| SQL
- `SELECT field AS`
- Переименование столбца в результате запроса.
- Пример:
`SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;`
43
Использование оператора OR для комбинирования условий.
| SQL
- `SELECT FROM WHERE OR`
-
- Пример:
`SELECT * FROM employees WHERE department = 'HR' OR department = 'IT';`
44
Получить данные с условием.
| SQL
- `SELECT FROM WHERE`
- Пример:
`SELECT * FROM employees WHERE department = 'HR';`
45
Использование оператора AND для комбинирования условий.
| SQL
- `SELECT FROM WHERE AND`
- Пример:
```
SELECT *
FROM employees
WHERE department = 'Finance' AND salary > 60000.00;
```
46
Поиск строк, где символ подчеркивания (_) заменяет один символ.
| SQL
- `LIKE _`
- Пример:
`SELECT * FROM employees WHERE last_name LIKE 'D_e';`
47
Поиск строк с произвольными символами перед и/или после указанных символов.
| SQL
- `LIKE %`
- Пример:
`SELECT * FROM employees WHERE first_name LIKE 'J%';`
48
SELECT * FROM employees
Поиск значений, которые не соответствуют условию department = 'IT'
Пример:
`SELECT * FROM employees WHERE NOT department = 'IT';`
48
SQL - Найти строку, имеющую в середине "g", имеющий один символ перед "g" и любое количество после.
- Пример:
`SELECT * FROM employees WHERE last_name LIKE '_g%';`
48
SQL Поиск значений, соответствующих списку значений.
SELECT * FROM employees где department находится в списке из HR и Finance
`SELECT * FROM employees WHERE department IN ('HR', 'Finance');`
49
SQL Поиск значений в заданном диапазоне
SELECT * FROM employees где salary между двумя суммами
`SELECT * FROM employees WHERE salary BETWEEN 55000.00 AND 75000.00;`
50
Сложное условие с операторами AND и сравнениями.
`SELECT * FROM employees WHERE зарплата больше 60000 и департамент - Finance`
`SELECT * FROM employees WHERE (salary > 60000.00) AND (department = 'Finance');`
51
SQL. Сортировка результатов по возрастанию.
SELECT * FROM employees - отсортировать по зарплате в возрастающем порядке
`SELECT * FROM employees ORDER BY salary ASC;`
52
SQL. Сортировка результатов по убыванию.
SELECT * FROM employees - отсортировать по зарплате по убыванию
`SELECT * FROM employees ORDER BY salary DESC;`
53
SQL. Получение минимального и максимального значения из столбца.
SELECT минимальна зарплата и максимальная зарплата
`SELECT MIN(salary) AS "Min Salary", MAX(salary) AS "Max Salary" FROM employees;`
54
SQL- Подсчет количества записей которые будут выданы
`SELECT COUNT(*) AS "Total Employees" FROM employees;`
55
SQL Получение среднего значения из столбца.
SELECT среднюю зарплату из таблицы employees
`SELECT AVG(salary) AS "Average Salary" FROM employees;`
56
SQL - объединить строки из двух таблиц на основе совпадающих значений
- INNER JOIN используется для объединения строк из двух таблиц на основе совпадающих значений в указанных столбцах.
- Пример:
`SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;`
- Этот запрос объединяет данные из таблиц "employees" и "departments" по значению столбца "department_id". В результате получается список имен сотрудников и названий департаментов, к которым они относятся.
57
LEFT JOIN:
- LEFT JOIN используется для объединения строк из левой таблицы (employees) со всеми совпадающими строками из правой таблицы (departments).
- Пример:
`SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;`
- Этот запрос вернет список всех сотрудников и их департаментов. Если сотрудник не привязан к департаменту, его данные также будут включены в результат.
58
RIGHT JOIN:
- RIGHT JOIN используется для объединения строк из правой таблицы (departments) со всеми совпадающими строками из левой таблицы (employees).
- Пример:
`SELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;`
- Этот запрос вернет список всех департаментов и их сотрудников. Если департамент не имеет сотрудников, его данные также будут включены в результат.
59
SQL Вложенные подзапросы:
Вложенные подзапросы позволяют выполнять запросы внутри других запросов.
**Пример (получение средней зарплаты в департаменте "HR"):**
`SELECT department_name, (SELECT AVG(salary) FROM employees WHERE department_id = departments.department_id) AS avg_salary FROM departments WHERE department_name = 'HR';`
В этом запросе внутренний подзапрос
`(SELECT AVG(salary) FROM employees WHERE department_id = departments.department_id)`
вычисляет среднюю зарплату для каждого департамента, а внешний запрос выбирает только департамент "HR" и его среднюю зарплату.
60
SQL Экспорт данных:
Экспорт данных в MySQL позволяет сохранить содержимое базы данных или конкретные таблицы во внешний файл. Это полезно для создания резервных копий данных, а также для передачи данных между разными системами или средами.
61
Импорт данных:
* Импорт данных в MySQL представляет собой процесс загрузки данных из внешнего файла (например, SQL-дампа или CSV-файла) в базу данных. Это может быть полезно для восстановления данных из резервных копий или для обновления базы данных с новыми данными.
* Также это может использоваться для тестирования участков дефектных данных от конечных пользователей и заказчиков для выяснения корневой причины проблемы.
* Импорт данных можно применять для загрузки заранее подготовленных тестовых данных в БД. Это позволяет создавать конкретные тестовые сценарии с известными данными.
62
Миграция данных:
* Миграция данных представляет собой процесс перемещения данных из одной базы данных в другую, часто при изменении структуры базы данных или переносе данных на другой сервер. Это может быть необходимо при обновлении приложений или масштабировании системы.
* После миграции данных тестировщику необходимо убедиться, что система работает в штатном режиме, все разрешения для ролей на месте, как и основные данные приложения.
63
SQL UNION:
- UNION используется для объединения результатов двух или более запросов в один набор результатов. Он удаляет дубликаты строк.
- Пример (получение списка всех сотрудников и департаментов):
`SELECT first_name, last_name, department_id FROM employees UNION SELECT department_name AS first_name, NULL AS last_name, department_id FROM departments;`
- Этот запрос объединяет список сотрудников из таблицы "employees" с названиями департаментов из таблицы "departments".
64
UNION ALL:
- UNION ALL также используется для объединения результатов двух или более запросов в один набор результатов, но он сохраняет все строки, включая дубликаты.
- Пример (получение списка всех сотрудников и департаментов):
```
SELECT first_name, last_name, department_id
FROM employees
UNION ALL
SELECT department_name AS first_name, NULL AS last_name, department_id
FROM departments;
```
- В этом запросе используется UNION ALL, поэтому дубликаты будут сохранены в результате.
65
SQL HAVING:
- HAVING используется для фильтрации результатов агрегированных функций (например, COUNT, SUM, AVG) в GROUP BY.
- Пример (получение департаментов со средней зарплатой выше 60000):
```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 60000;`
```
- Этот запрос сначала агрегирует данные по департаментам, а затем фильтрует результаты, оставляя только те департаменты, у которых средняя зарплата превышает 60000.
66
Агрегатные функции:
SELECT **AVG(number)** FROM courses; - посчитать среднее значение всех значений из столбца
1. AVG - среднее значение
1. SUM - сумма
1. MIN
2. MAX
1. COUNT - количество заполненных записей
67
SQL - Создать таблицу
`CREATE TABLE tablename (поля + типы данных + primary_key)`
```
CREATE TABLE users
( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, birthdate DATE );
```
68
SQL - удалить таблицу users
`DROP TABLE users;`
69
SQL - вставка записей в таблицу
Вставка новых записей в таблицу.
`INSERT INTO tablename (столбцы) VALUES (значения)`
`INSERT INTO users (username, email, birthdate) VALUES ('john_doe', 'john@example.com', '1990-05-15');`
70
Удалить запись из таблицы
Удаление записей из таблицы.
`DELETE FROM users WHERE id = 1;`
71
SQL - Обновление данных в таблице.
`UPDATE tablename SET поле = 'значение' WHERE условие`
`UPDATE users SET email = 'new_email@example.com' WHERE id = 2;`
72
SQL отличие UNION от UNION ALL
1. UNION - удаляет дубли
2. UNION ALL - выводит с дублями
73
SQL. Правильный порядок ключевых слов
1. SELECT -- перечисление полей результирующей таблицы
1. FROM -- указание источника данных
1. WHERE -- фильтрация данных
1. GROUP BY -- группировка данных
1. HAVING -- фильтрация данных после группировки
1. ORDER BY -- сортировка результирующей таблицы
1. LIMIT -- ограничение количества выводимых записей
