An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
, …

Since we just released Galera Manager, it seemed like a good idea to do a deployment on Amazon Web Services (AWS) using Red Hat Enterprise Linux 8 and MySQL 8.

We start by launching an EC2 instance and choosing Red Hat Enterprise Linux 8 (HVM) and most importantly, we only choose the 64-bit (x86) architecture as we do not support ARM just yet. We choose a t2.micro instance for Galera Manager since this is just a test instance, but it is well worth knowing that 10GB which is the default EBS size may be a little small if you plan on using this for sometime.

…

Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his …

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

• Submits credentials from a file and raises an error when they don’t work.
• Initializes a …

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );

INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM( …

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.

The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.

You build the little model with the following …

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 …

About a month ago, I published how you can connect to MySQL with a small form. One suggestion, or lets promote it to a request, from that post was: “Nice, but how do you create a reusable library for the MySQL Connection Dialog box?”

That was a good question but I couldn’t get back until now to write a new blog post. This reusable MySQL connection dialog lets you remove MySQL connection data from the command-line history. This post also shows you how to create and test a Powershell Module.

The first step to create a module requires that you set the proper %PSModulePath% environment variable. If you fail to do that, you can put it into a default PowerShell module location but that’s not too effective for testing. You launch the System Properties dialog and click the Environment …

Many of my students wanted to know how to write a simple PSM (Persistent Stored Module) for MySQL that saved the writes to all table as a group. So, to that end here’s simple example.

1. Create four sample tables in a re-runnable script file:

   /* Drop and create four tables. */
   DROP TABLE IF EXISTS one, two, three, four;
   CREATE TABLE one   ( id int primary key auto_increment, msg varchar(10));
   CREATE TABLE two   ( id int primary key auto_increment, msg varchar(10));
   CREATE TABLE three ( id int primary key auto_increment, msg varchar(10));
   CREATE TABLE four  ( id int primary key auto_increment, msg varchar(10));
   

2. Create a locking PSM across the four tables:

   /* Conditionally drop procedure. */
   DROP PROCEDURE IF EXISTS locking;
   
   /* Set delimiter to $$ to allow ; inside the procedure. */
   DELIMITER $$
   
   /* Create a transaction procedure. */
   CREATE PROCEDURE locking(IN pv_one   varchar(10)
                           ,IN pv_two …

Now that we’ve migrated to MySQL for our core database course, I’m building MySQL Workbench analysis problems. We start with a lecture trying to flush out a simple address, and then ask them to figure out how to link it to a customer table.

Designing it, I emphasized how it resolves the issue of a city occurring in multiple counties and states, like Fremont. Fremont occurs 17 times in the US and once in Haiti:

Naturally, I left two other design issues in the problem. I’m working through it for the first time with classes tomorrow. I hope it works well. Any comments?