Short Introduction On SQL Programing

2010-01-25T03:41:00.000-08:00

Here is a short intro on SQL programming in Oracle Blog, mostly for newbies, and with a few useful links if you are new to SQL programming.

Before continuing, Here is a a little background: There have been disputes for many years about the pronounciation of the letters SQL: Should it be pronounced S-Q-L or sequel?

The word sequel stands for Structured English QUEry Language, after an IBM project way back in time. Later, during the definition process of relational databases and a standard language for them, the term came to be Structured Query Language, SQL.

However, a product like Microsoft SQL Server is pronounced 'sequel Server'.

Here are a few basics on SQL programming:

SQL language is mainly divided into two sections:

DDL(Data Definition Language): DDL is used to create and maintain database objects such as tables, indexes, constraints and any other elements that together form the database structure for a given system.

DML(Data Manipulation Language): DML is used to create and maintain the data we want in our database tables.

DMl section has mainly four commands:

INSERT: To add new row(s) in a table.
UPDATE: To change existing row(s) in a table.
DELETE: To remove row(s) in a table.
SELECT: To retrieve one or more rows from one or more tables.

Most likely, the SELECT statement is the most used of these. It is also called as Dtat Retrieval Command

The most important feature of SQL programming is that it is set oriented.

Set Oriented means that one statement can work on many rows in one operation. A very big difference from traditional and procedural languages.

Consider this example:

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.1;

Every employee in the EMPLOYEE table get a 10% pay rise. Very nice.

Another example:

DELETE FROM EMPLOYEE;

All employees are removed... Not so nice.

You can reduce the damage by using restriction, or conditions:

DELETE FROM EMPLOYEE
WHERE DEPT_NO=10;

Now, only the employees of department no. 10 are removed.

However, it is difficult to use conditional programming in SQL (IF/THEN/ELSE). Because of this, the definition and use of ref cursors have emerged. This is a mechanism that allows you to step through a result set one row at a time. For a broader description on how to define it, you can read my ref cursor page.

Complete Oracle Blog

Short Introduction On SQL Programing