🖥 Задача. у вас в базе данных есть две таблицы: TABLE1 и TABLE2. Нужно ответить на один простой вопрос: отработает ли данный запрос или упадет с ошибкой? И объяснить, почему.

select * from table1 where null = null or null <> null or 123 <> null or null is null

Примечание: представьте, что таблица table1 не пустая. Вернет ли этот запрос строки? Почему?

Ответ

Да, вернет. Причина в последнем условии null is null. Оно указано правильно. Дело в том, что при операции сравнения (=, <> , > , <) значения null c каким-либо числом или полем всегда возвращается значение FALSE. Т.е. синтаксический запрос написан правильно и не упадет, но он отсекает строки.

NULL в базах данных — это специальное значение, которое может быть записано в поле таблицы. NULL соответствует понятию «пустое поле», т.е. «поле, не содержащее никакого значения». Но его нельзя сравнивать константой. NULL можно сравнивать только через оператор is т.е. null is null или field1 is null.

@sqlhub

🖥 Задачи, основанные на агрегатных функциях

Язык SQL отлично подходит для агрегации. Для этого есть множество функций, например SUM(), AVG(), MAX(), MIN(), COUNT() и т. д. Знание таких функций — это базовый уровень знаний, который ожидается от кандидата.

Рассмотрим следующую таблицу сотрудников. В каждой строке указаны данные о сотруднике — отдел, зарплата, руководитель и т. д.

-- Table: employees
-- | dept_id | employee_id | amount | manager_id |
-- |---------|-------------|--------|------------|
-- | 1 | 1 | 8000 | 3 |
-- | 1 | 2 | 5000 | 3 |
-- | 1 | 3 | 10000 | null |
-- | 2 | 4 | 15000 | null |
-- | 2 | 5 | 16000 | 4 |
-- | 3 | 6 | 8000 | null |

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

Лучший способ решить любую задачу — это представить ее в виде пошаговой логики. В данном случае мы определяем наибольшую сумму по каждому отделу. Затем определяем формат вывода, для чего нужен только показатель employee_id.

-- Часть 1: Получаем самую высокую зарплату в каждом отделе
SELECT max(amount) AS salary
From employees
GROUP BY dept_id

-- Часть 2: Получаем желаемый формат вывода employee_id
-- Поскольку employee_id нельзя напрямую использовать в группе путем агрегации, мы можем прибегнуть к подзапросам.

SELECT e1.employee_id
FROM employees e1
WHERE e1.amount IN (
SELECT max(e2.amount) AS amount
From employees as e2
GROUP BY e2.dept_id
HAVING e1.dept_id = e2.dept_id )

@sqlhub

🖥 Почему не выполнится этот запрос?

SELECT
user_name,
YEAR(user_birth_date) AS year_of_birth
FROM
users
WHERE
year_of_birth = 2000

Ответ
Запрос не выполнится из-за обращения к псевдониму year_of_birth в выражении WHERE. Дело в том, что псевдонимы полей в SQL используются для форматирования данных уже полученных из базы. Поэтому их можно использовать только в выражениях, которые отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING. В выражениях, отвечающих за получение данных, таких как WHERE, нужно использовать оригинальные имена полей.

WHERE
YEAR(user_birth_date) = 2000

@sqlhub

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

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

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

Стоимость: #бесплатно (но есть платный тариф)

#db #sql #web

@sqlhub

🖥 Имеет ли значение порядок колонок в составном индексе?

Ответ
Да.

CREATE NONCLUSTERED INDEX MyInd on users (user_name, user_birth_date);

это не то же самое, что

CREATE NONCLUSTERED INDEX MyInd on users (user_birth_date, user_name);

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

Другими словами, колонки, по которым поиск выполняется чаще всего, должны стоять в составном индексе первыми.

@sqlhub

🖥 SQL — Индексы.

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

Например, если вы хотите сослаться на все страницы в книге, на которых рассмотрена определенная тема, вам сначала нужно сослаться на индекс, в котором перечислены все темы в алфавитном порядке, а затем на несколько конкретных номеров страниц.
Индекс позволяет увеличить производительность запросов SELECT и WHERE, но замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы можно создать или удалить не затрагивая данные.

Создание индекса включает инструкцию CREATE INDEX, которая позволяет указать индекс, таблицу и столбцы или столбцы для индексации, а также задать порядок индексации: по возрастанию или по убыванию.

Индексы также могут быть уникальными, например ограничение UNIQUE, в котором мы запрещаем добавление дублирующих записей в столбец или в комбинацию столбцов с указанным индексом.

➡️ Читать

@sqlhub

🖥 Какая разница между TRUNCATE TABLE table_name и DELETE FROM table_name?

Ответ
Фактически обе эти команды вызовут удаление всех строк из таблицы под названием table_name, но вот произойдет это совсем по-разному:

1. При вызове команды TRUNCATE таблица полностью сбрасывается и создается снова, в то время как команда DELETE удаляет каждую строку таблицы по отдельности. Из-за этого TRUNCATE отрабатывает значительно быстрее.

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

3. В отличие от DELETE команда TRUNCATE не транзакционная. То есть, если в момент ее вызова, таблица table_name будет заблокирована какой-либо транзакцией — может возникнуть ошибка.

@sqlhub

🖥 Какая разница между типами VARCHAR и NVARCHAR?

Тип NVARCHAR, пожалуй, самый универсальный из строчных типов данных в БД. Он позволяет хранить строки переменной длины в формате Unicode. В этом формате каждый символ занимает 2 байта, а сама кодировка содержит 65 536 символов и включает в себя все языки мира, в том числе иероглифы.

Тип VARCHAR хранит данные в формате SACII. В этом формате каждый символ занимает 1 байт, но отельная кодировка содержит всего 256 символов. Из-за этого для каждого мирового языка выделяется своя кодировка.

Таким образом, в формате VARCHAR стоит хранить строчные данные, которые точно не придется переводить (например, адреса электронной почты). Для других случаев больше подойдет NVARCHAR.

@sqlhub

🖥 Какая разница между выражениями WHERE и HAVING ?

Вопрос, который задают практически на каждом собеседовании по базам данных: про HAVING.
Выражения WHERE и HAVING используются для фильтрации результата запроса и ожидают после себя некоторое условие, по которому нужно отфильтровать данные. Но, если WHERE работает само по себе и фильтрует данные каждой строки результата по отдельности, то выражение HAVING имеет смысл только в сочетании с выражением GROUP BY и фильтрует уже сгруппированные значения.

@sqlhub

🖥 Использование SQL для анализа данных

SQL или язык структурированных запросов — это язык программирования, используемый для связи с базами данных. Это позволяет пользователям извлекать, манипулировать и управлять данными, хранящимися в системах управления реляционными базами данных (RDBMS). SQL — это мощный инструмент для анализа данных, поскольку он позволяет пользователям быстро и легко получать доступ к большим объемам данных, хранящихся в базе данных, и манипулировать ими. В этой статье мы рассмотрим основы использования SQL для анализа данных.

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

➡️ Читать дальше

@sqlhub

🖥 Sql трюк дня

Использование SUM и CASE WHEN вместе


select
sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
, sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end) as allergies_oak

from patients

Предложение WHERE может работать, если вы хотите суммировать количество пациентов, отвечающих определенным условиям. Но если вы хотите проверить несколько условий, вы можете использовать ключевые слова SUM и CASE WHEN вместе. Это делает код лаконичным и легко читаемым.

Данную комбинацию также можно использовать в выражении WHERE, как в примере ниже.


select
*
FROM patients
WHERE TRUE
and 1 = (case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end)

@sqlhub

🖥 SQL Задачи.

Для проведения тестирования, была развернута схема, содержащая следующие таблицы

Требовалось составить SQL-запросы, для решения следующих пяти заданий:
Задание 1
Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя
Вариант ответа
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary

Задание 2
Вывести список сотрудников, получающих максимальную заработную плату в своем отделе
Вариант ответа
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )

Задание 3
Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек
Вариант ответа
select department_id
from employee
group by department_id
having count(*) <= 3

Задание 4
Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе
Вариант ответа
select a.*
from employee a
left join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where b.id is null

Задание 5
Найти список ID отделов с максимальной суммарной зарплатой сотрудников
Вариант ответа
with sum_salary as
( select department_id, sum(salary) salary
from employee
group by department_id )
select department_id
from sum_salary a
where a.salary = ( select max(salary) from sum_salary )

Не требовалось искать в каком-либо смысле оптимальное решение. Единственное требование: запрос должен возвращать правильный ответ на любых входных данных. Задания разрешалось решать в любом порядке, без ограничения времени. При правильном решении всех заданий, предлагалось следующее задание повышенной сложности:
Дополнительное задание
Составить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицы
Вариант ответа
select
exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
*decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
*sign(min(abs(salary)))
from employee

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

@sqlhub

🖥 SQL для науки о данных бесплатный курс от IBM

Узнайте, как использовать и применять SQL для более эффективного обмена данными и извлечения данных из баз данных — получите обязательные навыкик для всех, кто работает в области науки о данных.

➡️ Курс

@sqlhub

🖥 Что такое ограничения SQL?

Ограничения в SQL — это утверждения, используемые для установления правил для столбцов в таблице. Если какое-либо действие нарушает ограничение, это действие будет прервано. Ограничения задаются при создании самой базы данных с помощью оператора CREATE TABLE или после создания таблицы с помощью оператора ALTER TABLE. В SQL используются следующие пять основных ограничений:

NOT NULL: указывает на то, что столбец должен иметь определенное значение; его нельзя оставлять пустым (NULL). В примере ниже столбцы ID, LastName и FirstName не могут быть пустыми:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

UNIQUE: гарантирует, что каждая строка в столбце имеет уникальное значение; ни одно значение не повторяется ни в одной другой строке. Следующий SQL-запрос установит ограничение для ID (записи в таблице Persons должны будут иметь уникальные ID)

CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
PRIMARY KEY: поле в SQL-таблице, позволяющее однозначно идентифицировать каждую запись. PRIMARY KEYs должны быть уникальными — как правило, в качестве PRIMARY KEY используются ID записей. Запрос ниже задает первичный ключ (ID) в таблице Persons:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
FOREIGN KEY: поле (или набор полей) в одной таблице, которое ссылается на PRIMARY KEY в другой таблице. Используется для обеспечения ссылочной целостности данных, а также сопоставляет значение в одной таблице с другой. Следующий запрос создаст внешний ключ (FOREIGN KEY) в столбце PersonID при создании таблицы Orders:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

CHECK: используется для обеспечения соответствия значений в столбцах заданным условиям. В примере ниже ограничение гарантирует, что в таблице не будет записей людей, с возрастом (Age) меньше 18 лет

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);

@sqlhub

🖥 Какая ошибка в этом запросе?

UPDATE books SET sales_1999 >
(SELECT SUM(qty * price)
FROM sales
WHERE sales.book_id = books.id
AND sales.date BETWEEN '01/01/1999' AND '12/31/1999')
Вы не можете использовать арифметические операторы для функции SUM() (например SUM(qty * price))

Ответ

Следует использовать знак (=) для подзапроса

@sqlhub

🖥 Подробная Шпаргалка SQL на 2023 год

Руководство о всех SQL-запросах и примерах их использования.

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

Ставьте ❤️, если хотите больше подобного материала.

✔️ Шпаргалка

@sqlhub

🖥 Назовите SQL-операторы для составления условий

GROUP BY: используется при агрегировании для объединения идентичных данных в группы, предложение GROUP BY следует за предложением WHERE в операторе SELECT. В примере ниже результатом запроса с использованием GROUP BY будет таблица, показывающая количество пользователей (Customers) в каждой стране (Country):

SELECT COUNT(CustomerID), Country
FROM Customers

GROUP BY Country;
HAVING: используется для указания условия поиска в предложении GROUP BY, HAVING может использоваться в отсутствие предложения GROUP BY с помощью предложения WHERE. В примере ниже результатом запроса с использованием HAVING будет таблица, показывающая количество пользователей (Customers) в каждой стране (Country) с числом пользователей больше 5.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

ORDER BY: сортирует набор результатов в порядке возрастания (по умолчанию) или убывания (при использовании ключевого слова DESC). В примере ниже результатом запроса будет таблица пользователей (Customers), отсортированная по полю «Страна» (Country):

SELECT * FROM Customers
ORDER BY Country;

WHERE: используется для определения условия извлечения записей». В примере ниже результатом запроса будет таблица пользователей (Customers) из Мексики:

SELECT * FROM Customers
WHERE Country='Mexico';

@sqlhub