Relational Database Concepts › Relational Operations

Relational Operations

You can manipulate tables to form new tables with relational operations.

The three types of operations that you use most often against a relational database involve accessing specified rows, particular columns, and more than one table.

Specified Rows (SELECT)

You can request that specific rows of data be retrieved from a table or tables.

For example, you can retrieve all information on employees whose last names are Carlson or Jacobs. Information on other employees is not returned. This type of operation is called a select operation.

  EMPLOYEE
┌────────┬───────────┬───────────┬─────────┐
│ EMP_ID │ EMP_LNAME │ EMP_FNAME │ DEPT_ID │      ┌────────┬───────────┬───────────┬─────────┐
├────────┼───────────┼───────────┼─────────┤      │ EMP_ID │ EMP_LNAME │ EMP_FNAME │ DEPT_ID │
│ 2096   │ CARLSON   │ THOMAS    │ 4600    ├──┐   ├────────┼───────────┼───────────┼─────────┤
│        │           │           │         │  │   │ 2096   │ CARLSON   │ THOMAS    │ 4600    │
│ 2437   │ THOMPSON  │ HENRY     │ 4600    │  ├──►│        │           │           │         │
│        │           │           │         │  │   │ 2598   │ JACOBS    │ MARY      │ 5100    │
│ 2598   │ JACOBS    │ MARY      │ 5100    ├──┘   └────────┴───────────┴───────────┴─────────┘
└────────┴───────────┴───────────┴─────────┘

Particular Columns (PROJECT)

You can identify particular columns of data to be retrieved.

For example, you can retrieve only the last name and first name of each employee in the company, in order to create a personnel list. This type of operation is called a project operation.

┌────────┬───────────┬───────────┬─────────┐
│ EMP_ID │ EMP_LNAME │ EMP_FNAME │ DEPT_ID │
├────────┼───────────┼───────────┼─────────┤
│ 2096   │ CARLSON   │ THOMAS    │ 4600    │
│        │           │           │         │
│ 2437   │ THOMPSON  │ HENRY     │ 4600    │
│        │           │           │         │
│ 2598   │ JACOBS    │ MARY      │ 5100    │
└────────┴─────┬─────┴─────┬─────┴─────────┘
               │           │
               └─────┬─────┘
                     ▼
         ┌───────────┬───────────┐
         │ EMP_LNAME │ EMP_FNAME │
         ├───────────┼───────────┤
         │ CARLSON   │ THOMAS    │
         │           │           │
         │ THOMPSON  │ HENRY     │
         │           │           │
         │ JACOBS    │ MARY      │
         └───────────┴───────────┘

More than one table (JOIN)

You can retrieve data from more than one table at the same time.

For example, to create a list of department names and employees in each department, you need to retrieve information on each employee along with information on the department in which the employee works.

This data is in two tables: the employee information and department ID are in the EMPLOYEE table, and the department ID and department name are in the DEPARTMENT table. You can join the two tables to see both the employee and department information as a single table. This type of operation is called a join operation:

   EMPLOYEE
┌────────┬───────────┬───────────┬─────────┐
│EMP_ID  │EMP_LNAME  │EMP_FNAME  │DEPT_ID  │
├────────┼───────────┼───────────┼─────────┼───┐
│2096    │CARLSON    │THOMAS     │4600     │   │
│        │           │           │         │   │
│2437    │THOMPSON   │HENRY      │4600     │   │   ┌────────┬───────────┐
│        │           │           │         │   │   │EMP_ID  │DEPT_NAME  │
│2598    │JACOBS     │MARY       │5100     │   │   ├────────┼───────────┤
└────────┴───────────┴───────────┴─────────┘   ├──►│2096    │MAINTENANCE│
                                               │   │        │           │
              DEPARTMENT                       │   │2437    │MAINTENANCE│
           ┌─────────┬─────────────────────┐   │   │        │           │
           │DEPT_ID  │DEPT_NAME            │   │   │2598    │BILLING    │
           ├─────────┼─────────────────────┼───┘   └────────┴───────────┘
           │5200     │CORPORATE MARKETING  │
           │         │                     │
           │4600     │MAINTENANCE          │
           │         │                     │
           │5100     │BILLING              │
           └─────────┴─────────────────────┘

You use one or more of these basic operations to retrieve data from the database. For example, you may want to access two tables to see employee and department information (join) but show only the employee last name and the department name (project).