Инструкции по настройке

  1. Убедитесь, что у вас есть база данных, совместимая с SQL (например, MySQL или PostgreSQL), и необходимые разрешения на создание и запрос данных.
  2. Подготовьте примерную схему базы данных с соответствующими таблицами.

Чтобы узнать еще больше советов для data-анализа, регистрируйтесь на бесплатный интенсив по анализу данных на Python и ChatGPT. Вас ждут 3 урока: «Основы аналитики данных через знакомые примеры», «Первые шаги в анализе данных на Python» и «Визуализация данных». Также вы получите проверку проекта по итогам выполнения всех заданий, обратную связь и сертификат. Регистрируйтесь, будет интересно!

ОНЛАЙН-ПРАКТИКУМ
ЗАПУСК DEEPSEEK R1 ЛОКАЛЬНО НА СВОЕМ КОМПЬЮТЕРЕ
ЧТО БУДЕТ НА ОБУЧЕНИИ?
  • ПОКАЖЕМ, КАК РАЗВЕРНУТЬ МОДЕЛЬ DEEPSEEK R1 ПРЯМО НА СВОЁМ КОМПЬЮТЕРЕ
  • Где и как применять? Потестируем модель после установки на разных задачах
  • Как дообучить модель под себя?
Участвовать бесплатно

Практическая реализация

Подзапросы в выражении SELECT

sql
-- Using a subquery to return the maximum salary
SELECT name, department_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Подзапросы в секции FROM

sql
-- Subquery returns a derived table containing the average salary per department
SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
 FROM employees
 GROUP BY department_id) AS dept_avg;

Подзапросы в секции WHERE

sql
-- Subquery to filter employees with above average salaries
SELECT name, department_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Подзапросы в выражении INSERT

sql
-- Inserting the result of a subquery into a new table
INSERT INTO high_earners (name, salary)
SELECT name, salary
FROM employees
WHERE salary > 100000;

Создание временной таблицы

sql
-- Creating a temporary table for intermediate calculations
CREATE TEMPORARY TABLE temp_avg_salary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Использование временной таблицы

sql
-- Querying the temporary table
SELECT e.name, e.salary, t.avg_salary
FROM employees e
JOIN temp_avg_salary t ON e.department_id = t.department_id
WHERE e.salary > t.avg_salary;

Очистка временной таблицы

sql
-- Dropping the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_avg_salary;

Эти примеры помогут улучшить data-анализ и работу с данными с помощью подзапросов и временных таблиц в SQL.

Написание простых подзапросов: практическая реализация

Пример схемы базы данных

Предположим, что у нас есть две таблицы:

  • employees(employee_id, name, department_id, salary)
  • departments(department_id, department_name)

1. Использование подзапроса в секции SELECT

sql
SELECT 
 name, 
 (SELECT department_name 
 FROM departments 
 WHERE departments.department_id = employees.department_id) AS department_name
FROM 
 employees;

2. Использование подзапроса в секции FROM

sql
SELECT 
 department_name, 
 total_salary
FROM 
 (SELECT 
 department_id, 
 SUM(salary) AS total_salary
 FROM 
 employees
 GROUP BY 
 department_id) AS dept_salaries
JOIN 
 departments 
ON 
 dept_salaries.department_id = departments.department_id;

3. Использование подзапроса в секции WHERE

sql
SELECT 
 name, 
 salary
FROM 
 employees
WHERE 
 salary > (SELECT AVG(salary) FROM employees);

4. Использование подзапроса с оператором IN

sql
SELECT 
 name
FROM 
 employees
WHERE 
 department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

5. Использование подзапроса с оператором EXISTS

sql
SELECT 
 name, 
 department_id
FROM 
 employees e
WHERE 
 EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Engineering');

6. Использование нескольких подзапросов

sql
SELECT 
 e1.name AS employee_name,
 d1.department_name AS department_name,
 (SELECT AVG(salary) 
 FROM employees e2 
 WHERE e2.department_id = e1.department_id) AS avg_department_salary
FROM 
 employees e1
JOIN 
 departments d1 
ON 
 e1.department_id = d1.department_id;

Эти примеры иллюстрируют практические случаи использования подзапросов в SQL, которые можно применить в различных сценариях data-анализа и манипулирования данными.

Расширенные подзапросы и корреляция в SQL

Пример коррелированного подзапроса

sql
SELECT employee_id, salary
FROM employees e1
WHERE salary > (
 SELECT AVG(salary)
 FROM employees e2
 WHERE e1.department_id = e2.department_id
);

Использование временных таблиц

sql
-- Creating a temporary table
CREATE TEMPORARY TABLE high_salaries AS
SELECT employee_id, salary
FROM employees
WHERE salary > 100000;

-- Querying the temporary table
SELECT * FROM high_salaries;

Подзапросы в секции SELECT

sql
SELECT 
 employee_id,
 salary,
 (SELECT AVG(salary)
 FROM employees e2
 WHERE e1.department_id = e2.department_id) AS average_department_salary
FROM employees e1;

Подзапросы в секции FROM

sql
SELECT department_id, AVG(salary) AS avg_salary
FROM (
 SELECT department_id, salary
 FROM employees
) AS emp
GROUP BY department_id;

Подзапросы в секции EXISTS

sql
SELECT employee_id, first_name, last_name
FROM employees e1
WHERE EXISTS (
 SELECT 1
 FROM departments d
 WHERE d.manager_id = e1.employee_id
);

Обновления с помощью подзапросов

sql
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id IN (
 SELECT employee_id
 FROM high_salaries
);

Удаление с помощью подзапросов

sql
DELETE FROM employees
WHERE department_id NOT IN (
 SELECT department_id
 FROM departments
);

Очистка временной таблицы

sql
DROP TEMPORARY TABLE IF EXISTS high_salaries;

Заключение

Эти примеры можно использовать на практике для реализации расширенных подзапросов и корреляции в SQL.

Использование временных таблиц в SQL

Создание временной таблицы

sql
CREATE TEMPORARY TABLE temp_sales (
 sale_id INT,
 product_id INT,
 quantity INT,
 sale_date DATE
);

Вставка данных во временную таблицу

sql
INSERT INTO temp_sales (sale_id, product_id, quantity, sale_date)
VALUES
 (1, 101, 2, '2023-01-10'),
 (2, 102, 1, '2023-01-11'),
 (3, 101, 5, '2023-01-12');

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

sql
SELECT * FROM temp_sales;

Использование временной таблицы в JOIN

sql
SELECT 
 p.product_name,
 t.quantity,
 t.sale_date
FROM 
 products p
JOIN 
 temp_sales t ON p.product_id = t.product_id;

Обновление записей во временной таблице

sql
UPDATE temp_sales
SET quantity = 3
WHERE sale_id = 2;

Удаление записей из временной таблицы

sql
DELETE FROM temp_sales
WHERE sale_date < '2023-01-11';

Удаление временной таблицы

sql
DROP TEMPORARY TABLE IF EXISTS temp_sales;

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

Создание и использование временных таблиц в SQL

Создание временных таблиц

sql
-- Create a temporary table to store intermediate results
CREATE TEMPORARY TABLE temp_sales_summary (
 category_id INT,
 total_sales DECIMAL(10, 2),
 total_orders INT
);

Вставка данных во временные таблицы

sql
-- Populate the temporary table with summarized sales data
INSERT INTO temp_sales_summary (category_id, total_sales, total_orders)
SELECT 
 category_id,
 SUM(sales_amount) AS total_sales,
 COUNT(order_id) AS total_orders
FROM
 sales
GROUP BY
 category_id;

Использование временных таблиц

sql
-- Query the temporary table for further analysis
SELECT 
 tss.category_id,
 tss.total_sales,
 tss.total_orders,
 c.category_name
FROM
 temp_sales_summary tss
JOIN
 categories c ON tss.category_id = c.category_id;

Удаление временных таблиц

sql
-- Drop the temporary table once it is no longer needed
DROP TEMPORARY TABLE IF EXISTS temp_sales_summary;

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

Комбинирование подзапросов и временных таблиц для сложных запросов

sql
-- 1. Create a temporary table to store intermediate results
CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

-- 2. Use a subquery to calculate average quantity sold for products in 'Electronics' category
SELECT product_id, total_quantity
FROM temp_sales
WHERE product_id IN (
 SELECT id
 FROM products
 WHERE category = 'Electronics'
) AND total_quantity > (
 SELECT AVG(total_quantity)
 FROM temp_sales
);

-- 3. Clean up: Drop the temporary table
DROP TEMPORARY TABLE temp_sales;

Код демонстрирует:

  1. Создание временной таблицы (temp_sales) для хранения суммы количества проданных товаров.
  2. Использование подзапроса для фильтрации товаров, относящихся к категории «Электроника» и имеющих общее количество проданных товаров больше, чем среднее общее количество проданных товаров для всех товаров.
  3. Удаление временной таблицы после ее использования для очистки.

Примеры и команды из этого руководства помогут вам быстрее освоить SQL и получить только основу для обучения и хорошие результаты. А чтобы еще лучше прокачать навыки data-анализа, приходите на бесплатный интенсив по анализу данных на Python и ChatGPT. Вас ждут 3 мощных дня без воды с практикой и проверкой домашних заданий, не пропустите!

 

Большой практикум
ЗАМЕНИ ВСЕ НЕЙРОСЕТИ НА ОДНУ — PERPLEXITY
ПОКАЖЕМ НА КОНКРЕТНЫХ КЕЙСАХ
  • Освой Perplexity и узнай, как пользоваться функционалом остальных ИИ в одном
  • УЧАСТВОВАТЬ ЗА 0 РУБ.
  • Расскажем, как получить подписку (240$) бесплатно
Участвовать бесплатно
ОНЛАЙН-ПРАКТИКУМ
ЗАПУСК DEEPSEEK R1 ЛОКАЛЬНО НА СВОЕМ КОМПЬЮТЕРЕ
ЧТО БУДЕТ НА ОБУЧЕНИИ?
  • ПОКАЖЕМ, КАК РАЗВЕРНУТЬ МОДЕЛЬ DEEPSEEK R1 ПРЯМО НА СВОЁМ КОМПЬЮТЕРЕ
Участвовать бесплатно