CREATE DATABASE STATEMENT

SYNTAX EXAMPLE
CREATE DATABASE <databasename>; CREATE DATABASE SHGS;

DELETE DATABASE STATEMENT

SYNTAX EXAMPLE
DROP DATABASE <databasename>; DROP DATABASE SHGS;

CREATE TABLE STATEMENT

SYNTAX EXAMPLE
CREATE TABLE <table_name> ( CREATE TABLE Persons (
<column1> <datatype> <constraint>, PersonID integer Primary Key,
<column2> <datatype> <constraint>, FirstName varchar(15) NOT NULL,
<column3> <datatype> <constraint>, LastName varchar(15),
.... , Address varchar(255),
<column_n> <datatype> <constraint> City varchar(25)
); );
CREATE TABLE Orders (
OrderID integer Primary Key,
OrderNumber integer,
PersonID integer
);

DELETE (DROP) TABLE STATEMENT

SYNTAX EXAMPLE
DROP TABLE <table_name>; DROP TABLE Persons;

DELETE TABLE ALL DATA BUT NOT THE TABLE STATEMENT

SYNTAX EXAMPLE
TRUNCATE TABLE <table_name>; TRUNCATE TABLE Persons;

ALTER TABLE - ADD COLUMN AT RIGHT-MOST PLACE IN TABLE

SYNTAX EXAMPLE
ALTER TABLE <table_name> ALTER TABLE Persons
ADD <column_name> <datatype> <constraint>; ADD Email varchar(255);

ALTER TABLE - DELETE COLUMN IN A TABLE

SYNTAX EXAMPLE
ALTER TABLE <table_name> ALTER TABLE Persons
DROP COLUMN <column_name>; DROP COLUMN Email;

SQL CONSTRAINTS – RULES FOR SPECIFIC COLUMNS

NOT NULL - Ensures that a column cannot have any empty or NULL value
UNIQUE - Ensures that all values in a column are different which can include a NULL value.
PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
FOREIGN KEY - Prevents actions that would destroy links between tables in RDBMS.
CHECK - Ensures that the values in a column satisfies a specific condition mentioned.
DEFAULT - Sets a default value for a column if no value is specified in insert statement.

ADD SQL CONSTRAINTS TO EXISTING TABLE USING ALTER STATEMENT

SYNTAX (NOT NULL) EXAMPLE
ALTER TABLE <table_name> ALTER TABLE Persons
ALTER COLUMN <column_name> <datatype> ALTER COLUMN City varchar(25) NOT NULL;
NOT NULL;
 SYNTAX (UNIQUE) EXAMPLE
ALTER TABLE <table_name> ALTER TABLE Persons
ADD UNIQUE (<column_name>); ADD UNIQUE (Address);

SYNTAX (PRIMARY KEY) EXAMPLE

ALTER TABLE <table_name> ALTER TABLE Persons
ADD PRIMARY KEY (<column_name>); ADD PRIMARY KEY (PersonID);

SYNTAX (FOREIGN KEY) EXAMPLE

CREATE TABLE Orders (
OrderID integer NOT NULL,
OrderNumber integer NOT NULL,
SQL FOREIGN KEY on CREATE TABLE PersonID integer,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SYNTAX – ALTER TABLE <table_name>
FOR EXISTING TABLE ADD FOREIGN KEY (<column_name>) REFERENCES <main_table > (<column_name>);
EXAMPLE – ALTER TABLE Orders
FOR EXISTING TABLE ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

SYNTAX (CHECK) EXAMPLE

CREATE TABLE Voter (
V_ID integer NOT NULL,
FirstName varchar(15) NOT NULL,
SQL CHECK on CREATE TABLE LastName varchar(15),
Age int,
CHECK (Age>=18)
);
SYNTAX – ALTER TABLE <table_name>
FOR EXISTING TABLE ADD CHECK (<column_name> <comparision_operator> <value/data_to_compare);
EXAMPLE – ALTER TABLE Persons
FOR EXISTING TABLE ADD CHECK (Age>=18);

SYNTAX (DEFAULT) EXAMPLE

CREATE TABLE Persons (
PersonID integer Primary Key,
FirstName varchar(15) NOT NULL,
SQL CHECK on CREATE TABLE LastName varchar(15),
Address varchar(255),
City varchar(25) DEFAULT ‘ASANSOL’
);
SYNTAX – ALTER TABLE <table_name>
FOR EXISTING TABLE ALTER <column_name> DEFAULT <value/data_to_set);
EXAMPLE – ALTER TABLE Persons
FOR EXISTING TABLE ALTER City SET DEFAULT 'ASANSOL';

DROP SQL CONSTRAINTS FROM EXISTING TABLE USING ALTER STATEMENT

SYNTAX (NOT NULL) EXAMPLE
ALTER TABLE <table_name> ALTER TABLE Persons
DROP < constraint>; DROP PRIMARY KEY;