--Создайте БД и заполните таблицы.
--Создайте три таблицы: `Students`, `Courses`, `StudentCourses`.
--Таблица `Students` должна содержать следующие поля:
--`StudentID` (int, Primary Key, AutoIncrement)
--`FirstName` (nvarchar(50))
--`LastName` (nvarchar(50))
--`BirthDate` (date)
--Таблица `Courses` должна содержать следующие поля:
--`CourseID` (int, Primary Key, AutoIncrement)
--`CourseName` (nvarchar(100))
--`Instructor` (nvarchar(100))
--`CreditHours` (tinyint)
--Таблица `StudentCourses` должна содержать следующие поля для отслеживания принятых студентами курсов и их оценок:
--`StudentID` (int, Foreign Key references Students(StudentID))
--`CourseID` (int, Foreign Key references Courses(CourseID))
--`Grade` (tinyint)
CREATE DATABASE School_DB;

USE School_DB;

USE School_DB;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    BirthDate DATE
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY IDENTITY(1,1),
    CourseName NVARCHAR(100),
    Instructor NVARCHAR(100),
    CreditHours TINYINT
);

CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    Grade TINYINT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

INSERT INTO Students (FirstName, LastName, BirthDate) VALUES
    ('Иван', 'Иванов', '2003-01-15'),
    ('Петр', 'Петров', '2002-08-20'),
    ('Анна', 'Сидорова', '2001-05-10');


INSERT INTO Courses (CourseName, Instructor, CreditHours) VALUES
    ('Математика', 'Иванов И.И.', 4),
    ('Физика', 'Петрова А.П.', 3),
    ('История', 'Сидоров С.С.', 2);


INSERT INTO StudentCourses (StudentID, CourseID, Grade) VALUES
    (1, 1, 5),
    (1, 2, 4),
    (2, 1, 3),
    (3, 3, 5);
--Создайте представление `StudentCourseOverview`, которое должно включать следующую информацию о студентах и их курсах:
--`StudentFullName` (ФИО студента)
--`CourseName`
--`Grade`
--`CreditHours`
CREATE VIEW StudentCourseOverview AS
SELECT Students.FirstName + ' ' + Students.LastName AS StudentFullName,
       Courses.CourseName,
       StudentCourses.Grade,
       Courses.CreditHours
FROM Students
JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
JOIN Courses
ON StudentCourses.CourseID = Courses.CourseID;
--Создайте представление `CourseStatistics`, которое показывает статистику по каждому курсу:
--`CourseName`
--`TotalStudentsEnrolled` (общее количество студентов, зарегистрированных на курс)
--`AverageGrade` (средняя оценка по курсу)
CREATE VIEW CourseStatistics AS
SELECT
    Courses.CourseName,
    COUNT(DISTINCT StudentCourses.StudentID) AS TotalStudentsEnrolled,
    AVG(StudentCourses.Grade) AS AverageGrade
FROM Courses
JOIN StudentCourses
ON Courses.CourseID = StudentCourses.CourseID
GROUP BY Courses.CourseName;
--·        Напишите SQL-запросы для извлечения информации из созданных представлений.
SELECT * FROM StudentCourseOverview;
SELECT * FROM CourseStatistics;
--·        Один запрос должен выводить информацию о всех курсах и средних оценках по ним.
SELECT CourseName, AverageGrade
FROM CourseStatistics;
--·        Другой запрос должен выводить информацию о всех курсах, которые проходит конкретный студент, включая название курса, оценку --и количество кредитных часов.
DECLARE @StudentFullName NVARCHAR(100);

SET @StudentFullName = 'Иван Иванов';
SELECT StudentFullName, CourseName, Grade, CreditHours
FROM StudentCourseOverview
WHERE StudentFullName = @StudentFullName;