Manipulating data in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to join tables and use DML and DDL statements to manipulate and define data.

1. Joining tables

It is common to need to retrieve data from multiple tables in a single report. You can join the tables to do this.

Suppose that you need to generate a single report containing data from both the EMPLOYEES and the DEPARTMENTS tables. Each table contains a DEPARTMENT_ID column with identical data.

You want to cross-reference on the basis of the column the tables have in common.

To do this, you need to link the tables before you retrieve the information.

You are writing a statement that retrieves the LAST_NAME and DEPARTMENT_NAME columns from, respectively, the EMPLOYEES and DEPARTMENTS tables.

SELECT last_name, department_name
FROM employees, departments

You can link the two DEPARTMENT_ID columns using a suitable join condition in the relevant SELECT command's WHERE clause.

Because the join condition in this example equates two columns, it is sometimes called an equijoin.

SELECT last_name, department_name
FROM employees, departments

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id

You specify the join condition WHERE employees.department_id = departments.department_id, and then you click Execute to run the command.

The output lists each employee's surname and department.

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id

You can also express this using ANSI join syntax:

SELECT last_name, department_name
FROM employees
JOIN departments USING (department_id)

When linking tables, you don't have to actually query the column used to join the tables.

In this example, although the tables are joined on the basis of the DEPARTMENT_ID column, the columns that are actually queried are LAST_NAME and DEPARTMENT_NAME.

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id

Note

You can use multiple join conditions in a single SELECT statement.

You need to ensure that the column names are fully qualified to distinguish between them. This is not necessary when you are joining two columns with different names.

Generally, you use a join condition in a WHERE clause to link primary and foreign key columns, although this is not compulsory.

Question

You want to generate a list of departments, specifying the city in which each one is located, using information from the DEPARTMENTS and LOCATIONS tables. You want to link the tables using the LOCATION_ID column, which is present in both tables.

Complete this SELECT statement to generate the list you require.

SELECTdepartment_name, city from departments, locations
MISSING CODE

Answer

You type WHERE departments.location_id = locations.location_id to complete the statement.

You can also use a join condition to join two columns in the same table. This type of join is commonly known as a self-join.

Suppose, for example, you want to retrieve a list of employees and their managers from the EMPLOYEES table.

You need to fully qualify column names, using a different table alias for each column, so as to differentiate between the fields in the table. In this case, you use e1 and e2 as the aliases.

SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2

You now need to join the MANAGER_ID and EMPLOYEE_ID columns.

SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2
MISSING CODE

You type WHERE e1.manager_id = e2.employee_id to complete the SELECT statement. Then to run the command, you click Execute.

The output shows each employee's supervisor.

SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id

Question

You want to generate a list of employees and their managers using information from the EMPLOYEES table. You need to join the MANAGER_ID and EMPLOYEE_ID columns to generate the required output.

Complete this SELECT statement to retrieve the list.

SELECT emp1.last_name||' works for '||emp2.last_name
"Employees and Their Managers"
FROM employees emp1, employees emp2
MISSING CODE

Answer

You type WHERE emp1.manager_id = emp2.employee_id to complete the statement.

You can specify a join condition between two tables so that the query only retrieves the rows that satisfy the condition. This is known as an inner join or simple join.

This statement retrieves a list of the employee last names associated with each department name.

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id

An outer join returns all rows that satisfy the join condition, as well as some or all of the rows from one table that do not satisfy the join condition.

SELECT  e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN  departments d
ON  e.department_id = d.department_id 

For example, suppose you want to retrieve a list of the employee names associated with each department ID. You also want to ensure that all department names are listed, even those for which there are no corresponding employees.

You can do this using an outer join.

2. Data manipulation language

Data manipulation language (DML) consists of SQL commands that enable you to make changes to data.

These commands include

• INSERT
• UPDATE
• DELETE

The INSERT command adds rows to the specified database table. In this example, a single row is added to the EMPLOYEES table.

You specify the column headings, and then the corresponding values that go into the row.

INSERT INTO employees
(employee_id,first_name,last_name,email,phone_number,
hire_date,job_id,salary,commission_pct,manager_id,department_id)
VALUES
(9999,'Bob','Builder','bob@abc.com',NULL,sysdate,
'IT_PROG',NULL,NULL,100,90)

The UPDATE command changes an existing row or set of rows. You can use a WHERE clause to specify which rows need to be modified.

If you omit the WHERE clause, all rows are modified in the specified manner.

Suppose you need to update the EMPLOYEES table by setting the salary associated with employee ID 9999 to 6000.

You type UPDATE employees SET salary=6000 WHERE employee_id = 9999 to do this, and then to run the command you click Execute.

A confirmation message appears, indicating that the row has been updated successfully.

Question

You receive revised information about employee 9999, and need to make a change to personnel records in the EMPLOYEES table.

Type a SQL statement that sets the salary of the employee with ID 9999 to 20000.

MISSING CODE

Answer

You type UPDATE employees SET salary=20000 WHERE employee_id = 9999.

The DELETE command removes one or more rows from the specified table. You use a WHERE clause to specify which rows need to be removed.

In this example, the employee with ID 9999 is being deleted from the EMPLOYEES table.

DELETE from employees
WHERE EMPLOYEE_ID = 9999

It is uncommon to use a DELETE command from the command line. Generally, to remove a row, you use a GUI application, which generates the necessary DELETE statement automatically.

Question

Match each DML command with its correct definition.

Options:

1. INSERT
2. UPDATE
3. DELETE

Targets:

1. Adds rows to the specified database table
2. Changes an existing row or set of rows
3. Removes one or more rows from the specified table

Answer

The INSERT command adds rows to the specified database table. The UPDATE command changes an existing row or set of rows. The DELETE command removes one or more rows from the specified table.

To populate the row, you specify column headings and the corresponding values.

You use a WHERE clause to specify which rows need to be modified.

You use a WHERE clause to specify which rows should be deleted.

3. Data definition language

Data definition language(DDL) enables you to work with database objects, and consists of SQL commands, including

• CREATE
• ALTER
• DROP

You use the CREATE command to define a new object in the database.

CREATE TABLE local_temp
(temp_date DATE,hi_temp NUMBER(4,1),lo_temp NUMBER(4,1))

In this example, you create a table called LOCAL_TEMP with three columns: TEMP_DATE, HI_TEMP, and LO_TEMP.

The TEMP_DATE column's rows contain dates, and the other two columns contain simple numeric values.

You can also use CREATE to create other types of database objects, such as sequences, views, and indices.

You use the ALTER command to make changes to an existing database object.
The specific modifications you can make to the object's definition depend on the object type.

ALTER TABLE local_temp ADD (mean_temp NUMBER(4,1))

In this example, you add a column to the LOCAL_TEMP table.

You use the DROP command to remove a particular object from the database.

You can reinstate a dropped object, but you can't reference it while it is dropped.

In this example, you want to remove the LOCAL_TEMP table from the database.

To do this, you type DROP TABLE local_temp and then to run the command you click Execute.

The LOCAL_TEMP table has been removed from the database.

Question

All departments have been consolidated into a single location, so you need to remove the LOCATIONS table from the database.

Type the command that does this.

MISSING CODE

Answer

You type DROP TABLE locations.

Question

Match DDL commands to descriptions of their functionality.

Options:

1. CREATE
2. ALTER
3. DROP

Targets:

1. Defines a new object in the database
2. Makes changes to an existing database object
3. Removes a particular object from the database

Answer

The CREATE command defines a new object in the database. The ALTER command makes changes to an existing database object. The DROP command removes a particular object from the database.

When you define a new table, for example, you specify the column headings in the CREATE statement.

The modifications you can make using the ALTER statement vary according to object type.

A dropped object can be reinstated, but as long as it is dropped, you can't reference it.

Summary

A join enables you to cross-reference columns in a single table or in different tables. This makes it possible to customize your report output. There are several types of join. These include equijoin, self-join, inner join, and outer join.

Data manipulation language (DML) is composed of SQL commands that enable you to make changes to data. These commands include INSERT, UPDATE, and DELETE.

Data definition language (DDL) is made up of SQL commands for working with database objects. These commands include CREATE, ALTER, and DROP.