Hi,

We finished by creating our database file: see part 4 of the course

It’s time to populate it with data.

Step 1: Download the SQL file

Step 2: Open DBeaver

Make sure you have double-clicked on the previously created (and currently empty) database, and then press the SQL button.

Step 3: Open the downloaded file

The downloaded file has a .txt extension for security reasons. We have two options; choose the one that is simpler for you.

• Change the file extension to .sql
• Copy the contents

I’ll show the simpler version: I’m opening the file in Notepad.

Select all the text (Ctrl + A) or use the menu.

We copy the selected text and paste it into the DBeaver editor.

and click the Execute SQL script icon.

One last thing, and you will see the tables with data: right-click on the Tables name and click Refresh.

And there we have it.

This is our training database. An online bookstore.

Click on New SQL script.

When you expand the table names, you can then expand ‘columns’ to see what columns a given table consists of.

Time for some queries!

Look at how convenient DBeaver is. I started typing ‘select * from’ and it suggests the tables I have in my database.

Additionally, the system immediately suggests an alias, which is a shortened name for the table.

SQL

select * from authors a

Click Execute SQL query, and below we have the list of authors.

Let’s check the list of books:

SQL

select * from books b

Now for a join recap. Let’s select the books, but also display the author (right now we only see their ID) and the publisher. We want the following columns: Title, Author, Publisher, publication_year, price

SQL

select b.title,
a.first_name || ' ' || a.last_name as author,
p.name as publisher_name,
b.publication_year ,
b.price
from books b
join authors a on b.author_id = a.author_id
join publishers p on b.publisher_id = p.publisher_id

So, with `select`, we choose the columns, and instead of using the full table name, which can be long, we use the alias. Simple and convenient, right?

a.first_name || ‘ ‘ || a.last_name as author -> In SQLite, PostgreSQL, and Oracle, this is how you concatenate columns into strings. Here we combine the first and last name with a space in between. (In MS SQL Server, the same thing is often done using the + sign).

Additionally, you can see that the result screen can be switched between a grid (like in Excel) and a text view.

To end this lesson, let’s check which books are the most popular in our store, as an introduction to the next lesson.

We’ll do this in a simple way: we’ll display the orders, order items, and titles, show the customer, and sort by the book title.

SQL

SELECT c.name as customer_name,
b.title
from orders o
join order_items oi on o.order_id = oi.order_id
join customers c on o.customer_id = c.customer_id
join books b on oi.book_id = b.book_id
order by b.title

Here we have the main `orders` table, where we have information about the customer, date, status, and total amount of the order.

We have `order_items` where we have the individual items from the shopping cart.

We have the `books` linked to the items.

And at the end, we sort using `order by` on the book title.

Of course, we can see right away that the Harry Potter series rules, but what if there were a million records?

More on that in the next lesson, where we’ll start grouping and counting data.

See you!