Lesson 3 – Creating a Database

As discussed in lesson 2, we talked about the various types of SQL commands. In this lesson, we will focus on creating and inserting data into a database. We learnt that DDL commands are used to Create, Alter, Drop, Truncate, Comment and Rename database and database objects. While learning SQL, I realized that some concepts were better understood when approached with a basic English Language explanation. In this lesson, we will make use of non-technical terms to give basic explanations.

The Create statement as the name implies is used to create database and database objects like tables into being. Think of it as building something that does not exist, from the ground up. The command used to create a database is;

CREATE DATABASE [database_name]

The “CREATE DATABASE” command specifies that we are creating a completely new database, which does not exist in our system. Let's visualize this newly created database as a foundation upon which we are about to build a structure. After creating the database, we need to create our table(s) to house our data.

In our example, we have been contracted by the Ghana Electoral Commission to build a simple database system for them to collate the details of eligible voters for an upcoming election. In the image below, you realize that, we first created the database “Voter Register”, then went created a table called “Voters” which is made up of five columns. The columns are the sections that the collected details of voters will be saved into.

Now let’s go ahead and insert data into our "Voters" table. The syntax for inserting data into a table is;

INSERT INTO [table_name] (column1, column2, column3,…)
Values (…, …, …, ……….)

We have successfully inserted four records into our database. In writing the insert statement syntax, we made our intention clear by using the command “Insert into”, then we specified the table into which we would want our data saved. We then specified the columns we are inserting into; this is an optional step but highly recommended, to make your code readable and also in case there is no data for a specific column.

For example, if there is no data for “Region” and we do not specify the columns into which we want to insert data, we will end up getting an error message like in the image below. In the image below, we are notified that, the data we are inserting does not match the number of columns created in the table "Voters". We have five (5) columns in the table, but we are inserting just four sections of collected data without being specific.

However, if we do not have data for “Region” but specifies the columns we are inserting into, “Region” is automatically made “NULL”. Which indicates that, there is no data for "Region" for that particular row/record.

Note that in our insert statement syntax, some characters are put in single quotation marks while other are not. Those in quotation marks are known as ‘String’ data types, which is a collection of characters while numeric data types are not placed in quotation marks.

In this lesson, we covered how to create a database, create a table and insert data into tables. In our next lesson, we will be covering Data Types, Primary and Foreign key constraints and Null and Not Nulls.

You can visit w3schools web page to practice how to create database and tables with their built-in IDE for a practical and better understanding of this topic.

I hope I was able to help you understand and/or learn something new in your data journey today. Please leave a comment, suggestion or contribution for me and others to benefit from your knowledge as well.