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.

SQL is a set-based declarative language that provides an interface to an RDBMS such as Oracle Database.

Procedural languages such as C describe how things should be done. SQL is nonprocedural and describes what should be done.

SQL is the ANSI standard language for relational databases. All operations on the data in an Oracle database are performed using SQL statements. For example, you use SQL to create tables and query and modify data in tables.

A SQL statement can be thought of as a very simple, but powerful, computer program or instruction. Users specify the result that they want (for example, the names of employees), not how to derive it. A SQL statement is a string of SQL text such as the following:

SELECT first_name, last_name FROM employees;

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.

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.

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

Tools Support

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

• Oracle SQL Developer is a graphical tool that lets you browse, create, edit, and delete (drop) database objects, edit and debug PL/SQL code, run SQL statements and scripts, manipulate and export data, and create and view reports. With SQL Developer, you can connect to any target Oracle Database schema using standard Oracle Database authentication. Once connected, you can perform operations on objects in the database.

You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, and Microsoft Access, view metadata and data in
these databases, and migrate these databases to Oracle.

• 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 Webbased user interface called iSQL*Plus.

Database Management System (DBMS)

A database management system (DBMS) is software that controls the storage, organization, and retrieval of data.
Typically, a DBMS has the following elements:

• Kernel code
This code manages memory and storage for the DBMS.

• Repository of metadata
This repository is usually called a data dictionary.

• Query language
This language enables applications to access the data.

Relational Mode

A relational database is a database that conforms to the relational model. The relational model has the following major aspects:

• Structures
Well-defined objects store or access the data of a database.

• Operations
Clearly defined actions enable applications to manipulate the data and structures
of a database.

• Integrity rules
Integrity rules govern operations on the data and structures of a database.

A relational database stores data in a set of simple relations. A relation is a set of tuples. A tuple is an unordered set of attribute values.

A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes). Each row in a table has the same set of columns. A relational database is a database that stores data in relations (tables).