Types of SQL

 

SQL (Structured Query Language)

This is a common language through which we can interact with the database. SQL is classified mainly into three categories.

 

1. DDL (Data Definition Language).

1. Create 2. Alter 3. Truncate 4. Drop

 

2. DML (Data Manipulation Language).

1. Select 2. Insert 3. Update 4. Delete

 

3. TCL (Transaction Control Language).

1.Commit 2. Rollback 3. Save point

 

4. DCL (Data Control Language)

 

DDL

DATA DEFINITION LANGUAGE

These commands are used to create, modify and delete database objects.

DDL Commands are Auto-commit.

 

Create: This command is used to create database objects such as – Tables, Views,

Indexes, Synonyms, Sequences, Stored Procedures, Triggers, Functions, Packages and

user-defined data-types.

 

Alter: This command is used to modify the structure of the database objects.

 

Drop: This command is used to remove the objects from the database.

 

Truncate: Using this command we can permanently remove the rows from a table.

Here we cannot append any clauses to the Truncate statement.

 

Data Types

ORACLE SQL Server DB2
int int Int
char char Char
varchar2 —-
varchar varchar Varchar
number numeric Numeric
float float Float
decimal decimal Decimal
Date smalldatetime Date
timestramp datetime Timestamp

 

Constraints

 Constraint restricts the values that the table can store.

We can declare integrity constraints at the table level or column level.

In column-level constraints the constraint type is specified after specifying the column datatype i.e., before the delimiting comma.

Where as in the table-level constraints the constraint type is going to be specified as

separate comma-delimited clauses after defining the columns.

 

There are five constraints

1. Not Null

2. Unique Key

3. Check

4. Primary Key

5. Foreign Key

 

1. Not Null

If a column in a table is specified as Not Null, then it’s not possible to insert a null in such a column. It can be implemented with create and alter commands. When we implement the Not Null constraint with alter command there should not be any null values in the existing table.

 

2. Unique Key

The unique constraint doesn’t allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed. Note: There is a different behavior in the following Relational Databases.

 

DB2: To enforce unique constraint, the column must be a Not Null column. That means, we can not insert a single row with a Null value against the Unique Key constraint column

 

MS SQL Server: In this, we can insert one Row with a Null value against the Unique Key constraint column.

 

Oracle: In this, we can insert any number of Rows with a Null value against the Unique Key constraint column. Please keep it in mind that two Null values are not equal.

 

3. Check

Check constraint is used to restrict the values before inserting into a table.

 

4 Primary Key

The key column with which we can identify the entire Table is called as a primary key column. A primary key is a combination of a Unique and a Not Null constraint, it will not allow null and duplicate values. A table can have only one primary key. A primary key can be declared on two or more columns as a Composite Primary Key.

 

5 Foreign Key

Columns defined as foreign keys refer the Primary Key of other tables. The Foreign Key “points” to a primary key of another table, guaranteeing that you can’t enter data into a table unless the referenced table has the data already which enforces the REFERENTIAL INTEGRITY. This column will take Null values.

Except Not Null constraint all other constraints can be given at both the column level and table level.

 

TABLE CREATION

 

Syntax

CREATE TABLE <tab|e name> (

<co|umn_name1> <data type> [(<width>)]

[constraint <constraint name> <constraint type>],

<co|umn_name2> <data type> [(<width>)],

<co|umn_name3> <data type> [(<width>)],

<co|umn_name4> <data type> [(<width>)],

<co|umn_nameN> <data type> [(<width>)]

);

 

 

Example

CREATE TABLE Employee(

empno number(4) constraint pk_empno primary key,

ename varchar2(50) constraint nn_ename not null,

salary number(10,2),

hire_date date,

gender char(1) constraint chk_gen check(gender in (‘M’, ‘F’, ‘m’, ‘f’)),

email varchar2(50) unique

);

 

 

ALTER TABLE

Syntax

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> <constraint type>

<column name>;

Syntax to add referential integrity

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>

FOREIGN KEY(<foreign key column>)

REFERNCES <parent table name>(<primary key column>);

 

Example

ALTER TABLE Employee ADD CONSTRAINT nn_hdate NOT NULL hire_date;

ALTER TABLE Employee ADD CONSTRAINT Refidept

FOREIGN KEY (deptno)

REFERENCES Department(deptno);

 

 

DROP

Syntax

DROP <OBJECT> <object name>;

 

Example

DROP PROCEDURE ins_emp;

DROP TABLE Employee;

 

 

TRUNCATE

Syntax

TRUNCATE TABLE <table-name>;

 

Example

TRUNCATE TABLE Employee;

 

 

DML

DATA MANIPULATION LANGUAGE

 

These commands are used to append, change or remove the data in a Table.

COIVIIVIIT/ROLLBACK statement should be given to make the changes permanent or to revert back.

 

 

INSERT

Using this command we can append data into tables.

Syntax

INSERT INTO <table name>(<column_name1>, <column_name2>, …)

VALUES (column1_value, column2_value, …);

INSERT INTO <talJle-name>(<column_name2>, <co|umn_name1>, …)

VALUES (column2-value, columnl-value, …);

INSERT INTO <talJle-name> VALUES (valuel, value2, …);

 

Example

INSERT INTO Employee(empno, ename, salary, hire_date, gender, email)

VALUES(1234, ‘JOHN’, 8000, ’18-AUG-80, ‘M’, ‘john@miraclesoft.com’);

INSERT INTO Employee(email , ename, empno, hire_date, gender, salary)

VALUES(‘rhonda@miraclesoft.com’, ‘RHONDA’, 1235, ’24-JUL-S1′, ‘F’, 7500)

INSERT INTO Employee

VALUES(1236, ‘JACK’, 15000, ’23-SEP-79′, ‘m’, ‘jack@miraclesoft.com’);

 

 

UPDATE

This command is used to modify the data existing in the tables.

 

Syntax

UPDATE <table-name> SET <column-name> = <value>;

UPDATE <table-name> SET <column-name> = <value> WHERE <condition>;

 

Example

UPDATE Employee SET salary = 15000;

UPDATE employee SET salary = 15000 WHERE empno = 1235;

 

 

DELETE

This command is used to remove the data from the tables.

 

Syntax

DELETE FROM <table-name>;

DELETE FROM <table-name>WHERE <condition>;

 

Example

DELETE FROM Employee;

DELETE FROM Employee WHERE empno = 1236;

 

 

 

DCL

 

 

TCL

TRANSACTION CONTROL LANGUAGE

 

These command are used to maintain the consistency of the database.

 

Commit

This command is used to make the transaction permanent.

Rollback

This command is used to undo the recent transaction.

Save Point

This is used to rollback a transaction to a specified point.

 

 

 

 

 

 

Constraints

 

Constraint restricts the values that the table can store.

We can declare integrity constraints at the table level or column level.

In column-level constraints the constraint type is specified after specifying the column datatype i.e., before the delimiting comma.

Where as in the table-level constraints the constraint type is going to be specified as

separate comma-delimited clauses after defining the columns.

 

There are five constraints

1. Not Null

2. Unique Key

3. Check

4. Primary Key

5. Foreign Key

1. Not Null

 

If a column in a table is specified as Not Null, then it’s not possible to insert a null in such

a column. It can be implemented with create and alter commands. When we implement

the Not Null constraint with alter command there should not be any null values in the

existing table.

 

2. Unique Key

 

The unique constraint doesn’t allow duplicate values in a column. If the unique constraint

encompasses two or more columns, no two equal combinations are allowed.

Note: There is a different behavior in the following Relational Databases.

 

DB2: To enforce unique constraint, the column must be a Not Null column. That means,

we can not insert a single row with a Null value against the Unique Key constraint column

 

MS SQL Server: In this, we can insert one Row with a Null value against the Unique Key

constraint column.

 

Oracle: In this, we can insert any number of Rows with a Null value against the Unique

Key constraint column. Please keep it in mind that two Null values are not equal.

 

3. Check

 

Check constraint is used to restrict the values before inserting into a table.

 

4 Primary Key

The key column with which we can identify the entire Table is called as a primary key

column. A primary key is a combination of a Unique and a Not Null constraint, it will not

allow null and duplicate values. A table can have only one primary key.

A primary key can be declared on two or more columns as a Composite Primary Key.

 

5 Foreign Key

Columns defined as foreign keys refer the Primary Key of other tables. The Foreign Key

“points” to a primary key of another table, guaranteeing that you can’t enter data into a

table unless the referenced table has the data already which enforces the REFERENTIAL

INTEGRITY. This column will take Null values.

 

Except Not Null constraint all other constraints can be given at both the column level and table

level.

 

 

TABLE CREATION

 

Syntax

CREATE TABLE <tab|e name> (

<co|umn_name1> <data type> [(<width>)]

[constraint <constraint name> <constraint type>],

<co|umn_name2> <data type> [(<width>)],

<co|umn_name3> <data type> [(<width>)],

<co|umn_name4> <data type> [(<width>)],

<co|umn_nameN> <data type> [(<width>)]

);

 

Example

 

CREATE TABLE Employee(

empno number(4) constraint pk_empno primary key,

ename varchar2(50) constraint nn_ename not null,

salary number(10,2),

hire_date date,

gender char(1) constraint chk_gen check(gender in (‘M’, ‘F’, ‘m’, ‘f’)),

email varchar2(50) unique

);

 

 

ALTER TABLE

 

Syntax

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> <constraint type>

<column name>;

Syntax to add referential integrity

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>

FOREIGN KEY(<foreign key column>)

REFERNCES <parent table name>(<primary key column>);

 

Example

ALTER TABLE Employee ADD CONSTRAINT nn_hdate NOT NULL hire_date;

ALTER TABLE Employee ADD CONSTRAINT Refidept

FOREIGN KEY (deptno)

REFERENCES Department(deptno);

 

 

DROP

 

Syntax

DROP <OBJECT> <object name>;

 

Example

DROP PROCEDURE ins_emp;

DROP TABLE Employee;

 

 

TRUNCATE

Syntax

TRUNCATE TABLE <table-name>;

 

Example

TRUNCATE TABLE Employee;

 

 

 

 

DML

DATA MANIPULATION LANGUAGE

 

These commands are used to append, change or remove the data in a Table.

COIVIIVIIT/ROLLBACK statement should be given to make the changes permanent or to

revert back.

 

 

INSERT

Using this command we can append data into tables.

 

Syntax

INSERT INTO <table name>(<column_name1>, <column_name2>, …)

VALUES (column1_value, column2_value, …);

INSERT INTO <talJle-name>(<column_name2>, <co|umn_name1>, …)

VALUES (column2-value, columnl-value, …);

INSERT INTO <talJle-name> VALUES (valuel, value2, …);

 

Example

INSERT INTO Employee(empno, ename, salary, hire_date, gender, email)

VALUES(1234, ‘JOHN’, 8000, ’18-AUG-80, ‘M’, ‘john@miraclesoft.com’);

INSERT INTO Employee(email , ename, empno, hire_date, gender, salary)

VALUES(‘rhonda@miraclesoft.com’, ‘RHONDA’, 1235, ’24-JUL-S1′, ‘F’, 7500)

INSERT INTO Employee

VALUES(1236, ‘JACK’, 15000, ’23-SEP-79′, ‘m’, ‘jack@miraclesoft.com’);

 

 

UPDATE

This command is used to modify the data existing in the tables.

 

Syntax

UPDATE <table-name> SET <column-name> = <value>;

UPDATE <table-name> SET <column-name> = <value> WHERE <condition>;

 

Example

UPDATE Employee SET salary = 15000;

UPDATE employee SET salary = 15000 WHERE empno = 1235;

 

 

DELETE

This command is used to remove the data from the tables.

 

Syntax

DELETE FROM <table-name>;

DELETE FROM <table-name>WHERE <condition>;

 

Example

DELETE FROM Employee;

DELETE FROM Employee WHERE empno = 1236;

 

 

 

DCL

 

 

TCL

TRANSACTION CONTROL LANGUAGE

 

These command are used to maintain the consistency of the database.

 

Commit

This command is used to make the transaction permanent.

Rollback

This command is used to undo the recent transaction.

Save Point

This is used to rollback a transaction to a specified point.

Registration


A password will be e-mailed to you.

Feedback Form

Name (required)

Email (required)

Feedback