SQL CREATE Command is used to Create a table or database in SQL. We use the same CREATE command in MySQL and MsSQL Server.
CREATE DATABASE
SYNTAX:
CREATE DATABASE nameofdatabase;
EXAMPLE:
CREATE DATABASE students;
DESCRIPTION:
CREATE Query can also be used to create database. Just type CREATE DATABASE followed by the name of database. Once a databse is created then we can use create query again to create tables inside it.
CREATE TABLE
SYNTAX:
CREATE TABLE nameoftable(column1, column2, column3)
DESCRIPTION:
CREATE TABLE query is used for creating tables. We need to specify the name of columns (in example below itemid, itemname etc) and the datatypes(int, decimal). This example is for MySQL.
EXAMPLE:
CREATE TABLE ITEMS_SHAHID4
(
ItemID INT PRIMARY KEY AUTO_INCREMENT,
ItemName VARCHAR(60),
ItemPrice DECIMAL(10, 2),
ItemCat VARCHAR(60),
ItemDesc TEXT
)
Examples of Create Table Command for Ms SQL Server
CREATE TABLE
When a table is created its name, columns, data types, field length (not all data types) and constraints (optional) are defined using the following syntax:
Create table TableName (ColumnName DataType (Length) Constraints)
create table Product
(
Pid int identity(1,1) primary key,
Pname varchar(50),
Pcat varchar(20),
Pprice decimal(8,2),
Pdesc varchar(100)
)
create table Customer
(
Cid int identity(1000,10) primary key,
Cname varchar(50),
Cemail varchar(30),
Cadd varchar(100),
Cccno bigint,
Ccctype varchar(15)
)
create table Order
(
Oid bigint identity(10000,1) primary key,
Odate datetime,
Ostatus varchar(15),
Cid Int
)
create table OrderItems
(
ItemID Int identity(1,1),
ItemName varchar(50),
ItemPrice decimal(8,2),
ItemQty int,
Oid bigint
)
All tables have a Primary Key column (Pid, Cid, Oid, ItemID) and an identity. Identity provides an auto-generated number that is created every time a row is inserted into the table; it has a starting point and an increment. Identity (10, 2) means that the first record will have an ID number 10 and any following records will have an increment of two (10, 12, 14, 16, etc