SQL Database Tables

In this chapter we will learn what is table and how we can create it.

What is Table?

Table is collection of rows or records with tabular format. Actual data is stored in database with tables. Table contains property like Columns Name, Data Type, Table Name, Keys, and Constraints etc...

Example of table:

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990

Above is an example of table which contain actual data for employee with columns like Employee Code, First Name, Last Name, Gender and Birth Date.

Syntax to create table:

CREATE TABLE Table_Name
(
Column1 DataType(Size),
Column2 DataType(Size),
Column3 DataType(Size),
Column4 DataType(Size),
Column5 DataType(Size),
.....
ColumnN DataType(Size),
PRIMARYKEY (Column)
);

In above syntax example, we can see table we can create by defining column or field name, their data type and setup primary key to particular column at the end. We will look into more details about Primary Key in upcoming chapter.

Few rules we need to follow while creating table in database.

  • Table name must not be duplicated in database.
  • Column name must not be duplicated in same table.
  • Primary key is optional and can be defined with one column only.
  • Table name doesn’t contain special characters like #, @ or $ etc...
  • Table name must be string type of value.
  • Max number of column limit is depends on database system.

Let’s create an example of one table with actual field name and data types.

Example to create table:

CREATE TABLE Employee
(
Id INT IDENTITY,
EmployeeCode VARCHAR(10),
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
BirthDate DateTime,
PRIMARY KEY (Id)
);

In above example we can see there are six columns with having one as Integer(Id), one as DateTime(BirthDate) and all others as Varchar data type(EmployeeCode, FirstName, LastName, Gender).

We have also added one primary key constraint to Id column which will be used to store unique record for each row. Fr example, it will store auto number for each record by increasing one to each new record.

Create table from existing table:

There is also another way to create table of any existing table. For example, if you have already employee table with all above fields and if you want to create another copy of same table with different name, you can do that easily by using Create Table and Select sql statement.

Syntax:

CREATE TABLE TableName_New AS
SELECT ColumnName1, ColumnName2, ColumnName3,...
FROM Tablename_Existing

Above example will create mirror copy of existing table Employee.