academy dz

academy.sql

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

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

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

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

CREATE TABLE GroupsCurators (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CuratorId INT NOT NULL FOREIGN KEY REFERENCES Curators(Id),
    GroupId INT NOT NULL FOREIGN KEY REFERENCES Groups(Id)
);

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

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

CREATE TABLE Lectures (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    LectureRoom NVARCHAR(MAX) NOT NULL CHECK (LEN(LectureRoom) > 0),
    SubjectId INT NOT NULL FOREIGN KEY REFERENCES Subjects(Id),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teachers(Id)
);

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




















SELECT 
    t.Surname + ' ' + t.Name AS TeacherFullName,
    g.Name AS GroupName
FROM Teachers t, Groups g;

SELECT f.Name
FROM Faculties f
WHERE EXISTS (
    SELECT 1
    FROM Departments d
    WHERE d.FacultyId = f.Id
    GROUP BY d.FacultyId
    HAVING SUM(d.Financing) > f.Financing
);

SELECT 
    (SELECT c.Surname FROM Curators c WHERE c.Id = gc.CuratorId) AS CuratorSurname,
    (SELECT g.Name FROM Groups g WHERE g.Id = gc.GroupId) AS GroupName
FROM GroupsCurators gc;

SELECT DISTINCT 
    (SELECT t.Name FROM Teachers t WHERE t.Id = l.TeacherId) AS TeacherName,
    (SELECT t.Surname FROM Teachers t WHERE t.Id = l.TeacherId) AS TeacherSurname
FROM Lectures l
WHERE l.Id IN (
    SELECT gl.LectureId
    FROM GroupsLectures gl
    WHERE gl.GroupId = (SELECT Id FROM Groups WHERE Name = 'P107')
);

SELECT DISTINCT 
    (SELECT t.Surname FROM Teachers t WHERE t.Id = l.TeacherId) AS TeacherSurname,
    (SELECT f.Name 
     FROM Faculties f 
     WHERE f.Id = (
         SELECT d.FacultyId 
         FROM Departments d 
         WHERE d.Id = (
             SELECT g.DepartmentId 
             FROM Groups g 
             WHERE g.Id = (
                 SELECT gl.GroupId 
                 FROM GroupsLectures gl 
                 WHERE gl.LectureId = l.Id
             )
         )
     )
    ) AS FacultyName
FROM Lectures l;

SELECT 
    (SELECT d.Name FROM Departments d WHERE d.Id = g.DepartmentId) AS DepartmentName,
    g.Name AS GroupName
FROM Groups g;

SELECT DISTINCT 
    (SELECT s.Name FROM Subjects s WHERE s.Id = l.SubjectId) AS SubjectName
FROM Lectures l
WHERE l.TeacherId = (
    SELECT Id FROM Teachers WHERE Name = 'Samantha' AND Surname = 'Adams'
);

SELECT DISTINCT 
    (SELECT d.Name FROM Departments d WHERE d.Id = g.DepartmentId) AS DepartmentName
FROM Groups g
WHERE g.Id IN (
    SELECT gl.GroupId
    FROM GroupsLectures gl
    WHERE gl.LectureId IN (
        SELECT l.Id FROM Lectures l
        WHERE l.SubjectId = (SELECT Id FROM Subjects WHERE Name = 'Database Theory')
    )
);

SELECT g.Name
FROM Groups g
WHERE g.DepartmentId IN (
    SELECT d.Id FROM Departments d
    WHERE d.FacultyId = (SELECT Id FROM Faculties WHERE Name = 'Computer Science')
);

SELECT 
    g.Name AS GroupName,
    (SELECT f.Name 
     FROM Faculties f 
     WHERE f.Id = (SELECT d.FacultyId FROM Departments d WHERE d.Id = g.DepartmentId)
    ) AS FacultyName
FROM Groups g
WHERE g.Year = 5;

SELECT 
    (SELECT t.Name FROM Teachers t WHERE t.Id = l.TeacherId) AS TeacherName,
    (SELECT t.Surname FROM Teachers t WHERE t.Id = l.TeacherId) AS TeacherSurname,
    (SELECT s.Name FROM Subjects s WHERE s.Id = l.SubjectId) AS SubjectName,
    (SELECT g.Name 
     FROM Groups g 
     WHERE g.Id = (
         SELECT gl.GroupId 
         FROM GroupsLectures gl 
         WHERE gl.LectureId = l.Id
     )
    ) AS GroupName
FROM Lectures l
WHERE l.LectureRoom = 'B103';