SQL Flashcards
Что такое Реляционная Модель?
Предполагает мультитабличную систему организации данных, имитирующую отношения между сущностями программы (one-to-many/many-to-many/one-to-one)
Зачем в таблице primary key? Что это такое?
1) Чтобы соблюсти принцип Integrity: таблица не может содержать 2 полностью одинаковых ряда.
2) комбинация NOT NULL и UNIQUE constraints. Помечает каждую запись в базе данных уникальным значением.
~~~
country_name VARCHAR(40) NOT NULL UNIQUE, =>
ALTER TABLE countries ADD PRIMARY KEY (country_name);
also:
ADD CONSTRAINT pk_student PRIMARY KEY (name,birth_date);
~~~
Зачем в таблице foreign key?
1) создает связь между двумя таблицами и защищает от действий, которые могут нарушить связи между таблицами: он всегда ссылается на PRIMARY KEY другой таблицы.
2) С его помощью создаётся many-to-one relation.
3) Поддерживает referential actions при UPDATE / DELETE:
CASCADE:
SET NULL
SET DEFAULT
RESTRICT
NO ACTION
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(60)
department_id INT,
[ CONSTRAINT fk_department ] FOREIGN KEY (department_id)
REFERENCES departments(id) );
ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE employees DROP FOREIGN KEY fk_department;
Что такое one-to-one?
одному ряду (tuple) в данной таблице (relation) может соответствовать лишь один ряд в другой таблице, обратное тоже верно:гражданин - паспорт
Что такое many-to-many?
сотрудник - зона ответственности. Это отношение воплощено через сводную таблицу по принципу one-to-many.
Что такое identifying ralationship?
Когда ФК становится ПК или частью составного ПК в таблице.
Почему SQL - декларативный язык?
Потому, что он говорит что надо сделать но не говорит как.
Какие базовые типы данных есть в SQL?
- INTEGER
- FLOAT
- DECIMAL(20, 2): 20 - precision / 2 - scale
- VARCHAR(10): 10- max length.
- BOOLEAN
Что такое DDL? Какие операции в него входят? (4) Рассказать про них.
Операторы определения данных:
* CREATE создает объект БД (базу, таблицу, представление, пользователя и т. д.);
* ALTER изменяет объект;
- - ADD / MODIFY / DROP COLUMN | rename: CHANGE old new;
* DROP удаляет объект;
* TRUNCATE удаляет таблицу и создает ее пустую заново, но если в таблице были foreigh key, то создать таблицу не получится,
rollback после TRUNCATE невозможен.
NB: DDL команды транзакционны - разработчик не контролирует транзакцию, она открывается и закрывается сама.
Что такое DML? Какие операции в него входят? Рассказать про них.
- SELECT … FROM … WHERE … выбирает данные, удовлетворяющие заданным условиям;
SELECT concat(first_name, ' ', last_name) FROM persons;
**CASE** :
SELECT customer_id, first_name, CASE WHEN age >= 18 THEN 'Allowed' END AS can_vote FROM Customers;
COALESCE:
SELECT name, COALESCE(department, 'No department') AS dept
-
INSERT INTO добавляет новые данные (
VALUES);INSERT INTO SELECT FROM:
~~~
INSERT INTO table1 (column_1, column_2, …, column_n)
SELECT column_1, column_2, …, column_n
FROM table2 WHERE condition;
~~~ -
UPDATE изменяет существующие данные;
UPDATE table SET col1 = expr1, colN = exprN WHERE
- DELETE FROM удаляет данные при выполнении условия WHERE или все данные: DELETE FROM table_name;
Что такое TCL? Какие операции в него входят? Рассказать про них.
Transaction Control Language, TCL):
* BEGIN служит для определения начала транзакции;
* COMMIT применяет транзакцию;
* ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции;
* SAVEPOINT разбивает транзакцию на более мелкие.
Что такое DCL? Какие операции в него входят? Рассказать про них.
Data Control Language, DCL):
* GRANT предоставляет пользователю (группе) разрешения на определенные
операции с объектом;
* REVOKE отзывает ранее выданные разрешения;
* DENY задает запрет, имеющий приоритет над разрешением.
the order of evaluation of the statements in a quiery
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Нюансы работы с NULL в SQL. Как проверить поле на NULL?
NULL означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни 0.
1) При любой операции с NULL получится NULL (a + null = null)
2) При сравнении NULL с любым значением будет получен NULL
3) в агрегатных функциях значения null игнорируются
4)В логических операциях (OR) может вернуться true / false :
(NULL = 1) OR TRUE equals to TRUE
5) можно использовать COALISE:
SELECT id, name, COALESCE(age, 0) as age, grade FROM students;
6) можно задать constraint NOT NULL
Проверить на null: IS NULL, IS NOT NULL, сравнение с помощью “=” вернёт NULL.
Что такое JOIN? Виды Join’ов?
JOIN предназначен для обеспечения выборки данных из двух и более таблиц на основе общей колонки и включения этих данных в результирующую таблицу. Prymary key из одной таблицы должен ссылаться на Foreigh key другой таблицы.
* (INNER) JOIN Результатом объединения таблиц являются записи, общие для левой и правой таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
SELECT table1.col_name1 [AS name1], tableN.col_name2 [AS nameN]
FROM table1 [AS a]
[type_of_join] JOIN table2 [AS b]
ON table1.id = table2.id;
[type_of_join] JOIN table3
ON table2.col_name_table2 = table3.col_name_table3;
LIMIT 10, 5;
* LEFT (OUTER) JOIN. Производит выбор всех записей первой таблицы и соответствующих им записей второй таблицы. Если записи во второй таблице не найдены, то вместо них подставляется пустой результат (NULL). Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
* RIGHT (OUTER) JOIN с операндами, расставленными в обратном порядке. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
* FULL (OUTER) JOIN. Результатом объединения таблиц являются все записи, которые присутствуют в таблицах. Порядок таблиц для оператора не важен, поскольку
оператор является симметричным.
* CROSS JOIN (декартово произведение) При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
NB: вместо JOIN можно применять WHERE:
SELECT table1.col_name1, table2.col_name2
FROM table1 [AS a], table2 [AS b]
WHERE table1.id = table2.id;
https://sky.pro/media/join-v-sql/
Что лучше использовать join или подзапросы? Почему?
Подзапросы могут размещаться в SELECT, UPDATE, INSERT, and DELETE;
В SELECT могут размещаться в FROM и WHERE
Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя).
Подзапросы лучше использовать в случаях, когда нужно выбрать данные из таблицы на основании данных другой таблицы.
UPDATE students SET exams_passed = TRUE WHERE name in (
SELECT name FROM exam_results WHERE
math_exam_mark >= 18 AND english_exam_mark >= 18 );
DELETE FROM orders WHERE customer_id =
(SELECT customer_id FROM customers WHERE name = ‘Ann Smith’);
Что делает UNION?
UNION применяется для объединения результатов нескольких SQL-запросов в единую таблицу, состоящую из схожих записей. Оба запроса должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах.
UNION сам по себе не гарантирует порядок записей. В случаях, когда требуется определенный порядок, необходимо использовать ORDER BY.
Разница между UNION и UNION ALL заключается в том, что UNION будет пропускать дубликаты записей, тогда как UNION ALL будет включать дубликаты записей.
SELECT name FROM teachers UNION [ALL] / INTERSECT / MINUS (EXCEPT) SELECT name FROM administrative_staff
Что такое HAVING и чем отличается от WHERE (3) ?
SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) < 500;
HAVING используется для фильтрации результата применения агрегатных функций ( MIN(), MAX(), SUM(), AVG(), COUNT()) и для спецификации GROUP BY, WHERE проверяет условие для каждого ряда по отдельности и не используется с агрегатными функциями.
HAVING используется после GROUP BY, WHERE перед GROUP BY.
Что такое TRIGGER?
Это блок кода, который отработает до и/или после события.
Работают с событиями: INSERT , UPDATE , DELETE
Работают до или после события: BEFORE , AFTER
Можно организовать их последовательность: FOLLOWS , PRECEDES
CREATE TRIGGER some_trigger
BEFORE INSERT ON some_table
FOR EACH ROW [PRECEDES / FOLLOWS other_trigger]
SET @sum = @sum + NEW.int_value;
SELECT @sum as sum_of_values;
CREATE TRIGGER save_fired_employee
AFTER DELETE
ON employee
FOR EACH ROW
BEGIN
INSERT INTO fired_employee VALUES (OLD.id, OLD.name, CURDATE());
SET @sum = @sum - OLD.salary;
END;
DROP TRIGGER some_trigger;
SHOW TRIGGERS [FROM table_name];
Что такое ORDER BY?
ORDER BY сортирует запрос согласно значениям в выбранных столбцах.
В сортировке может участвовать выражение.
Возможно определять возрастание ASC или убывание DESC для каждого столбца. По умолчаниювозрастание ASC.
Что такое GROUP BY?
GROUP BY используется для колонок с повторяющимися значениями применяя агрегатную функцию к значениям ячеек других колонок:
Создает отдельную группу для всех возможных значений (включая значение NULL).
Делит результат запроса на группы рядов согласно значениям колонки-аттрибута обычно с целью применения к этой группе агрегатных функций. Таким образом SELECT, в котором выполнен GROUP BY вернёт один результат на каждую такую группу.
При использовании GROUP BY все значения NULL считаются равными.
SELECT column_name [, list_of_other_columns]
, aggregation [, list_of_aggregations]
FROM table_name
[WHERE list_of_conditions]
GROUP BY column_name [, list_of_other_columns]
[HAVING list_of_aggregate_conditions]
[ORDER BY list_of_columns/aliases];
SELECT country, state, MIN(age) as min_age
FROM Persons
GROUP BY country, state;
Что такое DISTINCT?
указывает, что для вычислений используются только уникальные значения колонки.
Что такое LIMIT / OFFSET?
LIMIT Ограничивает выборку заданным числом.
OFFSET = skip() в Stream API
SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;
Что такое EXISTS?
EXISTS используется в WHERE
Берет подзапрос, как аргумент, и оценивает его как TRUE, если подзапрос возвращает какие-либо записи, и FALSE, если нет.
Если вернулось TRUE - результат внешнего запроса добавляется в resultset,
если FALSE - результат скипается и идёт проверка следующего ряда.
SELECT customer_id, first_name
FROM Customers
WHERE [NOT] EXISTS (
SELECT order_id
FROM Orders
WHERE Orders.customer_id = Customers.customer_id
);
