My queries aren't working

Question

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

score 1 · Answer 1 · answered Jun 22 '16 at 06:20

1

a) SELECT s.SID, s.Name, s.Faculty, r.Year, r.Term, r.UnitCode, r.Grade FROM Student as s inner join Result as r on s.SID=r.SID WHERE s.Faculty = 'Business'

answered Jun 22 '16 at 06:20

Rakesh

11
4

score 0 · Accepted Answer · edited Jun 22 '16 at 02:37

0

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) .

edited Jun 22 '16 at 02:37

ArturoAP

432
2
13

answered Jun 21 '16 at 22:39

Mak R

56
3

My queries aren't working

2 Answers2