-- 1
SELECT StudentId, StudentName, GroupId
FROM Students
WHERE EXISTS(
              SELECT *
              FROM Marks
              WHERE (
                            Students.StudentId = Marks.StudentId
                            AND Mark = 4
                            AND CourseId in (
                            SELECT CourseId
                            FROM Courses
                            WHERE CourseName = 'Базы данных')
                        )
          );

-- 2a
SELECT StudentId, StudentName, GroupId
FROM Students
WHERE NOT EXISTS(
        SELECT *
        FROM Marks
        WHERE (
                      Students.StudentId = Marks.StudentId
                      AND CourseId in (
                      SELECT CourseId
                      FROM Courses
                      WHERE CourseName = 'Базы данных')
                  )
    );

-- 2b
SELECT StudentId, StudentName, GroupId
FROM Students
WHERE GroupId IN (
    SELECT GroupId
    FROM Plan
    WHERE Students.GroupId = Plan.GroupId
      AND CourseId in (
        SELECT CourseId
        FROM Courses
        WHERE CourseName = 'Базы данных')
)
  AND NOT EXISTS(
        SELECT *
        FROM Marks
        WHERE (
                      Students.StudentId = Marks.StudentId
                      AND CourseId in (
                      SELECT CourseId
                      FROM Courses
                      WHERE CourseName = 'Базы данных')
                  )
    );

-- 3
SELECT StudentId, StudentName, GroupId
FROM Students
WHERE StudentId IN (
    SELECT StudentId
    FROM Marks
    WHERE CourseId IN (
        SELECT CourseId
        FROM Plan
        WHERE LecturerId IN (
            SELECT LecturerId
            FROM Lecturers
            WHERE LecturerName = 'whatever'
        )
    )
);

-- 4
SELECT StudentId
FROM Students
    EXCEPT
SELECT DISTINCT StudentId
FROM Students
WHERE StudentId IN (
    SELECT StudentId
    FROM Marks
    WHERE CourseId IN (
        SELECT CourseId
        FROM Plan
        WHERE LecturerId IN (
            SELECT LecturerId
            FROM Lecturers
            WHERE LecturerName = 'whatever'
        )
    )
);

-- 5
SELECT StudentId
FROM Students
WHERE StudentId IN (
    SELECT StudentId
    FROM Marks
    WHERE CourseId IN (
        SELECT CourseId
        FROM Plan
        WHERE LecturerId IN (
            SELECT LecturerId
            FROM Lecturers
            WHERE LecturerName = 'whatever'
        )
    )
      AND CourseId NOT IN (
        SELECT CourseId
        FROM Courses
        WHERE CourseId NOT IN (
            SELECT CourseId
            FROM Plan
            where Plan.GroupId = Students.GroupId
        )
    )
);

-- 6
SELECT Students.StudentName, Courses.CourseName
FROM Students,
     Courses
WHERE GroupId IN (
    SELECT GroupId
    FROM Plan
    WHERE Courses.CourseId = Plan.CourseId
);

-- 7
SELECT StudentId
FROM Students
WHERE GroupId IN (
    SELECT GroupId
    FROM Plan
    WHERE LecturerId IN (
        SELECT LecturerId
        FROM Lecturers
        WHERE LecturerName = 'whatever'
    )
);