SESSIA 4ast 1

Сесія
1.
SELECT record_book, Students.last_name, Students.first_name, Subjects.name, ExamsLog.DATE, ExamsLog.mark_num, Teachers.last_name, Teachers.first_name
FROM ExamsLog
JOIN Students ON Students.record_book = ExamsLog.record_book,
JOIN Subjects ON Subjects.id_predm = ExamsLog.id_predm,
JOIN Teachers ON Teachers.tab_No = ExamsLog.tab_No
WHERE Students.last_name = “Petrenko”.
2.
SELECT record_book, Students.last_name, Students.first_name, Subjects.name, ExamsLog.DATE, ExamsLog.mark_num, Teachers.last_name, Teachers.first_name
FROM ExamsLog
LEFT OUTER  JOIN Students ON Students.record_book = ExamsLog.record_book,
 JOIN Subjects ON Subjects.id_predm = ExamsLog.id_predm,
JOIN Teachers ON Teachers.tab_No = ExamsLog.tab_No.
3.
SELECT Student.last_name
FROM Students AS stud
WHERE stud.id NOT IN (SELECT Students.id
                        FROM Students
                        JOIN ExamLog ON Examslog.id_stud = Student.id
                        WHERE Examslog.mark_num < 81).

4.
SELECT last_name
FROM Teachers
WHERE science_degree \= NULL
5.
SELECT last_name
FROM Teachers
WHERE science_degree \= ‘doctor_nauk’
6.a
SELECT id_predm, COUNT(record_book)
FROM ExamsLog
GROUP BY id_predm
6.b
SELECT id_predm, COUNT(record_book)
FROM ExamsLog
WHERE mark_num > ‘90’
GROUP BY id_predm
7.
SELECT COUNT(tab_nomer)
FROM TEACHERS
WHERE tab_nomers IN (SELECT tab_nomer
                      FROM ExamsLog
                      JOIN Students ON Students.id = ExamsLog.ids
                      WHERE Students.years = '3')

8.
SELECT *
FROM ExamsLog
JOIN Students ON Students.id = ExamsLog.ids
JOIN Subjects ON Subjects.id = ExamsLog.idp
ORDER BY Subjects.name Students.last_name
9.
SELECT prizv
FROM ExamsLog AS ex
JOIN Students AS sd ON sd.id = ex.ids
JOIN Predmets AS pd ON pd.id = ex.idp
WHERE sd.years = '3' AND pd.name = 'IS' AND ex.mark_char = 'F' IN(SELECT id
                                                                                    FROM ExamsLog
                                                                                    JOIN Students ON Students.id = Examslog.ids
                                                                                    JOIN Predmets ON Predmets.id = ExamsLog.idp
                                                                                    WHERE Students.years = '3' AND Predmets.name = 'OBDZ' AND ExamsLog.mark_char = 'A')
10.
SELECT prizv
FROM Students
WHERE id NOT IN(SELECT ids
                FROM ExamsLog
                JOIN Students ON Students.id = ExamsLog.ids
                WHERE ExamsLog.mark_char='vidminno')
11.
SELECT DISTINCT prizv
FROM examLog
JOIN stud ON stud.id = examlog.ids
JOIN teacher ON teacher.id = examlog.idt
WHERE teacher.degree="docent" AND ids NOT IN (SELECT DISTINCT ids
                                            FROM examLog
                                            JOIN stud ON stud.id = examlog.ids
                                            JOIN teacher ON teacher.id = examlog.idt
                                            WHERE teacher.degree <> "DOCENT")
12.