Question

schema:
Student( studID, name, major )   // dimension table, studID is key
Instructor( instID, dept );   // dimension table, instID is key
Class( classID, univ, region, country );   // dimension table, classID is key
Took( studID, instID, classID, score );   // fact table, foreign key references to dimension tables

Write a SQL query to find all students who took a class from an instructor not in the student's major department and got a score over 80.

Return the student name, instructor name, and score. ,

Please describe in sentences what you need to do to find this information

Answer 1

Note:name is missing in Instructor table,so we will add name column to Instructor table.

For this query You have to follow the steps as:

step-1.First of all you need the information from more than one table.Because student name is in student table,Instructor name is in Instructor table and score is in took table.so,You have to join 3 tables.

step-2.For joining tables you have to find same columns in two tables.Here.Student and Took table has same column StudID .Instructor and Took has same column InstID.

step-3.Now,as given in the question,Your conditions will be

1).Major of student and department of instructor should not be equal &

2).Score should be above 80

step-4.From this result you have to SELECT studID from Student table,instID from Intructor table and score from Took table.   

If you want SQL query for the same,Here it is:

SELECT Student.name,Instructor.name,Took.score

FROM ((Student INNER JOIN Took ON Student.studID=Took.studID)INNER JOIN Instructor ON Took.instID=Instructor.InstID)

WHERE Student.major != Instructor.dept

AND Took.score > 80;