REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 51B: QUIZZES & QUERIES & CONUNDRUMS GALORE

(continued from Step 51A)

1) Rather than have the name spread out over four columns (first name, middle name*, last name, and suffix**), how about combining it all into one column?

* Which is often blank

** Which is almost always blank

2) Rather than have Column labels in the result set that are the same as the Database column Names (which are only fit for geeks and propellerheads and such to look at), why not give them a more user-friendly name?

3) Last in this list but not least in the hearts of suffering mankind everywhere, how about 86ing the extraneous zeroes after the imdb_rating values? In other words, make them display as “8.1,” for example, rather than “8.1000”?

Note: To “86” something is to get rid of it. For example, if somebody were to look in the refrigerator and ask you, “Hey, what happened to the Apple Pie?” you could, if feeling brash, respond “I 86’d it.” If, on the other hand, you sense a certain danger in answering the question, you could 86 yourself from the house by diving out the window, with our without the sash.  

Let’s take these challenges, as is our wont and is logical, in order.

First, the combining of the four “Name” columns into one. We can take care of that this way:

By using Concat_WS(‘ ’, and following that up with the names of the columns that we want to “Concat” (Concatenate, or combine), it does just what we wanted it to do.

Now for the Column Names to be more Bohunkish, we can just extend what was already done above, using the AS to provide a new Column title, like so:

Finally, can we strip out those superfluous, extraneous 0s from the imdb_rating column?

Yes! By changing “M.imdb_rating AS POPULAR VOTE” to “trim(round(M.imdb_rating,1)) AS 'POPULAR VOTE'” as shown here:

So! We are getting close to the end of the Database-centric stuff here on Real-World Computer Programming for Kids of All Ages. We still have a little bit to go, though; in the next Step, we will talk about Triggers, Stored Procs, and using a certain set of Table columns to catch crooks in the act.

Until then!

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: