← К оглавлению занятия

💻 Примеры

⚡ Кратко: примеры

Категория цены через CASE.
Полное имя через CONCAT.
Топ-5 и пагинация через LIMIT / OFFSET.
Уникальные города через DISTINCT.

💻 Примеры

CASE: категория цены

SELECT
    product_name,
    standard_cost,
    CASE
        WHEN standard_cost > 50 THEN 'Expensive'
        WHEN standard_cost > 20 THEN 'Medium'
        ELSE 'Cheap'
    END AS price_category
FROM products;

IF: бинарная классификация

SELECT
    product_name,
    IF(standard_cost > 20, 'Expensive', 'Affordable') AS price_category
FROM products;

Алиас для вычисляемого столбца

SELECT
    product_name,
    unit_price * quantity AS total_price
FROM order_details;

Строковые функции

-- Полное имя
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Последние 2 символа компании
SELECT company, RIGHT(company, 2) AS short_name
FROM customers;

-- Подстрока телефона
SELECT business_phone, SUBSTRING(business_phone, 6, 3) AS code
FROM customers;

-- Замена NULL
SELECT notes, COALESCE(notes, 'Not filled') AS notes_filled
FROM employees;

ORDER BY

SELECT product_name, standard_cost
FROM products
ORDER BY standard_cost DESC;

LIMIT и OFFSET

-- Топ-5 дешёвых
SELECT product_name, standard_cost
FROM products
ORDER BY standard_cost ASC
LIMIT 5;

-- Вторая страница каталога
SELECT product_name
FROM products
ORDER BY product_name ASC
LIMIT 10 OFFSET 10;

DISTINCT

SELECT DISTINCT city
FROM customers
ORDER BY city ASC;