-- Question 1 : Give the list of teachers (name) and the name of the courses that they are teaching and the name of the courses that they are reviewing.
-- Hint: There will be a list of teacher names (first and last), teaching courses, reviewing courses.
-- write the query here
select name, surname, course_name, review.course_code
from teacher join teach on teacher.bsn = teach.bsn join course on teach.course_code = course.course_code join review on course.course_code = review.course_code;
-- Question 2: Give the list of students that completed their study programs together with their course points (sum of all the course points for each student).
-- Hint: There will be a list with first name and last name of students and total points of courses that each student followed.
-- write the query here
select name, surname, sum(study_points)
from study_program join completed on study_program.course_program = completed.course_program
join student on completed.student_id = student.student_id
join teach on student.student_id = teach.student_id
join course on teach.course_code = course.course_code
group by name, surname;
-- Question 3: Give the list of courses (names) that teachers with maximum scale are teaching.
-- write the query here
select distinct course_name, name, surname
from course join teach on course.course_code = teach.course_code
join teacher on teach.bsn = teacher.bsn
where scale = (select max(scale) from teacher);
-- Question 4: Give the list of students (names) and their assignments together with the number of prerequisites for each assignment.
-- Hint: There will be a list of students names, code of the assignment, code of the course of the assignment, total number assignments that are required to do the assignment.
-- write the query here
select name, surname, are_given.a_code, are_given.course_code, count(is_required_a_code)
from student join are_given on student.student_id = are_given.bsn
join assignment on are_given.a_code = assignment.a_code
join requirement on assignment.a_code = requirement.required_a_code
group by name, surname, are_given.a_code, are_given.course_code;
-- Question 5: Give a list of teachers (names) that are teaching courses, but do not work on assignments
-- write the query here
select distinct name, surname
from teach join teacher on teach.bsn = teacher.bsn
where teacher.bsn not in (select bsn from work_on);