Wednesday, 11 April 2018

Introduction to Oracle SQL

Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL as used by most database systems.

This chapter contains these topics:

1. History of SQL

Dr. E. F. Codd published the paper, "A Relational Model of Data for Large Shared Data Banks", in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of the ACM. Codd's model is now accepted as the definitive model for relational database management systems (RDBMS). The language, Structured English Query Language (SEQUEL) was developed by IBM Corporation, Inc., to use Codd's model. SEQUEL later became SQL (still pronounced "sequel"). In 1979, Relational Software, Inc. (now Oracle) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.

2. SQL Standards

Oracle strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.

Oracle SQL, Oracle DB Tutorials and Materials, Oracle DB Learning, Oracle DB Certifications

The latest SQL standard was adopted in July 2003 and is often called SQL:2003. The formal names of this standard are:

◈ ANSI/ISO/IEC 9075:2003, "Database Language SQL", Parts 1 ("SQL/Framework"), 2 ("SQL/Foundation"), 3 ("SQL/CLI"), 4 ("SQL/PSM"), 9 ("SQL/MED"), 10 ("SQL/OLB"), 11 ("SQL/Schemata"), 13 ("SQL/JRT") and 14 ("SQL/XML")

◈ ISO/IEC 9075:2003, "Database Language SQL", Parts 1 ("SQL/Framework"), 2 ("SQL/Foundation"), 3 ("SQL/CLI"), 4 ("SQL/PSM"), 9 ("SQL/MED"), 10 ("SQL/OLB"), 11 ("SQL/Schemata"), 13 ("SQL/JRT") and 14 ("SQL/XML")

At this writing, the next edition of Part 14, SQL/XML (ISO/IEC 9075-14) is in the process of final approval as an International Standard, with adoption expected in the final quarter of 2005.

How SQL Works

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

◈ It processes sets of data as groups rather than as individual units.

◈ 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 the PL/SQL extension to Oracle SQL is similar to PSM.

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 Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

SQL provides statements for a variety of tasks, including:

◈ Querying data

◈ Inserting, updating, and deleting rows in a table

◈ Creating, replacing, altering, and dropping objects

◈ Controlling access to the database and its objects

◈ Guaranteeing database consistency and integrity

SQL unifies all of the preceding tasks in one consistent language.

Common Language for All Relational Databases

All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.

3. Recent Enhancements

The Oracle Database SQL engine is the underpinning of all Oracle Database applications. Oracle SQL continually evolves to meet the growing demands of database applications and to support emerging computing architectures, APIs, and network protocols.

In addition to traditional structured data, SQL is capable of storing, retrieving, and processing more complex data:

◈ Object types, collection types, and REF types provide support for complex structured data. A number of standard-compliant multiset operators are now supported for the nested table collection type.

◈ Large objects (LOBs) provide support for both character and binary unstructured data. A single LOB can reach a size of 8 to 128 terabytes, depending on database block size.

◈ The XMLType datatype provides support for semistructured XML data.

Native support of standards-based capabilities includes the following features:

◈ Native regular expression support lets you perform pattern searches on and manipulate loosely formatted, free-form text within the database.

◈ Native floating-point datatypes based on the IEEE754 standard improve the floating-point processing common in XML and Java standards and reduce the storage space required for numeric data.

◈ Built-in SQL aggregate and analytic functions facilitate access to and manipulation of data in data warehouses and data marts.

Ongoing enhancements in Oracle SQL will continue to provide comprehensive support for the development of versatile, scalable, high-performance database applications.

4. Lexical Conventions

The following lexical conventions for issuing SQL statements apply specifically to the Oracle Database implementation of SQL, but are generally acceptable in other SQL implementations.

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle Database evaluates the following two statements in the same manner:

SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)
   FROM employees
   WHERE department_id = 30
   ORDER BY last_name;

SELECT last_name,
     salary * 12,
          MONTHS_BETWEEN( hire_date, SYSDATE )
FROM employees
ORDER BY last_name;

Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.

5. Tools Support

Oracle provides a number of utilities to facilitate your SQL development process:

◈ SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database server or client installation. It has a command-line user interface and a web-based user interface called iSQL*Plus.

◈ Oracle JDeveloper is a multiple-platform integrated development environment supporting the complete lifecycle of development for Java, Web services, and SQL. It provides a graphical interface for executing and tuning SQL statements and a visual schema diagrammer (database modeler). It also supports editing, compiling, and debugging PL/SQL applications.

◈ Oracle HTML DB is a hosted environment for developing and deploying database-related Web applications. SQL Workshop is a component of Oracle HTML DB that lets you view and manage database objects from a Web browser. SQL Workshop offers quick access to a SQL command processor and a SQL script repository.

The Oracle Call Interface and Oracle precompilers let you embed standard SQL statements within a procedure programming language.

◈ The Oracle Call Interface (OCI) lets you embed SQL statements in C programs.

◈ The Oracle precompilers, Pro*C/C++ and Pro*COBOL, interpret embedded SQL statements and translate them into statements that can be understood by C/C++ and COBOL compilers, respectively.