select StudentId, StudentName, GroupId
from Students
         natural join Marks
         natural join Courses
where CourseName = 'Базы данных'
  and Mark = 4;

select StudentId, StudentName, GroupId
from Students
         natural join
     (select StudentId
      from Students
          except
      select StudentId
      from Students
               natural join Marks
               natural join Courses
      where CourseName = 'Базы данных');

select StudentId, StudentName, GroupId
from (select StudentId, StudentName, GroupId
      from Students
               natural join Plan
               natural join Courses
      where CourseName = 'Базы данных')
         natural join
     (select StudentId
      from Students
          except
      select StudentId
      from Students
               natural join Marks
               natural join Courses
      where CourseName = 'Базы данных');

select StudentId, StudentName, GroupId
from Students
         natural join Marks
         natural join Plan
         natural join Lecturers
where lecturerid = 123;

select StudentId
from (select StudentId
      from Students
          except
      select StudentId
      from Students
               natural join Marks
               natural join Plan
               natural join Lecturers
      where LecturerName = 'name');

select StudentId
from ((select StudentId
       from Students)
      except
      (select StudentId
       from (select StudentId, CourseId
             from Students
                      natural join Plan
             where LecturerId = 123) except
       select StudentId, CourseId
       from Students
                natural join Marks));

select StudentName, CourseName
from Students
         natural join Plan
         natural join Courses;

select StudentId
from Students
         natural join Plan
where LecturerId = 123;

with lhs as (select StudentId as StudentId2, CourseId
             from Students
                      natural join Marks
             where Mark != 2),
     rhs as (select StudentId as StudentId1, CourseId
             from Students
                      natural join Marks
             where Mark != 2)
select StudentId1, StudentId1
from ((select StudentId2 from lhs)
         cross join
      (select StudentId1 from rhs))
        except
     (select StudentId1, StudentId2
        from ((select StudentId2 from lhs) cross join rhs)
            except
        (select *
        from lhs natural join rhs));

with lhs as (select StudentId, CourseId
             from Students
                      natural join Marks
             where Mark != 2),
     rhs as (select StudentId, GroupId
             from Students)
select CourseId, GroupId
from ((select CourseId from lhs)
         cross join
      (select GroupId from rhs))
        except
     (select CourseId, GroupId
        from ((select CourseId from lhs) cross join rhs)
            except
        (select *
        from lhs natural join rhs));

select avg(Mark) as Mark
from Students
         natural join Marks
where StudentId = 123;

select StudentId, avg(Mark) as Mark
from Students
         natural join Marks
group by StudentId;

select avg(Mark) as Mark
from (select GroupId, avg(Mark) as Mark
      from Students
               natural join Marks
      group by GroupId);

select *
from (select StudentId, count(*) as total
      from students
               natural join Plan
      group by StudentId)
         natural join (select StudentId, count(*) as passed
                       from students
                                natural join Marks
                       where Mark != 2
                       group by StudentId)
         natural join (select StudentId, count(*) as notpassed
                       from students
                                natural join Marks
                       where Mark = 2
                       group by StudentId);