Using SQL*Plus commands to work with data in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to use the DESCRIBE, PROMPT, and SELECT commands to work with data.

1. Using the DESCRIBE and PROMPT commands

The SQL*Plus DESCRIBE and PROMPT commands can be used together to generate and organize data table descriptions.

You end a command with a semicolon (;) at the SQL*Plus command prompt, but in iSQL*Plus you can omit the semicolon.

In this example, you are using iSQL*Plus.

The DESCRIBE command displays the description of the specified database object.

The command also has a short form: DESC.

PROMPT Employees table description
DESCRIBE employees
PROMPT Locations table description
DESCRIBE locations

For example, suppose that you want to view the description of the EMPLOYEES table.

You type DESC employees and then click Execute to generate the description of the table.

When you run the command, the display shows the table's column headings, each column's NULL status, and the column data type.

The value NOT NULL indicates that data is mandatory in a column that contains it.

The PROMPT command displays the text specified as the command argument.

In this example, to test the command's functionality, you type PROMPT This text is displayed when the command runs, and then you click Execute.

The text specified as the command argument displays.

Suppose that you need to determine the structure of the EMPLOYEES and LOCATIONS tables.

To do this, you use DESCRIBE commands to query both tables. To make it easier to distinguish between the output associated with each command, you use the PROMPT command to display suitable text.

PROMPT Employees table description
DESCRIBE employees
PROMPT Locations table description

You type DESCRIBE locations to complete the sequence of commands, and then you click Execute.

The SQL*Plus code executes, and the table descriptions are easily distinguishable in the command output.

Question

Match the DESCRIBE and PROMPT commands to the correct descriptions.

Options:

1. DESCRIBE
2. PROMPT

Targets:

1. Displays the name and characteristics of each column in a table
2. Displays text specified as the command's argument

Answer

The DESCRIBE command displays a column's headings, Null? status, and data type. The PROMPT command displays text specified as the command's argument.

The DESCRIBE command provides a complete description of the specified database object.

The PROMPT command displays text as command output.

Question

You want to view a description of the LOCATIONS table.

Type the code that displays this information and then execute it.

Answer

You type DESCRIBE locations (or DESC locations).

Question

You now want to view a description of the EMPLOYEES table.

Complete this SQL*Plus sequence of statements so that the text Employees table description appears before the table description. Then execute the code.

MISSING CODE 
DESCRIBE employees

Answer

You type PROMPT Employees table description.

2. Using the SELECT command

You can retrieve information from a database using a SELECT statement, which commonly contains these three sections:

• SELECT
• FROM
• WHERE

SELECT

The compulsory SELECT section of the command specifies a list of the columns you want to retrieve from the specified table or tables.

FROM

The compulsory FROM section of the command specifies the table or tables you want to query.

WHERE

The optional WHERE section of the command specifies conditions that limit the number of rows retrieved by the command. If no WHERE section is included in the command, the query returns all rows in the specified columns.

Question

Match each of these SELECT-statement clauses to the correct description.

Options:

1. FROM
2. SELECT
3. WHERE

Targets:

1. Specifies one or more columns
2. Specifies one or more tables
3. Specifies one or more conditions that limit the number of rows retrieved by the command

Answer

The SELECT clause specifies one or more columns. The FROM clause specifies one or more tables. The WHERE clause specifies one or more conditions that limit the number of rows retrieved by the command.

The FROM clause specifies the tables from which data will be retrieved.

The SELECT clause specifies the columns within the tables specified by the FROM clause from which data will be retrieved.

The WHERE clause specifies conditions that limit the number of rows retrieved by the command.

You are preparing a typical SELECT statement that retrieves the first and last names of employees from the EMPLOYEES table, and you want to narrow down the command's results to the names of employees in department 90.

SELECT last_name, first_name
FROM employees

To do this, type WHERE department_id=90, and then click Execute.

The command output lists the first and last names of each employee associated with department ID 90.

You can also use text strings to limit query results. However, it is important to be aware that although SQL*Plus is not case-sensitive, the text within such a string must match the case of the corresponding text in the database.

SELECT last_name, first_name, employee_id
FROM EMPLOYEES

Suppose you want to narrow down the results of this command to employees whose last name is Ernst.

Type WHERE last_name='Ernst' and then clicking Execute.

The command output lists the first name, last name, and employee ID of all employees with the last name Ernst. In this example, Bruce Ernst is the only one.

Question

You need to retrieve a list of all employee ID numbers in the EMPLOYEES table that are associated with the department ID 60.

Type the SQL*Plus statement that queries the EMPLOYEE_ID and DEPARTMENT_ID columns for the table to retrieve the required output.

MISSING CODE

Answer

You type this statement:
SELECT employee_id FROM employees WHERE department_id=60.

3. Using the ORDER BY clause

The ORDER BY clause can be used in a SELECT command to organize the output generated by the command.

Note

If you don't specify an ORDER BY clause, the output is arranged in ascending order by default.

Suppose that you want to generate a list of the employees in department 90.

You can do this using this command, which shows the results in no particular order.

SELECT last_name, first_name
FROM employees
WHERE department_id=90

In this example, you want to organize the output generated by your SELECT command in ascending order of surname.

SELECT last_name, first_name
FROM employees
WHERE department_id=90

You type ORDER BY last_name to do this. Then you click Execute.

The command output lists department 90 employee names in alphabetical order of surname.

You can organize the same output in descending order using the DESC keyword.

SELECT last_name, first_name
FROM employees
WHERE department_id=90
ORDER BY last_name desc

Question

You are using the statement shown to ensure that the output of your SELECT statement is sorted appropriately.

Which of these statements accurately describes what the following SQL code is doing?


SELECT last_name, first_name
FROM employees
WHERE department_id=90
ORDER BY last_name

Options:

1. It organizes its results first in ascending order of first name
2. It organizes its results first in ascending order of last_name
3. It organizes its results first in ascending order of last name, and then in ascending order of first name
4. It organizes its results first in descending order of last name

Answer

The statement organizes its results in ascending order of employee last name.

Option 1 is incorrect. The ORDER BY clause that would do this is ORDER BY first_name.

Option 2 is correct. Results are organized in ascending order by default. To organize them in descending order, you use the DESC keyword.

Option 3 is incorrect. The ORDER BY clause that would do this is ORDER BY last_name, first_name.

Option 4 is incorrect. The ORDER BY clause that would do this is ORDER BY last_name desc.

Question

You have prepared a SELECT statement that retrieves a list of the employees in department 90, along with their salaries.

To complete the statement, add a clause that ensures that results will be in ascending order of salary.

SELECT last_name, first_name, salary
FROM employees
WHERE department_id=90
MISSING CODE

Answer

You type ORDER BY salary.

Summary

The DESCRIBE command generates a description of the specified object. The PROMPT command displays the specified text. You can use PROMPT and DESCRIBE together to display and organize table descriptions.

The SELECT command, which includes the compulsory FROM clause and the optional WHERE clause, enables you to query specific columns within one or more tables.

The ORDER BY clause can be added to a SELECT statement to control how the command output is sorted. It displays output in ascending order by default, but you can switch to descending order using the DESC keyword.