---

/* =========================================================
   ПРАКТИЧЕСКАЯ РАБОТА №20
   База: user312_db
   Таблицы предыдущих работ: student13, predmet13
   ========================================================= */


/* =========================================================
   1. СОЗДАНИЕ ТАБЛИЦЫ ПОСЕЩАЕМОСТЬ
   ========================================================= */

CREATE TABLE posesh13
(
    Kod_studenta INT NOT NULL,
    Uvazh_prop INT NOT NULL DEFAULT 0,
    Neuvazh_prop INT NOT NULL DEFAULT 0,

    CONSTRAINT PK_posesh13
        PRIMARY KEY (Kod_studenta),

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


/* Заполняем таблицу студентами */

INSERT INTO posesh13
(
    Kod_studenta,
    Uvazh_prop,
    Neuvazh_prop
)
SELECT
    Kod_studenta,
    0,
    0
FROM student13;


/* Проверка */

SELECT *
FROM posesh13;


/* =========================================================
   2. ПОСЕЩАЕМОСТЬ В МАРТЕ
   ========================================================= */

SELECT *
INTO posesh_mart13
FROM posesh13;

ALTER TABLE posesh_mart13
ADD CONSTRAINT PK_posesh_mart13
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_mart13
ADD CONSTRAINT FK_posesh_mart13_student13
FOREIGN KEY (Kod_studenta)
REFERENCES student13(Kod_studenta);


/* Добавляем пропуски */

UPDATE posesh_mart13
SET
    Uvazh_prop = Kod_studenta % 5,
    Neuvazh_prop = (Kod_studenta % 4) * 2 + 2;


/* Проверка */

SELECT *
FROM posesh_mart13;


/* =========================================================
   3. ПОСЕЩАЕМОСТЬ В АПРЕЛЕ
   ========================================================= */

SELECT *
INTO posesh_aprel13
FROM posesh13;

ALTER TABLE posesh_aprel13
ADD CONSTRAINT PK_posesh_aprel13
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_aprel13
ADD CONSTRAINT FK_posesh_aprel13_student13
FOREIGN KEY (Kod_studenta)
REFERENCES student13(Kod_studenta);


/* Добавляем пропуски */

UPDATE posesh_aprel13
SET
    Uvazh_prop = Kod_studenta % 4,
    Neuvazh_prop = (Kod_studenta % 5) * 2 + 2;


/* Проверка */

SELECT *
FROM posesh_aprel13;


/* =========================================================
   4. ПОСЕЩАЕМОСТЬ В МАЕ
   ========================================================= */

SELECT *
INTO posesh_may13
FROM posesh13;

ALTER TABLE posesh_may13
ADD CONSTRAINT PK_posesh_may13
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_may13
ADD CONSTRAINT FK_posesh_may13_student13
FOREIGN KEY (Kod_studenta)
REFERENCES student13(Kod_studenta);


/* Добавляем пропуски */

UPDATE posesh_may13
SET
    Uvazh_prop = Kod_studenta % 3,
    Neuvazh_prop = (Kod_studenta % 3) * 2 + 2;


/* Проверка */

SELECT *
FROM posesh_may13;


/* =========================================================
   5. ПЕРЕВЕСТИ НА СЛЕДУЮЩИЙ КУРС
   ========================================================= */

/* Сначала показываем студентов первого курса */

SELECT *
FROM student13
WHERE Kurs = 1;


/* Переводим их на следующий курс */

UPDATE student13
SET Kurs = Kurs + 1
WHERE Kurs = 1;


/* Проверка */

SELECT *
FROM student13;


/* =========================================================
   6. ОТРАБОТКИ МАРТ
   ЗАМЕНИ ИВАНОВ НА ФАМИЛИЮ СВОЕГО СТУДЕНТА
   ========================================================= */

UPDATE m
SET m.Neuvazh_prop = m.Neuvazh_prop - 2
FROM posesh_mart13 AS m
INNER JOIN student13 AS s
    ON m.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов'
  AND m.Neuvazh_prop >= 2;


/* Проверка */

SELECT
    s.Familiya,
    m.Uvazh_prop,
    m.Neuvazh_prop
FROM posesh_mart13 AS m
INNER JOIN student13 AS s
    ON m.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов';


/* =========================================================
   7. ОТРАБОТКИ АПРЕЛЬ
   ========================================================= */

UPDATE a
SET a.Neuvazh_prop = a.Neuvazh_prop - 2
FROM posesh_aprel13 AS a
INNER JOIN student13 AS s
    ON a.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов'
  AND a.Neuvazh_prop >= 2;


/* Проверка */

SELECT
    s.Familiya,
    a.Uvazh_prop,
    a.Neuvazh_prop
FROM posesh_aprel13 AS a
INNER JOIN student13 AS s
    ON a.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов';


/* =========================================================
   8. ОТРАБОТКИ МАЙ
   ========================================================= */

UPDATE m
SET m.Neuvazh_prop = m.Neuvazh_prop - 2
FROM posesh_may13 AS m
INNER JOIN student13 AS s
    ON m.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов'
  AND m.Neuvazh_prop >= 2;


/* Проверка */

SELECT
    s.Familiya,
    m.Uvazh_prop,
    m.Neuvazh_prop
FROM posesh_may13 AS m
INNER JOIN student13 AS s
    ON m.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов';


/* =========================================================
   9. АКАДЕМИЧЕСКИЙ ОТПУСК
   ВАЖНО: ЭТОТ ЗАПРОС ПО ЗАДАНИЮ НЕ ВЫПОЛНЯТЬ
   ========================================================= */

/*

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

*/


/* =========================================================
   10. ТАБЛИЦА "НОВЫЕ ПРЕДМЕТЫ13"

   ЕЁ ПО ЗАДАНИЮ НУЖНО СОЗДАТЬ ВРУЧНУЮ.

   Поля:
   Nazvanie       nvarchar(100)
   Prepodavatel   nvarchar(100)

   Добавь вручную одну запись, например:

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


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

   ЭТОТ БЛОК ВЫПОЛНЯЙ ТОЛЬКО ПОСЛЕ ТОГО,
   КАК ВРУЧНУЮ СОЗДАШЬ ТАБЛИЦУ "Новые предметы13"
   ========================================================= */

INSERT INTO predmet13
(
    Nazvanie,
    Prepodavatel
)
SELECT
    Nazvanie,
    Prepodavatel
FROM [Новые предметы13];


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

SELECT *
FROM predmet13;