From A to SQL — creating a practice e-commerce database
Welcome back to the next instalment of my learning journey. Here I am, continuing to proactively improve my skills in SQL, one query at a time. In the first part of this series, I refreshed my foundation concepts of SQL through a LinkedIn Learning course. You can read all about it here.
Although independent and self-driven work is a strength of mine, I wanted to take a different approach with learning SQL. Therefore, I have enrolled on a course to learn in a structured way, and network along the way. In the first lesson of the course, we covered databases and tables. To solidify my knowledge, and help others to learn in an accessible way, here is your simple guide to creating databases and tables using SQL.
I’m confused — what is a database?
In the simplest form, a database is a container of data. In this container, data is organised in a set way, so that it can be stored and retrieved successfully. The data is organised in tables, each with rows and columns and a database can be made up of multiple sets of tables.
How about a relational database?
A relational database exists when the tables in a database are related to each other. In this way, you could access a specific piece of data which is related to another in the database.
What’s SQL got to do with it?
Imagine you met with a friend, and you want to ask them a series of questions. How would you do this? You would usually communicate with your friend using a particular language.
SQL is exactly that — it is a language used to communicate with databases. By writing questions (or queries) using code, you can ask the database for the information you need, and it will respond by providing you the data which you have asked for (assuming you have got the correct syntax!).
Ready? Set? CREATE.
Let’s imagine that your friend has an e-commerce business, and they have data relating to customers, items, and supply. We can practice our knowledge of SQL to create a database, called BUSINESS to store this information in a structured way. The task is to create the database and 3 tables, and then add data to the relevant tables. For this example, I will be using MySQL and MySQL Workbench.
Step 1 — Creating a database
The syntax for creating a database is:
CREATE DATABASE <database_name>;
Following the creation of a database, you can state:
USE DATABASE <database_name>;
or
USE <database_name>;
to ensure you scope future queries of your data. After you run the create database command comment out or delete this line of code to stop the system trying to create multiple databases of the same name — you will get an error!
Step 2 — Creating a table
The syntax for creating a table in a database is:
CREATE TABLE <table_name>
(column_name Type,
column_name2 Type,
column_name3 Type);
I will cover different column types in a later article, but for now you can read more about them here.
Notice that the column names + types are separated by a comma, apart from the last one! Don’t forget to add your semi-colon to tell the system that the query is finished and ready to be executed.
Step 3 — Add data into the table
The syntax for this is:
INSERT INTO <table_name>
(column_name1, column_name2, column_name3)
VALUES
(value1, value2, value3),
(value1, value2, value3);
Each list of values in parenthesis will give you one row of data. To avoid errors, ensure that if you have specified 3 columns, you provide a value for each one. Once again, notice that each list is separated by a comma, apart from the last one.
We can check if the data has been added in the way we have anticipated by running a SELECT query.
Congratulations! You have successfully created a database, multiple tables, and added relevant data to allow you to store the information and come back to it later. I encourage you to create your own practice databases and tables — it’s the best way to practice your skills!
Keep your eyes peeled for the next sequel of my learning journey :)
