Skip to content

Latest commit

 

History

History
329 lines (271 loc) · 19.8 KB

Lesson_1.md

File metadata and controls

329 lines (271 loc) · 19.8 KB
Raw

Описание датасета

Переменная Описание
school Тип школы, в которой учится ученик ('GP' - Gabriel Pereira или 'MS' - Mousinho da Silveira).
sex Пол ученика ('F' - женский или 'M' - мужской).
age Возраст ученика.
address Тип домашнего адреса ученика ('U' - городской или 'R' - сельский).
famsize Размер семьи ('LE3' - менее или равно 3 или 'GT3' - больше 3).
Pstatus Статус совместного проживания родителей ('T' - живут вместе или 'A' - раздельно).
Medu Образование матери (0 - нет, 1 - начальное образование (4-й класс), 2 - с 5-го по 9-й класс, 3 - среднее образование или 4 - высшее образование).
Fedu Образование отца (то же, что и Medu).
Mjob Работа матери.
Fjob Работа отца.
reason Причина выбора школы.
guardian Опекун ученика.
traveltime Время в пути от дома до школы (1 - <15 мин., 2 - 15 до 30 мин., 3 - 30 мин. до 1 часа или 4 - >1 часа).
studytime Еженедельное время ученика на учебу (1 - <2 часов, 2 - 2 до 5 часов, 3 - 5 до 10 часов или 4 - >10 часов).
failures Количество провалов в прошлых классах (если есть).
schoolsup Дополнительная образовательная поддержка (yes или no).
famsup Семейная образовательная поддержка (yes или no).
paid Дополнительные платные занятия (yes или no).
activities Внеклассные дополнительные активности (yes или no).
nursery Посещал ли детский сад (yes или no).
higher Хочет ли получить высшее образование (yes или no).
internet Есть ли доступ в интернет дома (yes или no).
romantic Состоит ли в романтических отношениях (yes или no).
famrel Качество семейных отношений (от 1 - очень плохо до 5 - очень хорошо).
freetime Свободное время после школы (от 1 - очень мало до 5 - очень много).
goout Время, проведенное с друзьями (от 1 - очень мало до 5 - очень много).
Dalc Потребление алкоголя в будние дни (от 1 - очень низкое до 5 - очень высокое).
Walc Потребление алкоголя в выходные (от 1 - очень низкое до 5 - очень высокое).
health Текущее состояние здоровья (от 1 - очень плохо до 5 - очень хорошо).
absences Количество пропусков школы.
G1 Оценка за первый семестр (от 0 до 20).
G2 Оценка за второй семестр (от 0 до 20).
G3 Итоговая оценка (от 0 до 20).

Описание init.sql файла

Создание таблицы

Оператор CREATE TABLE используется для создания новой таблицы в базе данных. Этот оператор позволяет определить структуру таблицы, включая названия столбцов, их типы данных и любые ограничения (например, ограничения на целостность данных).

Вот общий синтаксис оператора CREATE TABLE:

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    columnN datatype [constraint]
);

Где:

  • table_name - название новой таблицы, которую вы хотите создать.
  • column1, column2, ..., columnN - названия столбцов в новой таблице.
  • datatype - тип данных, который будет храниться в каждом столбце.
  • constraint - необязательное ограничение, которое можно применить к столбцу, такое как PRIMARY KEY, FOREIGN KEY, NOT NULL и другие.

Пример кода:

CREATE TABLE students (
    school VARCHAR(2),
    sex VARCHAR(1),
    age INTEGER,
    address VARCHAR(1),
    famsize VARCHAR(3),
    Pstatus VARCHAR(1),
    Medu INTEGER,
    Fedu INTEGER,
    Mjob VARCHAR(20),
    Fjob VARCHAR(20),
    reason VARCHAR(10),
    guardian VARCHAR(6),
    traveltime INTEGER,
    studytime INTEGER,
    failures INTEGER,
    schoolsup VARCHAR(3),
    famsup VARCHAR(3),
    paid VARCHAR(3),
    activities VARCHAR(3),
    nursery VARCHAR(3),
    higher VARCHAR(3),
    internet VARCHAR(3),
    romantic VARCHAR(3),
    famrel INTEGER,
    freetime INTEGER,
    goout INTEGER,
    Dalc INTEGER,
    Walc INTEGER,
    health INTEGER,
    absences INTEGER,
    G1 INTEGER,
    G2 INTEGER,
    G3 INTEGER
);

Описание

Параметр Тип в SQL Значение
school VARCHAR(2) строка длиной 2 символа
sex VARCHAR(1) строка длиной 1 символ
age INTEGER целое число
address VARCHAR(1) строка длиной 1 символ
famsize VARCHAR(3) строка длиной 3 символа
Pstatus VARCHAR(1) строка длиной 1 символ
Medu INTEGER целое число
Fedu INTEGER целое число
Mjob VARCHAR(20) строка длиной до 20 символов
Fjob VARCHAR(20) строка длиной до 20 символов
reason VARCHAR(10) строка длиной до 10 символов
guardian VARCHAR(6) строка длиной до 6 символов
traveltime INTEGER целое число
studytime INTEGER целое число
failures INTEGER целое число
schoolsup VARCHAR(3) строка длиной 3 символа
famsup VARCHAR(3) строка длиной 3 символа
paid VARCHAR(3) строка длиной 3 символа
activities VARCHAR(3) строка длиной 3 символа
nursery VARCHAR(3) строка длиной 3 символа
higher VARCHAR(3) строка длиной 3 символа
internet VARCHAR(3) строка длиной 3 символа
romantic VARCHAR(3) строка длиной 3 символа
famrel INTEGER целое число
freetime INTEGER целое число
goout INTEGER целое число
Dalc INTEGER целое число
Walc INTEGER целое число
health INTEGER целое число
absences INTEGER целое число
G1 INTEGER целое число
G2 INTEGER целое число
G3 INTEGER целое число

Заполнение таблицы

COPY students FROM '/docker-entrypoint-initdb.d/student-dataset.csv' DELIMITER ',' CSV HEADER;

Эта функция COPY в PostgreSQL используется для копирования данных из файла в таблицу базы данных. В данном случае, она копирует данные из файла student-dataset.csv в таблицу students. Вот разбор параметров этой функции:

  • COPY students: Указывает, что данные будут копироваться в таблицу с именем students.
  • FROM '/docker-entrypoint-initdb.d/student-dataset.csv': Указывает путь к файлу, из которого нужно скопировать данные. В данном случае, файл находится по пути /docker-entrypoint-initdb.d/student-dataset.csv.
  • DELIMITER ',': Определяет разделитель полей в файле. В данном случае, разделителем является запятая (,).
  • CSV: Указывает, что файл является файлом CSV (Comma-Separated Values), то есть данные разделены запятыми.
  • HEADER: Указывает, что первая строка файла содержит заголовки столбцов, которые нужно пропустить при копировании в таблицу.

Таким образом, данная функция COPY копирует данные из файла student-dataset.csv в таблицу students, учитывая, что данные в файле разделены запятыми, и первая строка файла содержит заголовки столбцов.

Базовые операторы SQL

Агрегирующие функции

1. COUNT()

Возвращает количество строк в группе.

SELECT COUNT(column_name) FROM table_name;

2. SUM()

Возвращает сумму значений в указанном столбце.

SELECT SUM(column_name) FROM table_name;

3. AVG()

Возвращает среднее значение в указанном столбце.

SELECT AVG(column_name) FROM table_name;

4. MIN()

Возвращает наименьшее значение в указанном столбце.

SELECT MIN(column_name) FROM table_name;

5. MAX()

Возвращает наибольшее значение в указанном столбце.

SELECT MAX(column_name) FROM table_name;

AS

AS - это ключевое слово в SQL, которое используется для создания псевдонимов для столбцов или таблиц в результирующем наборе данных. Это позволяет делать результаты запроса более читаемыми и понятными.

Когда вы используете AS с именем столбца, вы создаете временное имя для этого столбца в результирующем наборе. Например:

SELECT column_name AS alias_name
FROM table_name;

Здесь column_name - это имя столбца в таблице, а alias_name - это псевдоним, который вы хотите присвоить этому столбцу в результирующем наборе.

То же самое применяется и к таблицам:

SELECT column_name
FROM table_name AS alias_name;

Здесь table_name - это имя таблицы, а alias_name - это псевдоним, который вы хотите присвоить этой таблице в запросе.

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

WHERE

1. Выбрать всех учеников младше 18 лет

SELECT *
FROM students
WHERE age < 18;

Этот запрос выбирает всех учеников из таблицы students, возраст которых меньше 18 лет.

2. Показать среднее количество пропусков учеников, которые имеют образование матери (Medu) и отца (Fedu) выше среднего

SELECT AVG(absences) AS avg_absences_above_avg_parent_education
FROM students
WHERE Medu > (SELECT AVG(Medu) FROM students) AND Fedu > (SELECT AVG(Fedu) FROM students);

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

3. Показать средние оценки (G1, G2, G3) учеников, которые имеют доступ в интернет дома

SELECT AVG(G1) AS avg_G1, AVG(G2) AS avg_G2, AVG(G3) AS avg_G3
FROM students
WHERE internet = 'yes';

Этот запрос вычисляет средние оценки (G1, G2, G3) учеников из таблицы students, которые имеют доступ в интернет дома.

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

SELECT AVG(Dalc) AS avg_weekday_alcohol_consumption
FROM students
WHERE Pstatus = 'T';

Этот запрос вычисляет средний уровень потребления алкоголя в будние дни (Dalc) среди учеников, у которых родители живут вместе (Pstatus = 'T').

GROUP BY

1. Посчитать количество учеников в каждой школе

SELECT school, COUNT(*) AS total_students
FROM students
GROUP BY school;

Этот запрос считает количество учеников в каждой школе (school) и выводит результаты сгруппированными по школам.

2. Вычислить средний возраст учеников для каждого типа домашнего адреса

SELECT address, AVG(age) AS avg_age
FROM students
GROUP BY address;

Этот запрос вычисляет средний возраст учеников для каждого типа домашнего адреса (address) и выводит результаты сгруппированными по адресам.

3. Определить среднюю оценку за первый семестр (G1) для учеников с разным уровнем доступа в интернет

SELECT internet, AVG(G1) AS avg_G1
FROM students
GROUP BY internet;

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

4. Посчитать средний уровень потребления алкоголя в будние и выходные дни для учеников с разным количеством провалов в прошлых классах

SELECT failures, AVG(Dalc) AS avg_weekday_alcohol_consumption, AVG(Walc) AS avg_weekend_alcohol_consumption
FROM students
GROUP BY failures;

Этот запрос вычисляет средний уровень потребления алкоголя в будние дни (Dalc) и выходные дни (Walc) для учеников с разным количеством провалов в прошлых классах (failures). Результаты группируются по количеству провалов.

HAVING

1. Найти средний возраст (age) учеников, чья средняя оценка за первый семестр (G1) больше 10

SELECT sex, AVG(age) AS avg_age
FROM students
GROUP BY sex
HAVING AVG(G1) > 10;

Этот запрос сначала группирует учеников по полу (sex), затем вычисляет средний возраст (age) для каждой группы. Затем HAVING фильтрует группы, у которых средняя оценка за первый семестр (G1) больше 10.

2. Определить среднее количество пропусков (absences) для учеников, чья итоговая оценка (G3) превышает 15

SELECT famsize, AVG(absences) AS avg_absences
FROM students
GROUP BY famsize
HAVING MAX(G3) > 15;

Этот запрос группирует учеников по размеру семьи (famsize), затем вычисляет среднее количество пропусков (absences) для каждой группы. HAVING фильтрует группы, у которых максимальная итоговая оценка (G3) превышает 15.

3. Найти средний уровень потребления алкоголя в будние дни (Dalc) для учеников, у которых есть доступ в интернет (internet)

SELECT internet, AVG(Dalc) AS avg_weekday_alcohol_consumption
FROM students
GROUP BY internet
HAVING internet = 'yes';

Этот запрос группирует учеников по наличию доступа в интернет (internet), затем вычисляет средний уровень потребления алкоголя в будние дни (Dalc) для каждой группы. HAVING фильтрует только те группы, в которых есть доступ в интернет (internet = 'yes').

4. Определить средний уровень свободного времени после школы (freetime) для учеников, у которых качество семейных отношений (famrel) выше среднего

SELECT romantic, AVG(freetime) AS avg_freetime
FROM students
GROUP BY romantic
HAVING AVG(famrel) > (SELECT AVG(famrel) FROM students);

Этот запрос группирует учеников по состоянию в романтических отношениях (romantic), затем вычисляет средний уровень свободного времени после школы (freetime) для каждой группы. HAVING фильтрует группы, у которых среднее качество семейных отношений (famrel) выше среднего по всем ученикам.