Как сделать список группы студентов

Добавил пользователь Алексей Ф.
Обновлено: 04.10.2024

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

Задания

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

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

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

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).

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

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

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

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

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

Синтаксически основной и подчиненный запросы подчиняются общим правилам языка 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 , но транслятор правильно выполнит данный запрос, так как понятно, что мы ссылаемся на внешний основной запрос.

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

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

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

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

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

package Java_OOP_1 ;

/**

* Created by Nick on 24.01.2016.

public class MoreStudents

public static void main ( String [] args )

Student st1 = new Student ( " Иван " , " Иванович " , " Иванов " , " 01.01.1998 " , " 38044555667712 " , " Психология " , " 1 " , " 123 " );

Student st2 = new Student ( " Петр " , " Петрович " , " Петров " , " 12.04.1997 " , " 38044555667713 " , " Журналистика " , " 2 " , " 321 " );

Student st3 = new Student ( " Семен " , " Семенович " , " Семенов " , " 10.07.1996 " , " 38044555667714 " , " Юридический " , " 3 " , " 231 " );

Student st4 = new Student ( " Константин " , " Иванович " , " Галушко " , " 04.09.1999 " , " 38044555667715 " , " Психология " , " 1 " , " 123 " );

Student st5 = new Student ( " Сергей " , " Петрович " , " Пивоваров " , " 03.04.1998 " , " 38044555667716 " , " Журналистика " , " 2 " , " 321 " );

Student st6 = new Student ( " Василий " , " Николаевич " , " Пупкин " , " 07.01.1998 " , " 38044555667717 " , " Юридический " , " 3 " , " 231 " );

Student st7 = new Student ( " Анатолий " , " Сергеевич " , " Антонов " , " 03.12.1996 " , " 38044555667718 " , " Психология " , " 1 " , " 123 " );

Student st8 = new Student ( " Евгений " , " Анатольевич " , " Жуков " , " 15.07.1997 " , " 38044555667719 " , " Журналистика " , " 2 " , " 321 " );

Student st9 = new Student ( " Роман " , " Семенович " , " Решетников " , " 23.04.1998 " , " 38044555667720 " , " Юридический " , " 3 " , " 231 " );

Student st10 = new Student ( " Федор " , " Викторович " , " Блинов " , " 05.01.1998 " , " 38044555667721 " , " Психология " , " 1 " , " 123 " );

Student st11 = new Student ( " Виктор " , " Андреевич " , " Сидорченко " , " 12.05.1997 " , " 38044555667722 " , " Журналистика " , " 2 " , " 321 " );

Student st12 = new Student ( " Всеволод " , " Богданович " , " Ющенко " , " 14.12.1996 " , " 38044555667723 " , " Юридический " , " 3 " , " 231 " );

Student st13 = new Student ( " Геннадий " , " Николаевич " , " Порошенко " , " 13.09.1999 " , " 38044555667724 " , " Психология " , " 1 " , " 123 " );

Student st14 = new Student ( " Николай " , " Петрович " , " Соломатин " , " 11.09.1998 " , " 38044555667725 " , " Журналистика " , " 2 " , " 321 " );

StudentList newList = new StudentList ();

newList . addStudent(st1);

newList . addStudent(st2);

newList . addStudent(st3);

newList . addStudent(st4);

newList . addStudent(st5);

newList . addStudent(st6);

newList . addStudent(st7);

newList . addStudent(st8);

newList . addStudent(st9);

newList . addStudent(st10);

newList . addStudent(st11);

newList . addStudent(st12);

newList . addStudent(st13);

newList . addStudent(st14);

newList . facultyPrint( " Психология " ); // список студентов заданного факультета

newList . groupPrint( " 123 " ); // список учебной группы

newList . yearOfBirthPrint( " 1997 " ); // список студентов, родившихся после заданного года;

newList . facultyCoursePrint( " Психология " , " 1 " ); // списки студентов для каждого факультета и курса;

package Java_OOP_1 ;

/**

*Создать классы, спецификации которых приведены ниже. Определить кон-

структоры и методы setТип(), getТип(), toString(). Определить дополнительно

методы в классе, создающем массив объектов. Задать критерий выбора данных

и вывести эти данные на консоль. В каждом классе, обладающем информацией,

должно быть объявлено несколько конструкторов.

* Student: id, Фамилия, Имя, Отчество, Дата рождения, Адрес, Телефон,

Факультет, Курс, Группа

public class Student

private int id;

private static int nextId = 0 ;

private String name;

private String middleName;

private String dateOfBirth;

private String surname;

private String phoneNumber;

private String faculty;

private String course;

private String group;

public Student ( String name , String middleName , String surname , String dateOfBirth , String phoneNumber , String faculty , String course , String group )

this . name = name;

this . middleName = middleName;

this . surname = surname;

this . dateOfBirth = dateOfBirth;

this . phoneNumber = phoneNumber;

this . faculty = faculty;

this . course = course;

this . group = group;

id = nextId ++ ;

setName(name);

setMiddleName(middleName);

setSurname(surname);

setDateOfBirth(dateOfBirth);

setPhoneNumber(phoneNumber);

setFaculty(faculty);

setCourse(course);

setGroup(group);>

public String getName ()

return name;

public void setName ( String name )

this . name = name;

public String getMiddleName ()

return name;

public void setMiddleName ( String middleName )

this . middleName = middleName;

public String getSurname ()

return surname;

public void setSurname ( String surname )

this . surname = surname;

public String getPhoneNumber ()

return phoneNumber;

public void setPhoneNumber ( String phoneNumber )

this . phoneNumber = phoneNumber;

public String getFaculty ()

return faculty;

public void setFaculty ( String faculty )

this . faculty = faculty;

public String getCourse ()

return course;

public void setCourse ( String course )

this . course = course;

public String getGroup ()

return group;

public void setGroup ( String group )

this . group = group;

public String getDateOfBirth ()

return dateOfBirth;

public void setDateOfBirth ( String dateOfBirth )

this . dateOfBirth = dateOfBirth;

public String toString ()

return " Student < " + " id " + id +

" , name=' " + name + ' \' ' +

" , middleName=' " + middleName + ' \' ' +

" , dateOfBirth=' " + dateOfBirth + ' \' ' +

" , surname=' " + surname + ' \' ' +

" , phoneNumber=' " + phoneNumber + ' \' ' +

" , faculty=' " + faculty + ' \' ' +

" , course=' " + course + ' \' ' +

" , group=' " + group + ' \' ' +

' > ' ;

public String fullName ()

return surname + " " + middleName + " " + name;

package Java_OOP_1 ;

import java.util.ArrayList ;

import java.util.List ;

/**

* Created by Nick on 24.01.2016.

public class StudentList

private List Student > studentsList = new ArrayList<> ();

public void addStudent ( Student a )

studentsList . add(a);

public void facultyPrint ( String faculty )

System . out . println( " Список студентов факультета " + faculty);