Learn badly with me, ggx2ac! - chapter 1: build a relational database using SQLite

[ggx2ac]

This thread wouldn't have been made if it weren't for the fact that this happened. You would only need to read posts #510, #511, and #512 in that thread.

It is very likely that 99% of you will not learn SQL in this thread and that is fine. If you're already using spreadsheets like Excel to gather your data, then it is okay for you to continue using that.

The following is not going to be a complete guide; it is just a starting point, and if you want to continue using SQL then you will be off on your own to continue learning SQL and the choices you make to get better at it whether it be with using IDEs for productivity or GUIs to visualize things better.

This is what I have come up with after a few days of learning SQL and SQLite.

This guide assumes you are using a Windows 10 (or later) PC.

Database theory:
Before you even start downloading anything, it is assumed you don't know anything about relational database management systems. Go to this link: https://mariadb.com/kb/en/database-theory/

At that link, you are going to read the articles starting from "Introduction to Relational Databases" down to "Relational Databases: Views". Optionally, read the article: "ACID: Concurrency Control with Transactions".

Once you have finished reading the theory so that you understand why relational databases exist, we move onto...

Murder Mystery:
You will still not download anything because the moment you start with a blank database you are going to have no idea what to do.

Go to this link: https://mystery.knightlab.com/
Start with their walkthrough: https://mystery.knightlab.com/walkthrough.html
Then go back to the other link again and try to solve the murder mystery.

That website suggests a more in-depth tutorial which I didn't go through at the time but I will look at it later so it might be good for beginners: https://selectstarsql.com/

After you have solved the murder mystery...

Downloading SQLite:
If you read the posts of the linked thread for why I am making this thread, then the reason I am going with SQLite is because it is simple to setup and start because SQLite is for embedded devices, not for servers. Instead of downloading PostgreSQL or MariaDB and installing them which are over 200MB each (and the installation process requires setting up a user password for every time you try to access your database), SQLite is around 2MB in size and has no installation process.

One benefit of SQLite is that the databases you make are stored in files, that means it is easy for you to back it up on your cloud storage account or send the file to others.

To download SQLite, you have to go to: https://sqlite.org/download.html

As mentioned, this post assumes you are using a Windows PC, you scroll down to "Precompiled Binaries for Windows" and then download the zip file link that is on the same row as this description: "A bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program."

You will notice an SHA3-256 hash code underneath that description, this will be important. Once you have downloaded the Zip file, you need to check that it is safe because I won't know if what you downloaded was safe.

Go to this link and read the first 6 posts to get a solution for how to check the hash code for the zip file: https://sqlite.org/forum/info/4b7069b4b476689c

If you don't have openSSL to check the SHA3-256, one suggestion is that Git comes with openSSL, if you want to install Git for Windows to try and use openSSL to check hash codes on anything you install, then check this guide from GitHub: https://github.com/git-guides/install-git

Now, assuming you have successfully checked the hash code on the zip file you downloaded and have checked that it matches the one from the website where you downloaded it from, you can then unzip the file.

With the folder you extracted, go into it, and you will see three .exe files, the one you want to use is labeled sqlite3, double-click on that.

At this point, Windows Defender will likely pop-up telling you it won't run the file because the publisher is unknown. Again, assuming you did check those hash codes mentioned above and that they match meaning you know that you have not downloaded anything that has been tampered with, click on text that says, "More Info" and the option for "Run anyway" should appear, click on that and then SQLite will run by popping up a terminal window.

The terminal window should say SQLite in the first line with its version number. It will then say:

Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

For context, you want to go to this link and go to the section that says "2. Double-click Startup On Windows": https://sqlite.org/cli.html

Creating a Relational Database:
You can create a new file by typing something like:

.open test.db

That will create a new file called test.db and it's located in the same folder as the sqlite3.exe. You will then be making your database on that file.

You are very likely to run into syntax errors as you are learning this.

First, type into the terminal:

CREATE TABLE games(id INTEGER PRIMARY KEY, title TEXT, release_date TEXT);

In case none of the resources you've read have mentioned this, SQL is case insensitive, that means you can type "create table" in lowercase and it will still work. The reason that keywords are in uppercase is to help with readability. SQL also ignores whitespace, that means you could separate field into a new line by pressing the Enter key. Remember that the command won't run until you use the ";" semicolon to indicate the end of a command.

Next, you are going to insert data into that table:

INSERT INTO games(title, release_date)
VALUES('Resident Evil', 'Mar 1996');

By explicitly stating which fields you are going to fill in (what happened in the first line), it makes it easier for adding those values correctly. Why the id field isn't entered is that when using INTEGER PRIMARY KEY as its type, it will automatically increment the id field for you.

To see the data from the table, type in:

SELECT * FROM games;

The output you are given might not be to your liking. You have the option of changing the output, see "5. Changing Output Formats" in: https://sqlite.org/cli.html

To get a certain output you are very familiar with, type:

.mode table

Then repeat the SELECT query and you should see the new output.

Next, we create a new table:

create table playtime(
id integer primary key,
hours_played integer,
game_id integer,
foreign key(game_id) references games(id)) strict;

As mentioned, this works because it's case-insensitive but it might be more difficult for you to read especially as a beginner because you are not familiar with the keywords. The STRICT keyword at the end is related to this, I am using it for every table that has a FOREIGN KEY: https://sqlite.org/stricttables.html

Next, type in:

.tables

and it will show all the tables you have created so far.

Next, type in:

INSERT INTO playtime(hours_played, game_id)
VALUES(500, 1);

Then:

SELECT * FROM playtime;

Note, it's apparently bad practice to use the * wildcard because of what is mentioned in this PostgreSQL documentation: https://www.postgresql.org/docs/15/tutorial-select.html

While SELECT * is useful for off-the-cuff queries, it is widely considered bad style in production code, since adding a column to the table would change the results.

How do we query different tables (or the same table for that matter) so that we can get information from this data? By using the JOIN keyword.

Type in:

SELECT title, release_date, hours_played FROM games
JOIN playtime
ON games.id = playtime.game_id;

What is happening here is that you need to declare all the columns from every table you are going to select. Then "FROM games JOIN playtime" will join those two tables WHERE (or rather ON but the idea is similar) the id field in games is equal to the foreign key of game_id in playtime.

You should end up with this output:

+---------------+--------------+--------------+
|     title     | release_date | hours_played |
+---------------+--------------+--------------+
| Resident Evil | Mar 1996     | 500          |
+---------------+--------------+--------------+

We're not done yet. Next, create another table:

CREATE TABLE sales(
id INTEGER PRIMARY KEY,
price REAL,
sales_units INTEGER,
game_id INTEGER,
FOREIGN KEY(game_id) references games(id)) strict;

Try not to forget about the foreign key, one problematic issue with SQLite is that you can't add in one after you have created a table, see Question 11 in this FAQ: https://sqlite.org/faq.html#q11

That FAQ also has other helpful questions so give it a read.

Next:

INSERT INTO sales(price, sales_units, game_id)
VALUES(59.99, 200, 1);

You may be wondering, why am I not putting a $ sign next to the number in price? You'll see why.

We're going to join two tables again (it is possible to join more than two tables, but I am just doing two at most):

SELECT
title,
release_date,
format('$%g', price) AS US_price,
sales_units,
format('$%,d', price*sales_units ) AS net_sales
FROM games
JOIN sales
ON games.id = sales.game_id;

And the output of the table should look like this:

+---------------+--------------+----------+-------------+-----------+
|     title     | release_date | US_price | sales_units | net_sales |
+---------------+--------------+----------+-------------+-----------+
| Resident Evil | Mar 1996     | $59.99   | 200         | $11,998   |
+---------------+--------------+----------+-------------+-----------+

As you can see, new columns have appeared by doing calculations with the price and sales_units fields. That's why I couldn't have the price be of type TEXT when trying to multiply numbers.

Here's the link for the text format() function: https://www.sqlite.org/printf.html

And that was as far as I got. I made a video game-related database where one table could let you record your playtimes of a game you have played and another table contained the sales data of that same game, and they all linked together due to primary/foreign keys.

SQLite is good to start with but it still has its limitations, for example, it has only five data types: https://sqlite.org/datatype3.html

It only becomes a problem for you if there are other data types that you would need, look through the PostgreSQL or MariaDB docs I had linked elsewhere in this post to give you an idea of the differences between them and SQLite. Also, look at this page as well to understand any differences: https://sqlite.org/whentouse.html

That's the end of the guide. The "creating a database" part wasn't as smooth as you think, I obviously made mistakes which I didn't repeat here but it's part of the learning process.

Now that you have an idea about databases, you can spend time learning to get better at it and find ways to improve productivity (because this was only just a small part about using SQL) or if you feel it's not something that you would use then that's fine. It takes time having to remember a number of commands compared to say using Excel where you can just click on a field and start typing in what you want.

Chapter end.

 

[Astral_lion02]

thanks for taking the time to do something like this.

 

[Lelouch0612]

That's a great topic and very thankful to have a thread like this, even if it might be dedicated to a few members/lurkers.

 

[Necroxid]

I have an exam on DB in the following months so I'll definitely check this guide for better comprehending the subject. Thanks!
We use PostgreSQL instead of SQLite but it's not that different.

 

[ggx2ac]

I have an exam on DB in the following months so I'll definitely check this guide for better comprehending the subject. Thanks!
We use PostgreSQL instead of SQLite but it's not that different.

After you are done with that, if you haven't been recommended to read the documentation from the PostgreSQL official site then you should just do that: https://www.postgresql.org/docs/current/index.html

They also list a bunch of tutorials/resources that are PostgreSQL specific:

PostgreSQL: Tutorials & Other Resources

www.postgresql.org