---

SELECT 
    Students.student_id,
    Students.last_name,
    Students.first_name,
    Students.study_group,
    Subjects.subject_name,
    Grades.grade,
    Grades.grade_date
FROM Students
JOIN Grades ON Students.student_id = Grades.student_id
JOIN Subjects ON Grades.subject_id = Subjects.subject_id;

||||||||||||||||||||

SELECT DISTINCT
    Students.student_id,
    Students.last_name,
    Students.first_name,
    Students.study_group
FROM Students
JOIN Grades ON Students.student_id = Grades.student_id
WHERE Grades.grade > 4;

||||||||||||||||||||

SELECT
    Students.student_id,
    Students.last_name,
    Students.first_name,
    Students.study_group,
    ROUND(AVG(Grades.grade), 2) AS average_grade
FROM Students
JOIN Grades ON Students.student_id = Grades.student_id
GROUP BY
    Students.student_id,
    Students.last_name,
    Students.first_name,
    Students.study_group;

|||||||||||||||||||||

CREATE VIEW Great_Students AS
SELECT student_id, last_name, first_name, study_group
FROM Students
WHERE student_id IN (
  SELECT student_id FROM Grades GROUP BY student_id HAVING MIN(grade) = 5
);

|||||||||||||||||||||

CREATE VIEW Good_Students AS
SELECT student_id, last_name, first_name, study_group
FROM Students
WHERE student_id IN (
  SELECT student_id FROM Grades GROUP BY student_id HAVING MIN(grade) = 4
);

|||||||||||||||||||||

CREATE VIEW Decent_Students AS
SELECT student_id, last_name, first_name, study_group
FROM Students
WHERE student_id IN (
  SELECT student_id FROM Grades GROUP BY student_id HAVING MIN(grade) = 3
);