🖥 Какая разница между 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

🖥 Задача

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

Решение

WITH users (user_id, action, action_date)
AS (VALUES
(1, 'start', CAST('2-12-20' AS date)),
(1, 'cancel', CAST('2-13-20' AS date)),
(2, 'start', CAST('2-11-20' AS date)),
(2, 'publish', CAST('2-14-20' AS date)),
(3, 'start', CAST('2-15-20' AS date)),
(3, 'cancel', CAST('2-15-20' AS date)),
(4, 'start', CAST('2-18-20' AS date)),
(1, 'publish', CAST('2-19-20' AS date))),
-- create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function
t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),
-- filter on date rank column to pull latest and next latest actions from this table
latest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),
next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )
-- left join these two tables, subtracting latest from second latest to get time elapsed
SELECT
l1.user_id,
l1.action_date - l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1

@sqlhub

🖥 Задачи SQL с решениями. Десять практических упражнений SQL

Эти вопросы и примеры решений помогут Вам развить свои навыки в SQL.

➡️ Смотреть

@sqlhub

🖥 SQL Задачи.

--На входе у нас есть таблица
DECLARE @pays TABLE (
client_id int --Идентификатор клиента
,date datetime --Дата и время платежа
,pay_sum decimal(19,2) --Сумма платежа
)

-- Заполняем таблицу тестовыми данными

INSERT INTO @pays VALUES (1, '2022-01-01T16:11:11', 10)
INSERT INTO @pays VALUES (2, '2022-01-01T16:12:12', 20)
INSERT INTO @pays VALUES (3, '2022-01-01T16:13:13', 30)
INSERT INTO @pays VALUES (5, '2022-02-01T16:14:14', 50)
INSERT INTO @pays VALUES (3, '2022-02-01T16:15:15', 35)


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


SELECT select_1.client_id, select_1.date, select_1.pay_sum, sm, cnt
FROM
(SELECT a.client_id, a.date, a.pay_sum, SUM(b.pay_sum) sm
FROM @pays a
LEFT OUTER JOIN @pays b
ON a.date >= b.date
GROUP BY a.client_id, a.date, a.pay_sum) select_1
INNER JOIN
(SELECT a.client_id, a.date, a.pay_sum, count(c.client_id) cnt
FROM @pays a
LEFT OUTER JOIN @pays c
on a.client_id = c.client_id
AND a.date > c.date
GROUP BY a.client_id, a.date, a.pay_sum,c.client_id) select_2
ON select_1.client_id = select_2.client_id
AND select_1.date = select_2.date
AND select_1.pay_sum = select_2.pay_sum
ORDER BY select_1.date, select_1.client_id

@sqlhub

🖥 SQL Задачи.

Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id. Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.

SELECT * FROM page LEFT JOIN category ON page.catogory_id = category.id
LEFT JOIN sub_category ON category.id = sub_category.category_id

@sqlhub