I was given two problems with SQL to be written and database to be made in Access. Thats all well and good, the problem is the questions itself haha.
I have the following table:
Teacher (teacher_id, teacher_name)
Student (student_id, student_name, teacher_id)
Student_Subject (Student_id, subject_id, mark)
Subject (Subject_id, Subject_name)
Primary keys = underlined. Foreign keys = italic.
These are the following questions:
1 - Write an SQL statement to produce a listing showing the maximum mark obtained by each student (regardless of subject). Output student_id, student_name, make. Do this with a correlative subquery and only include those students who have done subjects.
2 - Repeat Q1 but include those students who have not done subjects. This will require a UNION. For those who have not done subjects list the subject_name as "no subject" and the mark as -99.
Those closest i got to the first query was the following:
The problem with that is it only produces one MAX (max of all the data in the fields) when it should be max mark per subject.
SELECT s2.subject_id, subject_name, s2.student_id, s1.student_name, mark
FROM student s1, student_subject s2, subject s3
WHERE mark=(SELECT MAX(mark) FROM student_subject WHERE s3.subject_id = s2.subject_id AND s1.student_id = s2.student_id);
Any help is appreciated. Thankyou.