SQL
SQL, Structure Query Language, is programming language
designed to manage data stored in relational databases.
SQL commands are the instructions used to communicate
with database to perform tasks , functions , and queries on
data.
SQL commands can be used to do functions such as create
tables, add data to tables ,modify data , search the
database as well as drop tables.
SQL
SQL operates through simple ,declarative statements .It
maintains consistency, accuracy ,integrity of databases
irrespective size.
SQL, was developed at IBM by Donald D. Chamberlin and
Raymond F.Boyce in the early 1970s.
This was initially called SEQUEL(Structured English QUEry
Language)
SQL
The main objective of SQL is to update ,store ,manipulate
and retrieve data stored in relational database.
Structured Query Language
SQL is designed to support
1.Data Definition
2.Data Manipulation
3.Data Control
in relational database management system.
Structured Query Language
SQL consist of 3 facilities:
1.Data Definition Language (DDL)
2.Data Manipulation Language (DML)
3.Data Control Language (DCL)
Data Definition Language (DDL)
Sr.No. Command & Description
CREATE
Creates a new table, a view of a table, or other
1
object in the database.
ALTER
2 Modifies an existing database object, such as a table.
DROP
Deletes an entire table, a view of a table or other
3
objects in the database.
Data Manipulation Language (DML)
Sr.No. Command & Description
SELECT
1
Retrieves certain records from one or more tables.
INSERT
2 Creates a record.
UPDATE
3 Modifies records.
DELETE
4 Deletes records.
Data Control Language (DCL)
Sr.No. Command & Description
GRANT
1
Gives a privilege to user.
REVOKE
2
Takes back privileges granted from user.
The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new
SQL database.
CREATE DATABASE databasename;
The following SQL statement creates a database called
"testDB"
CREATE DATABASE testDB;
The SQL Statement
Make sure you have admin privilege before creating
any database.
Once a database is created, you can check it in the
list of databases with the SQL command:
SHOW DATABASES;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an
existing SQL database.
Syntax
DROP DATABASE databasename;
Be careful before dropping a database. Deleting a
database will result in loss of complete information
stored in the database!
The SQL DROP DATABASE Statement
The SQL statement drops the existing database
"testDB":
Example
DROP DATABASE testDB;
Make sure you have admin privilege before
dropping any database. Once a database is
dropped, you can check it in the list of databases
with the SQL command: SHOW DATABASES;
The 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.).
SQL CREATE TABLE Example
Creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address,
and City
CREATE TABLE Persons ( PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
The empty "Persons" table will now look like this:
PersonID LastName FirstName Address City
The empty "Persons" table can now be filled with
data with the SQL INSERT INTO statement
The SQL DROP TABLE Statement
The DROP TABLE statement is used to drop an
existing table in a database.
Syntax
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a
table will result in loss of complete information
stored in the table!
DROP TABLE Persons;
SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete
the data inside a table, but not the table itself.
Syntax
TRUNCATE TABLE table_name;
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;
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the syntax (Note:
some databases systems don't allow deleting a
column)
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE - ALTER/MODIFY COLUMN
To change the data type of a column in a table, use
the following syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Change Data Type Example
Change the data type of the column named
"DateOfBirth" in the "Persons" table.
SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
"DateOfBirth" column is now of type year and is going
to hold a year in a two- or four-digit format.
SQL Create Constraints
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,
....
);
SQL constraints
SQL constraints are used to specify rules for the
data in a table.
Constraints are used to limit the type of data that
can go into a table.
This ensures the accuracy and reliability of the
data in the table.
If there is any violation between the constraint and
the data action, the action is aborted.
SQL constraints
Constraints can be column level or table level.
Column level constraints apply to a column, and
table level constraints apply to the whole table.
Constraints are commonly used in SQL
NOT NULL - Ensures that a column cannot have a NULL
value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another
table
Constraints are commonly used in SQL
CHECK - Ensures that all values in a column satisfies a
specific condition
DEFAULT - Sets a default value for a column when no value
is specified
INDEX - Used to create and retrieve data from the
database very quickly
SQL NOT NULL Constraint
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT
accept NULL values.
This enforces a field to always contain a value,
which means that you cannot insert a new record,
or update a record without adding a value to this
field.
The following SQL ensures that the "ID",
"LastName", and "FirstName" columns will NOT
accept NULL values:
SQL NOT NULL Constraint
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
If the table has already been created, you can add a
NOT NULL constraint to a column with the ALTER
TABLE statement.
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column
are different.
Both the UNIQUE and PRIMARY KEY constraints provide a
guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE
constraint.
However, you can have many UNIQUE constraints per
table, but only one PRIMARY KEY constraint per table.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
To name a UNIQUE constraint, and to define a
UNIQUE constraint on multiple columns
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
DROP a UNIQUE Constraint
ALTER TABLE Persons
DROP INDEX UC_Person;
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each
record in a database table.
Primary keys must contain UNIQUE values, and cannot
contain NULL values.
A table can have only one primary key, which may
consist of single or multiple fields.
SQL PRIMARY KEY on CREATE TABLE
SQL PRIMARY KEY Constraint
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)
);
To allow naming of a PRIMARY KEY constraint, and for defining
a PRIMARY KEY constraint on multiple columns, use the SQL
syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
There is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO
COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID"
column when the table is already created.
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the SQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables
together.
A FOREIGN KEY is a field (or collection of fields) in one
table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child
table, and the table containing the candidate key is
called the referenced or parent table.
Persons" table:
PersonID LastName FirstName Age
Orders" table:
OrderID OrderNumber PersonID
"PersonID" column in the "Orders" table points to the
"PersonID" column in the "Persons" table.
The "PersonID" column in the "Persons" table is the PRIMARY
KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is a FOREIGN KEY
in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that
would destroy links between tables.
The FOREIGN KEY constraint also prevents invalid data from
being inserted into the foreign key column, because it has to
be one of the values contained in the table it points to.
SQL FOREIGN KEY on CREATE TABLE
Create a FOREIGN KEY on the "PersonID" column when the
"Orders" table is created.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column
when the "Orders" table is already created:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining
a FOREIGN KEY constraint on multiple columns, use the
following SQL syntax:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following
SQL
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL CHECK Constraint
The CHECK constraint is used to limit the value range
that can be placed in a column.
If you define a CHECK constraint on a single column it
allows only certain values for this column.
If you define a CHECK constraint on a table it can limit
the values in certain columns based on values in other
columns in the row.
SQL CHECK on CREATE TABLE
SQL CHECK Constraint
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)
);
SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the
table is already created, use the SQL:
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining
a CHECK constraint on multiple columns
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK
(Age>=18 AND City=‘Mumbai');
DROP a CHECK Constraint
To drop a CHECK constraint, use the SQL
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for
a column.
The default value will be added to all new records IF no
other value is specified.
SQL DEFAULT on CREATE TABLE
Sets a DEFAULT value for the "City" column when the
"Persons" table is created.
SQL DEFAULT Constraint
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT ‘Mumbai'
);
The DEFAULT constraint can also be used to insert system
values, by using functions like GETDATE():
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when
the table is already created, use the following SQL:
ALTER TABLE Persons
ALTER City SET DEFAULT ‘Mumbai';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in
tables.
Indexes are used to retrieve data from the database very fast.
The users cannot see the indexes, they are just used to speed
up searches/queries.
The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a
table.
Syntax
It is possible to write the INSERT INTO statement in two ways.
The first way specifies both the column names and the values
to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
The SQL INSERT INTO Statement
If you are adding values for all the columns of the table, you
do not need to specify the column names in the SQL query.
However, make sure the order of the values is in the same
order as the columns in the table.
The INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
