Categories/Types Of SQL Statements:

Categories/Types Of SQL Statements:

There are basically 6 types of Sql statements which are described below.

1.    Data Manipulation Language (DML):

2.    Data Definition Language (DDL):

3.    Transaction control Language (TCL):

4.    Session control Language:

5.    System control Language:

6.  Embedded SQL

1) Data Manipulation Language (DML):

The DML statements manipulate database data. DML Statements include

v  Insert

v  Delete

v  Update

Before execute DML statements, you must have INSERT, UPDATE, and DELETE privilege.

1)  INSERT:

1-   INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUES (1800,’ASD’, 10, 900);

2-   INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUE (1600,NULL,10,100);

Be sure that before inserting null value in column check if it accepting null value or not.

-------------------------------------------------------------------------------------------------

=>You can insert a special values like user name (must be as character data type) and SYSDATE

For example:

Assume you want insert SYSDATE as using TO_DATE function to change a format Type

Insert into EMP (ename, hiredate)

Values (‘MEGA’, TO_DATE (’01-JAN-2011 10:00’,’DD-MON-RR HH24: MI’), 20);

--------------------------------------------------------------------------------------------------

=>Copy data from another table using SUBQUERY

=>You can insert data from another table using SUBQUERY

For example:

Assume that you want copy data from dept =10 in emp table to a newemp table

Insert into newemp

Select empno,ename,deptno

From emp

Where deptno=10;

---------------------------------------------------------------------------------------------------

2) UPDATE:

Also you can modify existing rows by using UPDATE statement

For example:

Assume that you want to change SMITH job and his salary

Update EMP

Set job=’PROGRAMMER’, SAL=2500

WHERE ENAME=’SMITH’;

----------------------------------------------------------------------------------------------------

3) DELETE:

You can delete all rows in the table or delete one row or more by specifying a condition in the where clause rows.

For example:

Assume that we have TRAINING table including data for students If you want delete all rows in table

=>Delete from TRAINING;

If you want delete specified row assume for trainer id =1700

Delete from TRAINING

Where TI=1700;

NOTE: that if you want deletes row content a primary key referenced as a foreign key in another table the server not accepted.

2) Data Definition Language (DDL):

The DDL statements define and maintain objects and drop objects. DDL Commands include

v  Create

v  Alter

v  Rename

v  Drop

v  Truncate

v  Comment

  1)    CREATE:

Before create a tables please follow the below rules not for table only but for all database objects.

·         All names begin with letters and contain no more than 30 characters.

·         Names contain only the characters A-Z,a-z,0-9,underscore (_),$,#.

·         You can’t duplicate the name of another object in the same schema.

·         Name can’t an oracle reserved word.

To create table a user must have the create table privilege.

=>Steps to create table

·         Define a table name using  CREATE TABLE statement

·         Define a column names, datatype, and length. If you want define multiple columns, separate each column with a comma.

·         Define constraints for the respective columns.

For example:

CREATE TABLE TRAINING

(T_NO NUMBER (4) CONSTRAINT TRAINING_T_NO_PK PRIMARY_KEY,T_NAME  VARACHAR2(25) CONSTRAINT TRAINING_T_NAME _NN NOT_NULL);

TABLE CREATED

-------------------------------------------------------------------------------------------------

=> Create table based on another

You can create table based on another table using AS sub query clause within the create table statement.

While create a table based on another table the second table maybe contain data this useful when creating a table to replace another similar table.

If you want to create table based on another without data you need to use a condition in the where keywords. This condition must Non-matching any data in second table

Like where 1=0;

For example:

Assume you want to select rows from EMP table and insert them in the new table

Create table NEWEMP as select

EMPNO, ENAME, DEPTNO, SAL

From EMP;

TABLE CREATED

If you want display data in a new table

SELECT * FROM NEWEMP;

  EMPNO ENAME          DEPTNO        SAL

------- ---------- ---------- ----------

   7369 SMITH               20                      800

   7499 ALLEN              30                     1600

   7521 WARD               30                     1250

   7566 JONES               20                     2975

   7654 MARTIN           30                     1250

   7698 BLAKE             30                      2850

   7782 CLARK             10                      2450

   7788 SCOTT              20                     3000

   7839 KING                 10                      5000

   7844 TURNER           30                      1500

   7876 ADAMS             20                      1100

  EMPNO ENAME          DEPTNO        SAL

------- ---------- ---------- ----------

 7900 JAMES              30                   950

 7902 FORD                20                   3000

 7934 MILLER            10                   1300

Assume that you want create structure only without data

CREATE TABLE NEWEMP2 AS

SELECT * FROM EMP

WHERE 1=0;

Since 1not equal 0 then the new table created without data If you want show the result

DESCRIBE NEWEMP2;

Name                                      Null?                 Type

 ----------------------------------------- -------- ---------------------------

 EMPNO                                     NOT NULL         NUMBER (4)

 ENAME                                                             VARCHAR2 (10)

 JOB                                                                  VARCHAR2 (9)

 MGR                                                                 NUMBER (4)

 HIREDATE                                                         DATE

 SAL                                                                  NUMBER (7,2)

 COMM                                                              NUMBER (7,2)

 DEPTNO                                                           NUMBER (2)

--------------------------------------------------------------------------------------------------

  2)    ALTER:

Some time you need to add column, modify column, and redefine labels

Add column

It used to add column to an existing table

Suppose that you want add a new column for NEWEMP table this column named ABC

Alter Table NEWEMP

Add (ABC varchar2 (20));

---------------------------------------------------------------------------------------------------

Dropping column

=>If you want drop column

Alter table NEWEMP

Drop ABC;

=>You can also set column as unused

Alter table NEWEMP

SET UNUSED ABC;

=>YOU CAN DROP ALL UNUSED COLUMN

ALTER TABLE NEWEMP

DROUP UNUSED COLUMN;

--------------------------------------------------------------------------------------------------------

MODIFYING COLUMN

To modify the column you need to used alter table statement and the MODIFY clause.

Also you can modify data type, default value, size, add or remove NOT NULL constraint.

FOR EXAMPLE

ALTER TABLE NEWEMP

MODIFY (ABC VARCHAR2 (50));

--------------------------------------------------------------------------------------------------------

   3)    DROP:

You can drop table using DROP TABLE statement. While drop a table index for these table will be dropped You can use THE CASCED CONSTRAINT option if there is any referential constraint.

For Example:

DROP TABLE NEWEMP

CASCADE CONSTRAINTS;

--------------------------------------------------------------------------------------------------------

  4)    RENAME:

You can rename a table by using RENAME statement

Syntax

RENAME old_name

To              new_name

For example

RENAME newemp

To              training;

-------------------------------------------------------------------

  5)    TRUNCATE:

Means that you can remove all the rows immediately without generating any rollback information

Before using the TRUNCATE TABLE statement, you should disable any foreign key constraint.

For example:

If you want remove rows from NEWEMP table

Truncate table NEWEMP;

---------------------------------------------------

  6)    COMMENT ON TABLE

You can add or remove a comment on the table as a massage

For example:

Comment on table TRAINING

Is ‘content information about trainees people ’

=>To remove a comment

Type

Comment on table TRAINING Is ‘’

 

3) Transaction control Language (TCL):

To complete your DML statements you need to control in it by these commands.

COMMIT:  Make all pending changes in a transaction permanent.

ROLLBACK: Discard pending changes and return to an earlier point in the statement.

SAVEPOINT: Defines a marker to which you can later rollback.

Examples of TCL

1-     Commit

Suppose you inset new row in NEWEMP table and you want to save the latest changes Type

Commit;

2-     ROLLBACK

Suppose you inset new row in NEWEMP table and you want Returns the latest commit Type

ROLLBACK;

3-     SAVEPOINT

As the below example after inserting data you can create a named save point

Insert into newemp

Select empno,ename,deptno

 From emp
 Where deptno=10;

SAVEPOINT inserting_finish;

You can returned to the latest savepoint named inserting_finish

ROLLBACK INSERTING_FINISH;

4) Session control Language:

Used to control the properties of current session enabling and disabling roles and changing 

For Example:

v  Alter Statements

v  Set Role

5) System control Language:

Change Properties of Oracle Instance 

For Example:

v  Alter System

5) Embedded SQL:

Incorporate DDL, DML and T.C.S in Programming Language

For Example:

Using the Sql Statements in languages such as 'C', Open, Fetch, execute and close

Feel Free to Add Up Comments