MySQL Query using foreign key relationship

Question

I am learning MySQL and I am trying to make simple arithmetic in the query, I am trying to write a command that would determine the number of Instructors in each Faculty in the university Can anyone help me in this? Thank you!

Here below are my two tables:

    ________________________________________________________
    |    Department       |    Faculty         |  Building  |      
    --------------------------------------------------------        
    |    Humanities       |  Arts and Sciences |  Chardon   |
    |     English         |  Arts and Sciences |  Chardon   |
    |   Mathematics       |  Arts and Sciences |  Monzon    |
    | General Engineering |    Engineering     |  Stefani   |
    |     Agronomy        |    Agriculture     |  Pinero    |

     _________________________________________________________________________
    |  Professor  |      Department        |      Rank           |  Salary    |
    ---------------------------------------------------------------------------
    |  Joe Blow   |         Biology        |     Professor       | $73,500.00 |
    |  Sam Snow   |       Mathematics      |     Instructor      | $45,700.00 |   
    | George Grow | Electrical Engineering | Associate Professor | $69,250.00 |
    | Hiram Lowe  |         English        | Assistant Professor | $63,750.00 |

maraspin · Accepted Answer · 2013-05-11 23:53:28Z

1

Something close to this:

select department.faculty, count(professor.professor) as instructors from department join professor on department.department = professor.department group by department.faculty

Basically you do a join to "merge" the two tables, then group results by faculty, and count professors for each faculty.

answered May 11, 2013 at 23:53

maraspin

2,43321 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query using foreign key relationship

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related