Is SQL, or Structured Query Language, a programming language? That’s a great question! A question that many answer with emphasis: “No, SQL is not a programming language!” There are some who answer yes; and they usually qualify that answer with something like: “SQL is a programming language designed to communicate with relational databases.”

It strikes me that those saying “yes” are saying that SQL is only a collection of interface methods to read from and write to a database engine. Those saying SQL is not a programming language often qualify that a programming language must have conditional logic and iterative structures, which don’t exist in SQL.

There’s a third group that are fence sitters. They decline to say whether SQL is a programming language, but they also say individuals who only write SQL aren’t programmers. That’s a bit harsh from my perspective.

Before …

Saturday, I posted how to use Microsoft ODBC DSN to connect to MySQL. Somebody didn’t like the fact that the PowerShell program failed to write a *.csv file to disk because the program used the Write-Host command to write to the content of the query to the console.

I thought that approach was a better as an example. However, it appears that it wasn’t because not everybody knows simple redirection. The original program can transfer the console output to a file, like:

powershell .\MySQLODBC.ps1 > output.csv

So, the first thing you need to do is add a parameter list, like:

param (
  [Parameter(Mandatory)][string]$fileName
)

Anyway, it’s trivial to demonstrate how to modify the PowerShell program to write to a disk. You should also create a virtual PowerShell drive before writing the file. That’s because you can change the physical directory anytime you want with minimal changes to rest of …

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 …

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, 3rd 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 …

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

 …

Somebody didn’t like the MySQLi Update Query example on the tutorialspoint.com website because it use the procedure mysqli_query style. Here’s a simple example of using the object-oriented method version. More or less, instead of query it uses the more intuitive execute() method.

The update_member function contains the logic and below it is a call to the test the function. It relies on a MySQLCredentials.inc file that contains the hostname, user name, password, and database name. You can create create member table, like my example in MySQL 8, or any other table in your MySQL database.

<?php /*
||  Function Name: update_member
*/
function update_member($account_number, $member_type, …

I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.

It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.

Here are the new instructions I’m adding and if you have any input leave it as a comment.

You connect as the student user, which puts you in the /home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you …

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

      SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' …

Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:

…

One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.

It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.

…