CREATE TABLE

CREATE TABLE statement is used to create a new table in the database. A table definition consists of the table name, a list of columns, their data types, default values, and any integrity constraints as shown in below SQL server syntax –

CREATE TABLE <table_name1> (

<column_name1> <data_type> <default_value> <identity_specification> <column_constraint>,

<column_name2> <data_type> <default_value>                          <column_constraint>,

  …,

  <table_constraint1>,

  <table_constraint2>,

  …

);

Syntax Description: After the key words CREATE TABLE, the table_name is specified. Within a pair of parentheses, a list of column definitions follows.

Column Definition-Each column is defined by its name, data type, an optional default value(Constant value in accordance to data type defined), and optional column constraints

After the list of column definitions, we can specify table constraints like Primary and Foreign Keys, Unique conditions, and general column conditions.

Note–
In case of SQL Server-<table_name1> argument will be-
[database_name.][schema_name.][table_name1]
where [database_name.] is the name of Database in which the table is being created and [shema_name.] is the name of Schema and [table_name1] is the name of the table being created. Assume your database name is student_db and schema is dbo and table name is msstudentsnew then in below example msstudentsnew will be replaced by-
student_db.dbo.msstudentsnew

Example-

CREATE TABLE msstudentsnew (

  st_id                   INT           IDENTITY(1,1)                PRIMARY KEY NOT NULL,

  st_name            VARCHAR(100)          DEFAULT ‘n/a’   NOT NULL,

  st_subject         VARCHAR(100),

  st_city               VARCHAR(100),

  st_fees               INT,

  st_regno            INT,

  CONSTRAINT reg_check CHECK (st_regno>0)

);

Example Description-

Parameters	Key	Description
<table_name1>	msstudentsnew	is the table name
<column_name1>	st_id	is the column’s name
<data_type>	INT	is the data type
<identity_specification>	IDENTITY(1,1)	states that column will have auto generated values starting at 1 and incrementing by 1 for each new row.
<column_constraint>	PRIMARY KEY	states that all values in this column will have unique values
	NOT NULL	states that this column cannot have null values
<table_constraint>	CONSTRAINT	reg_check is constraint name, CHECK will check the expression provided after keyword CHECK

Create Table From Select

Syntax-

CREATE TABLE <table_name1> AS [Select_querry] ;

Example-

CREATE TABLE msstudentsnew_copy AS SELECT * FROM msstudentsnew;