Introduction to SQL

The relational database uses an industry-standard language called Structured Query Language, or SQL (pronounced “sequel”). Dr. E. F. Codd is considered the father of relational databases because of his research in this area. The language, Structured English Query Language (SEQUEL) was initially developed by IBM Corporation, Inc. in 1976, based on Dr. Codd's work. SEQUEL later became SQL (still pronounced sequel). Oracle Corp (then Relational Software, Inc.) developed the first commercial relational database using the newly developed SQL language. SQL was adapted in the mid-1980s as the accepted RDBMS standard language.

The SQL language easily allows all types of users, including application programmers, database administrators, managers, and end users to access the data. Finally, a relational database management system is the software that manages a relational database. These systems come in several varieties and are available from many different software vendors ranging from single-user desktop systems to full-featured, global, enterprise-wide systems.

The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages such as C and BASIC. SQL processes data as groups of records (result sets) rather than as individual records (as in COBOL and other procedural programming languages).

It provides automatic navigation to the data. It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as persistent stored modules (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.

Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques you can use to make the optimizer perform its job better.

SQL provides a consistent language to control the whole relational database environment. SQL is used to select data from the tables, manipulating data (inserts, updates, and deletes) within the tables, creating/securing/ dropping objects, protecting the data and structures (backup and recovery), and sharing data with other relational database environments.

All major relational database management systems support SQL, so the skills you learn in this book can be used with non-Oracle relational database tools.

The academic theory underlying the relational database is somewhat complex, but using and building applications is rather easy. There are three basic components to a relational database: the relational data structures (tables and indexes), the rules that govern the organization of the data structures (constraints), and the creation and manipulation operations that can be performed on the data structures (inserts, updates, and deletes).

Figure 1.5 shows the complexity of traditional programming languages verses the relative ease of using SQL to produce the same results.

SQL is a nonprocedural language (see Figure 1.6) in that it processes sets of records at a time rather than single records at a time. Oracle uses its optimizer to determine the best method of accessing the data. Oracle has two optimizers: the original rule-based optimizer that makes its decisions based on 19 rules (which include use indexes if they exist, the order of items in the where clause, and so on), and the cost-based optimizer that makes its decisions based on statistics gathered by Oracle's ANALYZE command.

SQL provides commands for almost any task, including querying data; inserting, updating, truncating, and deleting rows in a table (data manipulation language or DML); controlling the access to the database and its objects; creating, altering, and dropping of objects (data definition language or DDL); and administrative tasks such as database startup/shutdown, backup and recovery, and so on.

The basic SQL syntax is comprised of the following commands (details of these commands will be covered in various chapters in this book):

• SELECT is used to retrieve information from tables.

• UPDATE makes changes to existing data in tables and indexes.

• INSERT adds data to tables and indexes.

• DELETE removes data from tables and indexes.

• CREATE is used to create almost any object.

• ALTER makes changes to the definition of the objects and/or database settings.

• DROP is used to remove objects from the database.

• COMMIT saves the current pending changes to the database where ROLLBACK removes any uncommitted changes.

• GRANT and REVOKE are used for privilege maintenance on the objects.

Other SQL commands perform specific tasks:

• TRUNCATE quickly removes all the data from a table.

• RENAME allows for objects to be renamed.

• AUDIT is used to track who is doing what to the database.

• EXPLAIN PLAN is used to view the choices that the Oracle optimizer is making for any SQL statement.

• ANALYZE is used to collect statistics (such as sort order, number of rows, size of rows, and so on) for the Oracle cost-based optimizer.

• SET TRANSACTION is used to identify a unit of work. This is useful if there will be several SQL statements that will comprise a unit of work. This transaction mode allows all the SQL statements to be committed or rolled back as a unit of work.

• ROLES are a way of grouping user privileges together and easily assigning these roles to users who have to perform similar tasks.

Chapter 2, “Fundamentals of the SQL Language,” covers the basics of the SQL language. The better you understand the SQL language, the better you will understand relational databases.