Создать базу данных для хранения информации о студентах. В БД должно быть две таблицы, одна для студентов, другая для данных группе. Заполнить таблицы следующими данными:

Фамилия	Имя	Группа	Год зачисления
Бянкин	Владислав	МРБ-18-1-1	2018
Валеев	Александр	МРБ-18-1-1	2018
Вяткина	Дарья	МРБ-18-1-1	2018
Гайнуллин	Максим	МРБ-18-1-1	2018
Дерюгин	Федор	МРБ-18-1-1	2018
Дугарнимаев	Александр	МРБ-18-1-1	2018
Игнатова	Анастасия	МРБ-18-1-1	2018
Иринчеев	Андрей	МРБ-18-1-1	2018
Исаева	Екатерина	МРБ-18-1-1	2018
Караулов	Иван	МРБ-18-1-1	2018
Карпов	Егор	МРБ-18-1-1	2018
Колесников	Константин	МРБ-18-1-2	2018
Корж	Максим	МРБ-18-1-2	2018
Кучеев	Дмитрий	МРБ-18-1-2	2018
Лавренюк	Валерия	МРБ-18-1-2	2018
Масленников	Ярослав	МРБ-18-1-2	2018
Моисеенко	Иван	МРБ-18-1-2	2018
Первушин	Владислав	МРБ-18-1-2	2018
Попов	Максим	МРБ-18-1-2	2018
Рафеков	Антон	МРБ-18-1-2	2018
Солобаев	Алексей	МРБ-18-1-2	2018
Черепанов	Иван	МРБ-18-1-2	2018
Шибанов	Александр	МРБ-18-1-2	2018

PS. Связи между таблицами осуществлять по первичным ключам

PSS. Таблицы должны находится во третьей нормальной форме.

Чтобы создать таблицу

Когда вы первый раз запустите access для своей БД, то скорее всего вам сразу предложат создать таблицу:

в учебных целях откажемся от этого предложения

А теперь сами создадим таблицу (сразу же переключимся в режим Конструктора и дадим таблице имя):

Далее добавим необходимые поля:

Теперь можно чего-нибудь добавить в таблицу

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

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

далее связываем соответствующие поля. Поле Группы таблицы Студенты с полем Код таблицы Группы

(на всякий пожарный закроем таблицу Студенты):

Добавляем форму

Теперь когда у нас настроенны связи, создадим форму для редактирования студентов. Кликнем на таблицу Студенты, а затим добавим форму:

Ура, форма есть! Теперь мы хотим чтобы вместо идентификатора группы отображалась сама группа. Удаляем старое поле, либо через Delete, либо правой кнопкой мыши затем пункт “Удалить строку”:

И добавляем новое поле

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

И можно листать теперь:

Изменяя данные на форме, данные будут менятся внутри таблиц

Доработать БД из предыдущего задания. Спроектировать схему БД для имитации журнала оценок. Типа:

ФИО	03.09 тема 1	04.09 тема 2	05.09 тема 3	06.09 тема 4	07.09 тема 5	…
Бянкин Владислав	5	н	4	3		…
Валеев Александр	3	5		н	4	…
Вяткина Дарья		5	4	3	н	…
Гайнуллин Максим	н	3	5	4		…
Дерюгин Федор		4	3	н	5	…
…	…	…	…	…	…	…

То есть есть набор занятий, у каждого занятие есть некоторая тема, дата когда это занятие проходило и группа для которой оно велось.

За каждое занятие Студент может получить оценку от 2 до 5 либо “н-ку” если он на этом занятии не присутствовал.

1. Все таблицы должны находится в третьей нормальной форме.
2. Связи между таблицами осуществлять по первичным ключам
3. Зафиксировать связи через Схему данных

Разработать форму для добавления оценок за пару. Форма должна позволять редактировать тему, дату и группу у занятия, а также фиксировать оценки за пару у студентов. Выглядеть она будет как-то так:

Для изучения SQL будем использовать СУБД sqlite. Это локальная база данных, которая весьма популярна и используется хотя бы теми же браузерами (chrome, firefox), всяким мессенджарами, и каждым вторым андроид приложением. В общем штука полезная.

Сначала необходимо скачать программку для работы с базой данных: http://sqlitebrowser.org/

ну либо если качать долго, взять у преподавателя.

Потом надо запустить программку:

Перетянуть в нее файлик marks.db, ну либо через Файл / Открыть базу данных…

Во вкладке Данные можно собственно посмотреть данные которые лежат в табличках

Но нам во вкладку SQL, там можно запросы писать:

Схема БД

Таблица marks

Поле value (значение оценки), расшифровка:

• от 0 до 6 – количество баллов, заработанных за занятие
• -2 (минус два) – пропущенное занятие
• прочие значения – это всякие ускорялки, обнулялки и т. п. При расчете суммарных балов не учитывать.

Таблица lessons

• Всякое занятие (таблица lessons) закреплено за группой (поле group_id) и дисциплиной (поле discipline_id).

• Поле lesson_type (тип занятия) допустимые значения:

  • 1 – практика
  • 2 – тест
  • 3 – лекция
  • 4 – лабораторная
  • 5 – экзамен

База нормализованная, поэтому каждый тип объектов хранится в отдельной табличке. Если какая-та табличка хочет указать свою связь на другую табличку, то используется так называемый внешний ключ. Например, в таблице студентов есть поле group_id, в котором хранится номер группы. Искать группу по этому номеру надо в табличке groups, поле id.

Оценка привязана к нескольким табличкам, например, там есть поле lesson_id, в котором хранится номер занятия. А само занятие лежит в табличке lessons, его номер в поле id.

Плюс в оценке есть поле student_id по которому можно узнать номер студента, которому поставили оценку.

Визуализация данных

Чтобы проще было ориентироваться что за данные хранятся в БД, можно посмотреть уже реализованный на их основе сайт:

tealeaf.su

Примеры запрсов

1) Вывести все столбики и всех студентов:

SELECT *
FROM students

2) Вывести конкретные столбцы

SELECT name, second_name
FROM students

3) Отфильтровать по первой букве имени

SELECT  name, second_name
FROM students
WHERE name like 'А%'

% – означает, любая последовательность символов, т.е) “А%” – означает все что начинается с А, по аналогии “%а” – все что оканчивается на а

4) Отфильтровать по первой букве имени И последней букве фамилии

SELECT  name, second_name
FROM students
WHERE name like 'А%' and second_name like '%а'

5) Отфильтровать по первой букве имени ИЛИ последней букве фамилии

SELECT  name, second_name
FROM students
WHERE name like 'А%' OR second_name like '%а'

6) Вывести всех Александров

SELECT  name, second_name
FROM students
WHERE name = 'Александр'

7) Упорядочить по фамилии:

SELECT  *
FROM students
WHERE name = 'Александр'
ORDER BY second_name

8) Упорядочить по имени по убыванию + по фамилии по возрастанию

SELECT  *
FROM students
ORDER BY name DESC, second_name

9) Сформировать новый столбец с целой фразой:

SELECT 'У ' || name || ' фамилия ' || second_name, *
FROM students
WHERE name like 'А%'
ORDER BY name DESC, second_name

10) Подклеить название группы

SELECT 'У ' || name || ' фамилия ' || second_name, g.title
FROM students s
 LEFT JOIN groups g ON g.id = s.group_id
ORDER BY name, second_name

11) Так тоже можно подклеить, и хотя в нашем случае получится одинаковый результат, это просто потому что так получилось

SELECT 'У ' || name || ' фамилия ' || second_name, g.title
FROM students s
 JOIN groups g ON g.id = s.group_id
ORDER BY name, second_name

12) По оценке вывести, название группы студентов чьи фамилии оканчиваются на a

SELECT m.id, m.value, g.title as [Название группы]
FROM marks m
 JOIN students s ON s.id = m.student_id
 JOIN groups g ON s.group_id = g.id
WHERE s.second_name like '%а'

13) Вывести студентов 2015 и 2016 года обучения

13.1)

SELECT s.*
FROM students s
 JOIN groups g ON s.group_id = g.id
WHERE g.year = 2015 OR g.year = 2016

13.2) или так

SELECT s.*
FROM students s
 JOIN groups g ON s.group_id = g.id
WHERE g.year in (2015, 2016)

13.3) или так

SELECT s.*
FROM students s
WHERE s.group_id IN (
  SELECT id -- вложенный запрос, а это комментарий
  FROM groups
  WHERE year IN (2015, 2016)
)

14) Подсчитать количество студентов

SELECT count(*)
FROM students

15) Подсчитать количество студентов в каждой группе

SELECT g.title, count(*)
FROM students s
 JOIN groups g ON g.id = s.group_id
GROUP BY s.group_id, g.title

16) Подсчитать среднее количество студентов в группе

SELECT avg(count)
FROM (
       SELECT
         g.title,
         count(*) AS count
       FROM students s
         JOIN groups g ON g.id = s.group_id
       GROUP BY s.group_id, g.title
     ) t

17) Вывести оценки за конкретную дату

SELECT value as [оценка], *
FROM marks m
  JOIN lessons l ON l.id = m.lesson_id
WHERE date(l.date) = '2017-11-10'

1. Вывести список студентов в форме: %Фамилия% %Имя% учится в %Название группы% (ориентироваться на запрос 11 из задания 6)
2. Вывести студентов 2017 года обучения в форме: Фамилия | Имя | Название группы
3. Вывести оценки студентов за 17-11-2017 в форме: Фамилия | Имя | Оценка (ориентироваться на запрос 17 из задания 6)

1. Вывести список студентов, которые не сдавали экзамены в форме таблицы: Фамилия | Имя | Название группы

2. Для данного студента (по фамилии и имени) вывести список всех его одногруппников в форме таблицы: Фамилия | Имя
   ориентироваться на запрос 13.3 из задания 6 Сначала по имени и фамилии во вложенном запросе узнать номер группы студента, а затем выбрать студентов у которых номер группы соответствует номеру группы этого студента

3. Вывести список групп, в которых не учатся Александры в форме таблицы: Год | Название группы
   тут имеет смысл сначала построить вложенный запрос со списком групп в котором учатся Александры, а потом взять студентов которые не учатся в этих группах. Опять же по аналогии с 13.3 из задания 6, только в фильтре вместо конструкции IN использовать NOT IN

1. Подсчитать количество прогулов в каждой группе. Вывести результат в форме: В %Название группы% зарегистрировано %Количество прогулов% прогулов
   Тут надо взять все оценки с прогулами (там где -2), потом по оценке определить используя связь со студентом в какой группе был прогул, ну а дальше ориентироваться на 15 запрос из задания 6.

Построить запросы и по результатам запроса построить гистограммы в Excel.

1. Подсчитать сколько раз каждое имя встречается среди студентов, и упорядочить список имен по частоте появления от большего к меньшему.
   Вывести в форме таблицы: Имя | Количество повторений
   Тут надо взять всех студентов, сгруппировать по полю имя студента, а дальше ориентироваться на 15 запрос из задания 6.

2. Найти самое популярное имя среди студентов в БД.
   Вывести в форме таблицы: Имя | Количество повторений
   Тут отталкиваться от предыдущего задания, для того чтобы ограничить количество записей в ответе добавлять в конец LIMIT 1

3. Подсчитать суммарное количество баллов, набранное каждым студентом текущего года обучения по дисциплине мат. логика. Учитывать только оценки от -2 до 5.
   Вывести результат в форме таблицы: Фамилия | Имя | Сумма баллов

4. Подсчитать суммарное количество баллов, набранное каждой группой по дисциплине мат. логика. Учитывать только оценки от -2 до 5.
   Вывести результат в форме таблицы: Название группы | Сумма баллов

5. Построить гистограммы по запросам.

Построить запросы и по результатам запроса построить гистограммы в Excel.

1. Рассчитать общее количество оценок у каждой группы. Вывести в виде:
   Вывести в виде: Название группы | количество оценок

2. Рассчитать сколько каждого вида оценок для каждой группы
   Вывести в виде: Название группы | Значение оценки | количество оценок с данным значением

3. Рассчитать сколько каждого вида оценок для каждой группы Вывести в виде: Название группы | Значение оценки | доля оценок с таким значением от общего количества оценок в группе

4. Рассчитать среднее арифметическое для каждой группы Вывести в виде: Название группы | Среднее арифметическое оценок

5. Построить гистограммы по запросам.