Вывести средний балл sql

Тема 3. Язык SQL. Формирование запросов к базе данных

Изучить методы написания сложных SQL-запросов и способы их реализации на сервере MS SQL Server 2000.

Оглавление

Задания

1. Изучить принципы работы агрегатных функций.
2. Изучить принципы создания составных запросов.
3. Выполнить указанные запросы к учебной базе данных «Сессия».
4. Выполнить тест по языку SQL в системе ВУОКСа.

1. Агрегатные функции в языке SQL

Назовем сложными такие запросы на языке SQL, которые требуют для своей реализации использования агрегатных функций или подзапросов. Агрегатные функции используются при дополнительной обработке информации, когда применяется группировка строк запроса по ряду специальных параметров. При выполнении операций группировки строки с одинаковыми значениями столбцов группировки объединяются в одну группу. После этого к созданным группам можно применить стандартные агрегатные функции обработки. Стандарт языка SQL предлагает пять базовых агрегатных функций (табл. 1).

Функция Результат COUNT Количество строк или непустых значений полей, которые выбрал запрос в каждой группе SUM Сумма всех выбранных значений данного поля в группе AVG Среднее арифметическое всех выбранных значений данного поля в группе MIN Наименьшее из всех выбранных значений данного поля в группе MAX Наибольшее из всех выбранных значений данного поля в группе

Запросы с группировкой имеют следующий синтаксис:

Select
FROM
Where
GROUP BY
Having

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

Например, пусть нам необходимо для каждого студента сосчитать количество сданных экзаменов. Сданным считаем экзамен, за который получена оценка, большая чем 2 балла (рис. 1).

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

Функция count(distinct ) считает количество различных значений столбца в группе. Если у данного студента всего четыре оценки, среди которых две четверки и две пятерки, то при выполнении группировки по номеру зачетной книжки значение функции count(distinct mark) для данного студента будет , а значение функции count(mark) будет .

Функция Avg() считает среднее арифметическое, но так как при вычислении среднего значения тип результата совпадает с типом столбца, для которого он вычисляется, то мы получим целочисленный результат, который округлен в меньшую сторону.

Например, при вычислении среднего балла за сессию мы получим следующий результат (рис. 2).

Если же мы выведем сумму баллов, полученных каждым студентом на экзамене, и количество полученных им оценок, результат будет таким (рис. 3).

Агрегатные функции могут применяться не только при использовании опции group by в запросе SELECT . Если мы не используем группировку, то агрегатные функции вычисляются для всей таблицы. В этом случае для того чтобы сосчитать количество строк в таблице, необходимо выполнить следующий запрос:

Select count(*) from groops

Мы получили результат: всего 5 групп.

Функция count(distinct ) действует здесь аналогично.

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

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

Задания для самостоятельной работы

Выполнить следующие запросы к учебной базе данных «Сессия»:

1. Вывести количество специальностей, по которым ведется подготовка в нашем виртуальном вузе.
2. Для каждой группы вывести количество студентов, которые в ней учатся.
3. Для каждой специальности вывести количество групп, которые учатся по данной специальности.
4. Для каждой группы и каждой дисциплины, которую сдавали студенты этой группы, вычислить количество попыток сдачи экзаменов, максимальную и минимальную оценки, полученные на экзаменах.
5. Вывести список студентов, которые имеют несколько двоек за сессию.
6. Вывести список дисциплин, по которым есть должники, то есть студенты, которые получили по данной дисциплине двойку и не пересдали ее на положительную оценку.
7. Вывести список студентов, которые исправили двойки, полученные в первый раз при сдаче экзамена, то есть получили положительную оценку после двойки по той же дисциплине.
8. Вывести список групп, в которых есть двоечники по сессии, то есть те, кто не пересдал полученную двойку.
9. Вывести список студентов с указанием количества троек, полученных каждым студентом за сессию.
10. Вывести список групп, у которых на сессию вынесено более двух экзаменов.

2. Принципы создания составных запросов

До сих пор мы не сталкивались с понятием составных запросов. Составным запросом мы будем называть любой запрос, в котором используются результаты другого запроса.

Основной и подчиненный запросы могут соединяться операциями сравнения или предикатами IN , NOT IN , Exist , Not Exist . Интерпретация предикатов приведена в табл. 2.

Предикат Смысл Интерпретация IN Входит в множество Истинен, когда значение, стоящее слева в основном запросе, входит в множество значений, выводимое подчиненным запросом NOT IN Не входит в множество Истинен, когда значение, стоящее слева в основном запросе, не входит в множество значений, выводимое подчиненным запросом Exist Существует Истинен, когда результат подчиненного запроса содержит хотя бы одну строку Not Exisit Не существует Истинен, когда результат подчиненного запроса не содержит ни одной строки

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

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

Однако этот запрос можно решить, перефразируя условие следующим образом: найти студентов, номера зачетных книжек которых не входят в множество номеров зачетных книжек, имеющих хотя бы одну двойку при сдаче экзаменов в сессию. И в этом случае мы должны применить вложенный запрос:

select Last_Name
from students
where N_zach not in (select N_zach
from sessia
Where mark = 2)

Встроенные запросы могут быть зависимыми и независимыми.

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

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

В этом случае общий текс запроса выглядит следующим образом:

select Last_Name
from students, Groops, uch_plan
where students.N_groop = Groops.N_groop
and groops.kod_spec = uch_plan.kod_spec
group by Last_Name, N_zach
Having count(distinct Discipline) = (select count(distinct Discipline)
from sessia
where mark > 2
and sessia.N_zach = students.N_zach)

Обратите внимание, что в нижнем запросе в части FROM нет таблицы Students , но транслятор правильно выполнит данный запрос, так как понятно, что мы ссылаемся на внешний основной запрос.

Задания для самостоятельной работы (продолжение)

1. Вывести список студентов, у которых количество полученных на сессии пятерок равно количеству полученных на сессии троек.
2. Вывести список групп с указанием количества студентов, не сдавших все экзамены в сессию.
3. Вывести список дисциплин, которые читаются всем специальностям (есть во всех учебных планах).
4. Вывести список групп, в которых нет двоечников (студентов, получивших на сессии двойки и не пересдавших этот экзамен).
5. Вывести список студентов, у которых на сессии были только тройки.
6. Вывести список студентов, которые сдали на 5 все экзамены, которые они сдавали (при этом не учитывать, сданы ли все экзамены, которые необходимо сдать за сессию).
7. Вывести списки групп, все студенты которых сдали экзамены на оценку, не ниже чем 4.
8. Вывести список дисциплин, по которым на экзаменах не было получено ни одной двойки.
9. Вывести список групп, в которых есть студенты-отличники (те, кто сдал все требуемые экзамены на отлично, причем с первого раза).

3. Зачетный SQL-тест в системе ВУОКСа

1. Поключитесь к системе ВУОКСа.
2. Выберите нужный тест из назначенного вам списка (рис. 6).

В разделе статистики постоянно ведется учет набранных вами баллов. Следите за этим показателем.

В правом нижнем фрейме работает счетчик оставшегося времени. Учет времени ведется на сервере, а на вашем экране отображается состояние счетчика. Не забывайте, что отправить последние ответы надо не позднее чем за минуту до окончания теста, иначе ваши результаты могут быть не засчитаны.

Выполните все задания, предъявите выполненные запросы преподавателю и получите зачет по данной практической работе.

Источник

Как рассчитать средний балл в MySql

В запросе должны быть указаны имя студента и средний балл.

Даны следующие таблицы:

Я попробовал этот код, попробовал это и получил бессмысленный вывод. Я довольно потерян

GPA кажется неправильным, потому что

Возвращенный результат от предложения @Barbaros Özhan, а также от моих собственных аналогичных решений:

‘1’, ‘Steve Inskeep’, ‘1.7857’

В качестве первого возвращенного ряда.

Но, судя по всему, регистрационный стол ученика № 1 не имеет 1,7857 ГПД.

Редактировать: ответил Гордон Линофф:

3 ответа

Проблема в том, что у вас есть NULL оценки. , , но вы считаете секции, поэтому они рассматриваются как нули.

Небольшая корректировка вашего расчета исправит это:

Учитывая, что учащийся получает оценку по разделам, я не думаю, что вам нужны таблицы Section и Course в вашем запросе (хотя без просмотра примеров данных трудно быть уверенным). Это должно работать:

Если вам необходимо оценивать оценки в соответствии с единицами курса, вам необходимо сложить умножение оценки на единицы, а затем разделить на сумму единиц, стараясь только суммировать единицы курса, где есть действительная оценка:

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

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

Могут быть и другие проблемы, но начните отсюда.

Источник

AVG (Transact-SQL)

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

Эта функция возвращает среднее арифметическое группы значений. Значения NULL она не учитывает.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

ALL
Применяет агрегатную функцию ко всем значениям. ALL является параметром по умолчанию.

DISTINCT
Указывает на то, что функция AVG выполняется только для одного уникального экземпляра каждого значения, независимо от того, сколько раз встречается это значение.

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

OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы. order_by_clause определяет логический порядок, в котором выполняется операция. Аргумент order_by_clause является обязательным. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

Тип возвращаемого значения определяется типом вычисленного результата выражения.

Результат выражения	Возвращаемый тип
tinyint	int
smallint	int
int	int
bigint	bigint
Категория decimal (p, s)	decimal(38, max(s,6))
Категории money и smallmoney	money
Категории float и real	float

Комментарии

Если тип данных expression является типом данных-псевдонимом, тип возвращаемого значения также является типом данных-псевдонимом. Однако если базовый тип данных типа данных-псевдонима может повышаться, например из tinyint в int, возвращаемое значение будет иметь повышенный тип данных, а не тип данных-псевдоним.

Функция AVG () вычисляет среднее арифметическое набора значений, выполняя деление суммы этих значений на число значений, не равных NULL. Если сумма превышает максимальное значение для типа данных возвращаемого значения, AVG() возвратит ошибку.

AVG — это детерминированная функция, если она используется без предложений OVER и ORDER BY. Она не детерминирована при использовании с предложениями OVER и ORDER BY. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

A. Использование функций SUM и AVG для вычислений

В этом примере вычисляется среднее количество часов отпуска и сумма часов отсутствия по болезни, которые использовали вице-президенты компании Компания Adventure Works Cycles. Каждая из этих агрегатных функций создает одно сводное значение для всех извлеченных строк. В этом примере используется база данных AdventureWorks2012.

Б. Использование функций SUM и AVG в предложении GROUP BY

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

В. Использование функции AVG с ключевым словом DISTINCT

Эта инструкция возвращает среднюю ориентировочную цену на продукцию из базы данных AdventureWorks2012. При использовании DISTINCT в расчете учитываются только уникальные значения.

Г. Использование функции AVG без ключевого слова DISTINCT

Без ключевого слова DISTINCT функция AVG находит среднюю ориентировочную цену всех продуктов в таблице Product в базе данных AdventureWorks2012, учитывая и все повторяющиеся значения.

Д. Использование предложения OVER

Следующий пример показывает использование функции AVG с предложением OVER для получения скользящего среднего годовых продаж на каждой территории в таблице Sales.SalesPerson в базе данных AdventureWorks2012. Данные секционируются по TerritoryID и логически сортируются по SalesYTD . Это означает, что функция AVG вычисляется для каждой территории на основании объема продаж за год. Обратите внимание, что в TerritoryID 1 для продаж за 2005 год используются две строки, в которых представлены два менеджера по продажам с показателями за этот год. После расчета среднего значения продаж для двух данных строк в вычисление включается третья строка, представляющая продажи за 2006 год.

В этом примере предложение OVER не включает в себя предложение PARTITION BY. Это означает, что функция будет применяться для всех строк, возвращаемых запросом. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции AVG. Запрос возвращает скользящее среднее значение продаж за год для всех территорий, указанных в предложении WHERE. Предложение ORDER BY, указанное в инструкции SELECT, определяет порядок, в котором эта инструкция отображает строки запроса.

Источник