---

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

   Основные таблицы:
   student11
   predmeti11
   ocenki11
   ========================================================= */


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

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


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

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

    CONSTRAINT FK_posesh_mart11_student11
        FOREIGN KEY (Kod_studenta)
        REFERENCES student11(Kod_studenta)
);


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

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


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

SELECT *
FROM posesh_mart11;



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

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

    CONSTRAINT FK_posesh_aprel11_student11
        FOREIGN KEY (Kod_studenta)
        REFERENCES student11(Kod_studenta)
);


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

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


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

SELECT *
FROM posesh_aprel11;



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

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

    CONSTRAINT FK_posesh_may11_student11
        FOREIGN KEY (Kod_studenta)
        REFERENCES student11(Kod_studenta)
);


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

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


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

SELECT *
FROM posesh_may11;



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

SELECT * FROM posesh_mart11;
SELECT * FROM posesh_aprel11;
SELECT * FROM posesh_may11;



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

   Нужно показать ДО и ПОСЛЕ.
   ========================================================= */


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

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


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

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


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

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


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

SELECT s.*
FROM student11 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_aprel11
WHERE Neuvazh_prop >= 2
ORDER BY Kod_studenta;


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

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


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

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


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

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



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

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


/*

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

*/


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


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

SELECT TOP 0 *
INTO novie_predmeti11
FROM predmeti11;


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

SELECT *
FROM novie_predmeti11;


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

   ЭТУ ОДНУ ЗАПИСЬ МОЖНО ВНЕСТИ ВРУЧНУЮ
   ЧЕРЕЗ "EDIT TOP 200 ROWS".
   ========================================================= */


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

SELECT *
FROM novie_predmeti11;


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

   ЭТОТ INSERT ПОКА НЕ ЗАПУСКАЙ,
   ПОКА НЕ УВИДИМ СТОЛБЦЫ predmeti11.
   ========================================================= */


/*

INSERT INTO predmeti11
SELECT *
FROM novie_predmeti11;

*/


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

SELECT *
FROM predmeti11;