Previous | Table of Contents | Next

Page 77

Understanding Pseudo Columns, Dual Table, Describe, and Tab Table

SQL*PLUS contains several pseudo columns that provide additional information for a report or listing. You can list these columns in the select, where, order by, or group by clauses. The following contains these columns.

Column Description null Null value sysdate The current system date rownum Sequential number indicating the order of retrieval

The null places a null value into the column. Alternatively, you can use it in a where condition to evaluate another argument as null. Sysdate is the current date. Rownum is the sequential number of the row, indicating the order in which it was retrieved.

The dual table is used when a value must be introduced and an existing table does not contain that value. This could occur when a date is needed in a report, or possibly in a SQL*FORM. The example shown in Listing 3.53 uses the dual table to select and display the current date.

Listing 3.53L_03_53.TXT—Example of a Pseudo Column and the Dual Table

31-SQL>  select sysdate from dual;
SYSDATE
MAY-97

The Describe or Desc command is used to list the contents of a table. It is an extremely useful command when you are not certain of a table's column names or the format of a column. Listing 3.54 includes an example of a Describe command in use.

Listing 3.54L_03_54.TXT—Example of the Describe Command

SQL> describe employee
Name                            Null?    Type
 --------------------------------------- ----
 PAYROLL_NUMBER                  NOT NULL NUMBER(4)
 LAST_NAME                                VARCHAR2(15)
 FIRST_NAME                               VARCHAR2(15)
 ABSENCES                                 NUMBER(2)
 WAGES                                    NUMBER(8,2)
 STREET                                   VARCHAR2(20)
 CITY                                     VARCHAR2(15)
 STATE                                    CHAR(2)
 PHONE                                    CHAR(13)
 SOCIAL_SECURITY_NUMBER                   CHAR(11)
 EMPLOYMENT_DATE                          DATE
                                                                           continues

Page 78

Listing 3.54Continued

 BIRTH_DATE                               DATE
 CURRENT_POSITION                         VARCHAR2(15)
 FK_DEPARTMENT                            CHAR(4)
 GENDER                                   CHAR(2)

The Tab table contains the names of the tables to which you have access to on your Oracle id. The table is a view of the data dictionary and contains several fields about the table that may be of interest. When you need to know the name of a table, this view may be of interest. Listing 3.55 shows the results of a select statement using this table.

Listing 3.55L_03_55.TXT—Example of the Tab Table

SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ---------
BONUS                          TABLE
CUSTOMER                       TABLE
DEPARTMENT                     TABLE
DEPT                           TABLE
DEPTCOST                       VIEW
DUMMY                          TABLE
EMP                            TABLE
EMPLOYEE                       TABLE
EMP_DEPT                       TABLE
EMP_GLASSES                    TABLE
EMP_HIST                       TABLE
EMP_TOOLS                      TABLE
GLASSES                        TABLE
ITEM                           TABLE
LOADTAB                        TABLE
ORD                            TABLE
PRICE                          TABLE
PRODUCT                        TABLE
SALES                          VIEW
SALGRADE                       TABLE
SECTAB                         TABLE
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ---------
TESTLOAD                       TABLE
TOOLCOST                       VIEW
TOOLS                          TABLE
WGE_MAINT                      TABLE
25 rows selected.

Page 79

Summary

This chapter introduced you to the SQL language. The first SQL command is select, which acquires data from the Oracle7 database. Select is used in all the Oracle products. The
command has five clauses. The from clause identifies the database tables that contain the data.
The where clause identifies the selected records. The group by clause determines the set of records to perform group by functions on. The having clause identifies selected records. This functionality is similar to the where clause, except it uses group by functions to identify records. The order by clause sorts the records for display in the specified manner.

The select command has a large array of functions. The date functions enable you to perform arithmetic on two dates. The character functions enable you to combine, extract, and modify character expressions. You can use the number functions to format numbers. The decode function enables you to substitute one value for one that is selected. Finally, you have pseudo columns such as sysdate and rownum that return special values.

From Here…

You should now be comfortable with extracting data by using the select command. The next chapter shows you how to create formatted reports in SQL*PLUS. The Developer 2000 tool set has Oracle Reports 2.5, which is a more powerful tool. If you do not have access to Oracle Reports 2.5, you will find SQL*PLUS a good report writing tool.

Review Questions

  1. Find out the column names for the employee and department tables.
  2. Select all the columns and records from the department table.
  3. Use the SQL*PLUS editor Change command to change the tablename in the buffered query to the employee table.
  4. List the employees by last name descending and first name ascending.
  5. List the employees by gender. Be certain to translate the values in gender to `MALE,' `FEMALE,' and `UNKNOWN.'
  6. List all the male employees.
  7. List all the male employees born in July.
  8. Compute the average wages for all the employees.
  9. Compute the average wages per department for all employees.
  10. Compute the weekly net pay for the employees in the interior department. Assume that 10 percent of the gross pay is taken out for taxes and $150 is deducted annually for charity.

Page 80

  1. List the employees in the Political Science department with the greatest seniority.
  2. Determine the employee in the Political Science department with the most seniority.
  3. List the employees in the Interior department and Political Science department whose wages are greater than Truman's.
  4. List the employees that have a `W' in the second position of their first name.
  5. List the employees whose last name contains a `V' and a `T'.
  6. Compute the total wages for all departments except the Welfare department.
  7. List the male employees from the employee table. Use the following example as a model for formatting the employee names: `Mr. John Palinski'.
  8. List all the tables to which you have access.

Page 81

PART II

Using SQL*PLUS to Access the Database

4 Formatting Reports in SQL*PLUS83

5 Combining Tables and Rows103

6 Defining the Database Objects119

7 Modifying Your Tables with DML Commands151

Page 82

Previous | Table of Contents | Next