POS/410 Flexnet Week 2 – On line

Lecture – Basic Data Retrieval in SQL

The purpose of this lecture is to provide the student with an introduction to basic data retrieval operations in SQL. In this lecture we will use one table only. Later lectures will extend this to multiple tables and to modifying and summarizing data.

In SQL, data is retrieved using the DQL sub-language. DQL stands for Data Query language. It has only one statement: the SELECT statement. However, this one statement has many sub parts, called clauses, so you can do a lot with it. One thing we will want to cover in this lecture is how to create a calculated column. I.e., a column that is not in the database but is rather the result of a calculation that appears in your query results. I’ll start with the basic syntax and expand on each clause in the remainder of this lecture.

SELECT Statement

Basic Syntax

The basic syntax of the SQL SELECT statement is shown below.

SELECT [DISTINCT] * | columns [AS alias]

FROM tables

[WHERE conditions]

[ORDER BY columns [DESC]];

Technically speaking, the FROM clause is optional, too. But we won’t have any use for that in this class. The FROM clause simply lists the table names you wish to retrieve data from. For now, just list the one table you want to use. There is actually a newer syntax used for joins of multiple tables, anyway.

Also an SQL statement has to be terminated by a semicolon according to the SQL standards. But in Access and SQL Server, this is usually optional. Also, note that SELECT * means to retrieve every column. We can either retrieve them all that way or list the columns separated by commas. This is generally considered better practice since we don’t want to pull more data than we need.

Here’s a simple example that retrieve an employee’s name, address and phone number. Note there are three records. Notice the address and phone number values.

DISTINCT keyword

The DISTINCT keyword means to only select distinct values. For example, in the above table SELECT address would show three records, SELECT DISTICT only two. Here is and example comparing two SQL SELECT’s.

SELECT address FROM employees; to

SELECT DISTINCT address FROM employees;

 VS.

Calculated fields and column aliases

Often we will want to calculate some value and place it in a column.  You do this by placing the calculation in the columns list just like it was a column. Then you give it a name. This name is technically called a column alias. In Access and most databases, if you don’t give it a name the database will make one up. Best practice is to always assign an alias. The following example calculates the salary range midpoints from the job_titles table. Salary_midpoint is the column alias. Note that the calculated field just appears in the result set. It is not actually in the database.

WHERE Clause

The basics

The WHERE clause is used to restrict the rows returned based on a “filter” condition. These filter conditions can get pretty complex. In fact they can even be another SELECT statement. This is called a subquery or sometimes, a sub-select. We’ll study those in a later lecture. Just be aware of it for now. Filter conditions can be AND’d and OR’d following the normal rules of Boolean logic. Here is a sample WHERE clause that retrieves only non-exempt titles with a midpoint > 100000.

Notice you cannot use the column alias in the WHERE clause. Sorry. It would seem that you could, but you can’t. As an aside, notice that Access has graciously “improved” my query by placing square brackets around column names. You shouldn’t have to do this unless you have spaces in your names. And you shouldn’t. It can cause problems.

Special Operators

When setting up your filter conditions there are four special SQL operators you need to be aware of:

            LIKE

            BETWEEN

            IS NULL

            IN

LIKE

This operator is used for wild card filters. In Access * means to match any string of characters. ? Means to match one character. In ANSI standard SQL the respective operators are % and _. You may see these latter two in most books. But remember, Access uses * and ? like DOS. Here is an example that lists employees with names starting with the letter B.

BETWEEN

This one is self-explanatory. For example:

SELECT * from orders WHERE order_date BETWEEN #12/12/2003# AND #12/15/2003#;

The only tricky point to note is that the range is inclusive. I.e. orders with dates 12/12/2003 and 12/15/2003 are included in the result set.

Also, NB that in Access dates must be enclosed by the # sign. This is not true in most databases. Some of them such as SQL Server and MySQL use the single quote like this: ‘12/12/2003’.

IS NULL

A null is not the same as a blank or 0. It means unknown. Therefore all comparisons against a null value will return false. You have to use IS NULL to find records with NULL values. Remember the phone numbers from the first query above? One is null. But even a query such as the following will not return that record.

Note the results do not return the record with the null value even though we tested for both = blank and <> blank!

This is because it is unknown whether or not the null phone equals a blank or doesn’t. The following query will retrieve the record with the null phone number.

Remember to always include the test for NULL if you want the records with null values. For example to list all employees without phone numbers you would use:

IN

One more important special operator is IN. It is used to filter for items in a list. For now we will type in the items. In a later lecture we will generate the items from a subquery. The IN operator can obviate the need for lengthy expressions like name = ‘Bob’ or name = ‘Sally’ or name = ‘Jane’ or name = ‘Tom’. We could write instead:

            WHERE name IN(‘Bob’, Sally, ‘Jane’, ‘Tom’)

Here is an example that finds employees with job codes 01 and 03:

There are only two items in this example list. But you can imagine the advantages of this syntax if there were say 10 or 20! Below are the results.

ORDER BY – Sorting the results

Often you want to sort the results according to one or more of the columns. For example sort the employees table by address, then salary with the highest salary first.  Here is the query for that:

Conclusion

This lecture reviewed the basic SQL SELECT statement. With a little practice and reading you should have the skills needed to retrieve and manipulate data from an SQL database. At least from one table which is all you will need for the first assignment. By way of summary here is what we covered:

            SELECT statement syntax

·        * | column list, distinct

·        the column alias

·        from clause

·        where clause and special operators

·        order by

We’ll look at multi-table queries and summarization later. In the next lecture I’ll also expand on the data modification material we started in Workshop 1 on ground.

Dan D'Urso
Laguna Niguel, CA
February 2005