🖥 Задача SQL

Есть кинотеатр, в нем идут фильмы. У фильма есть название, длительность (пусть для простоты будет 60, 90 или 120 минут), цена билета (в разное время и дни может быть разная), время начала сеанса (один фильм может быть показан несколько раз в разное время за разную цену). Также, есть информация о купленных билетах (номер билета, на какой сеанс).

Задания:

▪составь грамотную нормализованную схему хранения этих данных в БД. Внеси в нее 4-5 фильмов, расписание на один день и несколько проданных билетов.

Напишите запросы, считающие и выводящие в понятном виде:

▪ошибки в расписании (фильмы накладываются друг на друга), отсортированные по возрастанию времени.
Выводить необходимо колонки «фильм 1», «время начала», «длительность», «фильм 2», «время начала», «длительность».

▪перерывы больше или равные 30 минут между фильмами, выводятся по уменьшению длительности перерыва. Выводить надо колонки «фильм 1», «время начала», «длительность», «время начала второго фильма», «длительность перерыва».

▪список фильмов, для каждого указано общее число посетителей за все время, среднее число зрителей за сеанс и общая сумма сбора по каждому, отсортированные по убыванию прибыли. Внизу таблицы должна быть строчка «итого», содержащая данные по всем фильмам сразу.

▪число посетителей и кассовые сборы, сгруппированные по времени начала фильма: с 9 до 15, с 15 до 18, с 18 до 21, с 21 до 00:00. (то есть сколько посетителей пришло с 9 до 15 часов, сколько с 15 до 18 и т.д.).

@sqlhub

🖥 Освоение SQL: Использование данных для решения сложных задач

Я решил подробно рассказать о том, как я подхожу к использованию SQL для запроса баз данных. Я принял участие в еженедельном конкурсе Danny’s SQL challenge, чтобы начать тематическое исследование по этой теме. Вся необходимая вам информация об этом испытании доступна здесь.

▪Читать

@sqlhub

🖥 Руководство по наиболее востребованным базовым командам SQL

Продемонстрируем применение основных команд SQL на примерах.

▪ Читать

@sqlhub

🖥 Вводный курс в SQL

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

При правильном обучении и практике любой желающий может овладеть SQL и эффективно управлять данными.

▪ Читать

@sqlhub

🖥 Сложная задача про календарь

Давай возьмемся за действительно сложную задачу. Напиши SQL-код, выводящий календарь на текущий месяц в виде:

Пн Вт Ср Чт Пт Сб Вс

| | | | | 1 | 2
3 | 4 | 5 | 6 | 7 | 8 | 9 10 | 11 | 12 | 13 | 14 | 15 | 16 17 | 18 | 19 | 20 | 21 | 22 | 23 24 | 25 | 26 | 27 | 28 | 29 | 30

Подсказка: ты можешь делать запросы без таблиц, например SELECT 2 + 3, 'Hello'
Подсказка: здесь не надо использовать циклы или процедуры

Подсказка: функции работы с датой и временем ты можешь найти тут http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html (англ.)
Подсказка: для сокращения объема кода ты можешь использовать переменные (создаются командой SET)

Пишите свое решение в комментариях👇

@sqlhub

🖥 Разбор ошибок. SQL запрос кросс-джойн двух таблиц без явного указания оператора JOIN

SELECT *
FROM orders, customers
WHERE orders.customer_id = customers.customer_id
AND customers.first_name = 'John'

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

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

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

@sqlhub

🖥 Как транспонировать в SQL?

Для транспонирования таблицы в SQL необходимо использовать оператор PIVOT или функцию MAX или CASE в сочетании с оператором GROUP BY.

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

Синтаксис оператора PIVOT выглядит следующим образом:

SELECT *
FROM table_name
PIVOT
(
aggregate_function(column_to_aggregate)
FOR column_to_pivot IN (list_of_pivot_values)
) AS alias_name;

Здесь table_name - это имя таблицы, которую нужно транспонировать aggregate_function - это агрегатная функция, которую нужно применить к столбцу, column_to_aggregate - это имя столбца, который нужно агрегировать, column_to_pivot - это имя столбца, который нужно использовать для создания новых столбцов, list_of_pivot_values - это список значений столбца column_to_pivot, для которых нужно создать новые столбцы, alias_name - это имя для результирующей таблицы

Пример использования оператора PIVOT:

SELECT *
FROM (
SELECT product_id, year, sales
FROM sales_table
) AS source_table
PIVOT
(
SUM(sales)
FOR year IN (2023, 2022, 2021)
) AS pivot_table;

В этом примере мы выбираем данные из таблицы sales_table и используем оператор PIVOT, чтобы преобразовать строки в столбцы, используя годы продаж как заголовки новых столбцов.

Если оператор PIVOT недоступен в вашей версии SQL, вы можете использовать функцию MAX или CASE в сочетании с оператором GROUP BY.

Синтаксис функции MAX для транспонирования таблицы выглядит следующим образом:

SELECT column_to_group_by,
MAX(CASE column_to_pivot WHEN pivot_value_1 THEN value_to_show ELSE NULL END) AS pivot_value_1,
MAX(CASE column_to_pivot WHEN pivot_value_2 THEN value_to_show ELSE NULL END) AS pivot_value_2,
...
FROM table_name
GROUP BY column_to_group_by;

Здесь column_to_group_by - это имя столбца, по которому нужно группировать данные, column_to_pivot - это имя столбца, который нужно использовать для создания новых столбцов, pivot_value_1, pivot_value_2 - это значения столбца column_to_pivot, для которых нужно создать новые столбцы, value_to_show - это значение, которое нужно показать в новом столбце

Задача:

Дана таблица T:

id value
2 a
3 a
4 b
5 c

Необходимо сформировать запрос, который бы вернул следующий набор:

a b c
2 1 1

Пишите свое решение в комментариях👇

@sqlhub

🖥 Как написать SQL скрипт

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

Для написания SQL-скрипта необходимо выполнить следующие шаги:

Определить цель скрипта - что он должен делать и какие данные необходимо извлечь или изменить.

Открыть текстовый редактор, такой как блокнот или специализированный редактор SQL-скриптов.

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

CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE
);

Сохранить файл с расширением .sql. В некоторых редакторах можно также выполнить скрипт непосредственно из редактора.

Запустить скрипт на базе данных. Это можно сделать с помощью интерфейса базы данных или с помощью командной строки, используя утилиты командной строки, такие как sqlcmd или mysql.

Убедиться, что скрипт выполнен успешно и не было ошибок.

Пример SQL-скрипта, который добавляет новую запись в таблицу employees:

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (1, 'John', 'Doe', 'johndoe@email.com', '2022-01-01');

Этот скрипт добавляет нового сотрудника в таблицу employees с идентификатором 1, именем John, фамилией Doe, электронной почтой johndoe@email.com и датой найма 1 января 2022 года.

Задание:

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

Пишите свое решение в комментариях👇

@sqlhub

🖥 Как распарсить строку в SQL


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

Допустим, если строка содержит разделители, такие как запятые или пробелы, то можно использовать функцию SUBSTRING_INDEX() для извлечения определенного фрагмента строки.
Например, следующий запрос извлекает первое слово из строки Привет, мир:

SELECT SUBSTRING_INDEX('Привет, мир', ' ', 1);

Результатом запроса будет строка Привет.

Если же требуется извлечь определенный набор символов из строки, то можно использовать функцию SUBSTRING().
Например, следующий запрос извлекает первые три символа из строки Привет:

SELECT SUBSTRING('Привет', 1, 3);

Результатом запроса будет строка При.

Также можно использовать функцию REGEXP_SUBSTR() для извлечения определенного фрагмента строки, используя регулярные выражения.
Например, следующий запрос извлекает первое слово из строки Привет, мир:

SELECT REGEXP_SUBSTR('Привет, мир', '^[^ ]+');

Результатом запроса будет строка Привет.

@sqlhub

🖥 6 простых способов использовать передовые методы SQL

SQL является основой анализа и преобразования данных, но в некоторых случаях требуется более сложный подход. Вот тут-то и пригодятся передовые методы SQL.

▪ Читать

@sqlhub

🖥 Как заменить null на 0 в SQL

Чтобы заменить значение NULL на 0 в SQL, можно использовать функцию COALESCE. Эта функция принимает несколько аргументов и возвращает первый не NULL аргумент.

Если все аргументы NULL, функция вернет NULL. Вот пример использования COALESCE для замены значений NULL на 0:

SELECT COALESCE(column_name, 0) FROM table_name;

В этом запросе column_name - имя столбца, значения которого нужно заменить, а table_name - имя таблицы, в которой находится столбец. Функция COALESCE заменит все значения NULL в столбце на 0. Если значение столбца не NULL, то функция вернет его без изменений.

Также можно использовать оператор IS NULL для проверки на NULL и замены его на 0. Вот пример:

SELECT CASE WHEN column_name IS NULL THEN 0 ELSE column_name END FROM table_name;

Этот запрос также заменит значения NULL на 0. Если значение столбца не NULL, то запрос вернет его без изменений.

@sqlhub

🖥 Как правильно вводить дату в SQL

В SQL даты обычно вводятся в формате "ГГГГ-ММ-ДД", где "ГГГГ" - год, "ММ" - месяц (от 01 до 12) и "ДД" - день (от 01 до 31).

Например, чтобы вставить запись в таблицу "orders" с датой заказа 22 марта 2023 года, можно использовать следующий запрос:

INSERT INTO orders (order_date, customer_name)
VALUES ('2023-03-22', 'Иванов Иван');

Если же дата вводится с помощью функции, то можно использовать стандартную функцию преобразования даты, например, функцию TO_DATE() в Oracle:

INSERT INTO orders (order_date, customer_name)
VALUES (TO_DATE('22-03-2023', 'DD-MM-YYYY'), 'Иванов Иван');

В этом примере мы используем функцию TO_DATE() для преобразования строки "22-03-2023" в дату формата "ГГГГ-ММ-ДД". Второй аргумент функции ("DD-MM-YYYY") указывает формат входной строки.

@sqlhub

🖥 Как работает like в SQL

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

Шаблон запроса:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

Пример:

SELECT * FROM customers WHERE last_name LIKE 'Sm%';

Этот запрос вернет все строки из таблицы customers, в которых last_name начинается с букв "Sm".

Оператор LIKE чувствителен к регистру символов, то есть "A" и "a" будут восприниматься как разные символы. Однако, для того чтобы выполнить поиск, игнорируя регистр символов, можно использовать функцию UPPER или LOWER, например:

SELECT * FROM employees WHERE UPPER(first_name) LIKE '%JOHN%';

@sqlhub

🖥 Создание базы данных для маркетплейса с помощью SQL

В этой статье мы разработаем БД для практики. В этом проекте перед вами стоит задача создать реляционную базу данных для веб-сайта, предлагающего продажу подержанных автомобилей.

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

▪ Читать дальше

@sqlhub

🖥 Как создать временную таблицу в sql запросе

Для создания временной таблицы используется ключевое слово TEMPORARY в команде CREATE TABLE:

CREATE TEMPORARY TABLE название_таблицы

Удобно создавать временную таблицу на выборке из другой таблицы:

CREATE TEMPORARY TABLE customer_temp SELECT * FROM customer;

@sqlhub

🖥 Как работает CASE SQL

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

Оператор CASE может быть использован в двух форматах:

CASE без ELSE:
SELECT column1,
CASE
WHEN column2 = 'value1' THEN 'result1'
WHEN column2 = 'value2' THEN 'result2'
END AS column3
FROM table;

В этой форме оператора CASE используется блок WHEN для проверки значения столбца column2. Если значение равно value1, то будет возвращено значение result1. Если значение равно value2, то будет возвращено значение result2. Если значение не соответствует ни одному из условий, то для столбца column3 будет возвращено значение NULL.

CASE с ELSE:
SELECT column1,
CASE column2
WHEN 'value1' THEN 'result1'
WHEN 'value2' THEN 'result2'
ELSE 'result3'
END AS column3
FROM table;

В этой форме оператора CASE используется значение столбца column2 в блоке CASE. Если значение равно value1, то будет возвращено значение result1. Если значение равно value2, то будет возвращено значение result2. Если значение не соответствует ни одному из условий, то будет возвращено значение result3.

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

@sqlhub

🖥 Как в sql посчитать количество повторяющихся значений

Можно попробовать использовать функцию COUNT() в сочетании с оператором GROUP BY.

Предположим, у нас есть таблица "orders" с колонками "customer_name" и "order_date", и мы хотим посчитать, сколько заказов было сделано каждым клиентом. Мы можем написать следующий SQL-запрос:

SELECT customer_name, COUNT(*) as order_count
FROM orders
GROUP BY customer_name

Этот запрос выберет все уникальные значения из колонки "customer_name" в таблице "orders" и подсчитает количество строк, связанных с каждым уникальным значением. Результатом запроса будет таблица со столбцами "customer_name" и "order_count", в которой каждая строка будет показывать клиента и количество его заказов.

@sqlhub

🖥 Оператор FULL OUTER JOIN в SQL

Оператор FULL OUTER JOIN объединяет две таблицы на основе общего столбца и выбирает не только записи с совпадающими значениями в этих столбцах, но и все оставшиеся строки из обеих таблиц. Например:

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

На картинке видно как отработает данный код.

Здесь мы выбираем столбцы customer_id и first_name (из таблицы Customers) и столбец amount (из таблицы Orders). В результате получаем те строки, в которых есть совпадение между customer_id (таблицы Customers) и customer (таблицы Orders) вместе со всеми остальными строками из обеих таблиц.

Синтаксис оператора FULL OUTER JOIN
Синтаксис оператора FULL OUTER JOIN следующий:

SELECT столбцы
FROM таблица1
FULL OUTER JOIN таблица2
ON таблица1.имя_столбца = таблица2.имя_столбца;
Оператор FULL OUTER JOIN с оператором WHERE

Вот пример использования оператора FULL OUTER JOIN с оператором WHERE:

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;

Здесь мы объединяем две таблицы и выбираем строки, в которых сумма (amount) больше или равна 500.

Оператор FULL OUTER JOIN с псевдонимами AS
Мы можем использовать псевдонимы AS с оператором FULL OUTER JOIN, чтобы сделать наш код короче и чище. Например:

SELECT C.cat_name, P.prod_title
FROM Categories AS C
FULL OUTER JOIN Products AS P
ON C.cat_id = P.cat_id;

Здесь мы выбираем общие строки между таблицами Categories и Products.

@sqlhub

🖥 BACKUP DATABASE в SQL

Важно регулярно создавать резервные копии базы данных, чтобы данные не были потеряны в случае повреждения базы данных (БД). В SQL мы можем создавать резервные копии БД с помощью оператора BACKUP DATABASE.

Например:

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak';
Здесь мы создаем файл резервной копии базы данных orders на диске C с именем orders_backup.bak.

Примечание: Распространено использование расширения .bak для файлов резервных копий БД, однако это не является обязательным.

Резервное копирование только новых изменений в SQL
В SQL мы также можем сделать резервную копию только новых изменений по сравнению с предыдущей резервной копией, используя команду WITH DIFFERENTIAL. Например:

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak'
WITH DIFFERENTIAL;
Здесь мы добавляем только новые изменения в предыдущий файл резервной копии. Следовательно, эта команда работает быстрее, нежели создание резервной копии БД с нуля.

Восстановление базы данных из резервной копии

Для восстановления файла резервной копии в системе управления базой данных (СУБД) используется оператор RESTORE DATABASE. Например:

RESTORE DATABASE orders
FROM DISK = 'C:\orders_backup.bak';
Здесь мы восстанавливаем файл резервной копии orders_backup.bak в базе данных orders.

@sqlhub