Aggregation functions

Aggregation_functions.sql

CREATE TABLE Departments (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Financing MONEY NOT NULL CHECK (Financing >= 0) DEFAULT 0,
    Name NVARCHAR(100) NOT NULL UNIQUE,
    FacultyId INT NOT NULL
);

CREATE TABLE Faculties (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Groups (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(10) NOT NULL UNIQUE,
    Year INT NOT NULL CHECK (Year BETWEEN 1 AND 5),
    DepartmentId INT NOT NULL
);

CREATE TABLE GroupsLectures (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    GroupId INT NOT NULL,
    LectureId INT NOT NULL
);

CREATE TABLE Lectures (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    DayOfWeek INT NOT NULL CHECK (DayOfWeek BETWEEN 1 AND 7),
    LectureRoom NVARCHAR(MAX) NOT NULL,
    SubjectId INT NOT NULL,
    TeacherId INT NOT NULL
);

CREATE TABLE Subjects (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Teachers (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(MAX) NOT NULL,
    Surname NVARCHAR(MAX) NOT NULL,
    Salary MONEY NOT NULL CHECK (Salary > 0)
);

CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(MAX) NOT NULL,
    Surname NVARCHAR(MAX) NOT NULL,
    Rating INT NOT NULL CHECK (Rating BETWEEN 0 AND 5)
);

CREATE TABLE GroupsStudents (
    Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    GroupId INT NOT NULL,
    StudentId INT NOT NULL
);




SELECT COUNT(*)
FROM Teachers
WHERE Id IN (
    SELECT TeacherId
    FROM Lectures
    WHERE SubjectId IN (
        SELECT Id
        FROM Subjects
        WHERE Id IN (
            SELECT SubjectId
            FROM Lectures
            WHERE Id IN (
                SELECT LectureId
                FROM GroupsLectures
                WHERE GroupId IN (
                    SELECT Id
                    FROM Groups
                    WHERE DepartmentId = (
                        SELECT Id
                        FROM Departments
                        WHERE Name = 'Software Development'
                    )
                )
            )
        )
    )
);

SELECT COUNT(*)
FROM Lectures
WHERE TeacherId = (
    SELECT Id
    FROM Teachers
    WHERE Name = 'Dave' AND Surname = 'McQueen'
);

SELECT COUNT(*)
FROM Lectures
WHERE LectureRoom = 'D201';

SELECT LectureRoom, COUNT(*)
FROM Lectures
GROUP BY LectureRoom;

SELECT COUNT(*)
FROM GroupsStudents
WHERE GroupId IN (
    SELECT GroupId
    FROM GroupsLectures
    WHERE LectureId IN (
        SELECT Id
        FROM Lectures
        WHERE TeacherId = (
            SELECT Id
            FROM Teachers
            WHERE Name = 'Jack' AND Surname = 'Underhill'
        )
    )
);

SELECT AVG(Salary)
FROM Teachers
WHERE Id IN (
    SELECT TeacherId
    FROM Lectures
    WHERE Id IN (
        SELECT LectureId
        FROM GroupsLectures
        WHERE GroupId IN (
            SELECT Id
            FROM Groups
            WHERE DepartmentId IN (
                SELECT Id
                FROM Departments
                WHERE FacultyId = (
                    SELECT Id
                    FROM Faculties
                    WHERE Name = 'Computer Science'
                )
            )
        )
    )
);

SELECT MIN(StudentCount), MAX(StudentCount)
FROM (
    SELECT COUNT(*) AS StudentCount
    FROM GroupsStudents
    GROUP BY GroupId
) AS Sub;

SELECT AVG(Financing)
FROM Departments;

SELECT Name, Surname,
       (SELECT COUNT(DISTINCT SubjectId)
        FROM Lectures
        WHERE TeacherId = Teachers.Id) AS SubjectsCount
FROM Teachers;

SELECT DayOfWeek, COUNT(*)
FROM Lectures
GROUP BY DayOfWeek;

WITH RoomDepartments AS (
    SELECT L.LectureRoom, G.DepartmentId
    FROM Lectures L
    INNER JOIN GroupsLectures GL ON L.Id = GL.LectureId
    INNER JOIN Groups G ON GL.GroupId = G.Id
)
SELECT LectureRoom, COUNT(DISTINCT DepartmentId) AS DeptCount
FROM RoomDepartments
GROUP BY LectureRoom;


SELECT Name,
       (SELECT COUNT(DISTINCT SubjectId)
        FROM Lectures
        WHERE Id IN (
            SELECT LectureId
            FROM GroupsLectures
            WHERE GroupId IN (
                SELECT Id
                FROM Groups
                WHERE DepartmentId IN (
                    SELECT Id
                    FROM Departments
                    WHERE FacultyId = Faculties.Id
                )
            )
        )
       ) AS SubjectsCount
FROM Faculties;


SELECT TeacherId, LectureRoom, COUNT(*)
FROM Lectures
GROUP BY TeacherId, LectureRoom;