SELECT value FROM World;

SQL (Structured Query Language) is one of the most important tools in a programmer's tool chest. Programmers spend a great deal of time defining terms and data structures. Strong understanding of the relational model can improve most programs. My advice to anyone wanting to learn software development is to start with SQL and to learn basic relational database design.

Getting Started

It is easy to get started with relational programming. There are many low cost databases on the market. Also, the companies that make enterprise quality databases, like Oracle, will let students install the program for learning purposes free of charge.

If you use Windows, you might want to try Microsoft Access. If you prefer Linux box, you would probably want to start with mySQL.

It is probably best to install more than one database program on your local system; so that you can 

SQL is not a traditional programming language. It is best understood as a tool for describing a data. The language is simple and powerful. It was originally designed for business professionals who needed access to the corporate database without the aid of programmers. Consequently, the designers of the language made a concerted effort to keep the language simple and with a very small number of commands. Once again we see that power comes from simplicity.

Before jumping into the SQL commands. I want to set the right frame of mind. A database is a mechanism for storing and retrieving information. The information is stored as small electronic charges on a computer's hard disk. You cannot see the data. To excel in the world of relational databases, you must learn to think of the data abstractly; Separate in your mind the data from the representation of the data. 

The are basically only four commands that you use to access and update data. These commands are SELECT, INSERT, UPDATE and DELETE. The most commonly used statement is the SELECT statement. The SELECT statement draws information from a table. The INSERT statement lets you add data to a database, the UPDATE statement lets you change the data and the DELETE statement lets you remove things from the database.

The SELECT statement is best understood as a description of a set of data. With this statement, you first determine the source of the data, the columns you want to appear in your result set, and any conditions you want to place on your data. A SELECT command has the following structure:

SELECT columns

  FROM Table

 WHERE condition

 ORDER BY column

The structure is simple and straight forward. When you write SELECT statements, you need to concentrate on what you want to appear in your result set. As a matter of convention, I like to capitalize the entire SQL command, the first letter of the table and leave the column names in lower case. In this second example, I will select some user information from a table called TS_User.

SELECT user_id, first_nm, last_nm, homepage

  FROM TS_User

 WHERE status_fl = 'A'

 ORDER BY last_nm

This statement returns the following result set:

user_id  first_nm  last_nm  homepage
1 Zareen Delaney  
2 Virginia Vallee 
3 Kevin Delaney  

SELECT statements are substantially different from the read and write statements used in standard programming languages. The SELECT statement doesn't simply read a single piece of data from a disk, the SELECT statement returns an entire table. It is important to remember that SELECT statements always return a full table. There may be a single row in the table, but it will always return a full table.

 To fully master the SELECT statement, you will want to study a subject called Relational Theory.

Highlights of Relational Theory

SQL is more than a programming language. It is built on a solid mathematical foundation called Relational Theory. You may remember studying sets in high school algebra. Relational theory is built on top of that stuff. Imagine hundreds of mathematical theorists wandering through ivy covered halls arguing about the vagaries of language, logic and data.

Fortunately, the implementation of Relational Theory is easier than the mathematics. The center of the relational universe is a thing called a table. A table is a simple two dimensional structure with columns and rows. You can easily write a table down on a piece of paper.

Ideally, there should be a way to uniquely identify each row in the table. This way of identifying the row is called a key. For example, a bank might have a table called Customers. The key to the table might be the customer's social security number. Making sure you can uniquely identify a row is one of the biggest struggles of the theory. 

you want to break down information into a two dimensional structured called a table. The two dimensions are columns and rows. Each row in the table can be identified by a defined primary key.

The following is a table shows demographic information by state. There are four columns in this table. The columns have the names: state_cd, name, population and statehood. I show only six rows. As I recall there are almost 50 states in the United States.

state_cd name population statehood
Id Idaho  
NY New York  
Tx Texas  
Wy Wyoming  
Ms Mississippi  
Fl Florida  

The first column in the table is state_cd. Each state has a two character code that uniquely identifies the state. In this table, the state code is the primary key. Of course, each state has a unique name. You could use the state name as a key to the table. I chose to use the code as the primary key since it is shorter.

One table is rather boring. Most databases have a number of tables. For example, you might also have a list of cities and counties. My second table shows a list of cities and towns. You will notice I have two Springfields in my list. I cannot use the town name as the primary key. I might be able to use the town name and state code combination as the primary key, but I found it easier to make up a number called town_id.

town_id town_nm state_cd capital
1 Salmon Id No
2 New York NY No
3 Boise Id Yes
4 Springfield Il No
5 Springfield Ma No

Once again you should notice that the table has the structure of columns, rows and a key that you can use to identify the rows in the table.

The SELECT command is not a standard computer algorithm. It is best understood as a description of a set of data. For example, the following SELECT statement describes the set of Friends who are having a birthday Today():

SELECT first_nm, last_nm, email_addr

  FROM Friend

 WHERE birthday = Today()

 ORDER BY last_nm; 

The first line of the query describe the elements of the data (first name, last name and email address.) The second line describes the source of the data. The WHERE clause limits the number of rows returned in the set. The ORDER BY line determines the positions of the members in the set.

SQL is easy to learn. There are only a few key words to memorize, but they are extremely powerful. Taking a few days to master relational theory will do more to propel your programming career than any other activity. 

index -- next
, plusroot, search, rgreetings, sponsors