logic behind the code

Data tables are the most basic building blocks even of a large database. To query a particular data in a table we use SQL(Structured Query Language), and also those tables can be created by using SQL. In this post, you will learn how to create a simple Table in SQL.

Just suppose you are responsible for recording data about Employees in a company, you store those data in a table like below,

……………………………………..employee table……………………………………………

Above table has 5 columns and 5 rows (number of rows is dependent on the number of Employees in that company), but the first row is not considered because it is for the titles. Rows contain data for a particular Employee whereas columns contain data for a particular Title that is defined by the column.

Before inserting the data in the table, it has only Titles. So, here we consider that initial moment and create the table only with titles. After creating all the titles that are responsible for all 5 columns, we can insert our data into rows.

So, Let’s create the table and then insert all the data,

To create a table in SQL we use CREATE TABLE statement along with the Table name, here we have a table that records details of employees, so let’s name the Table as “employee”.

Syntax for creating a table in SQL;

CREATE TABLE <table_name>(
column_name   data_type[(size)]	constraint,
column_name	  data_type[(size)]	constraint,
...);

When you define the column names, the data type of the column and the size must be defined with its constraints.

What are Data types in SQL

A data can take place in different ways, numeric, characters, date, time,….etc. in the “employee” table, you can see there are different types of data that is placed in different columns.

For example,

• Age column contains numeric data (27,26,28,28),
• Department column contains Characters (SE,DB,SE,GD)…..etc.

we can simply say a data type is an attribute of a data.

Now, different Database Management Systems(DBMS’s) are supported for a different range of data types, but there are some common data types like numeric, string, and other miscellaneous things(dates, booleans, or even binary data) which are used by every DBMSs.

Here are some examples,

just click the link given below to see what other data types that are provided by Postgres,.

https://www.postgresql.org/docs/9.5/datatype.html

What is a SQL constraint

It is simply a rule that limits the type of data in a table. These rules could be either on a column level or a table level, rules that work on a particular column are called Column constraints whereas the rules that applied on the whole table are Table Constraints.

Let’s list down some important constraints in SQL,

click the link given below to see more details

https://www.postgresql.org/docs/9.4/ddl-constraints.html

The “Employee” table has 5 titles (or fields)in 5 columns(Emp_ID,Name,Age,Department,Salary). We have to define those titles when we are creating the table. let’s describe those titles one by one in details.

Emp_ID

This column contains the Employee ID for each employee in the company. One Emp_ID is given by the company only for one employee, so there is no chance of having the same Emp_ID by two employees, which means the same data cannot be repeated in the column (each of row in this column needs a unique data). And also a null value cannot be inserted because each of every employee has an ID.

When a person tries to enter null data or data that already exist, the table should restrict that by prompting an error message. This should happen only for this column.

So, we have to give a PRIMARY KEY constraint upon this special column.

Since this is an ID, we consider this is not a INTEGER value but a CHAR value.

NOTE:- why char, instead we can use varchar.
Now, char is used to fixed length strings, here this ID contains a fixed number of characters which is 3. So, char is the best datatype that is matched for this data

Name

Names of Employees who are assigned an Emp_ID by the company will be placed in this column.

Two employees can have the same name, so we can not use any unique constraint upon this column

each employee has a name, which means this column should have a NOT NULL constraint. But here we do not consider that, because we have the ID of every employees.

The data type of this column is VARCHAR ( number of characters in a name varies from name to name, so we use VARCHAR instead of CHAR ).

Age

Data type is INTEGER , no any constraint is applied on this column.

Department

The data type is CHARACTER, each of every employee is registered in any department, which means a null value cannot be inserted( NOT NULL constraint ).

Salary

The data type is INTEGER, no any constraint is applied on this column.

NOTE:- when a specific size of a column is not given, you can add any prefer size or not.

Now, you have learned all the things that you should learn before creating tables in SQL. So, let’s create the Employee table in SQL..

Syntax for creating the Employee table in SQL;

CREATE TABLE employee(
Emp_ID CHARACTER(3) PRIMARY KEY,
Name VARCHAR(50),
Age INTEGER,
Department CHARACTER(2) NOT NULL,
Salary INTEGER
);

NOTE:- in postgres you can not assign a size for INTEGER data type.

You can use “DROP TABLE <table name>” statement in SQL to delete a particular table in your Database

Ex:-

DROP TABLE employee;

Ex:- just suppose, you are given to check whether the Salary is greater than zero or not before inserting them in the Salary column. So you can put a CHECK constraint on the Salary column.

CREATE TABLE employee(
Emp_ID CHARACTER(3) PRIMARY KEY,
Name VARCHAR(50),
Age INTEGER,
Department CHARACTER(2) NOT NULL,
Salary INTEGER CHECK( Salary>0 )
);

Watch the video below to create this table in your PostgreSQL database.

This video is specially made for Ubuntu users,

For Windows users;

Step:1 Open the PSQL shell(if you haven’t install postgreSQL yet, then watch this video–>https://www.youtube.com/watch?v=7DQTtH0X-pg&t=2s).

Step:2 you have to type your server name.if your dataase in the same machine, just hit enter without typing anything. –> (for “Server [localhost]”).

Step:3 here, just hit enter to connect the default Postgres database.–> (for “Database [postgres] “).

Step:4 Hit enter without typing anything (if you have left the port number as default when you are installing Postgres)–> (for Port [5432]).

Step:5 to connect to the default user, Hit enter without typing anything(but, if you want to connect as a different user then type the name of that user here)–> (for “Username [postgres]”).

Step:6 Enter the password that you gave when you are installing the postgres–> (for “password for user postgres”).

Step:7 Wolah! you are connected to the database. Now skip the first 53 seconds of the above video and watch.

Now, you know how to create a table in SQL. In the next post you will learn to add the values into each column that you created here.