Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: MS Access

  1. #1
    New to the CF scene
    Join Date
    Jun 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation MS Access

    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:
    Code:
    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);
    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.

    Any help is appreciated. Thankyou.

  • #2
    Banned
    Join Date
    Jun 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    www.surveylover.com

    Create free online surveys and forms with surveylover.com


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •