MacLochlainns Weblog

The purpose of this example shows you how to navigate a list with a sparsely populated index. This can occur when one element has been removed after the list was initialized. Unlike Oracle’s VARRAY (array), removing an element from a TABLE or list does not re-index the elements of the list.

This example also shows you how to coupled lists. The outer loop increments, notwithstanding the gap in index values, while the inner loop decrements. The upper range of the inner loop is set by the index value of the outer loop.

The example program uses an abbreviated version of the Twelve Days of Christmas, and I’ve tried to put teaching notes throughout the example file.

DECLARE
  /* Create a single column collection that is a list strings
     less than 8 characters in length and another of strings
     less than 20 characters in length. */
  TYPE DAY   IS TABLE OF VARCHAR2(8);
  TYPE verse IS TABLE OF VARCHAR2(20);
 
  /* Create variables that use the user-defined types:
  || =================================================
  ||  1. We give the variable a name of lv_day and lv_verse.
  ||  2. We assign a user-defined ADT (Attribute Data Type) collection.
  ||  3. We assign a list of value to the constructor of the list, which
  ||     allocates memory for each item in the comma-delimited list of
  ||     string.
  */
  lv_day   DAY   := DAY('first','second','third','fourth','fifth');
  lv_verse VERSE := verse('Partridge','Turtle Doves','French Hen'
                         ,'Calling Birds','Gold Rings');
 
BEGIN
  /*
  ||  Remove an element from each of the two lists, which makes the two
  ||  lists sparsely indexed. A sparsely indexed list has gaps in the
  ||  sequential index of the list.
  */
 
  lv_day.DELETE(3);
 
  /*
  ||   Loop through the list of days:
  ||  ===================================================j
  ||   1. A list created by a comma-delimited list is densely populated,
  ||      which means it has no gaps in the sequence of indexes.
  ||   2. A list created by any means that is subsequently accessed
  ||      and has one or more items removed is sparsely populated,
  ||      which means it may have gaps in the sequence of indexes.
  ||   3. A FOR loop anticipates densely populated indexes and fails
  ||      when trying to read a missing index, which is why you should
  ||      use an IF statement to check for the element of a list before
  ||      accessing it.
  ||   4. A COUNT method returns the number of elements allocated memory
  ||      in a list of values and the LAST method returns the highest
  ||      index value. The index value is alway an integer for user-defined
  ||      ADT (Attribute Data Type) collections, but may be a string for
  ||      an associative array or a PL/SQL list indexed by a string.
  ||   5. Removing an element from a list does not change the other
  ||      index values but does if you create an array (or varray), which
  ||      means COUNT OR LAST may cause the same type of error for a list
  ||      with a missing element.
  */
 
  FOR i IN 1..lv_day.LAST LOOP
 
    /*
    ||  Verify the index is valid.
    || ====================================================
    ||  You check whether the element is present in the
    ||  list.
    */
 
    IF lv_day.EXISTS(i) THEN
 
      /* Print the beginning of the stanza. */
      dbms_output.put_line('On the ['||lv_day(i)||'] of Christmas ...');
 
      /* Print the song. */
      FOR j IN REVERSE 1..i LOOP
        /* Check if the day exists. */
        IF lv_verse.EXISTS(j) THEN
          /* All but first and last verses. */
          IF j > 1 THEN
            dbms_output.put_line('-   ['||lv_verse(j)||']');
          /* The last verse. */
          ELSIF i = j THEN
            dbms_output.put_line('- A ['||lv_verse(j)||']'||CHR(10));
          /* Last verse. */
          ELSE
            dbms_output.put_line('and a ['||lv_verse(j)||']'||CHR(10));
          END IF;
        END IF;
      END LOOP;
    ELSE
      CONTINUE;
    END IF;
  END LOOP;
END;
/

As always, I hope it helps you solve problems in the real world.

Some believe the most important part of SQL is the ability to query data. Queries typically retrieve data by joining many tables together into useful result sets. This tutorial takes the position that visibility into the data helps those new to SQL understand how joins work. To that end, the queries use Common Tabular Expressions (CTEs) instead of tables.

Default behavior of a JOIN without a qualifying descriptor is not simple because it may return:

• A CROSS JOIN (or Cartesian Product) when there is no ON or USING subclause, or
• An INNER JOIN when you use an ON or USING subclause.

The following query uses JOIN without a qualifier or an ON or USING subclause. It also uses two copies of the single CTE, which is more or less a derived table and the result of a subquery held in memory. This demonstrates the key reason for table aliases. That key reason is you can put two copies of the same table in memory under different identifiers or labels.

1
2
3
4
5
6
7

WITH alpha AS
 (SELECT 'A' AS letter, 130 AS amount
  UNION
  SELECT 'B' AS letter, 150 AS amount
  UNION
  SELECT 'C' AS letter, 321 AS amount)
SELECT * FROM alpha a JOIN alpha b;

It returns a Cartesian product:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    130 |
| B      |    150 | A      |    130 |
| C      |    321 | A      |    130 |
| A      |    130 | B      |    150 |
| B      |    150 | B      |    150 |
| C      |    321 | B      |    150 |
| A      |    130 | C      |    321 |
| B      |    150 | C      |    321 |
| C      |    321 | C      |    321 |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)

By adding an ON clause to line 8, the default JOIN keyword returns an INNER JOIN result.

1
2
3
4
5
6
7
8

WITH alpha AS
 (SELECT 'A' AS letter, 130 AS amount
  UNION
  SELECT 'B' AS letter, 150 AS amount
  UNION
  SELECT 'C' AS letter, 321 AS amount)
SELECT * FROM alpha a JOIN alpha b
ON a.letter = b.letter;

It displays results, like:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    130 |
| B      |    150 | B      |    150 |
| C      |    321 | C      |    321 |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)

The next example uses two CTEs. One uses letters 'A', 'B', 'C', and D and the other uses letters 'A', 'B', 'C', and 'E'. The letter D only exists in the alpha derived table and the letter 'E' only exists in the beta derived table. The amount column values differ for their respective letters in the two CTE tables.

The basic query below the comma delimited CTEs joins the alpha and beta derived tables with an INNER JOIN using an ON clause based on the letter column values found in both alpha and beta CTEs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

WITH alpha AS
 (SELECT 'A' AS letter, 130 AS amount
  UNION
  SELECT 'B' AS letter, 150 AS amount
  UNION
  SELECT 'C' AS letter, 321 AS amount
  UNION
  SELECT 'D' AS letter, 783 AS amount)
, beta AS
 (SELECT 'A' AS letter, 387 AS amount
  UNION
  SELECT 'B' AS letter, 268 AS amount
  UNION
  SELECT 'C' AS letter, 532 AS amount
  UNION
  SELECT 'E' AS letter, 391 AS amount)
SELECT * FROM alpha a INNER JOIN beta b
ON a.letter = b.letter;

The INNER JOIN returns only those rows in alpha and beta CTEs where the letter column values match:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    387 |
| B      |    150 | B      |    268 |
| C      |    321 | C      |    532 |
+--------+--------+--------+--------+
3 rows in set (0.01 sec)

If you change line 17 from an INNER JOIN to a LEFT JOIN, you return all the rows from the alpha CTE and only those rows from the beta CTE that have a matching letter column value. The new line 17 for a LEFT JOIN is:

17

SELECT * FROM alpha a LEFT JOIN beta b

It returns the three matching rows plus the one non-matching row from the alpha CTE that is on the left side of the LEFT JOIN operator. You should note that that a left outer join puts null values into the beta CTE columns where there is no matching row for the 'D' letter found in the alpha CTE.

The results are shown below:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    387 |
| B      |    150 | B      |    268 |
| C      |    321 | C      |    532 |
| D      |    783 | NULL   |   NULL |
+--------+--------+--------+--------+
4 rows in set (0.01 sec)

If you change line 17 from an LEFT JOIN to a RIGHT JOIN, you return all the rows from the beta CTE and only those rows from the alpha CTE that have a matching letter column value. The new line 17 for a RIGHT JOIN is:

17

SELECT * FROM alpha a RIGHT JOIN beta b

It returns the following result set:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    387 |
| B      |    150 | B      |    268 |
| C      |    321 | C      |    532 |
| NULL   |   NULL | E      |    391 |
+--------+--------+--------+--------+
4 rows in set (0.00 sec)

MySQL does not support a FULL JOIN operation but you can mimic a full join by combining a LEFT JOIN and RIGHT JOIN with the UNION operator. The UNION operator performs a unique sort operation, which reduces the two copies of matching rows returned by both the left and right join operation to a unique set.

This is the way to write the equivalent of a full join:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

WITH alpha AS
 (SELECT 'A' AS letter, 130 AS amount
  UNION
  SELECT 'B' AS letter, 150 AS amount
  UNION
  SELECT 'C' AS letter, 321 AS amount
  UNION
  SELECT 'D' AS letter, 783 AS amount)
, beta AS
 (SELECT 'A' AS letter, 387 AS amount
  UNION
  SELECT 'B' AS letter, 268 AS amount
  UNION
  SELECT 'C' AS letter, 532 AS amount
  UNION
  SELECT 'E' AS letter, 391 AS amount)
SELECT * FROM alpha LEFT JOIN beta
ON alpha.letter = beta.letter
UNION
SELECT * FROM alpha right JOIN beta
ON alpha.letter = beta.letter;

It returns one copy of the matching rows, and the non-matching rows from both the alpha and beta CTEs:

+--------+--------+--------+--------+
| letter | amount | letter | amount |
+--------+--------+--------+--------+
| A      |    130 | A      |    387 |
| B      |    150 | B      |    268 |
| C      |    321 | C      |    532 |
| D      |    783 | NULL   |   NULL |
| NULL   |   NULL | E      |    391 |
+--------+--------+--------+--------+
5 rows in set (0.00 sec)

A NATURAL JOIN would return no rows because it works by implicitly discovering columns with matching names in both CTEs and then joins the result set from both CTEs. While the letter column matches rows between the CTEs the amount column doesn’t hold any matches. The combination of letter and amount columns must match for a NATURAL JOIN operation to return any rows.

You also have the ability to override the cost optimizer and force a left to right join by using the STRAIGHT_JOIN operator. As always, I hope this helps those looking for a solution with an explanation.

MySQL membership conditions are in the MySQL 8 Documentation. They’re found in the 13.2.11.3 Subqueries with ANY, IN, or SOME section. The IN and =ANY operators both perform equality matches with one twist. The IN operator works with a set of values or a subquery but the =ANY operator only works with a subquery.

I created the digits, letters, and words tables for this example. They hold the following values respectively:

• The numbers table holds the values of 1, 2, 3, and 4
• The letters table holds the values of 'a', 'b', 'c', and 'd'
• The words table holds the values of 'Captain America', 'Iron Man', 'Thor', and Ant-Man

The following examples show the IN and =ANY membership comparison operators:

The IN membership operator:

SELECT 'True Statement' AS result
WHERE  'a' IN ('a','b','c','d');

SELECT 'True Statement' AS result
WHERE  'a' IN (SELECT letter FROM letters);

SELECT 'True Statement' AS result
WHERE   BINARY 'a' IN (SELECT letter FROM letters);

The =ANY membership operator:

SELECT 'True Statement' AS result
WHERE  'a' =ANY ('a','b','c','d');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a','b','c','d')' at line 2

The following math operators work with the ANY, SOME, and ALL membership operators:

• >
• >=
• <
• <=
• <>
• !=

While the ANY and SOME membership operators work alike, the AND operator works differently. The ANY and SOME membership operators work like an OR logical operator in the WHERE clause.

For example, the following <ANY comparison works when the right operand is in the set but not the largest element returned by the subquery.

SELECT 'True Statement' AS result
WHERE  2 <SOME (SELECT digit FROM numbers);

You can use a <=ANY or <=SOME when the left operand is equal to the highest digit in the subquery’s result set. The <ALL comparison only works when the left operand is a digit lower than the smallest element returned by the subquery.

SELECT 'True Statement' AS result
WHERE   0 <ALL (SELECT digit FROM numbers);

Here’s a set of SQL commands to setup the test cases for membership operators:

DROP TABLE letters;
CREATE TABLE letters (letter VARCHAR(10));
DROP TABLE numbers;
CREATE TABLE numbers (digit int);
DROP TABLE words;
CREATE TABLE words (word VARCHAR(20));
INSERT INTO letters VALUES ('a'),('b'),('c'),('d'),('e');
INSERT INTO numbers VALUES (1),(2),(3),(4),(5);
INSERT INTO words VALUES ('Captain America'),('Thor'),('Iron Man'),('Ant-Man');

As always, I hope this helps those looking for more examples and good solutions.

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory:

/Users/<user_name>/Documents/mysql

Use the /Users/<user_name>/Documents/mysql as the in this command:

docker run --name=mysql1 --volume=<path_to_folder>:/var/lib/mysql -p 33060:3306/tcp -d mysql/mysql-server

The --name option value is mysql1 and it becomes the container value. Docker mounts the column in the ~/Documents/mysql folder. All data from the Docker container under the /var/lib/mysql directory will persist in this directory. This directory will still contain the database when the container is shut down.

The docker run command maps the localhost’s 33060 port to the 3306 port on the Docker container. You will use the 33060 port to connect to the Docker instance of MySQL. It raises a dialog box asking for permission to access the directory. You need to allow Docker to write to the ~/Documents/mysql directory.

You can verify that the Docker container is running with the following command:

docker ps

It should return:

CONTAINER ID   IMAGE                COMMAND                  CREATED         STATUS                   PORTS                                      NAMES
142b5c491cd8   mysql/mysql-server   "/entrypoint.sh mysq…"   7 minutes ago   Up 6 minutes (healthy)   33060-33061/tcp, 0.0.0.0:33060->3306/tcp   mysql1

You can get the MySQL generated root password with this Docker command:

docker logs mysql1 2>&1 | grep GENERATED

It returns something like the following:

[Entrypoint] GENERATED ROOT PASSWORD: vop#3GNYqK3nC@S@N3haf3nox5E

Use the following Docker command to connect to the Docker container:

docker exec -it mysql1 /bin/bash

It launches a Bash shell inside the Docker container:

bash-4.2#

Start the mysql Command-Line Interface (CLI):

mysql -uroot -p

You are then prompted for a password:

Enter password:

After successfully entering the password, you’ll see the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.22
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Unless you want to remember that hugely complex root password, you should consider changing it to something simple like, 'cangetin' with the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cangetin';

Next, you should check for the installed databases with this command:

show databases;

It will return:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

Exiting mysql, you can see the contents of the root user’s directory with this list command:

ls -al

It should return:

total 84
drwxr-xr-x   1 root root 4096 Jan 12 03:41 .
drwxr-xr-x   1 root root 4096 Jan 12 03:41 ..
-rwxr-xr-x   1 root root    0 Jan 12 03:41 .dockerenv
lrwxrwxrwx   1 root root    7 Oct 12 22:06 bin -> usr/bin
dr-xr-xr-x   2 root root 4096 Apr 11  2018 boot
drwxr-xr-x   5 root root  340 Jan 12 03:41 dev
drwxr-xr-x   2 root root 4096 Oct 19 05:47 docker-entrypoint-initdb.d
-rwxr-xr-x   1 root root 7496 Oct 19 05:37 entrypoint.sh
drwxr-xr-x   1 root root 4096 Jan 12 03:41 etc
-rw-r--r--   1 root root   86 Jan 12 03:41 healthcheck.cnf
-rwxr-xr-x   1 root root 1073 Oct 19 05:37 healthcheck.sh
drwxr-xr-x   2 root root 4096 Apr 11  2018 home
lrwxrwxrwx   1 root root    7 Oct 12 22:06 lib -> usr/lib
lrwxrwxrwx   1 root root    9 Oct 12 22:06 lib64 -> usr/lib64
drwxr-xr-x   2 root root 4096 Apr 11  2018 media
drwxr-xr-x   2 root root 4096 Apr 11  2018 mnt
-rw-r--r--   1 root root    0 Jan 12 03:41 mysql-init-complete
drwxr-xr-x   2 root root 4096 Apr 11  2018 opt
dr-xr-xr-x 127 root root    0 Jan 12 03:41 proc
dr-xr-x---   1 root root 4096 Jan 12 04:21 root
drwxr-xr-x   1 root root 4096 Oct 19 05:47 run
lrwxrwxrwx   1 root root    8 Oct 12 22:06 sbin -> usr/sbin
drwxr-xr-x   2 root root 4096 Apr 11  2018 srv
dr-xr-xr-x  13 root root    0 Jan 12 03:41 sys
drwxrwxrwt   1 root root 4096 Jan 12 03:41 tmp
drwxr-xr-x   1 root root 4096 Oct 12 22:06 usr
drwxr-xr-x   1 root root 4096 Oct 12 22:06 var

At this point, you have to make a choice about how you will access the MySQL database. You have a couple options:

• Create an individual student user that can access the MySQL-Server as a micro-service, which would only be a MySQL user connecting through MySQL workbench. At least, that’s the only connection option unless you likewise install the mysql client on your host macOS. The mysql client lets you connect from the host operating system through the Command-Line Interface (CLI).
• Create a local student user account inside the Docker container. It will have access to the container file system and mimic the behavior of a non-root user on a server.

Let’s create both for this demonstration. Reconnect as the root user and issue the following two commands:

CREATE USER 'student'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student';
CREATE USER 'student'@'%.%.%.%'   IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student';

The first version of the student user lets you access the database from inside the Docker container. The second version of the student user lets you access the database from MySQL Workbench deployed on your base macOS.

You can add a sakila database and grant all privileges to the student user with the following command as the root user:

CREATE DATABASE sakila;
GRANT ALL ON sakila.* TO 'student'@'localhost';
GRANT ALL ON sakila.* TO 'student'@'%.%.%.%';

You need to get the sakila database from the Internet within the Docker container. Exit the mysql client with the following command:

quit;

As the root user, install the wget and tar Linux utilities with this command:

yum install -y wget tar

As the student user, you can use the wget command to grab a copy of the sakila database and store the database locally. Use the cd command to get to your ${HOME} directory, like:

cd

Use this syntax to get a copy of the sakila database:

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Use the ls command to verify the download, then run the following set of Linux commands from the Linux CLI:

tar -xzf sakila-db.tar.gz
cd sakila-db

Run the following two commands from the sakila-db directory:

mysql -ustudent -p < sakila-schema.sql
mysql -ustudent -p < sakila-data.sql

or, you can connect as the student user to the MySQL client and run them there:

source sakila-schema.sql
source sakila-data.sql

You create a non-root student user for the Docker container from the macOS host opearting system. Which means you need to quit; the mysql client, and exit the root user’s session with the Docker container.

At the terminal in your macOS, issue the following Docker command to create a student account in the mysql1 container:

docker exec mysql1 bash -c "useradd -u 501 -g mysql -G users \
>      -d /home/student -s /bin/bash -c "Student" -n student"

Now, you can connect as the student user to the mysql1 container, with the following Docker command:

docker exec -it --user student mysql1 bash

The first time you connect, you will be a the / (root) directory. Use the following cd command to go to the student user’s home directory:

cd

Then, type the following command to set the student user’s home directory as the default. You need to use this command because vim isn’t installed in the default Docker container, which would let you interactively edit files. It appends the necessary Bash shell command to the end of the .bashrc file.

echo 'cd ${HOME}' >> .bashrc

With this change, the student user will always be available form its home directory next time you connect to the mysql1 container. You can use scp to move files into the student user’s home (/home/student) directory. However, you can create a quick test.sql file like this:

echo "select user();" > test.sql

Connect to the mysql CLI with as the student user:

mysql -ustudent -p

Call your test.sql file from the Linux CLI, like:

mysql -ustudent -p < test.sql

or, you can run the test.sql program as follows form the MySQL command-line:

source test.sql

It will return:

+-------------------+
| user()            |
+-------------------+
| student@localhost |
+-------------------+
1 row in set (0.00 sec)

That’s the basic setup of the Docker MySQL Container on the macOS. You can do much more once you’ve configured it like this. For example, you can add vim to your library repository as the root user with the following command:

yum install -y vim

It just takes a minute or a bit more. Adding vim opens up so much flexibility for you inside the Docker container, it’s impossible for me to resist. 😉

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3^rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the “sakila database” under the Example Database section.
2. Put the files in the local directory such as C:\temp\sakila-db (used for the next two steps, but overwrite with your directory path).
3. Type

   source c:\temp\sakila-db\sakila-schema.sql

   source c:\temp\sakila-db\sakila-schema.sql

   and press enter.

4. Type

   source c:\temp\sakila-db\sakila-data.sql

   source c:\temp\sakila-db\sakila-data.sql

   and press enter.

These instructions let you create the sakila database without rerunning the MSI to add a product. Naturally, you can avoid these steps by using the GUI approach provided in the MySQL MSI file.

As always, I hope this helps those looking for how to solve problems.