Learning Structured Query Language (SQL) with open-source software : SQLite and DBeaver

Structured Query Language, or SQL is one of the top-rated computer languages, year after year. But learning its quirky syntax and how to control its power can be a challenge. 

Why?

First, you need a database. In most cases, that means setting up a relational database. You need to install the software, get it running, create accounts, and somehow load data before you can use it. This is like wanting to learn how to cook and having to build a stove before trying to make scrambled eggs.

Every database has a command-line interface, or CLI, that dates back to the 1970s and 1980s. Everyone is used to graphic interfaces, and CLIs feel clunky to many.

You may need to follow specific networking and firewall rules. If all you want is a simple way to work with SQL, this is an extremely time and labor-intensive process to surmount.

SQLite 

SQLite is a self-contained, lightweight, file-based relational database management system that does not require a separate server to run. It is popular for embedded systems, mobile applications, and small to medium-sized projects because it stores an entire database in a single file, is easy to use, and requires minimal configuration.

DBeaver Community Edition

The DBeaver Community is a free, open-source, cross-platform database tool for working with data. It supports a wide variety of popular SQL databases, such as MySQL, PostgreSQL, and SQLite, and includes features for data manipulation, querying, administration, and basic visualization. It's designed for developers, database administrators, and data analysts and is built on the Java Database Connectivity (JDBC) API.

And it comes with a sample SQLite database. This database can be used to learn SQL!

The DBeaver Community Edition, with the SQLite database, can be downloaded here.  Be sure to load the sample database when asked during installation.

Videos

DBeaver has a series of videos on learning SQL. There are excellent introductions to the facets of SQL.

Episodes:

1. SELECT

2. INSERT

3. CREATE TABLE

4. DELETE

5. ORDER BY

6. UPDATE

7. NULL

8. How To Get Started With JOINs

9. Right Joins & Left Joins

10. Aliases

11. Self Join

12. Full Join

13. Select Distinct

14. GROUP BY & COUNT

15. MAX & MIN Aggregate Functions

16. AVG

17. SUM

What You Get

When you start DBeaver and click on the Sample Database, you will see a fully functioning SQLite database with tables related to music tracks.

The DBeaver Sample SQLite Database

This collection of data is perfect for learning how to query data. it is easy to join tables together, create reports, and practice queries to build your skills.

And if something terrible happens, it is easy to delete this sample database and reload the entire database (Look under the Help menu).

You now have a platform for learning SQL. You did not have to set up a separate MySQL, PostgreSQL, MariaDB, or SQL Server instance with all the required prerequisite steps. All you have to do is install DBeaver CE on your Windows, Mac, or Linux system.

You can make the queries as straightforward or as complex as needed.

You are not causing contention on a production database server. Everything is self-contained. And you do not need a network connection after the software is installed. Perfect for learning on that long flight you have coming up, so you can make those hours productive.

First Query

Open an SQL Editor and enter your first query.

Hit Ctrl-Enter or the top orange triangle and see the results.

Conclusion

You can easily learn SQL with easy-to-install and use open-source products. Please share your results!

Tracking MySQL Query Plans Part I and Rebooting This Blog

Did you ever need to determine the performance of a query over time? One of the problems with database query optimizations is that the underlying data is always churning. Add in an increase in the number of users, expanding server demand use, and other items that impact your query. What was optimized is no longer performing as expected.

MySQL added a JSON format option to the output of the EXPLAIN command quite a while ago. More recently, Oracle added the feature of saving the output into a variable (see https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html and https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82). This provides a way to access the data in that variable and plop it into a database. The saved result can be compared to current or other past responses for analysis. 

mysql> explain format=json into @var
       SELECT * from city where District='Texas';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_pretty(@var)\G
*************************** 1. row ***************************
json_pretty(@var): {
  "query_block": {
    "table": {
      "filtered": "10.00",
      "cost_info": {
        "eval_cost": "40.46",
        "read_cost": "370.39",
        "prefix_cost": "410.85",
        "data_read_per_join": "97K"
      },
      "table_name": "city",
      "access_type": "ALL",
      "used_columns": [
        "ID",
        "Name",
        "CountryCode",
        "District",
        "Population"
      ],
      "attached_condition": "(`world`.`city`.`District` = 'Texas')",
      "rows_examined_per_scan": 4046,
      "rows_produced_per_join": 404
    },
    "cost_info": {
      "query_cost": "410.85"
    },
    "select_id": 1
  }
}
1 row in set (0.00 sec)
MySQL> 

The detailed items are easy to access.

mysql> SELECT JSON_EXTRACT(@var, "$.query_block.cost_info.query_cost") AS cost;

+----------+

| cost     |

+----------+

| "410.85" |

+----------+

1 row in set (0.00 sec)

In Part II, let's gather the various items we want to track to study query performance over time.

Blog Reboot

I am rebooting the blog after a long break. If you have ideas for a post, please let me know.