SQL Statements

Generally SQL statements are divided into two types.    

  

 DDL – Data Definition Language Statements

DDL statements are used to create, alter and drop schema objects.                   

                                                                                                         

Some of the examples of DDL statements are shown below.

1.       SQL statement to create a table

Create table EMP

(empno       Number,

Name    Varchar2(10),

Address    Varchar2(50),

Sal         Number,

Dept    Number);

This query creates EMP table with columns mentioned in the bracket.

2.       ALTER TABLE

This statement is used to add, delete or modify column in the table

ALTER TABLE EMP

ADD Bonus  Number;

This query will add new column Bonus in EMP table.

ALTER TABLE EMP

DROP COLUMN Dept;

This query will delete the Dept column from the EMP table.

3.       DROP – This statement can be used to drop table, functions, procedure and packages in the oracle.

Drop table  EMP;

The above statement will drop the EMP table from the database.

Like this you can use this DROP statement with procedure, function and packages in Oracle.

DML – Data Manipulation Language Statements.

              These statements are used to access and modify data from the schema objects.

              SELECT – This statement can be used to retrieve data from database tables.

                               SELECT * FROM EMP;

                               This query will retrieve all records from the EMP table.

             INSERT – This statement can be used to insert records into tables.

                             INSERT INTO EMP (EMPNO, NAME, ADDRESS, SAL, DEPT)

                                          VALUES(1, ‘andrew morrey’, ‘Newyork’, ‘10000’, 10);

                              This query will insert one record in EMP table.

             UPDATE – This statement can be used to modify any column value in the existing 

                           records.

                           UPDATE EMP SET SAL = 10000;

                           This query will update SAL column value to 10000 for all

                            records in the EMP table.

             DELETE – It is used to delete any record from the table.

                            DELETE from EMP;

                          This statement can delete all records from the EMP table.

                           DELETE from EMP WHRE EMPNO = 1;

                         It will delete record only for the employee number 1.

Apart from these, there is also one more important type of SQL statements is Transaction Control Statements.

Transaction Control Statements are used to manage the data changes made by DML statements.

COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION are the transaction control statements. These statements can be used in PL/SQL to control the transaction.

This is the brief introduction of the SQL statements. You can learn SQL statements in detail in the next sections.

In the next section of PLSQL Learning Point, you can learn SQL statements syntax, when to use them and more practical examples of SQL statements.