Home » SQL » Create Tables in SQL with examples

Create Tables in SQL with examples

How to create tables in SQL ?, you need to use CREATE TABLE statement in SQL to create a new table in a database. Table are combination of Rows and columns  which can be retrieve with the use of SQL commands.

What are mandatory parameters for CREATE Tables in SQL?

  • First, specify the name of the database in which the table is created
  • Second, specify the name of the new table
  • Third, each table should have Columns name specify the columns name
  • Fourth, all column should have the Data Type

Syntax: for CREATE TABLE

CREATE TABLE [database_name] table_name (
             column_1 data_type NOT NULL,
             column_2 data_type,
             column_3 data_type,
             ... 
             );

Remember before use of CREATE TABLE command in SQL always remember to use the Database with below syntax command, else table will not be created in the Database in which you want to create Table

USE Database_name;

with the selection of database, it is sure that the table which you are creating is in that database, so that you can use it in future.

Example #1: Create table “Customer_Test_Table“

CREATE TABLE Customer_Test_Table (
             Customer_ID INT PRIMARY KEY IDENTITY (1, 1)
             LastName VARCHAR(50) NOT NULL,
             FirstName VARCHAR(50) NOT NULL,
             Address VARCHAR(255),
             City VARCHAR(20)
       );

This CREAT TABLE command will create table with 5 columns and each columns has specified the Data Type. Currently this table only contain the structure of Table. So there is no data in table. If you would like to insert data in table INSERT INTO command used to do the same.

You can see the table and verify the table has been created or not, use below command called SELECT statement

SELECT * 
FROM Customer_Test_Table;

Result:

create table in SQL server

The above Customer_Test_Table contains 5 columns as explained below

  • Customer_ID  containes the IDs of customer with is integer datatype and as Primary key, The IDENTITY(1,1) instructs SQL Server to automatically generate integer numbers for the column starting from one and increasing by one for each new row
  • LastName column specify the customer last name with VARCHAR datatype and this column can store up to 50 characters. NOT NULL values meaning this column should not have blank and NULL value there should be last name of customer
  • FirstName column specify the customer First name with VARCHAR datatype and this column can store up to 50 characters. NOT NULL values meaning this column should not have blank and NULL value there should be First name of customer
  • Address column specify the customer address with VARCHAR datatype and this column can store up to 255 characters
  • City column specify the City with VARCHAR datatype and this column can store up to 20 characters