How to Create Your Own Database to Practice SQL

Welcome to the step-by-step guide to creating your own SQL database from scratch, designed with beginners in mind. This guide helps you set up your database and also introduces you to essential SQL practice exercises to get you started on your learning journey. Follow along to gain SQL hands-on practice and foster a deep understanding of SQL functionalities.

Everyone has a different style of learning, but one thing we all need is SQL hands-on practice. It is one thing to read about SELECT statements, JOINS, and GROUP BY clauses, but it is an entirely different experience to use them in queries and see the results.

This is a lot like when I took 3 years of Spanish in school. One thing I found both fascinating and frustrating was that I could understand the teacher but struggled to form sentences to respond. This is not unusual; it often happens because we practice learning new words but neglect using them.

Learning by doing is one of the most effective ways to acquire a new skill. So, what is the most effective way to practice “speaking” SQL? That is our focus in this article. If you’re interested in more on the best ways to learn SQL, be sure to check out this article here.

LearnSQL.com offers several comprehensive courses that include interactive exercises for SQL practice. You go through hundreds of exercises, covering dozens of hours to help you master SQL. For example, the SQL Basics course includes 129 practice exercises and 10 hours of hands-on learning. Our SQL Databases for Practice offers a set of 7 datasets to practice SQL on your own.

These courses are a great way to learn the “new words” of SQL, and the practice exercises help you use them in writing your queries. However, while structured learning is a great resource, you can take your learning to a whole new level by setting up your own SQL database for practice. In this article, I will walk you through the tools and steps you need to accomplish this task.

Why You Need Your Own Database for Practice

When you have your own database, you get the freedom to customize it however you want, from the table structure to the actual data it houses. You can create real-world scenarios tailored to your learning objectives. Your data stays with you, and you may explore and even make mistakes, without any judgment or fear of breaking something.

Plus, the ability to stimulate real-world scenarios allows you to practice SQL for job-specific conditions, making you better prepared for the professional world. Looking for a job in e-commerce? Set up your database with an order table, a customer table, and a product table. Interested in a career in finance? Great! Create a table for accounts receivable and another for accounts payable. The flexibility in creating your own database is unmatched by other ways to practice SQL.

If setting up a database sounds intimidating to you, don’t worry! It is very simple in practice. Different databases have different nuances. We won’t spend time here explaining them, but you can learn more about some of the most popular databases in 2023 in this article.

A Step-by-Step Guide to Setting up Your SQL Database

Step 1: Install Required Software

OK, let’s get started setting up your database for SQL training. The first thing you need to do is to download a database tool. One that I use that works on Windows, Linux, and Mac is DBeaver, which may be downloaded here. Best of all, it’s free!

Want some other tool? Check the article on the best SQL IDEs.

Once you’ve downloaded and installed the software, launch the application. You should see a screen that looks like this:

Step 2: Create a SQL Database

In the top toolbar, click on “Help” and then “Create Sample Database.” You then see a prompt asking if you would like to create a sample database. Click “Yes.”

You should now see a sample database listed under your connections under the Database Navigator panel.

This sample database comes with already created tables and views you can query immediately. Later in this article, there are a few examples of SQL queries for practice with your new database. For now, let’s look at how we may further customize this database.

Step 3: Create Custom Tables

Let’s go back to our example of practicing with e-commerce data. I’m going to rename my database to ecommerce_data by right-clicking on the database name and selecting “rename.”

To create a new table for orders, we execute a CREATE TABLE statement. In this statement, we define the table name of orders and then define the column names and the data types.

CREATE TABLE orders (
  order_id varchar(255),
  customer_id varchar(255),
  order_date date,
  price decimal,
  quantity int,
  shipping decimal,
  tax decimal,
  total_order_amount decimal
);

You can now run SELECT * FROM orders, but you will see an empty table. The next step is to insert rows into this table using an INSERT statement.

INSERT into orders
VALUES ('12345','654321', '2023-07-01', 20.00, 1, 3.99, 1.75, 25.74);

Now, you will see your new data when you run a SELECT statement.

Step 4: Import Data From CSVs

Inserting individual rows of data is tedious depending on how much data you want to set up in your database. Another option is to import whole CSV files rather than writing INSERT statements. To do this, right-click on the table name under your sample database and select “Import Data.”

From here, select a CSV from your computer to upload. If you do not see your table listed under your sample database, right-click on the database name and click “Refresh.” You should then see the newly created tables.

There you have it! Your own database to practice SQL.

If you’re unsure about creating your own data, you can also find several resources online with public datasets for download. You can then import these files into your database. A good, clean data set often used is the superstore data from Tableau. This Excel sheet includes 3 separate tabs to create 3 tables of store purchase data. Another great resource for finding data sets is our article, “Where Can I Find Free Online Data Sets to Practice SQL?.”

If you don't want to set up your own database, a great resource is our SQL Databases for Practice course. It contains 7 realistic databases for you to play with.

Practice Queries

Let’s walk through a few examples to get you started in using your new database. This is only the beginning; you’ll be on to exploring your data in no time. For an added challenge, try answering the question on your own before reading the SQL query.

The questions below use the customer and invoice tables in the DBeaver sample database.

Example 1: What is the distinct count of customers?

For this example, we only need to query the customer table. Use COUNT() and DISTINCT to answer this question.

SELECT COUNT(distinct CustomerId)
FROM customer;

This query counts the number of unique CustomerIds in the customer table. We now see there are 59 customers in our database.

Next, let’s see how many invoices each customer has on average.

Example 2: What is the average number of invoices per customer?

To answer this question, use the invoice table and create a list of all customer IDs with how many invoices each has.

SELECT 
  CustomerId,
  COUNT(DISTINCT invoiceId) AS invoices
FROM invoice
GROUP BY 1

At a glance, it looks like most customers have 6 or 7 invoices. Let’s calculate the average across all customer IDs by placing the above in a subquery and calculating the average:

SELECT AVG(invoices)
FROM (
  SELECT 
    CustomerId,
    COUNT(DISTINCT invoiceId) AS invoices
  FROM invoice
  GROUP BY 1);

The query returns 6.98 invoices, on average, for each customer.

These examples are just to get you started. Get familiar with what data lives in each table and how the tables relate to one another. Then, begin asking yourself questions and creating hypotheses. Finally, begin exploring the data by writing queries to answer those questions and test your hypotheses.

A great resource to reference is our SQL for Data Analysis Cheat Sheet. This is a curated list of SQL commands to help you get started. Once you get started, it’ll be hard to stop!

Practice SQL on Your Own Database!

So, there you have it! Your own SQL database setup for practice is not only possible but incredibly beneficial. You get customization, privacy, and the ability to simulate real-world scenarios.

If you don't want to set up your own database, a great resource is our SQL Databases for Practice course. It contains 7 realistic databases for you to play with.

If you’re looking to upskill further, check out courses offered by LearnSQL.com. The All Forever package provides lifetime access to all the courses and tracks plus any new courses released in the future! This option, by far, gives you the most bang for your buck with a huge discount.

Start your SQL journey now by setting up your personal database. Dive into it, practice daily, and you’ll be amazed at how far you go. Happy querying!