The Ubiquitous SELECT Statement


The Ubiquitous SELECT Statement

In the examples of tools for running SQL, you’ve seen the following simple SELECT statement:

select * from dept;

In its most basic form, the SELECT statement has a list of columns to select from a table, using the SELECT ... FROM syntax. The * means "all columns." To successfully retrieve rows from a table, the user running the query must either own the table or have the permissions granted to the user by the owner or a DBA. The most basic SELECT syntax can be described as follows:

 SELECT {* | [DISTINCT] column | expression [alias], ...}    FROM tablename; 

This type of statement representation is typical of what you’ll see in Oracle documentation, and it can be very complex. Here is a summary of what the elements in the syntax representation mean:

Element

Meaning

|

Pick one or the other

{ }

One within this list is required

[ ]

Item is optional

May repeat

Uppercase

Keyword or command

italics

Variable

Many more advanced features of the SELECT statement will be explored throughout this book. However, to begin with, let’s look at some examples of the column, alias, DISTINCT, and expression parts of a SELECT statement.

Column Specification

As you’ve seen, you can use the * character to view all columns in a table. But if the table contains too many columns to view at once, or your query only needs a small number of the total columns, you can pick the columns you need. For example, suppose that you want to view some information in the EMP table. How could you find out which columns are in this table without doing a SELECT * statement? You can use the DESCRIBE command in iSQL*Plus, as shown below.

click to expand

Now that you know which columns exist in the EMP table, you realize that you really need to see only the employee number, name, and salary. Therefore, your SELECT statement should be something like this:

select empno, ename, sal from emp;

It produces results similar to the following:

EMPNO      ENAME             SAL ---------- ---------- ----------       7369 SMITH             800       7499 ALLEN            1600       7521 WARD             1250       7566 JONES            2975       7654 MARTIN           1250       7698 BLAKE            2850       7782 CLARK            2450       7788 SCOTT            3000       7839 KING             5000       7844 TURNER           1500       7876 ADAMS            1100       7900 JAMES             950       7902 FORD             3000       7934 MILLER           1300 14 rows selected.

Column Renaming

In one of our earlier SQL*Plus examples, we wanted the column headers to be more readable, and we used some of the built-in features of SQL*Plus to do this. However, if your requirements for readability are fairly simple, you can use SQL’s built-in capability of column renaming, noted by the [alias] element of the SELECT syntax. Here is an example of providing aliases for the EMPNO, ENAME, and SAL columns in the EMP table. The alias is the renamed column seen in the results of the query.

select empno "Employee Number", ename "Name", sal "Salary" from emp; Employee Number Name           Salary --------------- ---------- ----------            7369 SMITH             800            7499 ALLEN            1600            7521 WARD             1250            7566 JONES            2975            7654 MARTIN           1250            7698 BLAKE            2850            7782 CLARK            2450            7788 SCOTT            3000            7839 KING             5000            7844 TURNER           1500            7876 ADAMS            1100            7900 JAMES             950            7902 FORD             3000            7934 MILLER           1300 14 rows selected.

alias

An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results of the query.

Duplicate Removal

The DISTINCT keyword removes all duplicate rows from the results of a query. For example, what if you wanted to see the department numbers for the employees in the EMP table? Your query might be something like this:

select deptno from emp; DEPTNO ----------         20         30         30         20         30         30         10         20         10         30         20         30         20         10 14 rows selected.

But what you probably want is one row for each of the departments found in the EMP table. In this case, use the DISTINCT keyword:

select distinct deptno from emp; DEPTNO ----------         10         20         30 3 rows selected.

That’s much easier to read. You now know that all of the employees belong to one of three departments. However, there may be many other departments, which would be listed in the department (DEPT) table. Some departments may not have any employees right now. In Chapter 5, "Using Multiple Tables," you’ll learn how to execute queries on joined tables to get this kind of information.

Expressions

To finish off our analysis of the SELECT syntax, let’s look at the expression part of the SELECT statement. Let’s say we would like to see how salaries would look if everyone got a 15% pay increase. All of the information we need to see is still in one table, the EMP table, but we need to perform some kind of calculation on one of the existing fields. To calculate a 15% pay increase, we need to not only see the existing salary, but we also need to multiply the SAL column by 1.15:

select empno, ename, sal, sal*1.15 from emp; EMPNO      ENAME             SAL   SAL*1.15 ---------- ---------- ---------- ----------       7369 SMITH             800        920       7499 ALLEN            1600       1840       7521 WARD             1250     1437.5       7566 JONES            2975    3421.25       7654 MARTIN           1250     1437.5       7698 BLAKE            2850     3277.5       7782 CLARK            2450     2817.5       7788 SCOTT            3000       3450       7839 KING             5000       5750       7844 TURNER           1500       1725       7876 ADAMS            1100       1265       7900 JAMES             950     1092.5       7902 FORD             3000       3450       7934 MILLER           1300       1495 14 rows selected.

To make the proposed salary column more readable, we could use either a column alias or iSQL*Plus column-formatting commands. We might also want to show a total for the SAL and SAL*1.15 columns, or show each salary increase to exactly two decimal places. Some of these more advanced formatting techniques will be covered in Chapter 9, "Reporting Techniques."




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net