creating a simple database using SQL

assuming you have some basic understanding of database and SQL, the logical next step is to interact with SQL and creating databases and have a feel of writing queries.If you what a quick reminder of theory behind SQL, here is a blog to help you on that.

On this blog, i will be taking you through creating a simple database for a coffee shop.

Firstly we will define the schema for the database tables which is simply the blueprint of how the database will look like.

The coffee shop database will have three tables:

1. Coffee Menu
2. Employee table
3. Orders table

columns for Coffee Menu table include:

1. id
2. Coffee name
3. price

columns for Employee table include:

1. id
2. employees name
3. role

columns for Orders table include:

1. id
2. coffee ordered (foreign key from coffee menu table)
3. served by (foreign key from Employees table)
4. order time

with this we have a basic look at how the database looks like and now comes the best but running some SQL. i'll be using PostgreSQL but the commands will still work for any RDBMS.
the command for creating a database is:

CREATE DATABASE <db_name>;

note that any SQL query written must end with a semi colon.In this case i will run the below command to create the table.

CREATE DATABASE coffee_shop;

next step is witting queries to create the different tables .The SQL format for creating a table is:

CREATE TABLE table_name (
column_name datatype,
column_name datatype,
 ….
);

There are different datatypes that can be stored in a database column.Read more on the different datatypes Here. For our tables i will run the following SQL queries:

CREATE TABLE coffee_menu (
id INTEGER PRIMARY KEY,
coffee_name VARCHAR(255),
price FLOAT
);

CREATE TABLE Employees(
id INTEGER PRIMARY KEY,
employees_name VARCHAR(255),
role VARCHAR(255)
);

CREATE TABLE Orders (
 id INTEGER PRIMARY KEY,
 coffee_ordered INT,
 served_by INT,
 order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (coffee_ordered) REFERENCES coffee_menu(id),
 FOREIGN KEY (served_by) REFERENCES Persons(id)
);

That is it, that's all the queries needed to create our database.this will create all the tables and required columns for each table.