Overview 

SQL is a computer language that is used for storing, manipulating, and retrieving data in a structured format. This language was invented by IBM. Here SQL stands for Structured Query Language. Interacting databases with SQL queries, we can handle a large amount of data. There are several SQL-supported database servers such as MySQL, PostgreSQL, sqlite3 and so on. Data can be stored in a secured and structured format through these database servers. SQL queries are often used for data manipulation and business insights better.

SQL DML Statements

1. SQL CREATE DATABASE Statement

• The CREATE DATABASE statement is used to create a posed to create a new SQL database.
• Syntax:
  CREATE DATABASE databasename;

2. SQL DROP DATABASE Statement

• The DROP DATABASE statement is used to drop an existing SQL database.
• Syntax:
  DROP DATABASE databasename;

3. SQL CREATE TABLE Statement

• The CREATE TABLE statement is used to create a new table in a database.
• Syntax:
  CREATE TABLE table_name(
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ....
  );
• The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
• Example
  CREATE TABLE Persons (
  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  );

4. SQL DROP TABLE Statement

• The DROP TABLE statement is used to drop an existing table in a database.
• Syntax
  DROP TABLE table_name;

5. SQL ALTER TABLE Statement

• The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column

• To add a column in a table, use the following syntax:
  • ALTER TABLE table_name
  • ADD column_name datatype;
• The following SQL adds an "Email" column to the "Customers" table:
  • ALTER TABLE Customers
  • ADD Email varchar(255);

ALTER TABLE - DROP COLUMN

• To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
  • ALTER TABLE table_name
  • DROP COLUMN column_name;
• The following SQL deletes the "Email" column from the "Customers" table:
  • ALTER TABLE Customers
  • DROP COLUMN Email;

ALTER TABLE - ALTER/MODIFY COLUMN

• To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN Column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

SQL Constraints

SQL constraints are used to specify rules for data in a table. Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
Syntax:

• CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  column3 datatype constraint,
  ...
  );

The following constraints are commonly used in SQL:
a) NOT NULL:

• Ensures that a column cannot have a NULL value.
• The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values:
  • CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
    );

b) UNIQUE:

• Ensures that all values in a column are different.
• The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:
  • CREATE TABLE Persons{
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
    );

c) PRIMARY KEY:

• A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
• The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
  • CREATE TABLE Persons
    (ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), Age int,
    PRIMARY KEY (ID)
    );

d) FOREIGN KEY: 

• Uniquely identifies a row/record in another table.
• CREATE TABLE Orders (
  OrderID int NOT NULL,
  OrderNumber int NOT NULL,
  PersonID int,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (PersonID) REFERENCES Persons (PersonID)
  );

e) CHECK: 

• Ensures that all values in a column satisfies a specific condition.
• The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years:
• CREATE TABLE Persons (
  ID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int,
  CHECK (Age>=18)
  );

f) DEFAULT:

• Sets a default value for a column when no value is specified.
• The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:
  • CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Birtamode'
    );

Altering Constraints

1) Primary Key

• To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:
  • ALTER TABLE Persons
    ​​​​​​​ADD PRIMARY KEY (ID);
• To drop a PRIMARY KEY constraint, use the following SQL:
  • ALTER TABLE Persons
    DROP PRIMARY KEY(ID);

2) Unique Constraint

• To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:
  • ALTER TABLE Persons
    ADD UNIQUE (ID);
• To drop a UNIQUE constraint, use the following SQL:
  • ALTER TABLE Persons
    DROP CONSTRAINT constraint_name;

3) Foreign Key

• To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
  • ALTER TABLE Orders
    ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
• To drop a FOREIGN KEY constraint, use the following SQL:
  • ALTER TABLE Orders
    DROP FOREIGN KEY foreign_key_name;
  • ALTER TABLE Orders
    DROP CONSTRAINT constraint_name;

4) Check Constraint

• To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:
  ALTER TABLE Persons
  ADD CHECK (Age>=18);
• To drop a CHECK constraint, use the following SQL:
  ALTER TABLE Persons
  DROP CHECK Age;

5) Default Constraint

• To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
  ALTER TABLE Persons
  MODIFY City DEFAULT 'Sandnes';
• To drop a DEFAULT constraint, use the following SQL:
  ALTER TABLE Persons
  ALTER COLUMN City DROP DEFAULT;