Network Computingwww.networkcomputing.com

Our series takes a look at Chapter 4: SQL Syntax and SELECT.

The main purpose of a database is to store information in such a way that relevant information may be retrieved easily. Storing information about customers, contacts, or orders in one place is very handy, but only if there is a systematic way to retrieve this information. We also need to be able to take advantage of relationships that may exist between different types of data (for instance, the relations between customers and orders, students and classes, authors and books).

As we already know, relational databases expose the SQL, which enables us to work with our data in these ways. It provides ways of retrieving, modifying, and performing complex calculations on our data. In this chapter we will look at ways to get our data out of our database using SQL.

It is very important to note that the SQL language is a standard that database providers have agreed to and support. Though the various vendors level of support varies according to how far they have implemented the standard, and enhanced it, the SQL that we look at here will work in any ANSI SQL-compliant database such as SQL Server, Access, Oracle, DB2, and MySQL.

• Introduce ourselves to the basic principles of SQL

• Look at the basic syntax of SQL, and the most fundamental SQL statement, the SELECT statement

• Look in some depth at the WHERE clause and several of the logical operators that can be used with this to effectively filter data retrieved by the SELECT statement

• Look at the meaning and usage of NULL

• Look at how we can order our information with the ORDER BY function

SQL is an easy language to learn since it contains a limited number of keywords, which are very English-like in their makeup. We will be looking at the fundamental SQL keywords throughout Chapters 4 and 5.

Keywords - Special words or phrases that are reserved by a language. Also known as "Reserved Words."

SQL, by default, is not case sensitive, although some databases can be configured to be so. Please check with your DBA or through your database configurations to see if the DBMS has been set for case sensitivity.

The following queries are both treated in exactly the same way:

select * from Authors

SELECT * FROM Authors

Note that SQL statements in Oracle and MySQL's 'Command Prompt' interface must end with a semicolon as follows: SELECT * FROM Authors;

MySQL's 'Admin' interface does not require the semicolon.

Throughout this book, the keywords for SQL will be capitalized in examples and source code. The names of fields and tables will use initial capital letters throughout our source code as well. While this is not necessary, it is good programming practice since it provides a convenient way of highlighting keywords, thus making our queries more readable.

Spaces

SQL is not sensitive to spaces or whitespace in general, for example, statements can span multiple rows and can be formatted with whitespace. When the statement is executed, SQL will ignore the extra whitespace in the command. For instance, the two SQL statements listed here are equivalent:

SELECT * FROM Jobs

SELECT
*
FROM
Jobs

Whitespace - extra spaces, return characters and tab characters used for formatting purposes. Using whitespace makes code much more readable and clear.

Since SQL ignores extra whitespace, we can use tabs, return characters, and spaces to make our statements much more readable. A SQL statement that uses several tables at once can easily become half a page long, so formatting becomes very important, especially if other people will be modifying or looking at our code.