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.

Oracle SQL NULL Operators

NULL is an Unknown value. If there is nothing in any column of the table, then it is known as NULL value. NULL and Zero values are not same.  You cannot use NULL instead of zero because both are not equivalent.

If any arithmetic expression contains NULL, then its result will be always NULL.

Null operations in SQL

Using NULL with SQL functions.

NVL

You can use NVL operator to return a value if the NULL value occurs.

EX:-  NVL(Emp_Commission, 0)

It will return Zero value if the Emp_Commission is NULL or it will return the Emp_Commission if it is not null.

Compare NULL Value

IS NULL

You cannot check the NULL by using the comparison operators <, =, > and <>.

If you want to test NULL value, you can use IS NULL operator in the SQL query as shown below.

SELECT EMP_NO, EMP_NAME, SALARY

FROM EMP

WHERE BONUS IS NULL;

This query will return all records from EMP table whose BONUS column contains NULL value.

IS NOT NULL

You can also use IS NOT NULL operator for comparing NULL value.

The below query returns all employees whose Bonus is not null

SELECT EMP_NO, EMP_NAME, SALARY

FROM EMP

WHERE BONUS IS NOT NULL;

Oracle SQL Tutorial for Beginners

If you are looking to learn Oracle SQL by your own, then this is the right place for you. This website content is designed in such a way that you can easily understand all the concepts of SQL theoretically as well as practically for Oracle Database. Let us have a glance at below sections of SQL tutorial.

1. Oracle SQL Introduction.

SQL or Structured Query Language is used to store, modify and retrieve data from database system. In order to access data from database, you need to write SQL queries. The data from database can be deleted, updated and retrieved by writing simple SQL commands. Before learning SQL commands, let us take a overview of the Database design.

2. What is Database?

Database is a storage unit which is used to store and access related information. You can easily maintain huge amount of data in a single database server. It makes easy to manage data in a multi-user environment where all users access database concurrently. 

In Database, the data is stored in the form of Table. Each table contains specific information like Employee details, Sales details, Customer details etc. The table contains many number of rows and columns as per the user requirement. Each column in the table is referred to as 'Field'.

3. SQL Statements

SQL has many statements that are used to perform actions on Oracle database. 

Some simple SQL statements are like 

SELECT

UPDATE

DELETE

are used to perform actions on Oracle database.

The detailed explanation of how to write SQL queries to fetch, modify or delete data from database will be explained in the next section.