SQL: CREATE DATABASE & CREATE TABLE Operations for Pharmacy Management System

Welcome to the first article in the Learn SQL series. In this part, we’ll start with two essential commands in SQL: Create Database and Create Table. While both are pretty simple, they should be used first before you start working on anything with data (unless you use some template database).

Later in this series, I’ll try to cover everything essential for the complete beginner to jump into the magical world of SQL and databases. So, let’s start:

The goal of this article is to create a database (using the SQL Create Database command) and two tables (using the SQL Create Table command) as shown in the picture above. In the upcoming articles, we’ll insert data into these tables, update and delete data, but also add new tables and create queries.

What is a database?

Before we create a database using the SQL Create database command, I want to define what a database is. I’ll use the definition provided by Oracle:

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).
(source: https://www.oracle.com/database/what-is-database.html)

In this article, I’ll use the MYSQL Workbench.

SQL Create Database statement

After installing and opening MYSQL Workbench, our screen looks something like this:

 

It doesn’t look fun at all. We’ll make it more fun by creating a new database. After clicking on the New Query, the new window opens and we’re able to type something in. It looks like this in the picture below:

Before typing anything, we should be sure we’re typing it in the right way. T-SQL is a language and as such it has its’ words – a set of rules on how to write different commands.

I’ll simplify it a lot and go only with the most basic form. To create a new database on our server, we need to use the following command:

1	Create database database_name;

Where we’ll use the desired name instead of the database_name.

SQL Create Database Pharmacy

OK, let’s try it. We’ll run a command:

1	Create database pharmacy;

After running this command, our database is created, and you can see it in the databases list:

Click on the + next to the folder Databases, and besides two folders, you’ll also see that the pharmacy database had been created.

This is cool and you’ve just successfully created your pharmacy database. The problem is that we don’t have anything stored inside the database. Let’s change that.

SQL Create Table statement

In database theory, a table is a structure (“basic unit”) used to store data in the database.

 If you think of a pharmacy, a database is one shelf with medicines, and each medicine is a table. Each medicine has its own contents but is somehow related to other tablets on the same shelf – either by sharing some properties or by just being close.

There is a lot of theory behind database tables, and how to decide what goes where, but the simplest you can do is follow. When we look at our data and we need to decide what goes where we should group data in tables in such a manner that everything that belongs to the same real-life entity goes to the same table.

E.g. if we want to store data describing medicine and cashier, we’ll have two separate tables in our database – one for medicines and another one for cashiers. We won’t mix their data but rather relate them. This goes out of the scope of this article and shall be covered in the upcoming parts of this series.

1 2 3 4 5

CREATE TABLE table_name (     column_name column_type,     column_name column_type,     ... );

We’ll simply choose the name for our table and list all the columns we want to have in this table. Columns are also called attributes and each column describes a property of one record in the table. The column has its type and we should choose the type based on the values we expect in that column (number, text, etc.).

SQL Create Table

Let’s take a look at the definition of our two tables:

First, we’ll define the cashier table.

1 2 3 4 5 6 7 8 9

-- Table: cashier create table cashier ( Cashier_cashierId int primary key, Name varchar (45), Tel_No int, Address varchar(45)); );

Second, we'll define the medicine table.

1 2 3 4 5

-- Table: medicine table create table Medicine ( Medicine_Id int, Medicine_Name varchar(45), Exp_Date varchar(45), MNF_Date varchar(45));

After executing these commands, the status of our database is as in the picture below:

Cashier Table

Medicine Table

Screen Shot of Pharmacy Management System SQL Table   

Tables

Conclusion

Congratulations. You have successfully created your Pharmacy database using SQL Create Database and Create Table commands. We have 2 example tables in our database. Now we’re ready to populate them with data and test if we did it as expected. We’ll do it in the next article, so, stay tuned!