SQL Programming : A short intro

While my site contains mostly themes related to database design, without SQL programming, the database itself would be of little or no use.

If you visit my ref cursor page, you will find a lesson on how to include your code in the database itself; by far the most efficient and safe way of approaching the matter.

Before I continue, I would like to give you 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'.

Choose what you like. I prefer SQL; Or, perhaps a more precise acronym that you will find further down..

Here are a few basics on SQL programming:

We can divide the SQL language into two sections:

DDL: Data Definition Language. 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. Used to create and maintain the data we want in our database tables.

Luckily, in the DML section, we face only four statements to learn:

INSERT: To add new row(s) in a table.
UPDATE: To change exsisting 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.

Notice one thing, though; According to the above, SQL is much more than a query language: It's actually a Structured Query, Insert, Update and Delete Language - so perhaps we should rename it to SQIUDL ? Just joking, but it's a fact that it has (amongst other features) all those characteristics.

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

What does that mean?

It means that one statement can work on many rows in one operation. A very big difference from traditional, 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. The management department, perhaps?

As you can tell, SQL programming is very powerful, and as you can see, you can create very impressive things, and some not-so impressive, in just a small sentence.

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. T his 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.

As for how you can use a ref cursor from a procedural language, read more on my using an Oracle ref cursor in Java page.

If you are completely new to SQL programming, here is a starter at w3schools.

You can learn more about the CIA factbook by doing some SQL programming here :-).

Much more resources are found at SQL.org

Return to Programming