---

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


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

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

    CONSTRAINT PK_posesh12
        PRIMARY KEY (Kod_studenta),

    CONSTRAINT FK_posesh12_student12
        FOREIGN KEY (Kod_studenta)
        REFERENCES student12(Kod_studenta)
);


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

INSERT INTO posesh12
(
    Kod_studenta,
    Uvazh_prop,
    Neuvazh_prop
)
SELECT
    Kod_studenta,
    0,
    0
FROM student12;


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

SELECT *
FROM posesh12;


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

SELECT *
INTO posesh_mart12
FROM posesh12;

ALTER TABLE posesh_mart12
ADD CONSTRAINT PK_posesh_mart12
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_mart12
ADD CONSTRAINT FK_posesh_mart12_student12
FOREIGN KEY (Kod_studenta)
REFERENCES student12(Kod_studenta);


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

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


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

SELECT *
FROM posesh_mart12;


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

SELECT *
INTO posesh_aprel12
FROM posesh12;

ALTER TABLE posesh_aprel12
ADD CONSTRAINT PK_posesh_aprel12
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_aprel12
ADD CONSTRAINT FK_posesh_aprel12_student12
FOREIGN KEY (Kod_studenta)
REFERENCES student12(Kod_studenta);


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

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


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

SELECT *
FROM posesh_aprel12;


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

SELECT *
INTO posesh_may12
FROM posesh12;

ALTER TABLE posesh_may12
ADD CONSTRAINT PK_posesh_may12
PRIMARY KEY (Kod_studenta);

ALTER TABLE posesh_may12
ADD CONSTRAINT FK_posesh_may12_student12
FOREIGN KEY (Kod_studenta)
REFERENCES student12(Kod_studenta);


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

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


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

SELECT *
FROM posesh_may12;


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

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

SELECT *
FROM student12
WHERE Kurs = 1;


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

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


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

SELECT *
FROM student12;


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

UPDATE m
SET m.Neuvazh_prop = m.Neuvazh_prop - 2
FROM posesh_mart12 AS m
INNER JOIN student12 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_mart12 AS m
INNER JOIN student12 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_aprel12 AS a
INNER JOIN student12 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_aprel12 AS a
INNER JOIN student12 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_may12 AS m
INNER JOIN student12 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_may12 AS m
INNER JOIN student12 AS s
    ON m.Kod_studenta = s.Kod_studenta
WHERE s.Familiya = N'Иванов';


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

/*

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

*/


/* =========================================================
   10. ТАБЛИЦА "НОВЫЕ ПРЕДМЕТЫ"
   
   ЕЁ ПО ЗАДАНИЮ НУЖНО СОЗДАТЬ ВРУЧНУЮ.
   
   Поля:
   Nazvanie       nvarchar(100)
   Prepodavatel   nvarchar(100)

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

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


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

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


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

SELECT *
FROM predmet12;