Monday, September 20, 2021

Dave's MySQL Quiz Number 2 Answers

    This week's MySQL was a great one for novices looking to level up from beginner level SQL development to a higher level.  

    The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals. 

The answer:

First we need to get the customer_id from the customer table.  Then it takes a bit of struggle to get the information on the rental.   It is often easier to write queries by determining the needed output columns, then the 'qualifiers'  or stuff on the right of the WHERE clause before determining what has to be joined to get between the two.

The part of the query to find the overdue entries requires the rental date where it and the length of rental time are before the current date.  r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()

Finding the those customers with more than one rental needs to have the count of r.rental_id greater than 1. 

So besides the rental table, we will need the inventory table to tie between the rental table and the film table. 

 SQL > select c.customer_id,    
sum(count(r.rental_id)) over (partition by c.customer_id) as'total',  r.rental_id, group_concat(f.title order by f.title) as 'titles' 
from rental r   
join inventory i on (r.inventory_id=i.inventory_id)   
join film f on i.film_id = f.film_id   
join customer c on r.customer_id = c.customer_id   
where r.return_date IS NULL   
AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE() 
group by c.customer_id  
having count(r.rental_id) >  1  
order by c.customer_id;
| customer_id | total | rental_id | titles                                 |
|          15 |     2 |     13798 | CANDIDATE PERDITION,SMOKING BARBARELLA |
|          42 |     2 |     13351 | RIVER OUTLAW,TORQUE BOUND              |
|          43 |     2 |     15644 | MOTIONS DETAILS,POLLOCK DELIVERANCE    |
|          53 |     2 |     11657 | LAWLESS VISION,PEACH INNOCENT          |
|          60 |     2 |     12489 | BOOGIE AMELIE,CHAMBER ITALIAN          |
|          75 |     3 |     13534 | LUST LOCK,SLEEPY JAPANESE,TROUBLE DATE |
|         107 |     2 |     13079 | BLADE POLISH,CLUB GRAFFITI             |
|         155 |     2 |     11496 | CHASING FIGHT,HYDE DOCTOR              |
|         163 |     2 |     11754 | HOLES BRANNIGAN,SONS INTERVIEW         |
|         175 |     2 |     13161 | DEER VIRGINIAN,PIRATES ROXANNE         |
|         208 |     2 |     13719 | CURTAIN VIDEOTAPE,SEATTLE EXPECATIONS  |
|         216 |     2 |     11676 | SWEDEN SHINING,WOMEN DORADO            |
|         228 |     2 |     12672 | CYCLONE FAMILY,GRAPES FURY             |
|         267 |     2 |     12066 | LUST LOCK,PHILADELPHIA WIFE            |
|         269 |     2 |     12610 | PRINCESS GIANT,THEORY MERMAID          |
|         284 |     2 |     12064 | BERETS AGENT,FRIDA SLIPPER             |
|         354 |     2 |     11782 | TITANIC BOONDOCK,TROJAN TOMORROW       |
|         361 |     2 |     13298 | HALF OUTFIELD,INSECTS STONE            |
|         448 |     2 |     13577 | FAMILY SWEET,STATE WASTELAND           |
|         457 |     2 |     12645 | CLEOPATRA DEVIL,GLEAMING JAWBREAKER    |
|         516 |     2 |     12130 | FALCON VOLUME,MINORITY KISS            |
|         560 |     2 |     12116 | MOVIE SHAKESPEARE,PIANIST OUTFIELD     |
|         576 |     2 |     11942 | TITANIC BOONDOCK,VANISHED GARDEN       |

Bonus:  Add the customer name

select c.customer_id, 
 concat(c.first_name, ' ', c.last_name) AS 'Customer Name',
  sum(count(r.rental_id)) over (partition by c.customer_id) as 'tots',  
 r.rental_id, group_concat(f.title) from rental r 
 join inventory i on (r.inventory_id=i.inventory_id) 
 join film f on i.film_id = f.film_id 
 join customer c on r.customer_id = c.customer_id 
 where r.return_date IS NULL 
 AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE() 
 group by c.customer_id
 having count(r.rental_id) >  1
 order by c.customer_id;

Friday, September 17, 2021

Dave's MySQL Quiz #2

     This week's MySQL uses the Sakila database (details on how to get this data) and this week's quiz is a  great one for those wanting to move from beginner level SQL development to a higher level.  There will be lots of tables to joins.

    The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals.    You will need to display the customer's ID number, the number of overdue videos, and the names of the videos!  Bonus points for the customer name!

An answer will be posted Monday.

Monday, September 13, 2021

Quiz #1 Answers

Here are answers to Quiz #1 and please note there are several ways to solve just about any problem so your answer may be different.

QUIZ #1:   How would you modify the following query on the world database to find only the official languages of each country?  Bonus - list all the official languages for each country on one row.

from country
join countrylanguage 
    on country.code = countrylanguage.CountryCode);

First step is to see what in the countrylanguage table provides us on the official language status.

 SQL > desc countrylanguage;
| Field       | Type          | Null | Key | Default | Extra |
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |

For those not used to dealing with enums they let enumerate a list of allowed values. Is this case IsOfficial can can either be 'T' or 'F'.  

I prefer to use alias on table names and below I use c for the country table and l for the countrylanguage table. Why? It improves readability and saves typing.    For extra karma points if you are a novice, rewrite the above query with alias.

The above query needs to qualify the query so that we receive only the rows where the column IsOfficial is equal to 'T'.  

from country c         
join countrylanguage l
     on  (c.code = l.CountryCode) 
where  l.IsOfficial = 'T';


Listing all the official languages for each country on one row is a little more complex. First we have to GROUP BY each country.   If you simply add GROUP BY you will find that you only get one official language in the cases there there  are many.    You also have to use GROUP_CONCAT() to provide all the possible official languages.  I also added aliases for the selected column names.

select as 'Country',           
       GROUP_CONCAT(l.Language) as 'Official Languages'
from country c         
join countrylanguage l 
     on(c.code = l.CountryCode) 
where  l.IsOfficial = 'T' 

The GROUP_CONCAT() adds the commas between each language automatically and you can change the separator as you desire.

But if you peer through the results there is one thing that may catch you eye that could be cleaned up for readability.  Take a look at the entry for South Africa where the languages are list but are not in alphabetical order.  Here is where you can add ORDER BY to the GROUP_CONCAT() to fix that issues.

Final code:

select as 'Country',           
         GROUP_CONCAT(l.Language order by l.language) as 'Offical Langauges' 
from country c         
join countrylanguage l 
       on  (c.code = l.CountryCode) 
where  l.IsOfficial = 'T' 

Thursday, September 9, 2021

Sample Databases for Learning MySQL and a QUIZ!!

     I frequently see requests on various web sites from MySQL novices looking for example databases with which to practice.  There are several that are highly recommended and we will take a look at them over the next few weeks.

1. World Data Base -- See

This has been THE database used in MySQL's documentation, classes, examples, and just about anything else.  There are three tables -- city, country, and countrylanguage -- that are fantastic for teaching the basics such as JOINs, GROUP BY, HAVING, and other SQL commands.  

1x. World X Database -- See

With the advent of the X DevAPI there needed to be a test database with JSON columns and the World Database was modified.  The countryinfo table was added to the inherited three so that you can practice with JSON columns and a JSON document collection.

3. Sakila -- See

Once upon a time, if you wanted to see a movie in your home you had to first go to a store filled with BETAMAX and VHS format tapes and rent them.  The Sakila database is data representing the operations of such a store.  This is a bigger set of data than the previous two and provides twenty three tables giving novices an opportunity to join more tables together.  

4. Northwind -- Various sites

Northwind is a fictitious trading company and the sample dataset came from Microsoft. There are various version available for MySQL and they feature ten  tables. 

In the coming weeks I will work with various data sets hopefully showing novices how to use these test databases to expand their knowledge.  And if it proves popular, there will be a quiz each week featuring one of the above.

QUIZ:  How would you modify the following query on the world database to find only the official languages of each country?  Bonus - list all the official languages for each country on one row.

from country
         join countrylanguage on
               (country.code = countrylanguage.CountryCode);

Friday, September 3, 2021

Southern California Linux Expo 19x MySQL Track

    There will be a MySQL tract at the Southern California Linux Expo 19x (SCaLE)  March 3-6, 2022 in Pasadena, California.   I will organizing the track and the first part of that is asking you to submit talks for the MySQL track.  We needs talks for novices, we need talks for the advanced, and we need talks for those in between!  November 20th, 2021 is the deadline for abstracts/proposals submissions.  

    SCaLE is the largest Linux show in Southern California and not only has amazing talks on anything open source but the expo hall is not to be missed.  Pasadena is an amazing city with great food within walking distance of the expo hall.  Plus they have been recording America's Got Talent next door - please note Simon Cowell's knowledge of shell scripting is suspect so double check any code he offers you.

Seriously, this is an amazing event.  I need 8-10 hours of presentations!  If you have questions, need help with your proposal, or have questions about SCaLE please email me - David.Stokes at   -- I will help you get your proposal ready!

If you have not been to SCaLE before, I urge you to attend as the price is beyond reasonable, the crowd is amazing, and the presentations are fantastic.  And I hope one of the talks in the MySQL Track is yours!

Tuesday, August 31, 2021

The MySQL Shell and the MySQL Config Editor

    One of the things I love about the MySQL Shell is that is saves your authentication credentials (no longer just passwords) in an obfuscated login path file named .mylogin.cnf. You can find that file in the %APPDATA%\MySQL directory on Windows or the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs, such as the MySQL Shell, to obtain your authentication credentials for connecting to MySQL Server.

Which Credentials?

I had an email this morning from someone wanting to a) change the password stored by mysqlsh and b) find out which credentials are stored on the shell account being used.

Let's start out in reverse order.  The mysql_config_editor will show you the accounts with credentials saved for that login. 

$ mysql_config_editor print --all
user = "root"
password = *****
host = "localhost"
user = "demouser"
password = *****
host = "localhost"

The two accounts on the shell account being used are root@localhost and demouser@localhost.

Quick Password Reset

To reset the server password on the second account use ALTER USER 'demouser'@'localhost' IDENTIFIED BY '<newpasswordhere>';  

If you login again from mysqlsh and the the stored password does not work, you will be prompted for the new password.  And that new password will be encrypted in stores in the .mylogin.cnf file. 

Start From Scratch

Is you want remove all the stored authentication information then type mysql_config_editor reset and all will be removed. 

Monday, August 30, 2021

Novices and Their SQL Misconceptions

     Do yourself a favor and spend ten minutes watching this video on the CER2021 paper Identifying SQL Misconceptions of Novices: Findings from a Think-Aloud study.  Structured Query Language is hard for many new programmers as it is a declarative language which makes it much different that the object orientated or procedural languages with which they started their software journeys. 

    The authors have categories problems in four general misconceptions programmers have -- Previous Knowledge, Generalization, Language, and Bad Mental Model. Those are then refined more and I am sure anyone who has to fix poor queries will immediately recognize. 

    Why novices should read this:  Structured Query Language or SQL is a lot different than other languages and its logic is designed around data sets.  The assumptions you bring in from other languages can hinder your developing SQL skills.   The syntax and keywords are not what you are used to and frankly things about data groups instead of one row of data can be hard for some to master.  This video and the paper will help you hurdle those knowledge gaps.

    Why SQL professional should read this:  After you have mastered SQL, it is easy to forget the points that you stumbled over learning it.  it will also help you identify where your journals flounder so that hopefully you can aid them.

A few times each week I run across someone asking why SQL is 'so weird', where is the '' or while (...) flow control, or why you can not have a table of tables pointing to other tables.  MySQL is a very easy database to learn but there is still a learning curve and trying to make it act like other software many not work.

So a big round of applause to the authors : Daphne Miedema, Efthimia Aivaloglou and George Fletcher.