Basic about CREATE TABLE Statement – using and example

This SQL tutorial explains how to use the SQL CREATE TABLE statement with syntax, examples, and exercise exercises.

Description

The SQL CREATE TABLE assertion lets in you to create and outline a table.

Syntax

The syntax for the CREATE TABLE announcement in SQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name The identify of the desk that you wish to create. column1, column2 The columns that you wish to create in the table. Each column should have a datatype. The column ought to both be described as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let’s look at a SQL CREATE TABLE example.

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

This SQL CREATE TABLE instance creates a table known as suppliers which has three columns.

The first column is referred to as supplier_id which is created as a range datatype (maximum 10 digits in length) and can now not comprise null values. The second column is referred to as supplier_name which is a char datatype (50 maximum characters in length) and also can now not comprise null values. The 0.33 column is called contact_name which is a char datatype however can incorporate null values.

Now the only trouble with this SQL CREATE TABLE announcement is that you have no longer defined a principal key for the table. We may want to adjust this SQL CREATE TABLE declaration and define the supplier_id as the essential key as follows:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Learn about primary keys.

Learn about foreign keys.

Practice Exercise #1:

Create a SQL desk referred to as clients that shops consumer ID, name, and tackle information.

Solution for Practice Exercise #1:

The SQL CREATE TABLE announcement for the clients desk is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10)
);

Practice Exercise #2:

Create a SQL desk referred to as customers that shops purchaser ID, name, and tackle information.

But this time, the client ID must be the principal key for the table.

Solution for Practice Exercise #2:

The SQL CREATE TABLE announcement for the customers table is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Practice Exercise #3:

Based on the departments desk below, create a SQL table called employees that stores employee number, worker name, department, and revenue information. The predominant key for the personnel desk be the employee number. Create a overseas key on the personnel table that references the departments desk based totally on the department_id field.

CREATE TABLE departments
( department_id int NOT NULL,
  department_name char(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The SQL CREATE TABLE statement for the employees desk is:

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);