As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

This is the first Python program:

# Import the library.
import sys
import …

I found myself explaining the nuances of INSERT statements and whether you should use named or positional notation. While the class was on Zoom, I could imagine the blank stares in the silence of my headphones. Then, I had to remind them about mandatory (NOT NULL constrained) and optional (nullable) columns in tables and how an INSERT statement requires an explicit NULL value for optional columns when the INSERT statement isn’t inserting a value into that column.

Then, I asked if somebody could qualify the different types of INSERT statements; and what would happen if a table with a first_name and last_name column order evolves when a new DBA decides to restructure the table and uses a last_name and first_name column order in the new table structure. Only a couple of the students recalled using a column-list …

The first tutorial supplementing the MySQL Connector/NET Developer Guide showed you how to connect and run static INSERT statement. It was a barebones PowerShell script with the MySQL Connector. This post shows you how to run a PowerShell script that uses a dynamic form to gather the MySQL credentials and then run a static query. Below is the MySQL Credentials form.

You enter the correct user name, password, hostname (or IP address), port, and database, like this:

Here’s the complete code for this staticQuery.ps1 PowerShell script:

# Add libraries for form components. …

I’ve been using MySQL 8 on Linux for a couple years but the Linux repository version didn’t install the new MySQL Shell. So, I discovered the new MySQL Shell when I installed MySQL 8 (8.0.21) on Windows to teach my student how to use it to learn SQL commands. I encourage you to read the full MySQL Shell document.

The following is a tutorial to provision a student user and studentdb database in MySQL. It uses the MySQL Shell (mysqlsh) and stages for uploads of comma-separated values files.

After installing MySQL on the Windows 10 OS, open the Window OS Command Line Interface (CLI) shell by entering the following in the search field at the bottom left:

cmd

It launches a CLI interface to the Windows OS. The cmd (command) utility opens the CLI in the following …

These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:

1. Configure your Node.js development directory.
2. Build a small application to test a MySQL connection.
3. Build a small application that connects to the MySQL database and queries data.

This blog post assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. Then, create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory. You can use the following command …

A student posed the question about why table names are case sensitive. That’s because case sensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:

SELECT CASE
         WHEN @@lower_case_table_names = 1 THEN
           'Case insensitive tables'
         ELSE
           'Case sensitive tables.'
         END AS "Table Name Status";

The default value returned on Linux is:

+------------------------+
| Table Name Status      |
+------------------------+
| Case sensitive tables. |
+------------------------+
1 row in set (0.00 sec)

The default value for the lower_case_table_names value on the Windows OS is 1 not …