---

/* =========================================================
   ПРАКТИЧЕСКАЯ РАБОТА №20
   База: user312_db

   Основные таблицы:
   student13
   predmeti13
   ocenki13
   ========================================================= */


/* =========================================================
   ЗАДАНИЕ 2
   ПОСЕЩАЕМОСТЬ ЗА МАРТ, АПРЕЛЬ И МАЙ

   Kod_studenta:
   - НЕ PRIMARY KEY
   - FOREIGN KEY
   - UNIQUE для связи 1 к 1

   В каждой таблице по 15 записей.
   ========================================================= */


/* =========================================================
   МАРТ
   ========================================================= */

CREATE TABLE posesh_mart13
(
    Kod_studenta INT NOT NULL UNIQUE,
    Uvazh_prop INT NOT NULL,
    Neuvazh_prop INT NOT NULL,

    CONSTRAINT FK_posesh_mart13_student13
        FOREIGN KEY (Kod_studenta)
        REFERENCES student13(Kod_studenta)
);


/* Заполняем 15 записей */

INSERT INTO posesh_mart13
(
    Kod_studenta,
    Uvazh_prop,
    Neuvazh_prop
)
SELECT TOP 15
    Kod_studenta,
    Kod_studenta % 5,
    (Kod_studenta % 4) * 2
FROM student13
ORDER BY Kod_studenta;


/* Показываем март */

SELECT *
FROM posesh_mart13;



/* =========================================================
   АПРЕЛЬ
   ========================================================= */

CREATE TABLE posesh_aprel13
(
    Kod_studenta INT NOT NULL UNIQUE,
    Uvazh_prop INT NOT NULL,
    Neuvazh_prop INT NOT NULL,

    CONSTRAINT FK_posesh_aprel13_student13
        FOREIGN KEY (Kod_studenta)
        REFERENCES student13(Kod_studenta)
);


/* Заполняем 15 записей другими значениями */

INSERT INTO posesh_aprel13
(
    Kod_studenta,
    Uvazh_prop,
    Neuvazh_prop
)
SELECT TOP 15
    Kod_studenta,
    (Kod_studenta * 2) % 7,
    (Kod_studenta * 3) % 10
FROM student13
ORDER BY Kod_studenta;


/* Показываем апрель */

SELECT *
FROM posesh_aprel13;



/* =========================================================
   МАЙ
   ========================================================= */

CREATE TABLE posesh_may13
(
    Kod_studenta INT NOT NULL UNIQUE,
    Uvazh_prop INT NOT NULL,
    Neuvazh_prop INT NOT NULL,

    CONSTRAINT FK_posesh_may13_student13
        FOREIGN KEY (Kod_studenta)
        REFERENCES student13(Kod_studenta)
);


/* Заполняем 15 записей другими значениями */

INSERT INTO posesh_may13
(
    Kod_studenta,
    Uvazh_prop,
    Neuvazh_prop
)
SELECT TOP 15
    Kod_studenta,
    (Kod_studenta * 3) % 8,
    (Kod_studenta * 4) % 11
FROM student13
ORDER BY Kod_studenta;


/* Показываем май */

SELECT *
FROM posesh_may13;



/* =========================================================
   ПОКАЗАТЬ ВСЕ 3 МЕСЯЦА
   ========================================================= */

SELECT * FROM posesh_mart13;
SELECT * FROM posesh_aprel13;
SELECT * FROM posesh_may13;



/* =========================================================
   ЗАДАНИЕ 2.1
   ПЕРЕВЕСТИ 3 СТУДЕНТОВ С 1 КУРСА НА 2 КУРС

   Показываем ДО и ПОСЛЕ.
   ========================================================= */


/* Запоминаем 3 студентов первого курса */

SELECT TOP 3 Kod_studenta
INTO #Perevod
FROM student13
WHERE Kurs = 1
ORDER BY Kod_studenta;


/* =========================================================
   ДО
   ========================================================= */

SELECT s.*
FROM student13 AS s
INNER JOIN #Perevod AS p
    ON s.Kod_studenta = p.Kod_studenta;


/* =========================================================
   ОБНОВЛЕНИЕ
   ========================================================= */

UPDATE s
SET Kurs = Kurs + 1
FROM student13 AS s
INNER JOIN #Perevod AS p
    ON s.Kod_studenta = p.Kod_studenta;


/* =========================================================
   ПОСЛЕ
   ========================================================= */

SELECT s.*
FROM student13 AS s
INNER JOIN #Perevod AS p
    ON s.Kod_studenta = p.Kod_studenta;


/* Удаляем временную таблицу */

DROP TABLE #Perevod;



/* =========================================================
   ЗАДАНИЕ 2.2
   СТУДЕНТ ОТРАБОТАЛ 2 ЧАСА
   НЕУВАЖИТЕЛЬНЫХ ПРОПУСКОВ

   Берём любой один месяц — АПРЕЛЬ.
   Выбираем студента, у которого минимум 2 часа.

   Показываем ДО и ПОСЛЕ.
   ========================================================= */


/* Выбираем одного студента */

DECLARE @Kod_studenta INT;

SELECT TOP 1
    @Kod_studenta = Kod_studenta
FROM posesh_aprel13
WHERE Neuvazh_prop >= 2
ORDER BY Kod_studenta;


/* =========================================================
   ДО ОТРАБОТКИ
   ========================================================= */

SELECT
    s.*,
    p.Uvazh_prop,
    p.Neuvazh_prop
FROM student13 AS s
INNER JOIN posesh_aprel13 AS p
    ON s.Kod_studenta = p.Kod_studenta
WHERE s.Kod_studenta = @Kod_studenta;


/* =========================================================
   ОТРАБОТАЛ 2 ЧАСА
   ========================================================= */

UPDATE posesh_aprel13
SET Neuvazh_prop = Neuvazh_prop - 2
WHERE Kod_studenta = @Kod_studenta;


/* =========================================================
   ПОСЛЕ ОТРАБОТКИ
   ========================================================= */

SELECT
    s.*,
    p.Uvazh_prop,
    p.Neuvazh_prop
FROM student13 AS s
INNER JOIN posesh_aprel13 AS p
    ON s.Kod_studenta = p.Kod_studenta
WHERE s.Kod_studenta = @Kod_studenta;



/* =========================================================
   ЗАДАНИЕ 3
   АКАДЕМИЧЕСКИЙ ОТПУСК

   ТОЛЬКО НАПИСАТЬ И СФОТКАТЬ.
   НЕ ВЫПОЛНЯТЬ!
   ========================================================= */


/*

DELETE FROM student13
WHERE Familiya = N'Иванов';

*/


/*
   Вместо Иванов можно написать фамилию
   реального студента из student13.

   ЭТОТ DELETE НЕ ЗАПУСКАТЬ!
*/


/* =========================================================
   ЗАДАНИЕ 4
   СОЗДАЁМ ТАБЛИЦУ НОВЫХ ПРЕДМЕТОВ
   НА ОСНОВЕ predmeti13
   ========================================================= */


/* Создаём такую же структуру */

SELECT TOP 0 *
INTO novie_predmeti13
FROM predmeti13;


/* Проверяем новую таблицу */

SELECT *
FROM novie_predmeti13;


/* =========================================================
   ТЕПЕРЬ В novie_predmeti13 НУЖНО ДОБАВИТЬ
   ОДНУ ДИСЦИПЛИНУ И ОДНОГО ПРЕПОДАВАТЕЛЯ.

   ЭТУ ЗАПИСЬ ВНЕСТИ ВРУЧНУЮ:

   Tables
   -> novie_predmeti13
   -> ПКМ
   -> Edit Top 200 Rows

   Например:

   Программирование | Иванов И.И.

   Если в predmeti13 есть Kod_predmeta,
   его тоже нужно заполнить уникальным кодом.
   ========================================================= */


/* =========================================================
   ПОСЛЕ ЗАПОЛНЕНИЯ novie_predmeti13
   ПОКАЗЫВАЕМ НОВЫЙ ПРЕДМЕТ
   ========================================================= */

SELECT *
FROM novie_predmeti13;


/* =========================================================
   ДОБАВЛЕНИЕ НОВОГО ПРЕДМЕТА В predmeti13

   ЭТОТ INSERT ПОКА НЕ ЗАПУСКАЙ,
   ЕСЛИ ЕЩЁ НЕ ЗАПОЛНИЛ novie_predmeti13.
   ========================================================= */


/*

INSERT INTO predmeti13
SELECT *
FROM novie_predmeti13;

*/


/* Показываем существующую таблицу предметов */

SELECT *
FROM predmeti13;