The Relational Model defines two



Download 41.55 Kb.
Date conversion21.03.2016
Size41.55 Kb.

Introduction


SQL (Structured Query Language) is a database sublanguage for querying and modifying relational databases. It was developed by IBM Research in the mid 70's and standardized by ANSI in 1986.

The Relational Model defines two root languages for accessing a relational database -- Relational Algebra and Relational Calculus. Relational Algebra is a low-level, operator-oriented language. Creating a query in Relational Algebra involves combining relational operators using algebraic notation. Relational Calculus is a high-level, declarative language. Creating a query in Relational Calculus involves describing what results are desired.

SQL is a version of Relational Calculus. The basic structure in SQL is the statement. Semicolons separate multiple SQL statements.

SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks." Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language.


History of SQL


SQL was initially developed at IBM by Donald D. Chamberlin, Donald C. Messerly, and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers.

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.

SQL Features


SQL is used by and benefits all types of users - application programmers, database administrators, managers, and end users. 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. The features of SQL can be summed up as 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.

In General SQL does variety of tasks like:

  • Access data in relational database management systems.

  • describe the data (Querying data)

  • define the data in database and manipulate that data (Inserting, updating, and deleting rows in a table)

  • Facilitate Embedding within other languages using SQL modules, libraries & pre-compilers.

  • create and drop databases, tables and other objects (Creating, replacing, altering, and dropping schema objects)

  • set permissions on tables, procedures, and views (Controlling access to the database and its objects)

  • Guarantees database consistency and integrity

SQL lets us work with data at the logical level. When we want to manipulate the data, we only need to be concerned about the implementation details. For example, to retrieve a set of rows from a table, we need to specify 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. We need not deal with the rows one by one, nor do we 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 we can use to make the optimizer perform its job better.

SQL unifies multiple functions in one consistent language.

Common Language for All Relational Databases


All major relational database management systems support SQL, so us can transfer all skills us 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.

How SQL Works


There exists, broadly, two families of computer languages: declarative languages [nonprocedural and describe what should be done], and procedural languages such as C++ and Java [describe how things should be done].

SQL is declarative in the sense that we specify the result that we want, not how to derive it. The SQL language compiler generates a procedure to navigate the database and perform the desired task.

For e.g., following statement queries records from employees whose first name begins with N:

SELECT last_name, first_name

FROM hr.emp

WHERE first_name LIKE 'N%'

ORDER BY last_name, first_name;

The database retrieves all rows satisfying the WHERE condition mentioned, also known as the predicate, in a single step. Also, these rows can be passed as a single unit to the user, to another SQL statement, or to an application. We do not need to process the rows one by one, nor are we required to know how the rows are physically stored or retrieved.

All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient way of accessing the specified data. Oracle Database also supports techniques that can be used to make the optimizer perform its job better.


Overview of SQL Statements


All operations performed on the data in an Oracle database are run using SQL statements. A SQL statement consists of

  • identifiers,

  • parameters,

  • variables,

  • names,

  • data types,

  • and SQL reserved words.

Note:

SQL reserved words have special meaning in SQL and should not be used for any other purpose. For example, SELECT and UPDATE are reserved words and should not be used as table names.

A SQL statement must be an equivalent of a complete SQL sentence, such as:

SELECT last_name, department_id FROM emp

Oracle Database only runs complete SQL statements. A fragment of it, such as the following, if run, generates an error indicating that more text is required:

SELECT last_name;

SQL statements can be divided into the following categories:


  1. Data Definition Language (DDL) Statements: to define, structurally change, and drop schema objects.

  2. Data Manipulation Language (DML) Statements: to query or manipulate data in existing schema objects.

  3. Transaction Control Statements: to manage the changes made by DML statements and group DML statements into transactions.

  4. Session Control Statements: to dynamically manage the properties of a user session.

  5. System Control Statement: to change the properties of the database instance.

  6. Embedded SQL Statements: to incorporate DDL, DML, and transaction control statements within a procedural language program.

We will read about these in details in later chapters.

Overview of the Optimizer


In order to understand the processing of SQL statements, it is necessary to understand how, a part of the database called the optimizer (also known as the query optimizer or cost-based optimizer) works. All SQL statements use the optimizer to determine the most efficient way of accessing the specified data.

To execute a DML statement, the Database may have to perform several steps. In each step, it either retrieves rows of data physically from the database or prepares them for the user (issuing the statement).

Different ways of processing a SQL (DML) statement are often possible. For e.g., the order in which tables or indexes are accessed may vary. The number and type of steps that the database uses to execute a statement greatly affects how quickly the statement runs. The optimizer generates execution plans describing all possible methods of execution.

To determine which execution plan is most efficient, the optimizer considers several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. The operations performed are as follows:



  • Evaluation of expressions and conditions

  • Inspection of integrity constraints to learn more about the data and optimize based on this metadata

  • Statement transformation

  • Choice of optimizer goals

  • Choice of access paths

  • Choice of join orders

The optimizer generates several possible ways for processing a query and assigns a cost to each step in the generated execution plan. The plan with the lowest cost is then chosen as the query plan for execution.

Note:

An execution plan for a SQL statement can be obtained without executing the plan. Only an execution plan that the database actually uses to execute a query is termed as a query plan.



Optimizer choices can be influenced by setting the optimizer goal and by gathering representative statistics for the optimizer. For example, optimizer goal could be:

  • Total throughput: The ALL_ROWS hint instructs the optimizer to get the last row of the result to the client application as fast as possible.

  • Initial response time: The FIRST_ROWS hint instructs the optimizer to get the first row to the client as fast as possible.

Using initial response time optimization would be suitable for a typical end-user, interactive application whereas total throughput optimization would suit a batch-mode, non-interactive application better.

Tools Support


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

  • Oracle SQL Developer: A graphical tool that lets us 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, We can connect to any target Oracle Database schema using standard Oracle Database authentication. Once connected, we can perform operations on objects in the database. We 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: 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: 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 diagrammatic editor (database modeler). It also supports editing, compiling, and debugging PL/SQL applications.

  • Oracle Application Express is a hosted environment for developing and deploying database-related Web applications. SQL Workshop is a component of Oracle Application Express that lets us 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 pre-compilers let us embed standard SQL statements within a procedure programming language.

    • The Oracle Call Interface (OCI) lets us embed SQL statements in C programs.

    • The Oracle pre-compilers, 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.


The database is protected by copyright ©essaydocs.org 2016
send message

    Main page