REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 51A: QUIZZES & QUERIES & CONUNDRUMS GALORE

Los datos se han completado. Ahora lo consultaremos.

That was for the Spanish speakers – or readers, I should say.

The data has been populated. Now we will query it.

We only have 21 Records in our Tables, but it will still be interesting and valuable to see how we can filter and sort the data we do have using SQL Statements.

To decide what it is that we want to see, we need to keep in mind what data we have – that is, which columns? They are the following (in human language, not the terse wording we used in naming the columns):

Name, or Title, of the Movie

What type of movie it is (Genre or Genres)

Who plays in it (Actors)

What it’s rated (is it suitable for children? That is, is it rated G, or PG, or even PG-13, maybe?)

What other viewers thought of it (The movie’s IMDB Rating, which is an average of all ratings that august body receives)

The length of the movie (How long is it – not in miles, but in minutes?)

When the movie was made (Is it new, or old? How new? How old?)

I think we can assume that, when we are searching for movies that we might want to watch, of all those pieces of data, the one we will need the most is the Title. But the Title is probably not something we would normally search on – unless we already have a particular movie in mind and we just want to see the data for it – we may be curious about who wrote the screenplay or some other detail about it.

So let’s do that first – that is, look for a movie by its Title. Now bear in mind that sometimes there are multiple movies with the same title. In the case of our data, though (21 Records), that’s not the case. So when we search for the name or title of a movie, we will get either 0 or 1 Records back. If we get 0, it means one of two things:

1) That movie is not among our 21

-or:

2) We misspelled it.

Let’s try each kind of query implied above. First, the search for a particular movie:

So this gives us some information (this is all the data in the movies_main Table, as the SQL query shows).

But it leaves us a little in the dark, or even baffled. We might wonder, What does the movies_id column give us? Who was the Director? All we see is an ID value for him or her. Who was the Screenwriter? The same situation – just an ID is shown. Who were the Actors? What is or are the Genre/Genres the movie is categorized by?

So let’s answer these burning questions with some more SQL, as we proceed. We’ll now answer the question “Who was the Director?”

To find that out, we have to look him up in the directors table, like so:

So we see that the Director of the 2019 version of Little Women was Greta Gerwig. By the way, I don’t care if you’re man, woman, child, or a Duckbilled Platypus, you should watch this flick if you enjoy a good story and enjoy being “transported” back in time to another era and place. Normally, when a movie jumps back and forth in time, I am quickly “lost” as to what order things are taking place in, but Ms. Gerwig must be a genius, because I always knew exactly “where I was” (in the stream of time) watching her masterpiece.

But we don’t want to have to jump through those kinds of hoops to discover who the Director was every time, right? We want to see the data all together in one place. So let’s try this, where we take advantage of the relation between our tables:

This is probably the most complicated query we’ve done so far. Let’s break it down, people!

SELECT M.movie_title, M.mpaa_rating, M.imdb_rating, M.movie_length, D.first_name, D.middle_name, D.last_name, D.suffix

 FROM movies_main M, directors D

 WHERE M.movie_title = 'Little Women'

 AND M.director_id = D.director_id

Here is where we see in action the “relational” characteristic of the way this database was designed. We can pull data together from multiple Tables because they are related by a common value, namely (in this case) director_id.

Do you see the “M.” and “D.” prefixes to the column names? They are declared in the “FROM” part of the SQL. “M” is the “alias” for the Main (movies_main) Table, and “D” is the “alias” for the Directors (directors) Table. So we pick which columns from “M” that we want to view, and which columns from “D” that we want to view, in the SELECT portion of the Statement. The keys (no pun intended) to bringing together the related data is the WHERE clause, where we are stipulating that we want to grab the director_id value from the “M” Table where the movie’s title is “Little Women.” But then the relational part comes in with the “AND” part of that Where clause, where it’s saying, in effect, “Get the “D” values from the record which has the same director_id value as the one in the “M” Table for that particular movie.”

Pretty downright cool, hey, daddy-o? Or kiddy-o?

Now on to the next question:

Who was the Screenwriter?

We’re dealing with the same situation here as we did with Director. So can you “suss out”* how the SQL needs to change to pull in the Screenwriter’s name, too?

* Note: To “suss out” is another Britishism. It means to “figure out” something based on clues provided or evident.

We need to add a relational piece from movies_main to screenwriters, too, something like this:

Wallaby! It worked! But how did it work? I will bold what changed (was added) between the last Query and this one:

SELECT M.movie_title, M.mpaa_rating, M.imdb_rating, M.movie_length,  

D.first_name, D.middle_name, D.last_name, D.suffix,

S.first_name, S.middle_name, S.last_name, S.suffix

FROM movies_main M, directors D, screenwriters S

 WHERE M.movie_title = 'Little Women'

 AND M.director_id = D.director_id

AND M.screenwriter_id = S.screenwriter _id

So we invited another Table to the party (screenwriters), and drew on its relation to the main Table via the screenwriter_id column’s value.

But wait! Look at the data! Is it correct? It indicates that Greta Gerwig was not only the Director (in the first set of name columns), but also the Screenwriter (second set of name columns, which have an “_1” appended to them, to differentiate them from the “plain” set of column names for Director.

By looking at the Screenwriter Table you can see that yes, Ms. Gerwig wore both hats: she was Director and Screenwriter. I told you she was a genius (she is also an actor (as well as a homo sapien)). Of course, the author of the book (“Little Women”) was named Louise June Apricot, or something like that, but she couldn’t help what her parents named her.

The next question bellowing forth from a million throats is, Who were the Actors in said Flick?

Can you guess what we might need to add to the SQL to get that information? We will need to pull in the actors Table, right? Buuuuuuut ... there is no actor_id column in the Main (movies_main) Table. So how do we make the connection? What’s the relation?

Does the actors Table have a movie_id column that we can use to connect these two Tables? No. So what, then? How about this:

The movies_main Table has a movies_id column; the actors Table has an actor_id column. So is there a Table that contains both of these columns? The answer is ... drum roll rumbles on, with much bated breath (or baited breath, if they want to go fishing later) among the audience ... YES! The Many-to-Many Table actors_movies has both of those columns. So we can combine data from the movies_main Table and the actors Table via this “middle man,” the Many-to-Many Relational Table.

So how can we alter our SQL to grab this data? While you think about it, I will write it ... Here it is:

If you know the movies_id value (from the movies_main Table), you can do it as I did above, making that the first part of the WHERE clause. The movies_id value is the only part of the SQL you will need to change in order to update the results.

Next Question, you’re up!

What is or are the Genre/Genres the movie is categorized by?

This is a similar problem as showing all the Actors in a given movie. There is another Many-to-Many Table, movies_genres that will help us here:

This is the same idea as the previous code showing the Actors, but this time we are leaving out everything from the main table (movies_main) except for the Title.

Other movies have three Genres assigned to them:

...or sometimes only one:

On to the next Question: How can I filter the Database by MPAA rating?

If you want to choose among movies with a particular MPAA rating (G, PG, or PG-13), you can get this:

...or this (showing the first ten movies, in order of length in minutes (from shortest to longest):

...or this:

Next Question: How can we filter the movies in the Database by how popular they were amongst viewers, in other words “by poplar vote?” There are two ways: we can sort, from highest to lowest, or we could actually filter out those below a certain real number that we specify. I’ll demonstrate both ways of doing it. First the sorting by most popular on the top to least popular on the bottom:

In the above, we did not filter the data -- all the records display, with the WHERE clause simply tying the directors and screenwriters Tables to the main table (movies_main) -- but we are sorting (ORDERing) the result set. And we are doing so DESC (Descending), so the Records display from highest (best-rated by viewers) to lowest.

Now we will show just the top few Records by filtering out those below a certain threshold:

The first Records returned with that SQL were the same as those from the previous Query. However, as to the rest of the data, it was constrained by the “WHERE M.imdb_rating >= 8.0” part of the SQL. In plain English, it is in effect saying, “Show me only the Records where the IMDB Rating was 8.0 or better/greater.”

The final question (before we turn to some “housekeeping” or formatting issues) is:

When was the movie made?

Here, too, we’ll show two options: Maybe you want to see a movie filmed in a certain year for some reason. We will search for that. Alternatively or perhaps additionally, you want to see all the movies between a range of years, say from 1997 to 2012.

Here is how we search for a movie made in a particular year (here we’re showing 2019 as the chosen year):

Now we will search for movies made within a range of years, specifically, as earlier mentioned, from 1997 to 2012:

Now, as they (used to) say, “Meat first and spoon vittles to top off on!”

Now that we’ve got the data we want, how about its appearance – what do you make of it? What’s your opinion about it? I dare say in some respects it is a multitide of times more unseemly than a carton of delapidated odds-and-ends and stuff.

I have three complaints in particular, but we will have to deal with those in the next step.