Chapter 4. Accessing Table Data, DML Statements, and Transactions

Always desire to learn something useful. - Sophocles

In this chapter, we will install Oracle's free graphical tool, SQL Developer and use it to communicate with Oracle Database XE. You use SQL Developer to browse database objects and execute SQL statements. Querying the database is the most common job and this chapter focuses on writing simple and multi-table queries. We will also see some of the common database functions that are used in a daily routine. The following are the topics that are covered in this chapter:

• Unlocking sample user accounts

• Installing SQL Developer

• Connecting SQL Developer to Oracle Database 11g XE

• About TAB and DUAL

• Writing simple queries

• Selecting data from multiple tables

• Exploring common functions

• What are Transaction Control Statements?

• Commit Understanding DML statements

Unlocking sample user accounts

Oracle Database 11g XE comes with sample database users such as HR, MDSYS, and others. Some of the user accounts...

Unlocking sample user accounts

Oracle Database 11g XE comes with sample database users such as HR, MDSYS, and others. Some of the user accounts are by default locked. In the rest of the chapters, we will use HR schema objects for our testing and building applications.

Log on to SQL*Plus as SYSDBA, query account status for all users, and unlock the HR account as shown below:

SQL> connect /as sysdba
SQL> select username, account_status from dba_users;
SQL> alter user hr account unlock; # Unlock the locked account
SQL> alter user hr identified by hr; # Open the expired account

Or:

SQL> alter user hr identified by hr account unlock; #Unlock and open in a single statement
SQL> connect hr/hr

Installing SQL Developer

SQL Developer is a graphical tool that enables us to interact with an Oracle database. Using SQL Developer we can query, create/modify/drop database objects, run SQL statements, write PL/SQL stored procedures, and more.

SQL Developer is a separate tool not bundled with Oracle Database 11g XE. SQL Developer is free to download. You can download SQL Developer by following this link: http://www.oracle.com/technetwork/developer-tools/sql-developer/.

Installing SQL Developer on Windows and Linux is very straightforward and effortless. The following is the procedure to install in a Windows environment:

1. 1. Unzip the sqldeveloper-3.1.06.82.zip to a folder.

2. 2. Navigate to the new folder sqldeveloper created by the ZIP file and double-click on the sqldeveloper.exe file. That's it, we are done.

Connecting SQL Developer to Oracle Database 11g XE

Start SQL Developer and select Connections in the pane on the left-hand side and click on the plus symbol to create a new database connection. Now, in the following window that pops up, fill Connection Name, Username, Password, select the checkbox next to Save Password, and click on the Test button.

Resolve any errors. You should see Status: Success above the Help button. Save the connection information by clicking on the Save button and proceed by clicking on the Connect button.

About TAB and DUAL

DUAL is a SYS owned table. It is normally used to return values from stored functions, sequence values, and so on. It is recommended not to drop or perform any DML operations against a DUAL table. The following is an example of fetching the current date using the DUAL table:

SQL> SELECT sysdate FROM dual;
SYSDATE
---------
10-MAY-12
SQL>

TAB is a SYS owned view which is used to list tables and views in a table. The following is a sample query to list all tables/views in the current schema:

SQL> SELECT * FROM tab;

Writing simple queries

In general, a "query" means a "question". Within the realm of databases, a query fetches information from the database objects such as tables and views. We can combine one or more tables/views in a single query. In this chapter, we will execute queries using the SQL*Plus environment. However, you may prefer to use either SQL Developer or SQL*Plus.

To list columns of a table we use the DESC command in SQL*Plus:

SQL> DESC employees

Now, let us execute a simple query against the employees table and fetch a few columns:

SQL> SELECT employee_id, first_name, last_name, job_id FROM employees;

We can restrict the results returned by the query using the WHERE clause as shown below:

SQL> SELECT employee_id, first_name, last_name, job_id FROM employees WHERE salary < 2500;

Selecting data from multiple tables

In the above query, we queried data of the job_id column from the employees table; however, the column values are codes. The actual job description is in the job_title column in the jobs table. The following is a pictorial view of the jobs and employees tables with a pointer to the join columns:

Let us write a query by joining these two tables on the common column job_id to fetch information from both the tables. The query is shown as follows:

SELECT e.employee_id, e.first_name, e.last_name, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

Exploring common functions

We can use Oracle Supplied functions in our queries. Alternatively, we can create our own functions and later use them in queries. We will learn about functions in the following chapters. In this section, we will explore a few commonly used functions. A complete list of Oracle Supplied functions is out of the scope of this book. You can refer to the Oracle documentation for more details.

Functions in Oracle can be of type scalar or aggregate. Scalar functions operate on a single row whereas the aggregate functions work on multiple rows. In this book we will cover the following Oracle Supplied functions:

UPPER	RPAD	MAX	TO_NUMBER
LOWER	LPAD	MIN	TO_DATE
CONCAT or ("||")	SUBSTR	AVG
LTRIM	COUNT	ADD_MONTHS
RTRIM	SUM	TO_CHAR

The UPPER function converts the character string to uppercase. Similarly, the LOWER function converts a given string to lowercase. The following is a sample query from an HR schema:

SQL> SELECT...

What are Transaction Control Statements?

A transaction is a sequence of one or more SQL statements treated as one unit. Either all of the statements are performed or none of them are performed. There are two main Transaction Control Statements (TCS) , namely COMMIT and ROLLBACK:

• COMMIT: When we COMMIT a transaction it means that Oracle has made the change permanent in the database

• ROLLBACK: When we ROLLBACK a transaction, all the changes performed since the previous COMMIT/ROLLBACK are all erased

Understanding DML statements

Data Manipulation Language (DML) statements are used to manipulate data in existing tables. INSERT, UPDATE, and DELETE are examples of DML statements. We use INSERT to add a new record to the table, UPDATE to modify one or more columns of a table, and DELETE to remove a record from the table.

The following is an example of an INSERT statement. We insert a new record in the regions table of the HR schema:

SQL> INSERT INTO regions VALUES (5, 'Australia');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>

An example of the UPDATE statement is shown next, where we modify the value of region_name from Australia to Aus and NZ. This is done as follows:

SQL> UPDATE regions SET region_name = 'Aus and NZ' Where region_id = 5;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL>

The following is an example of the DELETE statement. We remove the newly added record from the regions table as follows:

SQL> DELETE FROM regions Where region_id = 5;
1 row deleted...

Summary

In this chapter we have learned how to interact with Oracle database using SQL*Plus and SQL Developer. This chapter also exposed you to a few commonly used Oracle Supplied functions. We also learned what TCS and DML statements do.

Now that we are familiar with performing DML operations against tables, the next chapter discusses creating and managing tables and indexes. We will also learn about other database objects such as views, sequences, and synonyms.

References

• Oracle Database SQL Language Reference 11g Release 2 (11.2)

The rest of the chapter is locked

What are Transaction Control Statements?

A transaction is a sequence of one or more SQL statements treated as one unit. Either all of the statements are performed or none of them are performed. There are two main Transaction Control Statements (TCS) , namely COMMIT and ROLLBACK:

• COMMIT: When we COMMIT a transaction it means that Oracle has made the change permanent in the database

• ROLLBACK: When we ROLLBACK a transaction, all the changes performed since the previous COMMIT/ROLLBACK are all erased

Understanding DML statements

Data Manipulation Language (DML) statements are used to manipulate data in existing tables. INSERT, UPDATE, and DELETE are examples of DML statements. We use INSERT to add a new record to the table, UPDATE to modify one or more columns of a table, and DELETE to remove a record from the table.

The following is an example of an INSERT statement. We insert a new record in the regions table of the HR schema:

SQL> INSERT INTO regions VALUES (5, 'Australia');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>

An example of the UPDATE statement is shown next, where we modify the value of region_name from Australia to Aus and NZ. This is done as follows:

SQL> UPDATE regions SET region_name = 'Aus and NZ' Where region_id = 5;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL>

The following is an example of the DELETE statement. We remove the newly added record from the regions table as follows:

SQL> DELETE FROM regions Where region_id = 5;
1 row deleted...

Summary

In this chapter we have learned how to interact with Oracle database using SQL*Plus and SQL Developer. This chapter also exposed you to a few commonly used Oracle Supplied functions. We also learned what TCS and DML statements do.

Now that we are familiar with performing DML operations against tables, the next chapter discusses creating and managing tables and indexes. We will also learn about other database objects such as views, sequences, and synonyms.

References

• Oracle Database SQL Language Reference 11g Release 2 (11.2)