---

-- 1) Представление "Абитуриенты (забрали документы)"
CREATE VIEW [Applicants (withdrawn)] AS
SELECT 
    LastName + ' ' + SUBSTRING(FirstName, 1, 1) + '.' + SUBSTRING(MiddleName, 1, 1) + '.' AS FullName
FROM Applicants
WHERE Status = 'забрал документы';
GO

-- 2) Представление "Абитуриенты (зачислены)"
CREATE VIEW [Applicants (enrolled)] AS
SELECT 
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.Name AS SpecialtyName
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
JOIN Specialties s ON app.SpecialtyID = s.IDspecialties
WHERE app.Status = 'зачислен';
GO

-- 3) Представление "Специальность (зачисленных)"
CREATE VIEW [Specialties (enrolled count)] AS
SELECT 
    s.Name AS SpecialtyName,
    COUNT(app.ApplicantID) AS EnrolledCount
FROM Specialties s
LEFT JOIN Applications app ON s.IDspecialties = app.SpecialtyID AND app.Status = 'зачислен'
GROUP BY s.Name;
GO

-- 4) Представление "Специальность (экзаменов)"
CREATE VIEW [Specialties (exams count)] AS
SELECT 
    s.Name AS SpecialtyName,
    COUNT(e.ExamID) AS ExamsCount
FROM Specialties s
LEFT JOIN Exams e ON s.IDspecialties = e.SpecialtyID
GROUP BY s.Name;
GO

-- 5) Представление "Абитуриенты (экзаменов)"
CREATE VIEW [Applicants (passed exams)] AS
SELECT 
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.Name AS SpecialtyName,
    COUNT(g.ExamID) AS PassedExamsCount
FROM Applicants a
JOIN Grades g ON a.ApplicantID = g.ApplicantID
JOIN Exams e ON g.ExamID = e.ExamID
JOIN Specialties s ON e.SpecialtyID = s.IDspecialties
WHERE g.Grade >= e.PassingScore
GROUP BY a.LastName, a.FirstName, a.MiddleName, s.Name;
GO
-- 6) Представление "Абитуриенты (общежитие)"
CREATE VIEW [Applicants (dormitory)] AS
SELECT DISTINCT
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
WHERE app.Status = 'зачислен' 
  AND a.City <> 'Ханты-Мансийск';
GO

-- 7) Представление "Абитуриенты (по городам)"
CREATE VIEW [Applicants (by cities)] AS
SELECT 
    a.City AS City,
    COUNT(DISTINCT a.ApplicantID) AS EnrolledCount
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
WHERE app.Status = 'зачислен'
GROUP BY a.City;
GO

-- 8) Представление "Абитуриенты (баллы)"
CREATE VIEW [Applicants (total scores)] AS
SELECT 
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.Name AS SpecialtyName,
    SUM(g.Grade) AS TotalScore
FROM Applicants a
JOIN Grades g ON a.ApplicantID = g.ApplicantID
JOIN Exams e ON g.ExamID = e.ExamID
JOIN Specialties s ON e.SpecialtyID = s.IDspecialties
GROUP BY a.ApplicantID, a.LastName, a.FirstName, a.MiddleName, s.Name;
GO

-- 9) Представление "Специальность (конкурс)"
CREATE VIEW [Specialties (competition)] AS
SELECT 
    s.Name AS SpecialtyName,
    CAST(COUNT(app.ApplicationID) AS FLOAT) / NULLIF(s.Vakansii, 0) AS Competition
FROM Specialties s
LEFT JOIN Applications app ON s.IDspecialties = app.SpecialtyID
GROUP BY s.IDspecialties, s.Name, s.Vakansii;
GO

-- 10) Представление "Специальность (проходной балл)"
CREATE VIEW [Specialties (passing score)] AS
WITH ApplicantTotalScores AS (
    SELECT 
        a.ApplicantID,
        s.IDspecialties AS SpecialtyID,
        s.Name AS SpecialtyName,
        SUM(g.Grade) AS TotalScore
    FROM Applicants a
    JOIN Applications app ON a.ApplicantID = app.ApplicantID
    JOIN Specialties s ON app.SpecialtyID = s.IDspecialties
    JOIN Grades g ON a.ApplicantID = g.ApplicantID
    JOIN Exams e ON g.ExamID = e.ExamID AND e.SpecialtyID = s.IDspecialties
    WHERE app.Status = 'зачислен'
    GROUP BY a.ApplicantID, s.IDspecialties, s.Name
)
SELECT 
    SpecialtyName,
    MIN(TotalScore) AS PassingScore
FROM ApplicantTotalScores
GROUP BY SpecialtyID, SpecialtyName;
GO
-- 11) Представление "Абитуриенты (сдали все экзамены)"
CREATE VIEW [Applicants (passed all exams)] AS
SELECT 
    a.ApplicantID AS [Applicant ID],
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.IDspecialties AS [Specialty ID],
    s.Name AS SpecialtyName
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
JOIN Specialties s ON app.SpecialtyID = s.IDspecialties
WHERE (
    -- Количество сданных экзаменов абитуриентом равен общему числу экзаменов по специальности
    SELECT COUNT(DISTINCT g.ExamID)
    FROM Grades g
    JOIN Exams e ON g.ExamID = e.ExamID
    WHERE g.ApplicantID = a.ApplicantID 
      AND e.SpecialtyID = s.IDspecialties
      AND g.Grade >= e.PassingScore
) = (
    SELECT COUNT(e.ExamID)
    FROM Exams e
    WHERE e.SpecialtyID = s.IDspecialties
) AND (
    SELECT COUNT(e.ExamID) 
    FROM Exams e 
    WHERE e.SpecialtyID = s.IDspecialties
) > 0;
GO

-- 12) Представление "Абитуриенты (не сдали экзамены)"
CREATE VIEW [Applicants (failed exams)] AS
SELECT DISTINCT
    a.ApplicantID AS [Applicant ID],
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.IDspecialties AS [Specialty ID],
    s.Name AS SpecialtyName
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
JOIN Specialties s ON app.SpecialtyID = s.IDspecialties
JOIN Grades g ON a.ApplicantID = g.ApplicantID
JOIN Exams e ON g.ExamID = e.ExamID AND e.SpecialtyID = s.IDspecialties
WHERE g.Grade < e.PassingScore;
GO

-- 13) Представление "Абитуриенты (не зачислены)"
CREATE VIEW [Applicants (passed but not enrolled)] AS
SELECT 
    a.ApplicantID AS [Applicant ID],
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.IDspecialties AS [Specialty ID],
    s.Name AS SpecialtyName
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
JOIN Specialties s ON app.SpecialtyID = s.IDspecialties
WHERE app.Status <> 'зачислен'
  AND (
    SELECT COUNT(DISTINCT g.ExamID)
    FROM Grades g
    JOIN Exams e ON g.ExamID = e.ExamID
    WHERE g.ApplicantID = a.ApplicantID 
      AND e.SpecialtyID = s.IDspecialties
      AND g.Grade >= e.PassingScore
) = (
    SELECT COUNT(e.ExamID)
    FROM Exams e
    WHERE e.SpecialtyID = s.IDspecialties
) AND (
    SELECT COUNT(e.ExamID) 
    FROM Exams e 
    WHERE e.SpecialtyID = s.IDspecialties
) > 0;
GO

-- 14) Представление "Специальность (свободных вакансий)"
CREATE VIEW [Specialties (vacant spots)] AS
SELECT 
    s.Name AS SpecialtyName,
    s.Vakansii - ISNULL(COUNT(CASE WHEN app.Status = 'зачислен' THEN 1 END), 0) AS FreeVacancies
FROM Specialties s
LEFT JOIN Applications app ON s.IDspecialties = app.SpecialtyID
GROUP BY s.IDspecialties, s.Name, s.Vakansii;
GO

-- 15) Представление "Абитуриенты (процент поступивших)"
-- Согласно подсказке, создаем 3 представления:

-- 15.1) Первое представление: Общая статистика абитуриентов по городам
CREATE VIEW [v_CityTotalApplicants] AS
SELECT 
    City,
    COUNT(ApplicantID) AS TotalApplicants
FROM Applicants
GROUP BY City;
GO

-- 15.2) Второе представление: Статистика зачисленных по городам
CREATE VIEW [v_CityEnrolledApplicants] AS
SELECT 
    a.City,
    COUNT(DISTINCT a.ApplicantID) AS EnrolledApplicants
FROM Applicants a
JOIN Applications app ON a.ApplicantID = app.ApplicantID
WHERE app.Status = 'зачислен'
GROUP BY a.City;
GO

-- 15.3) Третье (итоговое) представление: Процент зачисленных от общего числа
CREATE VIEW [Applicants (enrollment percentage)] AS
SELECT 
    t.City AS City,
    CAST(ISNULL(e.EnrolledApplicants, 0) * 100.0 / t.TotalApplicants AS NUMERIC(5,2)) AS EnrollmentPercentage
FROM v_CityTotalApplicants t
LEFT JOIN v_CityEnrolledApplicants e ON t.City = e.City;
GO