Easy SQL Queries Are in Reach

There are different versions of SQL; for example, a query written for Microsoft Access is different then a query written for Oracle, even though they produce identical results. To meet American National Standards Institute guidelines, all SQL queries include the SELECT, FROM and WHERE clauses.

There are several SQL editing and reporting packages available including Microsoft Access and Crystal Reports; however, for this tutorial, the query will be written for Oracle SQL Plus. SQL Plus is a command line interface that is bundled with the Oracle Database Client and Server. It is interactive or script driven and is used by database administrators and developers to access Oracle databases.

A table, the basic unit for a relational database, is a set of related information. Tables are then further separated into columns that hold a specific data type within the table. For example a data table of employee information would have data in columns of employee number, first name, last name and telephone number.

The first step to understanding how to use SQL to get information is to know what is available in a database. A schema is a listing of all of the data that is in a database and includes the table layout and a diagram that shows the relationship between the data.

In Oracle SQL plus, a user can see what columns are available by typing the command 'desc' and the name of a specific table. In this tutorial, one of the tables used is the 'employee_information table which has five columns. In the example below, entering DESC employee_information at the command line produces the following result.

SQL> DESC employee_information

EMP_NB

FIRST_NAME

LAST_NAME

TELEPHONE_NB

HOME_TOWN

There are three clauses that make up a SQL query: the 'SELECT'clause; the 'FROM' clause; and the 'WHERE' clause. Each clause provides the query with specific information on the data that is to be pulled.

The 'select' clause defines the columns to be retrieved from a table or tables in the 'from' clause. This clause is the first command in almost every query and follows the following format: SELECT, {ALL or DISTINCT} {column name}.

The 'FROM' cause follows the select statement and lists the table or tables that are to be accessed by the query. The 'WHERE' clause is after the 'FROM' clause and filters the information that is the user defines in the 'SELECT' and 'FROM' clauses.

In this example, an analyst is tasked to run a report that lists current employee salary. The company database has several tables and the user can get the information he needs from two tables: employee_information and employee_payroll.

As shown in the DESC example, the five columns in the employee_information table are: emp; first_name; last_name; telephone_nb and home_town. The employee_payroll table has four columns, emp_nb, last_name, last_paid and salary. Each of the tables has over seven-hundred rows of data and contains information on current and former employees.

A statement of 'SELECT *' will return all columns in a table selected. For example, if the user queries the employee information table using 'SELECT * FROM employee_information', all of the data from the table would be returned and formatted in the table's five columns. The user can focus the query to return only the first and last name by writing the following query 'SELECT last_name, first_name FROM employee_information'.

Example one is a screen print that demonstrates the differences between the two queries and how the results are displayed.

A query where only the first_name and last_name columns are in the 'SELECT' clause will produce results without the employee_nb, telephone_nb and home_town fields. In addition, where the column is positioned in the select statement will determine how the results are displayed. When the below query is run, the first name and last name columns appear differently.

The above 'SELECT' clause returned only the results from the last_name and first_name columns of the employee_information table. Adding the employee salary information will require the query to add the appropriate columns to the select clause then identify the employee_payroll table in the 'FROM' clause.

The last_paid and salary columns in the employee_payroll table are added to the 'SELECT' clause. Also, the 'FROM' clause now identifies the employee_payroll table. In the 'FROM' clause, the letters 'n' and 'p' are variables that define which column is used in the SELECT clause. This is important because the first_name and last_name columns are common to both tables, and a logic error will occur if the correct table is not identified.

When more than one table is identified in the 'FROM' clause, the rows in each of the tables must be matched with the rows of the others. In SQL, this matching is accomplished by creating an inner join between columns with identical information on the two tables. The inner join combines works with the 'FROM' clause to allow the query to draw information from multiple tables in the database.

In our example, the column in both tables with identical information is the emp_nb column. The statement to join these tables is written in the 'WHERE' clause is 'n.emp_nb = p.emp_n'.

The updated query and results are shown in example two.

Additional statements can be added in the 'SELECT' and 'WHERE' clauses to eliminate duplicate rows, how the output is formatted, and remove obsolete information from the final report.

The 'SELECT' clause will determine can be written to display all rows in query results or to exclude duplicate rows buy use of the 'distinct' parameter. The all parameter is the default and will return all rows in the column or group of columns. The distinct parameter specifies that duplicate rows not be returned.

Using greater than or less than statements in the 'WHERE' clause will further refine the results. For the query, it can be assumed that the last pay date was August 15 2007: also, it can be reasoned that there are some employees on both data tables who are not currently on the company's payroll but are kept in the database for historical purposes.

Adding the statement 'p.last_paid in to_date('08/15/2007','MM/DD/YYYY')' will format the date in the during the query. The 'IN' operator is similar to the '=' comparison operator as both test whether a value matches any value in the list of values. In this case, it is testing whether the date is equal to August 15, 2007 and eliminating any rows that do not contain this date.

The query is also using a 'TO_DATE' function to convert the information in the 'last_paid' column to a date. This function, along with the corresponding 'TO_CHAR' function in the select statement is used to convert the date format in the table so that it can be used in a different application. For example if the last_paid is represented in the table as '15-AUG-07, the 'TO_DATE' and 'TO_CHAR' functions will convert it to 08/15/2007.

The report can be further defined by using optional 'GROUP BY' and 'ORDER BY' clauses after the 'WHERE' clause. The 'GROUP BY' clause checks rows for common values and orders them into groups. For example, if there are ten employees with a last name of Johnson and five employees with a last name of Smith, using the clause, 'GROUP BY last_name' will order all of the employee's with a last name of Johnson together, then order all of the rows with a last name of Smith together. A screen shot of how the group by clause works is in example three.

The 'ORDER BY' clause is at the end of the query and sorts the rows based on columns entered in the 'SELECT' clause. This clause can be further defined to return result in ascending or descending order by adding the expressions ASC for ascending and DESC for descending at the end of the clause.

Example four shows the completed query and displays the results of employees in the database who were paid on the last payday.

There are several expressions and functions that can be used in the query to format the results. For example, adding the statement "ROWNUM < 11" and changing the ORDER BY clause to read "SALARY desc will return the top ten employees by highest salary. The example used is a basic query and additional resources are available online that can expand a user's understanding of SQL