---

CREATE TABLE student
(
    Kod_studenta INT IDENTITY(1,1) PRIMARY KEY,
    Familia NVARCHAR(50) NOT NULL,
    Imya NVARCHAR(50) NOT NULL,
    Data DATE,
    Pol NCHAR(1),
    Telephon VARCHAR(20),
    Ulica NVARCHAR(50),
    Dom INT,
    Kvartira INT,
    Gruppa_Zdorovia NVARCHAR(30),
    Uvlecenie NVARCHAR(50)
);

INSERT INTO student
    (Familia, Imya, Data, Pol, Telephon, Ulica,
     Dom, Kvartira, Gruppa_Zdorovia, Uvlecenie)
VALUES
(N'Суханов', N'Сергей', '2004-02-16', N'м', '89000000001', N'Чайковского', 23, 74, N'основная', N'футбол'),
(N'Пирогов', N'Юрий', '2005-12-05', N'м', '89000000002', N'Куйбышева', 6, 31, N'основная', N'музыка'),
(N'Лебедева', N'Светлана', '2005-06-16', N'ж', '89000000003', N'Пушкина', 37, 65, N'специальная', N'рисование'),
(N'Горин', N'Сергей', '2006-05-23', N'м', '89000000004', N'Лермонтова', 12, 10, N'основная', N'спорт'),
(N'Елкина', N'Наталья', '2006-12-20', N'ж', '89000000005', N'Чайковского', 37, 48, N'специальная', N'чтение'),
(N'Суханова', N'Наталья', '2006-12-20', N'ж', '89000000006', N'Ленина', 12, 22, N'подготовительная', N'музыка'),
(N'Петрова', N'Светлана', '2005-04-18', N'ж', '89000000007', N'Пушкина', 37, 3, N'основная', N'танцы'),
(N'Горина', N'Ольга', '2006-12-20', N'ж', '89000000008', N'Свердлова', 66, 99, N'подготовительная', N'рисование'),
(N'Попов', N'Михаил', '2005-07-07', N'м', '89000000009', N'Лермонтова', 72, 6, N'подготовительная', N'футбол'),
(N'Сергеев', N'Алексей', '2006-11-30', N'м', '89000000010', N'Куйбышева', 3, 31, N'основная', N'спорт'),
(N'Павлова', N'Елена', '2005-12-13', N'ж', '89000000011', N'Пушкина', 5, 6, N'основная', N'чтение'),
(N'Емельянова', N'Наталья', '2006-05-25', N'ж', '89000000012', N'Папина', 40, 47, N'основная', N'музыка'),
(N'Евдокимов', N'Михаил', '2004-08-18', N'м', '89000000013', N'Чайковского', 3, 40, N'основная', N'футбол'),
(N'Евсеева', N'Елена', '2004-10-14', N'ж', '89000000014', N'Ленина', 14, 82, N'основная', N'рисование'),
(N'Суханова', N'Светлана', '2004-07-29', N'ж', '89000000015', N'Куйбышева', 37, 32, N'основная', N'танцы');

CREATE TABLE predmeti
(
    Kod_predmeta INT IDENTITY(1,1) PRIMARY KEY,
    Nazvanie NVARCHAR(100) NOT NULL,
    Prepodavatel NVARCHAR(100) NOT NULL
);

INSERT INTO predmeti (Nazvanie, Prepodavatel)
VALUES
(N'Информационные системы', N'Сивакова С. Ф.'),
(N'Телекоммуникационные сети', N'Иванов В. М.'),
(N'Базы данных', N'Матвеев И. Л.'),
(N'Экономика', N'Наумова С. Д.'),
(N'Операционные системы', N'Волков А. А.'),
(N'Программирование', N'Шелест В. К.'),
(N'Физическая культура', N'Смирнов Б. Н.');

CREATE TABLE ocenki
(
    Kod_studenta INT NOT NULL,
    Kod_predmeta INT NOT NULL,
    Ocenka INT NOT NULL,

    CONSTRAINT PK_ocenki
        PRIMARY KEY (Kod_studenta, Kod_predmeta),

    CONSTRAINT FK_ocenki_student
        FOREIGN KEY (Kod_studenta)
        REFERENCES student(Kod_studenta),

    CONSTRAINT FK_ocenki_predmeti
        FOREIGN KEY (Kod_predmeta)
        REFERENCES predmeti(Kod_predmeta),

    CONSTRAINT CK_ocenki
        CHECK (Ocenka BETWEEN 2 AND 5)
);

INSERT INTO ocenki (Kod_studenta, Kod_predmeta, Ocenka)
SELECT
    s.Kod_studenta,
    p.Kod_predmeta,
    CASE
        WHEN s.Kod_studenta BETWEEN 1 AND 3 THEN 5
        WHEN s.Kod_studenta BETWEEN 4 AND 10 THEN
            CASE
                WHEN (s.Kod_studenta + p.Kod_predmeta) % 3 = 0 THEN 5
                ELSE 4
            END
        ELSE
            CASE
                WHEN (s.Kod_studenta + p.Kod_predmeta) % 3 = 0 THEN 3
                ELSE 4
            END
    END
FROM student s
CROSS JOIN predmeti p;

SELECT * FROM student;

SELECT * FROM predmeti;

SELECT * FROM ocenki;

SELECT COUNT(*) AS Kolichestvo_Ocenok
FROM ocenki;

SELECT
    s.Kod_studenta,
    s.Familia,
    s.Imya,
    p.Nazvanie,
    o.Ocenka
FROM ocenki o
JOIN student s
    ON o.Kod_studenta = s.Kod_studenta
JOIN predmeti p
    ON o.Kod_predmeta = p.Kod_predmeta
ORDER BY s.Kod_studenta, p.Kod_predmeta;