Most SQL statements are written to retrieve data from a table or tables. This type of statement is called a query. To issue a query, a SELECT command is used. The basic SELECT command is made up of two clauses, a SELECT clause and a FROM clause. The SELECT clause is always first and is immediately followed by the FROM clause.

Selection is an operation in relational algebra that selects specified rows from a relation. The SELECT command is used in all SQL queries. Don't confuse selection and SELECT.

To retrieve all the rows from a table the SQL format for that selection is as follows:

SQL> SELECT * ç select clause

FROM <table_name>; ç from clause

SELECT * will cause all the rows to be returned from the table named in the FROM clause. Remember, the SQL query is ended with a semi-colon(;) which is called a command terminator.

To retrieve the data from a physical table named 'EMPLOYEE', using the format shown above, the selection would be entered as shown below:

All the rows currently in the table are returned.

To further specify the search criteria, use predicates in a WHERE clause. A WHERE clause causes ORACLE to search the data in the table specified by the FROM clause and retrieve only the rows which meet the search condition. The SQL implementation including a WHERE clause follows:

WHERE <column_name> = <qualifier>; ç where clause

For Example:

WHERE EMPNO = 213; ç where clause with qualifier

Note: Number values do not require quotes around them during comparisons.

This is the results table that what would be returned as a result of the query above:

In the above example since the value 213 resides in the employee number column for the table named EMPLOYEE and the query specified SELECT *, all the attributes for that row are returned to you.

To tell ORACLE to sort the data before returning it to you, use the ORDER BY clause. Suppose you wanted information on all the employees from the EMPLOYEE table who held the position of clerk with the results ordered by their employee number. For example:

WHERE JOB = 'Clrk' ç where clause with qualifier

ORDER BY EMPNO; ç order by clause with sort criteria

Note: Single quotes are required around the string value being compared in the JOB column. Only those rows in the table in which the value in the JOB column matches precisely the spelling and case of the string you place in quotes will be returned.

The results table should look like this:

The default for the sort is ascending order. If you wanted EMPNO sorted in descending order the query would have been:

WHERE JOB = 'Clrk' ç where clause with qualifier

ORDER BY EMPNO desc; ç order by clause with descending sort

The results table from this query would look like this:

Notice that now the rows are in reverse order.

FOR YOU TO TRY:

1. Write a SQL query that will return information about all managers in the EMPLOYEE table, ordered alphabetically by name.

Hints: Since your WHERE clause will be looking for equality of the value of a string, specifically 'Mngr', case and spelling are important. ORDER BY works for strings as well as for numbers. Just use the column name.

2. Write a SQL query that will return information about all employees in Department 40, ordered by employee number.
3. Write a SQL query that will return information about all employees that are female, ordered alphabetically by name.

Go here to check your answers.

Last revised on 10/21/97 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to Oracle/SQL Tutorial Contents Page | Return to Exercise 1 | Go to SQL Exercise 3 - Projection