/*=====================================================================*/
/***  Drill across multiple fact tables                              ***/
/*=====================================================================*/
/* I wonder if instructors assign higher grades if they are paid more  */
/*=====================================================================*/
/* I want to query both fact tables, the grade and payment fact tables */
/* - Query the grade fact table,   and average the grades              */
/* - Query the payment fact table, and average or sum the payments     */
/* - Join the above 2 queries on instructor_id                         */
/*=====================================================================*/

SELECT instructor_id, g.fname, g.lname, pay, ROUND(avg_grade,2) 
FROM
    (SELECT instructor_id, fname, lname, AVG(grade) AS avg_grade 
       FROM instructor NATURAL JOIN grade
      GROUP BY instructor_id, fname, lname) g
JOIN 
    (SELECT instructor_id, fname, lname, SUM(gross_pay) AS pay 
       FROM instructor NATURAL JOIN payment
      GROUP BY instructor_id, fname, lname) p 
USING(instructor_id)
ORDER BY pay