---

/* =========================================================
   ПРАКТИЧЕСКАЯ РАБОТА №20
   База: user312_db
   Таблицы: student13, predmet13, ocenki13
   ========================================================= */


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

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

    PRIMARY KEY (Kod_studenta),

    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 PRIMARY KEY (Kod_studenta);


/* Связываем со студентами */

ALTER TABLE posesh_mart13
ADD 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 PRIMARY KEY (Kod_studenta);


ALTER TABLE posesh_aprel13
ADD 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 PRIMARY KEY (Kod_studenta);


ALTER TABLE posesh_may13
ADD 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. ОТРАБОТКИ МАРТ
   ========================================================= */

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

UPDATE posesh_mart13
SET Neuvazh_prop = Neuvazh_prop - 2
WHERE Kod_studenta IN
(
    SELECT Kod_studenta
    FROM student13
    WHERE Familiya = N'Иванов'
)
AND Neuvazh_prop >= 2;


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

SELECT *
FROM posesh_mart13;


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

UPDATE posesh_aprel13
SET Neuvazh_prop = Neuvazh_prop - 2
WHERE Kod_studenta IN
(
    SELECT Kod_studenta
    FROM student13
    WHERE Familiya = N'Иванов'
)
AND Neuvazh_prop >= 2;


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

SELECT *
FROM posesh_aprel13;


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

UPDATE posesh_may13
SET Neuvazh_prop = Neuvazh_prop - 2
WHERE Kod_studenta IN
(
    SELECT Kod_studenta
    FROM student13
    WHERE Familiya = N'Иванов'
)
AND Neuvazh_prop >= 2;


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

SELECT *
FROM posesh_may13;


/* =========================================================
   9. АКАДЕМИЧЕСКИЙ ОТПУСК
   НЕ ЗАПУСКАТЬ!
   ========================================================= */

/*

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

*/


/*
   Этот запрос только сохранить:

   Академический отпуск.sql

   EXECUTE НЕ НАЖИМАТЬ.
*/


/* =========================================================
   10. НОВЫЕ ПРЕДМЕТЫ13
   ========================================================= */

/*
   ЭТУ ТАБЛИЦУ СОЗДАЁМ ВРУЧНУЮ!

   Tables
   -> ПКМ
   -> New
   -> Table

   Поля:

   Nazvanie       nvarchar(100)
   Prepodavatel   nvarchar(100)

   Сохраняем как:

   Новые предметы13

   Потом:
   ПКМ -> Edit Top 200 Rows

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

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


/* =========================================================
   11. ДОБАВЛЯЕМ НОВЫЙ ПРЕДМЕТ В predmet13
   ========================================================= */

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


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

SELECT *
FROM predmet13;