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 |
---|---|
|
Lists columns to retrieve. |
|
Optional keyword that eliminates duplicate rows. |
|
Identifies the tables to search for values. |
|
Limits rows retrieved to a subset of all available rows. |
|
Groups rows retrieved according the value of the specified column. |
|
Specifies a search condition to use with the |
|
Orders the output of a |
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.
- Enter the following statement:
SELECT department, last_name, first_name, emp_no FROM Department, Employee WHERE department = 'Engineering' AND mngr_no = emp_no
- The image below shows the expected result: