VIEW SQL — 100 универсальных примеров на все случаи жизни

VIEW SQL. Введение

Введение

VIEW — это представление, виртуальная таблица, формируемая запросом. Оно помогает структурировать логику, скрывать сложные JOIN, повторно использовать код и обеспечивать безопасность данных. view sql чаще всего используют, при создании представлений для аналитики, e-commerce, финансов, медицины, HR и других сфер.

Синтаксис

CREATE VIEW имя_представления AS
SELECT
    столбцы
FROM
    таблица
WHERE
    условие;

Типовая конструкция

CREATE VIEW high_value_orders AS
SELECT
    o.order_id,
    o.customer_id,
    o.total_amount
FROM
    orders AS o
WHERE
    o.status = 'PAID'
    AND o.total_amount > 1000
ORDER BY
    o.total_amount DESC;

Блок 1. Базовые представления из одной таблицы

Список активных пользователей:

CREATE VIEW active_users AS
SELECT
    u.user_id,
    u.email
FROM
    users AS u
WHERE
    u.is_active = 1;

Недорогие товары до 100 у.е.:

CREATE VIEW cheap_products AS
SELECT
    p.product_id,
    p.title,
    p.price
FROM
    products AS p
WHERE
    p.price < 100;

Фильмы с высоким рейтингом:

CREATE VIEW top_rated_films AS
SELECT
    f.film_id,
    f.title,
    f.rating
FROM
    films AS f
WHERE
    f.rating >= 8;

Сотрудники в активном статусе:

CREATE VIEW current_employees AS
SELECT
    e.emp_id,
    e.full_name
FROM
    employees AS e
WHERE
    e.status = 'ACTIVE';

Опубликованные статьи:

CREATE VIEW published_posts AS
SELECT
    p.post_id,
    p.title
FROM
    posts AS p
WHERE
    p.is_published = 1;

Пациенты с полисом страхования:

CREATE VIEW insured_patients AS
SELECT
    pt.patient_id,
    pt.policy_no
FROM
    patients AS pt
WHERE
    pt.policy_no IS NOT NULL;

Задачи с высоким приоритетом:

CREATE VIEW high_priority_tasks AS
SELECT
    t.task_id,
    t.title,
    t.priority
FROM
    tasks AS t
WHERE
    t.priority = 'HIGH';

Счета с положительным балансом:

CREATE VIEW positive_balance_accounts AS
SELECT
    a.account_no,
    a.balance
FROM
    accounts AS a
WHERE
    a.balance > 0;

Визиты за последние 30 дней:

CREATE VIEW recent_visits AS
SELECT
    v.visit_id,
    v.visit_date
FROM
    visits AS v
WHERE
    v.visit_date >= CURRENT_DATE - INTERVAL '30' DAY;

Активные курсы:

CREATE VIEW active_courses AS
SELECT
    c.course_id,
    c.title
FROM
    courses AS c
WHERE
    c.is_active = 1;

Блок 2. Алиасы и упрощение структуры

Пользователи с читаемыми именами:

CREATE VIEW users_with_aliases AS
SELECT
    u.user_id   AS id,
    u.full_name AS name
FROM
    users AS u;

Товары с укороченными колонками:

CREATE VIEW products_short AS
SELECT
    p.product_id AS id,
    p.title      AS product,
    p.price      AS cost
FROM
    products AS p;

Сотрудники с унифицированными полями:

CREATE VIEW employees_alias AS
SELECT
    e.emp_id    AS id,
    e.full_name AS employee_name,
    e.hire_date AS joined
FROM
    employees AS e;

Города клиентов:

CREATE VIEW customer_cities AS
SELECT
    c.customer_id AS id,
    c.city        AS hometown
FROM
    customers AS c;

Фильмы с сокращёнными именами:

CREATE VIEW films_short AS
SELECT
    f.film_id AS id,
    f.title   AS movie,
    f.rating  AS score
FROM
    films AS f;

Аккаунты с удобными колонками:

CREATE VIEW accounts_clear AS
SELECT
    a.account_no AS account,
    a.balance    AS money
FROM
    accounts AS a;

Задачи с псевдонимами:

CREATE VIEW tasks_view AS
SELECT
    t.task_id    AS id,
    t.title      AS task,
    t.priority   AS importance
FROM
    tasks AS t;

Курсы с читаемыми названиями:

CREATE VIEW courses_view AS
SELECT
    c.course_id AS id,
    c.title     AS course,
    c.level     AS difficulty
FROM
    courses AS c;

Пациенты с короткими колонками:

CREATE VIEW patients_view AS
SELECT
    pt.patient_id AS id,
    pt.full_name  AS name,
    pt.age        AS years
FROM
    patients AS pt;

Статьи с простыми полями:

CREATE VIEW posts_view AS
SELECT
    p.post_id AS id,
    p.title   AS headline
FROM
    posts AS p;

Блок 3. Фильтрация через WHERE

Заказы с оплаченным статусом:

CREATE VIEW paid_orders AS
SELECT
    o.order_id,
    o.total_amount
FROM
    orders AS o
WHERE
    o.status = 'PAID';

Книги, изданные после 2020 года:

CREATE VIEW recent_books AS
SELECT
    b.book_id,
    b.title,
    b.year
FROM
    books AS b
WHERE
    b.year > 2020;

Клиенты из Москвы:

CREATE VIEW moscow_customers AS
SELECT
    c.customer_id,
    c.full_name,
    c.city
FROM
    customers AS c
WHERE
    c.city = 'Moscow';

Акции со сроком действия:

CREATE VIEW active_promotions AS
SELECT
    pr.promo_id,
    pr.name,
    pr.end_date
FROM
    promotions AS pr
WHERE
    pr.end_date >= CURRENT_DATE;

Билеты в продаже:

CREATE VIEW available_tickets AS
SELECT
    t.ticket_id,
    t.flight_no
FROM
    tickets AS t
WHERE
    t.is_sold = 0;

Пациенты старше 60 лет:

CREATE VIEW senior_patients AS
SELECT
    p.patient_id,
    p.full_name,
    p.age
FROM
    patients AS p
WHERE
    p.age > 60;

Студенты с GPA выше 4.0:

CREATE VIEW top_students AS
SELECT
    s.student_id,
    s.full_name,
    s.gpa
FROM
    students AS s
WHERE
    s.gpa > 4.0;

Товары с нулевым остатком:

CREATE VIEW out_of_stock AS
SELECT
    p.product_id,
    p.title
FROM
    products AS p
WHERE
    p.stock = 0;

Логины за последние сутки:

CREATE VIEW last_day_logins AS
SELECT
    l.user_id,
    l.login_time
FROM
    logins AS l
WHERE
    l.login_time >= CURRENT_TIMESTAMP - INTERVAL '1' DAY;

Непроверенные заказы:

CREATE VIEW unchecked_orders AS
SELECT
    o.order_id,
    o.status
FROM
    orders AS o
WHERE
    o.is_checked = 0;

Блок 4. Агрегаты с GROUP BY и HAVING

Общая сумма заказов по клиентам:

CREATE VIEW customer_totals AS
SELECT
    o.customer_id,
    SUM(o.total_amount) AS total_spent
FROM
    orders AS o
GROUP BY
    o.customer_id;

Количество товаров в категориях:

CREATE VIEW product_counts AS
SELECT
    p.category,
    COUNT(*) AS total_products
FROM
    products AS p
GROUP BY
    p.category;

Средняя зарплата по отделам:

CREATE VIEW avg_salary_departments AS
SELECT
    e.department_id,
    AVG(e.salary) AS avg_salary
FROM
    employees AS e
GROUP BY
    e.department_id;

Города с большим числом заказов:

CREATE VIEW top_city_orders AS
SELECT
    c.city,
    COUNT(o.order_id) AS orders_count
FROM
    customers AS c
    INNER JOIN orders AS o
        ON o.customer_id = c.customer_id
GROUP BY
    c.city
HAVING
    COUNT(o.order_id) > 100;

Категории с продажами свыше 1 млн:

CREATE VIEW big_sales_categories AS
SELECT
    p.category,
    SUM(o.total_amount) AS revenue
FROM
    orders AS o
    INNER JOIN products AS p
        ON o.product_id = p.product_id
GROUP BY
    p.category
HAVING
    SUM(o.total_amount) > 1000000;

Количество студентов на курсе:

CREATE VIEW course_enrollments AS
SELECT
    e.course_id,
    COUNT(e.student_id) AS students_count
FROM
    enrollments AS e
GROUP BY
    e.course_id;

Средняя продолжительность визитов:

CREATE VIEW avg_visit_length AS
SELECT
    v.hospital_id,
    AVG(v.duration) AS avg_duration
FROM
    visits AS v
GROUP BY
    v.hospital_id;

Минимальная цена по брендам:

CREATE VIEW min_price_brand AS
SELECT
    p.brand,
    MIN(p.price) AS min_price
FROM
    products AS p
GROUP BY
    p.brand;

Средний рейтинг фильмов по жанрам:

CREATE VIEW avg_rating_genres AS
SELECT
    f.genre,
    AVG(f.rating) AS avg_rating
FROM
    films AS f
GROUP BY
    f.genre;

Сумма баллов студентов по группам:

CREATE VIEW scores_by_group AS
SELECT
    s.group_id,
    SUM(s.score) AS total_score
FROM
    scores AS s
GROUP BY
    s.group_id;

Блок 5. Соединения (JOIN)

Заказы с именами клиентов:

CREATE VIEW orders_with_customers AS
SELECT
    o.order_id,
    c.full_name,
    o.total_amount
FROM
    orders AS o
    INNER JOIN customers AS c
        ON c.customer_id = o.customer_id;

Отзывы с названиями фильмов:

CREATE VIEW reviews_with_titles AS
SELECT
    r.review_id,
    f.title,
    r.rating
FROM
    reviews AS r
    LEFT JOIN films AS f
        ON f.film_id = r.film_id;

Сотрудники с названиями отделов:

CREATE VIEW employees_with_departments AS
SELECT
    e.emp_id,
    e.full_name,
    d.name AS department
FROM
    employees AS e
    INNER JOIN departments AS d
        ON e.department_id = d.department_id;

Продажи по категориям:

CREATE VIEW sales_by_category AS
SELECT
    p.category,
    SUM(o.total_amount) AS total_sales
FROM
    orders AS o
    INNER JOIN products AS p
        ON o.product_id = p.product_id
GROUP BY
    p.category;

Студенты с названиями курсов:

CREATE VIEW students_courses AS
SELECT
    s.student_id,
    s.full_name,
    c.title AS course
FROM
    students AS s
    INNER JOIN enrollments AS e
        ON s.student_id = e.student_id
    INNER JOIN courses AS c
        ON e.course_id = c.course_id;

Билеты с рейсами:

CREATE VIEW tickets_with_flights AS
SELECT
    t.ticket_id,
    f.flight_no,
    f.departure_time
FROM
    tickets AS t
    INNER JOIN flights AS f
        ON t.flight_no = f.flight_no;

Логины с данными пользователей:

CREATE VIEW logins_with_users AS
SELECT
    l.login_id,
    u.full_name,
    l.login_time
FROM
    logins AS l
    LEFT JOIN users AS u
        ON l.user_id = u.user_id;

Продукты с брендами:

CREATE VIEW products_with_brands AS
SELECT
    p.product_id,
    p.title,
    b.name AS brand
FROM
    products AS p
    INNER JOIN brands AS b
        ON p.brand_id = b.brand_id;

Задачи с именами сотрудников:

CREATE VIEW tasks_with_employees AS
SELECT
    t.task_id,
    t.title,
    e.full_name AS assignee
FROM
    tasks AS t
    INNER JOIN employees AS e
        ON t.assigned_to = e.emp_id;

Пациенты с именами врачей:

CREATE VIEW patients_with_doctors AS
SELECT
    p.patient_id,
    p.full_name,
    d.full_name AS doctor
FROM
    patients AS p
    INNER JOIN doctors AS d
        ON p.doctor_id = d.doctor_id;

Блок 6. Подзапросы внутри представлений

Клиенты с максимальными заказами:

CREATE VIEW top_customers AS
SELECT
    c.customer_id,
    c.full_name,
    (SELECT MAX(o.total_amount)
     FROM orders AS o
     WHERE o.customer_id = c.customer_id) AS max_order
FROM
    customers AS c;

Студенты с последним экзаменом:

CREATE VIEW last_exam_students AS
SELECT
    s.student_id,
    s.full_name,
    (SELECT MAX(e.exam_date)
     FROM exams AS e
     WHERE e.student_id = s.student_id) AS last_exam_date
FROM
    students AS s;

Фильмы с количеством отзывов:

CREATE VIEW films_with_reviews AS
SELECT
    f.film_id,
    f.title,
    (SELECT COUNT(*)
     FROM reviews AS r
     WHERE r.film_id = f.film_id) AS reviews_count
FROM
    films AS f;

Сотрудники с минимальной зарплатой в отделе:

CREATE VIEW employees_min_salary AS
SELECT
    e.emp_id,
    e.full_name,
    e.department_id,
    (SELECT MIN(salary)
     FROM employees AS e2
     WHERE e2.department_id = e.department_id) AS min_salary_in_dept
FROM
    employees AS e;

Товары с последней датой продажи:

CREATE VIEW products_last_sale AS
SELECT
    p.product_id,
    p.title,
    (SELECT MAX(o.order_date)
     FROM orders AS o
     WHERE o.product_id = p.product_id) AS last_sale_date
FROM
    products AS p;

Клиенты с количеством заказов:

CREATE VIEW customer_order_counts AS
SELECT
    c.customer_id,
    c.full_name,
    (SELECT COUNT(*)
     FROM orders AS o
     WHERE o.customer_id = c.customer_id) AS orders_count
FROM
    customers AS c;

Курсы с максимальной оценкой:

CREATE VIEW courses_max_score AS
SELECT
    c.course_id,
    c.title,
    (SELECT MAX(s.score)
     FROM scores AS s
     WHERE s.course_id = c.course_id) AS max_score
FROM
    courses AS c;

Врачи с количеством пациентов:

CREATE VIEW doctors_patient_counts AS
SELECT
    d.doctor_id,
    d.full_name,
    (SELECT COUNT(*)
     FROM patients AS p
     WHERE p.doctor_id = d.doctor_id) AS patients_count
FROM
    doctors AS d;

Пользователи с датой последнего логина:

CREATE VIEW users_last_login AS
SELECT
    u.user_id,
    u.full_name,
    (SELECT MAX(l.login_time)
     FROM logins AS l
     WHERE l.user_id = u.user_id) AS last_login
FROM
    users AS u;

Фильмы с максимальным рейтингом в жанре:

CREATE VIEW films_top_in_genre AS
SELECT
    f.film_id,
    f.title,
    f.genre,
    (SELECT MAX(rating)
     FROM films AS f2
     WHERE f2.genre = f.genre) AS max_genre_rating
FROM
    films AS f;

Блок 7. Аналитические сценарии (финансы, e-commerce, HR, медицина)

Доходы по месяцам:

CREATE VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(o.total_amount) AS revenue
FROM
    orders AS o
GROUP BY
    DATE_TRUNC('month', o.order_date);

Средний чек по магазинам:

CREATE VIEW avg_check_stores AS
SELECT
    s.store_id,
    AVG(o.total_amount) AS avg_check
FROM
    orders AS o
    INNER JOIN stores AS s
        ON o.store_id = s.store_id
GROUP BY
    s.store_id;

Уровень текучести сотрудников:

CREATE VIEW employee_turnover AS
SELECT
    d.department_id,
    COUNT(e.emp_id) FILTER (WHERE e.status = 'FIRED')::float /
    COUNT(e.emp_id) AS turnover_rate
FROM
    employees AS e
    INNER JOIN departments AS d
        ON e.department_id = d.department_id
GROUP BY
    d.department_id;

Количество госпитализаций по годам:

CREATE VIEW hospitalizations_yearly AS
SELECT
    EXTRACT(YEAR FROM v.visit_date) AS year,
    COUNT(*) AS total_visits
FROM
    visits AS v
GROUP BY
    EXTRACT(YEAR FROM v.visit_date);

Популярные товары по продажам:

CREATE VIEW top_selling_products AS
SELECT
    p.product_id,
    p.title,
    SUM(o.total_amount) AS total_sales
FROM
    orders AS o
    INNER JOIN products AS p
        ON o.product_id = p.product_id
GROUP BY
    p.product_id,
    p.title
ORDER BY
    total_sales DESC;

Средний балл студентов по курсам:

CREATE VIEW avg_score_courses AS
SELECT
    c.course_id,
    c.title,
    AVG(s.score) AS avg_score
FROM
    scores AS s
    INNER JOIN courses AS c
        ON s.course_id = c.course_id
GROUP BY
    c.course_id,
    c.title;

Пациенты по возрастным группам:

CREATE VIEW patient_age_groups AS
SELECT
    CASE
        WHEN p.age < 18 THEN 'Child'
        WHEN p.age BETWEEN 18 AND 60 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS count_patients
FROM
    patients AS p
GROUP BY
    age_group;

Продажи по каналам:

CREATE VIEW sales_by_channel AS
SELECT
    o.channel,
    SUM(o.total_amount) AS total_sales
FROM
    orders AS o
GROUP BY
    o.channel;

Доходы врачей по специализациям:

CREATE VIEW revenue_doctors AS
SELECT
    d.specialization,
    SUM(v.price) AS total_revenue
FROM
    visits AS v
    INNER JOIN doctors AS d
        ON v.doctor_id = d.doctor_id
GROUP BY
    d.specialization;

Производительность сотрудников:

CREATE VIEW employee_performance AS
SELECT
    e.emp_id,
    e.full_name,
    COUNT(t.task_id) AS tasks_done
FROM
    employees AS e
    LEFT JOIN tasks AS t
        ON e.emp_id = t.assigned_to
        AND t.status = 'DONE'
GROUP BY
    e.emp_id,
    e.full_name;

Блок 8. Представления для безопасности и маскирования данных

Клиенты без отображения телефона:

CREATE VIEW customers_masked AS
SELECT
    c.customer_id,
    c.full_name,
    '***-***-' || RIGHT(c.phone, 2) AS phone_masked
FROM
    customers AS c;

Пациенты без паспортных данных:

CREATE VIEW patients_safe AS
SELECT
    p.patient_id,
    p.full_name,
    NULL AS passport_no
FROM
    patients AS p;

Сотрудники с окладом без премий:

CREATE VIEW employees_salary_only AS
SELECT
    e.emp_id,
    e.full_name,
    e.salary AS base_salary
FROM
    employees AS e;

Маскирование email-адресов:

CREATE VIEW masked_emails AS
SELECT
    u.user_id,
    CONCAT(LEFT(u.email, 3), '***@***') AS email_masked
FROM
    users AS u;

Ограничение полей по GDPR:

CREATE VIEW gdpr_users AS
SELECT
    u.user_id,
    u.full_name
FROM
    users AS u;

Анонимные отзывы:

CREATE VIEW anonymous_reviews AS
SELECT
    r.review_id,
    'Anonymous' AS author,
    r.text
FROM
    reviews AS r;

Финансовые транзакции без деталей карт:

CREATE VIEW transactions_safe AS
SELECT
    t.trans_id,
    t.amount,
    NULL AS card_number
FROM
    transactions AS t;

Маскирование идентификаторов пациентов:

CREATE VIEW patients_masked AS
SELECT
    MD5(CAST(p.patient_id AS TEXT)) AS anon_id,
    p.age,
    p.diagnosis
FROM
    patients AS p;

Ограниченный список заказов для стажёров:

CREATE VIEW trainee_orders AS
SELECT
    o.order_id,
    o.total_amount
FROM
    orders AS o;

Сокрытие зарплат руководителей:

CREATE VIEW no_exec_salaries AS
SELECT
    e.emp_id,
    e.full_name,
    CASE WHEN e.role = 'EXEC' THEN NULL ELSE e.salary END AS salary
FROM
    employees AS e;

Блок 9. Сложные конструкции (UNION, CASE, CTE)

Объединение клиентов и сотрудников:

CREATE VIEW people_union AS
SELECT
    c.customer_id AS id,
    c.full_name   AS name,
    'Customer'    AS role
FROM
    customers AS c
UNION
SELECT
    e.emp_id,
    e.full_name,
    'Employee'
FROM
    employees AS e;

Товары со статусом по цене:

CREATE VIEW products_case AS
SELECT
    p.product_id,
    p.title,
    CASE
        WHEN p.price < 100 THEN 'Cheap'
        WHEN p.price BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'Expensive'
    END AS price_category
FROM
    products AS p;

CTE в представлении:

CREATE VIEW top_departments AS
WITH dept_avg AS (
    SELECT
        e.department_id,
        AVG(e.salary) AS avg_salary
    FROM
        employees AS e
    GROUP BY
        e.department_id
)
SELECT
    d.department_id,
    d.name,
    da.avg_salary
FROM
    dept_avg AS da
    INNER JOIN departments AS d
        ON d.department_id = da.department_id;

Объединение активных и архивных заказов:

CREATE VIEW all_orders AS
SELECT
    o.order_id,
    o.status
FROM
    orders AS o
WHERE
    o.status = 'ACTIVE'
UNION
SELECT
    o.order_id,
    o.status
FROM
    orders AS o
WHERE
    o.status = 'ARCHIVED';

CASE для статуса сотрудников:

CREATE VIEW employees_status AS
SELECT
    e.emp_id,
    e.full_name,
    CASE
        WHEN e.status = 'ACTIVE' THEN 'Работает'
        WHEN e.status = 'FIRED' THEN 'Уволен'
        ELSE 'Неизвестно'
    END AS status_text
FROM
    employees AS e;

Фильмы с категоризацией рейтинга:

CREATE VIEW films_case AS
SELECT
    f.film_id,
    f.title,
    CASE
        WHEN f.rating >= 8 THEN 'Hit'
        WHEN f.rating >= 5 THEN 'Average'
        ELSE 'Flop'
    END AS category
FROM
    films AS f;

UNION студентов и преподавателей:

CREATE VIEW people_union_roles AS
SELECT
    s.student_id AS id,
    s.full_name,
    'Student' AS role
FROM
    students AS s
UNION
SELECT
    t.teacher_id,
    t.full_name,
    'Teacher'
FROM
    teachers AS t;

CTE для сумм по регионам:

CREATE VIEW sales_regions AS
WITH regional_sales AS (
    SELECT
        c.region,
        SUM(o.total_amount) AS sales
    FROM
        orders AS o
        INNER JOIN customers AS c
            ON o.customer_id = c.customer_id
    GROUP BY
        c.region
)
SELECT
    region,
    sales
FROM
    regional_sales;

UNION фильмов и сериалов:

CREATE VIEW media_union AS
SELECT
    f.film_id AS id,
    f.title,
    'Film' AS type
FROM
    films AS f
UNION
SELECT
    s.series_id,
    s.title,
    'Series'
FROM
    series AS s;

CASE для возрастных групп:

CREATE VIEW age_groups AS
SELECT
    p.patient_id,
    p.full_name,
    CASE
        WHEN p.age < 18 THEN 'Child'
        WHEN p.age < 60 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM
    patients AS p;

Блок 10. Комбинированные многослойные представления

Заказы с клиентами и товарами:

CREATE VIEW orders_full AS
SELECT
    o.order_id,
    c.full_name AS customer,
    p.title     AS product,
    o.total_amount
FROM
    orders AS o
    INNER JOIN customers AS c
        ON o.customer_id = c.customer_id
    INNER JOIN products AS p
        ON o.product_id = p.product_id;

Студенты с оценками и курсами:

CREATE VIEW students_scores_courses AS
SELECT
    s.student_id,
    s.full_name,
    c.title AS course,
    sc.score
FROM
    students AS s
    INNER JOIN scores AS sc
        ON s.student_id = sc.student_id
    INNER JOIN courses AS c
        ON sc.course_id = c.course_id;

Сотрудники с отделами и задачами:

CREATE VIEW employees_dept_tasks AS
SELECT
    e.emp_id,
    e.full_name,
    d.name AS department,
    t.title AS task
FROM
    employees AS e
    INNER JOIN departments AS d
        ON e.department_id = d.department_id
    LEFT JOIN tasks AS t
        ON e.emp_id = t.assigned_to;

Фильмы с отзывами и рейтингом:

CREATE VIEW films_with_reviews_avg AS
SELECT
    f.film_id,
    f.title,
    AVG(r.rating) AS avg_rating
FROM
    films AS f
    LEFT JOIN reviews AS r
        ON f.film_id = r.film_id
GROUP BY
    f.film_id,
    f.title;

Пациенты с визитами и врачами:

CREATE VIEW patients_visits_doctors AS
SELECT
    p.patient_id,
    p.full_name,
    v.visit_date,
    d.full_name AS doctor
FROM
    patients AS p
    INNER JOIN visits AS v
        ON p.patient_id = v.patient_id
    INNER JOIN doctors AS d
        ON v.doctor_id = d.doctor_id;

Транзакции с пользователями и магазинами:

CREATE VIEW transactions_full AS
SELECT
    t.trans_id,
    u.full_name AS user,
    s.name      AS store,
    t.amount
FROM
    transactions AS t
    INNER JOIN users AS u
        ON t.user_id = u.user_id
    INNER JOIN stores AS s
        ON t.store_id = s.store_id;

Логины с устройствами:

CREATE VIEW logins_devices AS
SELECT
    l.login_id,
    u.full_name,
    d.device_type,
    l.login_time
FROM
    logins AS l
    INNER JOIN users AS u
        ON l.user_id = u.user_id
    INNER JOIN devices AS d
        ON l.device_id = d.device_id;

Курсы с преподавателями и студентами:

CREATE VIEW courses_full AS
SELECT
    c.course_id,
    c.title,
    t.full_name AS teacher,
    COUNT(e.student_id) AS students_count
FROM
    courses AS c
    INNER JOIN teachers AS t
        ON c.teacher_id = t.teacher_id
    LEFT JOIN enrollments AS e
        ON c.course_id = e.course_id
GROUP BY
    c.course_id,
    c.title,
    t.full_name;

Заказы с оплатой и доставкой:

CREATE VIEW orders_payments_shipments AS
SELECT
    o.order_id,
    o.total_amount,
    p.payment_date,
    s.shipment_date
FROM
    orders AS o
    LEFT JOIN payments AS p
        ON o.order_id = p.order_id
    LEFT JOIN shipments AS s
        ON o.order_id = s.order_id;

Сотрудники с ролями и проектами:

CREATE VIEW employees_roles_projects AS
SELECT
    e.emp_id,
    e.full_name,
    r.name AS role,
    pr.title AS project
FROM
    employees AS e
    INNER JOIN roles AS r
        ON e.role_id = r.role_id
    LEFT JOIN projects AS pr
        ON e.emp_id = pr.manager_id;

Заключение

view sql — это универсальный способ структурировать SQL-код. Представления позволяют скрывать сложные соединения, повторно использовать запросы, ограничивать доступ к данным и ускорять разработку аналитических решений.

В статье показаны 100 примеров: от простых фильтров и алиасов до аналитики с GROUP BY,
условными выражениями CASE, объединениями UNION и сложными JOIN.
Используйте VIEW для удобства и читаемости, но помните: чрезмерная вложенность представлений может влиять на производительность.