acadamy

academy1.sql

CREATE TABLE Teacher (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Surname NVARCHAR(MAX) NOT NULL CHECK (Surname <> '')
);

CREATE TABLE Subject (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> '')
);


CREATE TABLE Lecture (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SubjectId INT NOT NULL FOREIGN KEY REFERENCES Subject(Id),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);

CREATE TABLE Assistant (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);

CREATE TABLE Curator (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);

CREATE TABLE Dean (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);

CREATE TABLE Head (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(Id)
);

CREATE TABLE Faculty (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> ''),
    DeanId INT NOT NULL FOREIGN KEY REFERENCES Dean(Id)
);

CREATE TABLE Department (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(100) NOT NULL UNIQUE CHECK (Name <> ''),
    FacultyId INT NOT NULL FOREIGN KEY REFERENCES Faculty(Id),
    HeadId INT NOT NULL FOREIGN KEY REFERENCES Head(Id)
);

CREATE TABLE [Group] (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(10) NOT NULL UNIQUE CHECK (Name <> ''),
    Year INT NOT NULL CHECK (Year BETWEEN 1 AND 5),
    DepartmentId INT NOT NULL FOREIGN KEY REFERENCES Department(Id)
);

CREATE TABLE GroupCurator (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CuratorId INT NOT NULL FOREIGN KEY REFERENCES Curator(Id),
    GroupId INT NOT NULL FOREIGN KEY REFERENCES [Group](Id)
);

CREATE TABLE GroupLecture (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    GroupId INT NOT NULL FOREIGN KEY REFERENCES [Group](Id),
    LectureId INT NOT NULL FOREIGN KEY REFERENCES Lecture(Id)
);

CREATE TABLE LectureRoom (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Building INT NOT NULL CHECK (Building BETWEEN 1 AND 5),
    Name NVARCHAR(10) NOT NULL UNIQUE CHECK (Name <> '')
);


CREATE TABLE Schedule (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Class INT NOT NULL CHECK (Class BETWEEN 1 AND 8),
    DayOfWeek INT NOT NULL CHECK (DayOfWeek BETWEEN 1 AND 7),
    Week INT NOT NULL CHECK (Week BETWEEN 1 AND 52),
    LectureId INT NOT NULL FOREIGN KEY REFERENCES Lecture(Id),
    LectureRoomId INT NOT NULL FOREIGN KEY REFERENCES LectureRoom(Id)
);




















SELECT Name
FROM LectureRoom
WHERE Id IN (
    SELECT LectureRoomId
    FROM Schedule
    WHERE LectureId IN (
        SELECT Id
        FROM Lecture
        WHERE TeacherId IN (
            SELECT Id FROM Teacher WHERE Name='Edward' AND Surname='Hopper'
        )
    )
);

SELECT Surname
FROM Teacher
WHERE Id IN (
    SELECT TeacherId
    FROM Assistant
    WHERE TeacherId IN (
        SELECT TeacherId
        FROM Lecture
        WHERE Id IN (
            SELECT LectureId
            FROM GroupLecture
            WHERE GroupId IN (
                SELECT Id FROM [Group] WHERE Name='F505'
            )
        )
    )
);



SELECT Name
FROM Subject
WHERE Id IN (
    SELECT SubjectId
    FROM Lecture
    WHERE TeacherId IN (
        SELECT Id FROM Teacher WHERE Name='Alex' AND Surname='Carmack'
    )
    AND Id IN (
        SELECT LectureId
        FROM GroupLecture
        WHERE GroupId IN (
            SELECT Id FROM [Group] WHERE Year=5
        )
    )
);

SELECT Surname
FROM Teacher
WHERE Id NOT IN (
    SELECT TeacherId
    FROM Lecture
    WHERE Id IN (
        SELECT LectureId FROM Schedule WHERE DayOfWeek=1
    )
);

SELECT Name, Building
FROM LectureRoom
WHERE Id NOT IN (
    SELECT LectureRoomId
    FROM Schedule
    WHERE DayOfWeek=3 AND Week=2 AND Class=3
);

SELECT Name, Surname
FROM Teacher
WHERE Id IN (
    SELECT TeacherId
    FROM Department
    WHERE FacultyId IN (
        SELECT Id FROM Faculty WHERE Name='Computer Science'
    )
)
AND Id NOT IN (
    SELECT TeacherId
    FROM Curator
    WHERE Id IN (
        SELECT CuratorId
        FROM GroupCurator
        WHERE GroupId IN (
            SELECT Id
            FROM [Group]
            WHERE DepartmentId IN (
                SELECT Id FROM Department WHERE Name='Software Development'
            )
        )
    )
);

SELECT Building FROM Faculty
UNION
SELECT Building FROM Department
UNION
SELECT Building FROM LectureRoom;

SELECT Name, Surname, 'Dean' AS Status
FROM Teacher WHERE Id IN (SELECT TeacherId FROM Dean)
UNION
SELECT Name, Surname, 'Head'
FROM Teacher WHERE Id IN (SELECT TeacherId FROM Head)
UNION
SELECT Name, Surname, 'Teacher'
FROM Teacher
UNION
SELECT Name, Surname, 'Curator'
FROM Teacher WHERE Id IN (SELECT TeacherId FROM Curator)
UNION
SELECT Name, Surname, 'Assistant'
FROM Teacher WHERE Id IN (SELECT TeacherId FROM Assistant);

SELECT DISTINCT DayOfWeek
FROM Schedule
WHERE LectureRoomId IN (
    SELECT Id FROM LectureRoom WHERE Name IN ('A311','A104')
);






INSERT INTO Teacher (Name, Surname) VALUES
('Edward', 'Hopper'),
('Alex', 'Carmack'),
('John', 'Smith'),
('Mary', 'Johnson'),
('Robert', 'Brown');

INSERT INTO Subject (Name) VALUES
('Mathematics'),
('Computer Science'),
('Software Development'),
('Physics'),
('Philosophy');

INSERT INTO Dean (TeacherId) VALUES (3);

INSERT INTO Head (TeacherId) VALUES (4);

INSERT INTO Faculty (Building, Name, DeanId) VALUES
(1, 'Computer Science', 1),
(2, 'Engineering', 1);

INSERT INTO Department (Building, Name, FacultyId, HeadId) VALUES
(1, 'Software Development', 1, 1),
(2, 'Mathematics', 2, 1);

INSERT INTO [Group] (Name, Year, DepartmentId) VALUES
('F505', 5, 1),
('M101', 1, 2);

INSERT INTO Curator (TeacherId) VALUES (5);

INSERT INTO Assistant (TeacherId) VALUES (2);

INSERT INTO GroupCurator (CuratorId, GroupId) VALUES (1, 1);

INSERT INTO LectureRoom (Building, Name) VALUES
(1, 'A311'),
(1, 'A104'),
(2, 'B201');

INSERT INTO Lecture (SubjectId, TeacherId) VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 3);

INSERT INTO GroupLecture (GroupId, LectureId) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4);

INSERT INTO Schedule (Class, DayOfWeek, Week, LectureId, LectureRoomId) VALUES
(1, 1, 1, 1, 1),
(2, 3, 2, 2, 2),
(3, 3, 2, 3, 3),
(4, 5, 1, 4, 1);