Урок 2 SQL. Переименование столбца и применение агрегатных функций

На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов

Переименование полей AS

Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.

В языке SQL переименование полей связано с использованием ключевого слова AS, которое и используется для переименования имен полей в результирующих наборах

Синтаксис:

SELECT <имя поля> AS <псевдоним> FROM

Рассмотрим пример переименования в SQL:

Пример БД «Институт»: Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»

✍ Решение:
 

1
2
3
SELECT name, zarplata AS низкая_зарплата
  FROM teachers
  WHERE zarplata<15000;

Результат:
sql переименование столбца

SQL As 2_1. БД Компьютерный магазин. Вывести данные о компьютерах. Переименовать столбец Скорость в «Мб», а столбец HD в «Гб»

Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:

Пример БД «Институт»: Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»

✍ Решение:
 

1
2
SELECT name, (zarplata+premia) AS zarplata_premia 
  FROM teachers;

Результат:
sql переименование

SQL As 2_1. БД Институт. Вывести фамилии учителей и разницу между их зарплатой и премией. Назвать вывод «зарплата_минус_премия»
Задание 2_1. БД «Компьютерные курсы». Из таблицы Личные данные вывести значения полей Код студента, Word, Excel, Access и вычислить среднее значение по полям Word, Excel, Access, назвав поле «Среднее»
SQL As 2_2. БД Компьютерный магазин. Вывести объем оперативной памяти в Килобайтах (из Мб получить Кб). Выводить и исходное и получившееся значение
Задание 2_2. БД «Компьютерные курсы». Вывести номера телефонов и оценки по Word студентов, успеваемость по курсу Word которых ниже 4 баллов, переименовать поле Word на Низкая успеваемость

Агрегатные функции в SQL

Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:

Функция Описание
COUNT(*) Возвращает количество строк таблицы.
COUNT(имя поля) Возвращает количество значений в указанном столбце.
SUM(имя поля) Возвращает сумму значений в указанном столбце.
AVG(имя поля) Возвращает среднее значение в указанном столбце.
MIN(имя поля) Возвращает минимальное значение в указанном столбце.
MAX(имя поля) Возвращает максимальное значение в указанном столбце.

Все агрегатные функции возвращают единственное значение.

Функции COUNT, MIN и MAX применимы к любым типам данных.

Функции SUM и AVG используются только для числовых полей.
Между функциями COUNT(*) и COUNT(<имя поля>) есть разница: вторая при подсчете не учитывает NULL-значения.

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

✍ Решение:
 

SELECT MAX(zarplata) AS макс_зп
  FROM teachers;

Результаты:
агрегатные функции в sql

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

Пример: БД Компьютерный магазин. Найти имеющееся в наличии количество компьютеров, выпущенных производителем Америка

✍ Решение:
 

1
2
3
4
5
6
7
8
SELECT COUNT( * ) 
FROM  `pc` 
WHERE  `Номер` 
IN (
  SELECT  `Номер` 
  FROM product
  WHERE Производитель =  "Америка"
)

sql запросы count

SQL As 2_3. БД Компьютерный магазин. Вывести общее количество продуктов, странами-производителями которых является Россия
SQL As 2_4. БД Компьютерный магазин. Вывести среднюю цену на ноутбуки
Agr func 2_2. БД Институт. Вывести минимальную и максимальную зарплату учителей
Agr func 2_3. БД Институт. Выберите название курса, уроки по которому не проводились и не запланированы проводиться. Дополните код:

1
2
3
4
5
6
SELECT  `title` 
FROM  `courses` 
WHERE  `title` NOT 
IN (
   ...
)
Agr func 2_4. БД Институт. Измените предыдущее задание: Посчитайте количество тех курсов, уроки по которым не проводились и не запланированы проводиться. Выводите результат с именем «нет_уроков»
Задание 2_3. БД «Компьютерные курсы». Вывести год рождения самого младшего студента, назвать поле «Младший»
Задание 2_4. БД «Компьютерные курсы». Посчитать количество всех студентов группы 101. Назвать поле «Группа101»

Предложение GROUP BY в SQL

Оператор group by в sql обычно используется совместно с агрегатными функциями.

Агрегатные функции выполняются над всеми результирующими строками запроса. Если запрос содержит оператор GROUP BY, каждый набор строк, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно.

Рассмотрим пример с таблицей lessons:
sql group by

Пример:

  1. Выдавать количество проведенных уроков учителем Иванов из таблицы lessons (порядковый номер Иванова 1 (tid)).
  2. SELECT COUNT(tid) AS Иванов 
    FROM lessons
    WHERE tid=1

    Результат:
    Оператор group by в sql

  3. Выдавать количество проведенных уроков учителем Иванов по разным курсам из таблицы lessons
  4. SELECT course, COUNT( tid ) AS Иванов
    FROM lessons
    WHERE tid =1
    GROUP BY course

    Результат:
    group by в sql

Важно: Таким образом, в результате использования GROUP BY все выходные строки запроса разделяются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах (т.е. агрегатные функции выполняются для каждой группы отдельно).

При этом стоит учесть, что при группировке по полю, содержащему NULL-значения, все такие записи попадут в одну группу.

SQL group by 2_5. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость и количество (т.е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции AVG(поле) и COUNT(поле). Результат должен выглядеть так:

GROUP BY в SQL

SQL group by 2_5. БД Институт. Посчитать количество уроков, проведенных одним и тем же учителем. Результат должен выглядеть так:
sql group by пример
Задание 2_5. БД «Компьютерные курсы». Вывести количество человек в каждой группе и количество человек на каждом курсе из таблицы Список. Назвать вычисляемые поля «кол_во_в_гр» и «кол_во_на_курс»

Оператор Having SQL

Предложение HAVING в SQL необходимо для проверки значений, которые получены с помощью агрегатной функции после группировки (после использования GROUP BY). Такая проверка не может содержаться в предложении WHERE.

Пример: БД Компьютерный магазин. Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

✍ Решение:
 

SELECT AVG(`Цена`) ,`Скорость`
FROM  `pc` 
GROUP BY `Скорость` 
HAVING AVG(`Цена`) <30000

Результат:
sql having пример

Важно: В операторе Having нельзя использовать псевдоним (например, сред_цена), используемый для именования значений агрегатной функции.

Having SQL 2_6. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость (т.е. отдельно по лазерным, струйным и матричным). Вести подсчет только если средняя стоимость <10000. Результат: Оператор Having SQL
Having SQL 2_6. БД Институт
Посчитать количество уроков, проведенных одним и тем же учителем. Выдавать значение только для тех учителей, у которых уроков больше двух.
Задание 2_6. БД «Компьютерные курсы». Получить количество учеников каждой группы при условии, что курс не меньше 3-го
Поделитесь уроком с коллегами и друзьями:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*
*

Вставить формулу как
Блок
Строка
Дополнительные настройки
Цвет формулы
Цвет текста
#333333
Используйте LaTeX для набора формулы
Предпросмотр
\({}\)
Формула не набрана
Вставить