InterBase Quick Start: Part IV - Overview of SELECT

Go Up to InterBase Quick Start: Part IV - Retrieving Data

The simplified version of the SELECT statement is:

SELECT   [DISTINCT] columns
FROM     tables
WHERE    search_conditions
[GROUP BY COLUMN [HAVING   search_condition]]
[ORDER BY order_list]

The SELECT statement syntax above has seven main keywords. A keyword with associated information is called a clause. The clauses above are:

Clause	Descriptions
SELECT columns	Lists columns to retrieve.
DISTINCT	Optional keyword that eliminates duplicate rows.
FROM tables	Identifies the tables to search for values.
WHERE <search_conditions>	Limits rows retrieved to a subset of all available rows.
GROUP BY column	Groups rows retrieved according the value of the specified column.
HAVING <search_conditions>	Specifies a search condition to use with the GROUP BY clause.
ORDER BY <order_list>	Orders the output of a SELECT statement by the specified columns.

For more information about the SELECT statement, see SELECT.

Retrieving Data From Two Tables at Once

In this section you retreive the name and employee number of the person who manages the engineering department. The Department table contains the manager number (mngr_no) for each department. That manager number matches an employee number (emp_no) in the Employee table, which has a first and last name in the record with the employee number. You link the corresponding records of the two tables by using the WHERE clause to specify the foreign key of one (mngr_no) as equal to the primary key (emp_no) of the other. Since the primary key is guaranteed to be unique, you are specifying a unique row in the Employee table. None of the keys has to be a part of the SELECT clause. In this example, the primary key that you reference is part of the SELECT clause but the foreign key is not.

1. Enter the following statement:

   SELECT department,
          last_name,
          first_name,
          emp_no
   FROM   Department,
          Employee
   WHERE  department = 'Engineering'
          AND mngr_no = emp_no
   

2. The image below shows the expected result:

   

Advance To:

• Removing Duplicate Rows with DISTINCT