Search code examples
sqlsubquerylibreoffice-base

My queries aren't working

enter image description here

Hey everyone I'm trying to do these 2 queries but I can't seem to get them right. I'm not sure what I'm doing wrong, can anyone help me?

a. List all results for all students from the Faculty of Business. The query result will display SID, Name, Faculty, Year, Term, UnitCode, Grade

What I have so far:

SELECT 
    Student.SID, 
    Student.Name,
    Student.Faculty,
    Result.Year,
    Result.Term,
    Result.UnitCode,
    Result.Grade
FROM Result, Student
WHERE Student.Faculty = 'Business'

When this executes it prints repeating data, how can I fix this?

b. List all the results of all students group by Year, Term and UnitCode. The query result will display SID, Name, Faculty, Year, Term, UnitCode, Grade.
What I've got so far:

SELECT
    Student.SID,
    Student.Name,
    Student.Faculty,
    Result.Year,
    Result.Term,
    Result.UnitCode,
    Result.Grade
FROM Result, Student
GROUP BY Result.Year, Result.Term, Result.UnitCode
Solution

  • a. You have to join both Student and Result table like 

    SELECT * FROM
Result r JOIN Student s ON r.SID = s.SID
WHERE s.Faculty = 'Business'

    b. The question is not clear or the expectation is wrong. You cannot display columns like ( Sid, name etc) , but only the columns in Group clause or aggregate functions ( like Sum(Sid) .