Joining and matching tables in SQL with query

Question

So im trying to select multiple tables and do a comparison between starNumb, mvNumb from Star table and Movie table, if the two match I then want it to return / display the starName along with a total count of how many times that StarName is related to mvNumb if that makes sense.

So far I have this and it's not playing ball, I'm sure this is something very simple but struggling.

SELECT STARNUMB.STAR, MVNUMB.MOVIE
FROM MOVIES JOIN STAR
where STARNUMB = MVNUMB;

What I want from this is something that looks like this below as an output.

Column1 StarName = TestName1

Column2 MovNumb = 1

Meaning that star has appeared in the movie 1 x time.

The tables and FD's look something like this:

Bold = Primary keys

• Star(starNumb, starName, brthPlce, starBorn, starDied)
• MovStar(mvNumb*, starNumb*). <----- foreign keys
• Movie(mvNumb, mvTitle, yrMde, mvType, crit, mpaa, noms, awrd, dirNumb*)
• Borrow(txNumb, mvNumb*, borDte, mmbNumb*)
• Member(mmbNumb, mmbName, mmbAddr, mmbCty, mmbSt, numRent, bonus, joinDate)
• Director(dirNumb, dirName, dirBorn, dirDied)**

Answer 1

There's an n-to-m relation between stars and movies which results in a bridging table MovStar, you need to add this to the join. But as long as you only want to count the number of movies per star you need to join only Movies and MovStar:

SELECT m.starName, count(*)
FROM MOVIES m JOIN MovStar ms
  on m.STARNUMB = ms.starNumb
group by m.starName
order by 2 desc