REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 41: HOW TO RETRIEVE DATA FROM THE states TABLE

Now that we have populated the states Table, let’s see what data it contains. Of course, we could just do a SELECT * FROM states to see all of the data. Let’s do that first.

In DBWB, select Editors > SQL Editors... or select the F2 key on the keyboard.

Enter that query (SELECT * FROM states) within the Single SQL tab.

Click the “Run Query and Select All” (double green arrow) button, as shown here:

This will give you all of the data; here is a subset of it:

The various rankings are interesting to me. Note how Alaska is number 1 in size (square miles), but way down the list in both population and date of admission to the Union. I wonder if any States are in the top 10 in all categories? If not, how about top 20? 25? Actually, a quick scroll down the result set gives the answer to the first question: no State is top 10 in all 3 categories (size, population, and date of admission). So which State has the best overall “score”? Let’s alter the Table again, making a new column, which is the combined score of all three rankings. We can then loop through the data, add those three values for each State, and populate that new column.

First, though, let’s do a few more queries (SELECT statements). I will show the SQL statement (the Query) followed by the first 10 records returned from that Query.

SELECT * FROM states ORDER BY stateName

 SELECT * FROM states ORDER BY population2020 DESC

Do any of these surprise you? Personally, I was surprised to see that North Carolina is the 9th most populous State.

SELECT * FROM states ORDER BY dateAdmitted ASC

Any surprises here? Well, how many people know that Delaware was the first State? When I think of Delaware, I think of the old song (it’s even older than me) “What did Della Wear?” and, speaking of jaunty tunes, George Thorogood (and the Delaware Destroyers) also comes to mind. But first State? Nah... Yep! The State stats don’t lie. And note that Delaware is “the Alaska” of States when comparing its Rank Admission-Date-wise to the other two rankings. As Alaska is (by far) the largest State in terms of physical size, but far down the list as to population and when it became a State, Delaware is first at becoming a State, but near the bottom in the other two categories.

Photo of George Thorogood by Piedmontstyle at English Wikipedia / CC BY (https://creativecommons.org/licenses/by/3.0)

Note: By the way, the “ASC” (short for ASCENDING) appended to the SELECT statement is not necessary, but I added it to make clear that the dates are ordered from lowest (earliest) to highest/most recent. This differs from the Population query, which shows the values from largest to smallest population.

Another way of showing the top 10 States as to Population would be to run this Query:

SELECT * FROM states WHERE population2020 > 10000000

Notice anything surprising here? It returns the same ten Rows as the first ten returned from “SELECT * FROM states ORDER BY population2020,” but they appear in a different order here. Since we didn’t specify which order we wanted, the Database gave us the records in states_id order, not populationRank order.

To retrieve the same top ten Rows as we got with “SELECT * FROM states ORDER BY population2020,” we have to do it this way:

SELECT * FROM states WHERE population2020 > 10000000 ORDER BY populationRank

And another way to get the same results with a Query is this:

SELECT * FROM states ORDER BY populationRank LIMIT 10

So the top ten States population-wise and those States with more than 10 million residents turn out to be the same thing.

And still another way to get the same exact results would be:

SELECT * FROM states ORDER BY population2020 DESC LIMIT 10

In the last two examples, the “LIMIT 10” part appended to the Query retrieves the first 10 Rows from the Table that coincide to the first part of the Query. In one, populationRank (1..10) are returned. In the next, population2020 in descending order (high to low) are returned. But this is two ways of saying the same thing/getting the same results. The top 10 in population Rank are, by definition, the top 10 population-size-wise.

Note: The “moral of the story” of those last few Queries is: There’s more than one way to skin a potato. But, wait! Why would anyone ever want to skin a potato?!? The skin is the best part! Anyway, what I mean is, as in programming in general (and life in general?), there are usually several different ways of accomplishing the same thing. The way that you choose to accomplish your goals will depend on your knowledge, preferences, personality -- and perhaps be influecned by last night’s movie or dream or what you had for breakfast this morning!

Photo of Spuds being separated from their Dermis by eiliv-sonas-aceron, from unsplash.com

Another interesting thing we can do with SQL that we haven’t explored yet is finding Rows based on part of the value in one of the columns. For example, if you wanted to find all the States that begin with the letter “M” you would do this:

SELECT * FROM states WHERE stateName LIKE 'M%' ORDER BY stateName

...and that would give you this:

But what if you wanted to sort by something other than their name? Say the Date of Admission to the Union? This would be the ticket:

SELECT * FROM states WHERE stateName LIKE 'M%' ORDER BY dateAdmitted

That Query returns the same data as the previous one, but in a different order:

But...remember the plan to create a new Column to get the aggregate value of the three rankings (physical size, size in human souls, and age)? We can also accomplish something similar with the following query, which only includes the States within the top half (25) in all three ranking categories:

SELECT *  

FROM states  

WHERE areaRank < 26

AND populationRank < 26

AND dateAdmittedRank < 26

That Query returns only three Rows:

When combining the three counts, Georgia is the “winner” with 33 points (lowest point total wins – Illinois has 51 and Missouri 60). But Missouri is the most “well-rounded” State of all – it is in the upper half of all three categories, but is not in the top third in any of them! It is at or near the top of the middle third with all three scores. It is also in the middle of the Country, geographically speaking.

Side Note: Where would American culture be (the arts, particularly) without Missouri’s contribution? Included among those from the “Show-Me State” are Maya Angelou, Yogi Berra, Chuck Berry, Thomas Hart Benton, Walter Cronkite, Dick Gregory, Langston Hughes, John Huston, Dennis Weaver and -- last but certainly not least, The Ozark Mountain Daredevils of Springfield, Dick Van Dyke of West Plains and Samuel Clemens (“Mark Twain”) of Hannibal. Also my maternal grandfather, Albert Lee Benjamin Kollenborn, of DeWitt (1907-1984).

REFACTORING THE TABLE BY ADDING A NEW COLUMN

Let’s now go ahead and create that new column, so we can quantify which States are the “best” or most “well-rounded” States (being “jacks of all trades” or “jacks of all rankings” -- maybe we could call them “Union Jacks” (albeit not “Union Jacks” in the sense of the British flag)).

Actually, we can do this completely in SQL, with this:

SELECT stateName, areaRank, populationRank, dateAdmittedRank, areaRank+populationRank+dateAdmittedRank AS combinedRank

FROM states  

ORDER BY combinedRank

So we add (mathematically) the three rankings, put that computation into a new (temporary) column (combinedRank), and then display it sorted by that new column, so that we see the lowest (best) combined rankings first:

So Texas is top dog! That is due to their being number 2 in both size (after Alaska) and Population (after California).

In case you want to see where your State falls, and have not been “playing along,” here is the entire listing from that Query:

"Texas" areaRank="2" populationRank="2" dateAdmittedRank="28" combinedRank="32"

"Georgia" areaRank="21" populationRank="8" dateAdmittedRank="4" combinedRank="33"

"California" areaRank="3" populationRank="1" dateAdmittedRank="31" combinedRank="35"

"Pennsylvania" areaRank="32" populationRank="5" dateAdmittedRank="2" combinedRank="39"

"New York" areaRank="30" populationRank="4" dateAdmittedRank="11" combinedRank="45"

"North Carolina" areaRank="29" populationRank="9" dateAdmittedRank="12" combinedRank="50"

"Illinois" areaRank="24" populationRank="6" dateAdmittedRank="21" combinedRank="51"

"Florida" areaRank="26" populationRank="3" dateAdmittedRank="27" combinedRank="56"

"Michigan" areaRank="22" populationRank="10" dateAdmittedRank="26" combinedRank="58"

"Virginia" areaRank="37" populationRank="12" dateAdmittedRank="10" combinedRank="59"

"Ohio" areaRank="35" populationRank="7" dateAdmittedRank="17" combinedRank="59"

"Missouri" areaRank="18" populationRank="18" dateAdmittedRank="24" combinedRank="60"

"New Jersey" areaRank="46" populationRank="11" dateAdmittedRank="3" combinedRank="60"

"Tennessee" areaRank="34" populationRank="16" dateAdmittedRank="16" combinedRank="66"

"Massachusetts" areaRank="45" populationRank="15" dateAdmittedRank="6" combinedRank="66"

"Colorado" areaRank="8" populationRank="21" dateAdmittedRank="38" combinedRank="67"

"Arizona" areaRank="6" populationRank="14" dateAdmittedRank="48" combinedRank="68"

"Minnesota" areaRank="14" populationRank="22" dateAdmittedRank="32" combinedRank="68"

"Maryland" areaRank="42" populationRank="19" dateAdmittedRank="7" combinedRank="68"

"Oregon" areaRank="10" populationRank="27" dateAdmittedRank="33" combinedRank="70"

"South Carolina" areaRank="40" populationRank="23" dateAdmittedRank="8" combinedRank="71"

"Indiana" areaRank="38" populationRank="17" dateAdmittedRank="19" combinedRank="74"

"Alabama" areaRank="28" populationRank="24" dateAdmittedRank="22" combinedRank="74"

"Nevada" areaRank="7" populationRank="32" dateAdmittedRank="36" combinedRank="75"

"Washington" areaRank="20" populationRank="13" dateAdmittedRank="42" combinedRank="75"

"Wisconsin" areaRank="25" populationRank="20" dateAdmittedRank="30" combinedRank="75"

"Louisiana" areaRank="33" populationRank="25" dateAdmittedRank="18" combinedRank="76"

"Kentucky" areaRank="36" populationRank="26" dateAdmittedRank="15" combinedRank="77"

"Kansas" areaRank="13" populationRank="35" dateAdmittedRank="34" combinedRank="82"

"Connecticut" areaRank="48" populationRank="29" dateAdmittedRank="5" combinedRank="82"

"Iowa" areaRank="23" populationRank="31" dateAdmittedRank="29" combinedRank="83"

"Mississippi" areaRank="31" populationRank="34" dateAdmittedRank="20" combinedRank="85"

"Arkansas" areaRank="27" populationRank="33" dateAdmittedRank="25" combinedRank="85"

"Utah" areaRank="12" populationRank="30" dateAdmittedRank="45" combinedRank="87"

"New Mexico" areaRank="5" populationRank="36" dateAdmittedRank="47" combinedRank="88"

"Montana" areaRank="4" populationRank="43" dateAdmittedRank="41" combinedRank="88"

"Nebraska" areaRank="15" populationRank="37" dateAdmittedRank="37" combinedRank="89"

"Idaho" areaRank="11" populationRank="38" dateAdmittedRank="43" combinedRank="92"

"Oklahoma" areaRank="19" populationRank="28" dateAdmittedRank="46" combinedRank="93"

"New Hampshire" areaRank="44" populationRank="41" dateAdmittedRank="9" combinedRank="94"

"Delaware" areaRank="49" populationRank="45" dateAdmittedRank="1" combinedRank="95"

"Alaska" areaRank="1" populationRank="48" dateAdmittedRank="49" combinedRank="98"

"South Dakota" areaRank="16" populationRank="46" dateAdmittedRank="40" combinedRank="102"

"Wyoming" areaRank="9" populationRank="50" dateAdmittedRank="44" combinedRank="103"

"North Dakota" areaRank="17" populationRank="47" dateAdmittedRank="39" combinedRank="103"

"Maine" areaRank="39" populationRank="42" dateAdmittedRank="23" combinedRank="104"

"Vermont" areaRank="43" populationRank="49" dateAdmittedRank="14" combinedRank="106"

"Rhode Island" areaRank="50" populationRank="44" dateAdmittedRank="13" combinedRank="107"

"West Virginia" areaRank="41" populationRank="39" dateAdmittedRank="35" combinedRank="115"

"Hawaii" areaRank="47" populationRank="40" dateAdmittedRank="50" combinedRank="137"  

So I’m a data geek. Now you know.

On that confession, I’d say it’s a good time to sign off for today. In the next Step, we will permanently add the combinedRank column to the Table, and compute its contents/value with C# in our Utility app, updating the states Table again, this time so that the “combinedRank” values shown above are housed in the Table itself.

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: