REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 36: BASIC SQL and BEAR CLAWS

The last Step provided an introduction to Databases. The next thing to learn is some examples of the language used to talk to databases, SQL. After that (in the following Step), we will go into the actual creation of a Database, which we will use to demonstrate practical uses for the various SQL commands.

The key SQL statements we will use are:

SELECT

INSERT

UPDATE

DELETE

SELECT

Select is used to query the Database. For example, this SQL statement:

SELECT * FROM STATES

...is the simplest and most straightforward of all possible queries. The “*” stands for “All” or “Everything,” so what the statement above is saying to the Database is, “Give me everything from the table named States.” The results returned will be just what was requested – perhaps even including some columns the user of the data doesn’t really want or care about -- or even knew existed, or understand why they exist.

Note: The SELECT statement above, besides using the “*” symbol and the name of the Table (“STATES”), also uses the keyword FROM. The FROM keyword is what is used in SQL to indicate which Table orTables in the Database you want to read from. Recall that a Database normally has multiple Tables. You must already have a Connection to the Database you want to use before issuing a SELECT (or any other) statement. But we’re getting ahead of ourselves (or, to be honest, I’m the one getting ahead of us); so, more on this later.

So what if you want to limit the results to only include certain columns rather than all of them? In that case you would issue the command:

SELECT [ comma-separated list of columns ] FROM States

For example, it could be:

SELECT Name, AreaSqMi, Population2020, DateAdmitted FROM States

This would limit the returned column values to those explicitly listed, rather than all (*) of them.

Note: It’s a good habit to get into to not use “*” just because it’s quick and easy to do so, as it can slow down Database performance (not just for you, but also for other people using the Database).

You can also limit the returned Data set to just what you are really interested in by using the WHERE clause (“Where clauses” differ from Bear Claws in that they are not as scratchy as the real ones, and not as tasty as the fake ones).

Photo of “Bear Claw” pastry by Glane23 / CC BY-SA (https://creativecommons.org/licenses/by-sa/3.0)

Limiting the returned data using a Where clause filters the data to only return data for a few States. For example, if you just wanted to see data for the States with a population of more than 10,000,000 residents, you could use this SQL statement:

SELECT Name, AreaSqMi, Population2020, DateAdmitted FROM States WHERE Population2020 > 10000000

Note: As you may have noticed above, you should not put commmas in numbers when talking to the database (or in programming in general). The Database would not understand what sort of trickery you were up to and refuse to show you any data at all, wagging its virtual finger at you and telling you that you created a syntax error. To avoid that, just add the correct number sans commas (without the commas), as shown: 10000000 (not 10,000,000).

One more thing you might want to do at this point is to sort the results. You can do this by using ORDER BY in your SQL, like so:

SELECT Name, AreaSqMi, Population2020, DateAdmitted FROM States WHERE Population2020 > 10000000 ORDER BY Population2020

The SQL query above will display the records returned ordered in the default way, which is ASC (Ascending). In other words, the State with the lowest population among this limited group (“this limited group” meaning the States with more than 10 million residents) will be first, at the top of the list, and the one with the greatest population will be last (in this particular list). If you want it the other way around (the State with the largest population first, the State with the second-largest population second, etc.), then you need to append DESC (which is short for “Descending”) to the statement, like so:

SELECT Name, AreaSqMi, Population2020, DateAdmitted FROM States WHERE Population2020 > 10000000 ORDER BY Population2020 DESC

If you really do want the data in ASC (Ascending) order, you might still want to explicitly append an ASC, even though it isn’t required, as a way of “commenting your code” to show that yes, you really do want to see the default Ascending view (lowest to highest), not Descending (highest to lowest).

Our SQL statement may look like a bit of confusing, jumbled mess to you now. And actually, most SQL is written like the following, with each portion of the statement on its own line:

SELECT Name, AreaSqMi, Population2020, DateAdmitted  

FROM States

WHERE Population2020 > 10000000  

ORDER BY Population2020 DESC

Our SQL statement now limits the number of columns returned (by not using the “*” after SELECT), limits the rows to States where the population is over 10 million, and sorts the result set of data for you by the column you designated and in the direction you prefer (ascending or descending).

INSERT

The next key SQL statement is INSERT. Its name may make it clear what it does – it adds, or inserts, a record into a Table.

By way of example, an INSERT command for the Table we’ve been using could be:

 INSERT INTO States  

 (Name, AreaSqMi, Population2020, DateAdmitted)

 VALUES  

('California', 163696, 39512223, '1850-09-09');

The INSERT statement above inserts a record (a row full of values) into the Table named on the first line (States). The values listed on the last line of the statement are inserted into the columns listed in the second line of the statement. Note that the order of the columns listed on line two doesn’t have to match the order in which the columns appear in the Database, but the order of values on the last line must match the order that you specified on the second line.

If you were to put a value in the wrong spot, it may seem to “work” but “pollute” the database with bad data. For example, if you did this instead:

INSERT INTO States  

 (Name, AreaSqMi, Population2020, DateAdmitted)

 VALUES  

('California', 39512223, 163696, '1850-09-09');

...the record would successfully be inserted into the Table, but the data would be wrong, as the square miles (AreaSqMi) column contains the population figure, and the population column (Population2020) contains the square miles value. Any other mismatches would just fail and not allow the record to be inserted. The Name column would accept ‘1850-09-09’ but would, of course, be a bogus name for a State.

Note: This brings to mind the acronym GIGA, which has nothing to do with Gigabytes, but stands for “Garbage In, Garbage Out.” In other words, if you put bad data into your database, that is what you will get out of it (when you query it). The computer has no brain. The database has no brain. If you put a value in the wrong column (or “field” as it’s sometimes called), that is the value it will return to you. It won’t know that the data is wrong (unless you apply restrictions for what range of values are inserted into a column, and it “catches” the bad data that way). But that’s a more advanced topic.

If you tried to put the Name value where the int values belong, it would not work. If you tried to put the int values where the string/Text values belong, it would not work, either. The record wouldn’t be Inserted, because there would be a data type mismatch. For example, this would fail:

INSERT INTO States  

 (Name, AreaSqMi, Population2020, DateAdmitted)

 VALUES  

(39512223, 'California', 163696, '1850-09-09');

...because a value that is not enclosed within single quotes would not be accepted in the Name column (the first one), and a value with single quotes would not be accepted in the AreaSqMi column (the second one).

UPDATE

Sometimes actual data changes (in the “real world,” that is, not just inside the database). When that happens, you need to update existing data in your Database Table or tables. You could do a DELETE of the old record and then an INSERT of a new one to replace the one you relegated to the dustbin of antiquity, but it’s usually more sensible to simply UPDATE the existing record. It may be that there is only one value that needs to change, or that you want to retain the record’s ID value, or have some other reason for preferring Updating over Deleting the old and then Inserting a new record.

Some common examples of when you would UPDATE a record is when the Table contains an address or phone number which has changed. Or say a person changed his name, such as from Cassius Clay to Muhammad Ali, or from Lew Alcindor to Kareem Abdul-Jabbar.

Or, in our case, perhaps the name of a State changed. We would take care of that this way:

 UPDATE States

 SET Name = ‘Twainiana’

 WHERE Name = ‘Missouri’

In the UPDATE statement the WHERE clause holds the old/existing value, and the SET clause contains the new/updated value. In English, what you are telling the Database with the statement above is, “Find the State named Missouri, and change its name to Twainiana.”

DELETE

The Delete statement can be dangerous. Use it with care and caution. It does exactly what you would expect – it deletes data. It deletes the row or rows you tell it to – even all the rows in a Table, if you’re not careful.

Here is an example. Let’s say you mistakenly thought that Puerto Rico was the 51st State, and you added it to the States Table. Now you find out that you were mistaken, and you want to get rid of that record. You would do so this way:

 DELETE  

 FROM States

 WHERE Name = ‘Puerto Rico’;

With that SQL statement, just that one out-of-place record would be deleted. The other 50 would not be touched.

Note, though, that if you forgot to specify a WHERE clause for your DELETE command, you would obliterate all of the data in the States Table. The following is very “destructive” and dangerous in that sense:

 DELETE  

 FROM States;

This would delete all of the rows in the States Table. Beware.

Note: Reality check! In most cases, the data you “oblierated” still exists somewhere, in a Database backup. If you ever accidentally delete a lot of data and don’t know how to restore it, go see the Database Administrator (which is sometimes somebody’s full-time job, but often just one of the “hats” that some developer has “volunteered” to wear).

So of the four main SQL statements or commands (besides the “CREATE”s which we will cover in the next Step) -- namely SELECT, INSERT, UPDATE, and DELETE -- the only one that does not change the contents of the Database is the first one we discussed, SELECT. The SELECT statement only “borrows” a copy of a subset of the data to look at; the other three respectively add (INSERT), change (UPDATE), or remove (DELETE) data.

In the next Step of our Journey down life’s highway to computer programming proficiency, we will create the so-far hypotheticcal Database and its States table and get to work populating it with data and then slicing and dicing it, and even adding Calculated (run-time) values to it -- Until then!

Photo by Thinh Nguyen, from unsplash.com

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: