Subqueries

Subqueries.sql

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

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

CREATE TABLE StudentGroup (
    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 Curator (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Surname NVARCHAR(MAX) NOT NULL CHECK (Surname <> '')
);

CREATE TABLE Teacher (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    IsProfessor BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Salary MONEY NOT NULL CHECK (Salary > 0),
    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 Student (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(MAX) NOT NULL CHECK (Name <> ''),
    Rating INT NOT NULL CHECK (Rating BETWEEN 0 AND 5),
    Surname NVARCHAR(MAX) NOT NULL CHECK (Surname <> '')
);

CREATE TABLE Lecture (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Date DATE NOT NULL CHECK (Date <= GETDATE()),
    SubjectId INT NOT NULL FOREIGN KEY REFERENCES Subject(Id),
    TeacherId INT NOT NULL FOREIGN KEY REFERENCES Teacher(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 StudentGroup(Id)
);

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

CREATE TABLE GroupStudent (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    GroupId INT NOT NULL FOREIGN KEY REFERENCES StudentGroup(Id),
    StudentId INT NOT NULL FOREIGN KEY REFERENCES Student(Id)
);































SELECT Building
FROM Department
GROUP BY Building
HAVING SUM(Financing) > 100000;

SELECT Name
FROM StudentGroup
WHERE Year = 5
  AND DepartmentId = (SELECT Id FROM Department WHERE Name = 'Software Development')
  AND Id IN (
      SELECT GroupId
      FROM GroupLecture
      WHERE LectureId IN (
          SELECT Id FROM Lecture WHERE DATEPART(WEEK, Date) = 1
      )
      GROUP BY GroupId
      HAVING COUNT(LectureId) > 10
  );

SELECT sg.Name
FROM StudentGroup sg
WHERE (SELECT AVG(s.Rating)
       FROM Student s
       WHERE s.Id IN (SELECT StudentId FROM GroupStudent WHERE GroupId = sg.Id)
      ) >
      (SELECT AVG(s2.Rating)
       FROM Student s2
       WHERE s2.Id IN (
           SELECT StudentId FROM GroupStudent
WHERE GroupId = (SELECT Id FROM StudentGroup WHERE Name = 'D221')       )
      );

SELECT Surname, Name
FROM Teacher
WHERE Salary > (SELECT AVG(Salary) FROM Teacher WHERE IsProfessor = 1);

SELECT Name
FROM StudentGroup
WHERE Id IN (
    SELECT GroupId
    FROM GroupCurator
    GROUP BY GroupId
    HAVING COUNT(CuratorId) > 1
);

SELECT sg.Name
FROM StudentGroup sg
WHERE (SELECT AVG(s.Rating)
       FROM Student s
       WHERE s.Id IN (
           SELECT StudentId FROM GroupStudent WHERE GroupId = sg.Id
       )
      ) <
      (SELECT MIN(avgRating)
       FROM (
           SELECT gs.GroupId, AVG(s2.Rating) AS avgRating
           FROM GroupStudent gs
           JOIN Student s2 ON s2.Id = gs.StudentId
           WHERE gs.GroupId IN (SELECT Id FROM StudentGroup WHERE Year = 5)
           GROUP BY gs.GroupId
       ) AS Sub
      );

SELECT Name
FROM Faculty
WHERE Id IN (
    SELECT FacultyId
    FROM Department
    GROUP BY FacultyId
    HAVING SUM(Financing) > (
        SELECT SUM(Financing)
        FROM Department
        WHERE FacultyId = (SELECT Id FROM Faculty WHERE Name = 'Computer Science')
    )
);

SELECT s.Name, t.Name, t.Surname
FROM Subject s, Teacher t
WHERE s.Id IN (
    SELECT SubjectId
    FROM Lecture
    GROUP BY SubjectId, TeacherId
    HAVING COUNT(Id) = (
        SELECT MAX(cnt)
        FROM (
            SELECT COUNT(Id) AS cnt
            FROM Lecture
            GROUP BY SubjectId, TeacherId
        ) AS Sub
    )
);

SELECT Name
FROM Subject
WHERE Id IN (
    SELECT SubjectId
    FROM Lecture
    GROUP BY SubjectId
    HAVING COUNT(Id) = (
        SELECT MIN(cnt)
        FROM (
            SELECT COUNT(Id) AS cnt
            FROM Lecture
            GROUP BY SubjectId
        ) AS Sub
    )
);

SELECT
  (SELECT COUNT(DISTINCT StudentId)
   FROM GroupStudent
   WHERE GroupId IN (
       SELECT Id FROM StudentGroup WHERE DepartmentId = (SELECT Id FROM Department WHERE Name = 'Software Development')
   )) AS StudentCount,
  (SELECT COUNT(DISTINCT SubjectId)
   FROM Lecture
   WHERE Id IN (
       SELECT LectureId
       FROM GroupLecture
       WHERE GroupId IN (
           SELECT Id FROM StudentGroup WHERE DepartmentId = (SELECT Id FROM Department WHERE Name = 'Software Development')
       )
   )) AS SubjectCount;