How to create a table in database

The CREATE TABLE statement is one of the data definition language (DDL) statements. DDL are SQL statements used to create, modify, or remove database structures. To create a table, a user must have the CREATE TABLE rights and sufficient storage in which user can create objects.

Syntax

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

Options

schema	Is  the same as the owner’s name
table	Is  the name of the table
DEFAULT expr	Specifies a default value if a value is omitted in the INSERT statement
column	Is the name of the column
datatype	Is the column’s data type and length

When the table is not present in the DB it will throw error message as shown below.

Example

Let us create table students having First name, Last name, Roll no, Address and City column.

CREATE TABLE students

(

FirstName varchar(255),

LastName varchar(255),

RollNo int,

Address varchar(255),

City varchar(255)

);

Now when the table is created we can show all the records of table by select statement as shown below:

select * from students

As we have not inserted any records in the table, hence no rows are reported.

TAB and COL

We have tab to show all the tables currently present in the database.

Also we have col to search for specific table name when we only know column name for reference.

This two are very useful for finding tables in day to day operations.

select * from tab

where tname = 'STUDENTS'

select * from col

where cname = 'FIRSTNAME'