Pass1000

The SELECT statement from Structured Query Language (SQL) has to be the single most powerful nonspoken language construct. The SELECT statement is an elegant, flexible, and highly extensible mechanism created to retrieve information from a database table. A database would serve little purpose if it could not be queried to answer all sorts of interesting questions. For example, you may have a database that contains personal financial records like your bank statements, your utility bills, and your salary statements. You could easily ask the database for a date-ordered list of your electrical utility bills for the last six months or query your bank statement for a list of payments made to a certain account over the same period. The beauty of the SELECT statement is encapsulated in its simple English-like format that allows questions to be asked of the database in a natural manner.
Tables, also known as relations, consist of rows of information divided by columns. Consider two of the sample tables introduced in the previous chapter: the EMPLOYEES table and the DEPARTMENTS table. This sample dataset is based on the Human Resources (HR) information for some fictitious organization. In Oracle terminology, each table belongs to a schema (owner): in this case the HR schema.
The EMPLOYEES table stores rows or records of information. These contain several attributes (columns) that describe each employee in this organization. The DEPARTMENTS table contains descriptive information about each department within this organization, stored as rows of data divided into columns.
Assuming a connection to a database containing the sample HR schema is available, then using either SQL*Plus or SQL Developer you can establish a user session. Once connected to the database, you are ready to begin your tour of SQL.

A Set-oriented Language :Summarize the SQL Language
Most 3GLs are procedural languages. Programmers working in procedural languages specify what to do with data, one row at a time. Programmers working in a setoriented language say what they want to do to a group (a “set”) of rows and let the database work out how to do it to however many rows are in the set.
Procedural languages are usually less efficient than set-oriented languages at managing data, as regards both development and execution. A procedural routine for looping through a group of rows and updating them one by one will involve many lines of code, where SQL might do the whole operation with one command: programmers’ productivity increases. During program execution, procedural code gives the database no options; it must run the code as it has been written. With SQL, the programmer states what he or she wants to do but not how to do it: the database has the freedom to work out how best to carry out the operation. This will usually give better results.
Where SQL fails to provide a complete solution is that it is purely a data access language. Most applications will need procedural constructs, such as flow control: conditional branching and iteration. They will also usually need screen control, user interface facilities, and variables. SQL has none of these. SQL is a set-oriented language capable of nothing other than data access. For application development, one will therefore need a procedural language that can invoke SQL calls. It is therefore necessary for SQL to work with a procedural language.
Consider an application that prompts a user for a name, retrieves all the people with that name from a table, prompts the user to choose one of them, and then deletes the chosen person. The procedural language will draw a screen and generate a prompt for a name. The user will enter the name. The procedural language will construct a SQL SELECT statement using the name and submit the statement through a database session to the database server for execution. The server will return a set of rows (all the people with that name) to the procedural language, which will format the set for display to the user and prompt him to choose one (or more) of them. The identifier for the chosen person (or people) will then be used to construct a SQL DELETE statement for the server to execute. If the identifier is a unique identifier (the primary key) then the set of rows to be deleted will be a set of just one row; if the identifier is nonunique, then the set selected for deletion would be larger. The procedural code will knownothing about the likely size of the sets retrieved or deleted.

Summarize the SQL Language
SQL is defined, developed, and controlled by international bodies. Oracle Corporation does not have to conform to the SQL standard but chooses to do so. The language itself can be thought as being very simple (there are only 16 commands), but in practice SQL coding can be phenomenally complicated. That is why a whole book is needed to cover the bare fundamentals.
SQL Standards
Structured Query Language (SQL) was first invented by an IBM research group in the ’70s, but in fact Oracle Corporation (then trading as Relational Software, Inc.) claims to have beaten IBM to market by a few weeks with the first commercial implementation: Oracle 2, released in 1979. Since then the language has evolved enormously and is no longer driven by any one organization. SQL is now an international standard. It is managed by committees from ISO and ANSI. ISO is the Organisation Internationale de Normalisation, based in Geneva; ANSI is the American National Standards Institute, based in Washington, DC. The two bodies cooperate, and their SQL standards are identical.
Earlier releases of the Oracle database used an implementation of SQL that had some significant deviations from the standard. This was not because Oracle was being deliberately different: it was usually because Oracle implemented features that were ahead of the standard, and when the standard caught up, it used different syntax. An example is the outer join (detailed in Chapter 8), which Oracle implemented long before standard SQL; when standard SQL introduced an outer join, Oracle added support for the new join syntax while retaining support for its own proprietary syntax. Oracle Corporation ensures future compliance by inserting personnel onto the various ISO and ANSI committees and is now assisting with driving the SQL standard forward.
SQL Commands
These are the 16 SQL commands, separated into commonly used groups:The Data Manipulation Language (DML) commands:
SELECT
INSERT
UPDATE
DELETE
MERGE
The Data Definition Language (DDL) commands:
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
The Data Control Language (DCL) commands:
GRANT
REVOKE
The Transaction Control Language (TCL) commands:
COMMIT
ROLLBACK
SAVEPOINT
The first command, SELECT, is the main subject of Chapters 2 through 9. The remaining DML commands are covered in Chapter 10, along with the TCL commands. DDL is detailed in Chapters 11 and 12. DCL, which has to do with security, is only briefly mentioned: it falls more into the domain of the database administrator than the developers.

The process of modeling data into relational tables is known as normalization and can be studied at university level for years. There are commonly said to be three levels of normalization: the first, second, and third normal forms. There are higher levels of normalization: fourth and fifth normal forms are well defined, but any normal data analyst (and certainly any normal human being) will not need to be concerned with them. It is possible for a SQL application to address un-normalized data, but this will usually be inefficient as that is not what the language is designed to do. In most cases, data stored in a relational database and accessed with SQL should be normalized to the third normal form.

Looking at the tables, the two-dimensional structure is clear. Each row is of fixed length, each column is of fixed length (padded with spaces when necessary), and the rows are delimited with a new line. The rows have been stored in code order, but this is a matter of chance, not design: relational tables do not impose any particular ordering on their rows. Department number 10 has one employee, and department number 40 has none. Changes to data are usually very efficient with the relational model. New employees can be appended to the employees table, or they can be moved from one department to another simply by changing the DEPTNO value in their row.
Consider an alternative structure, where the data is stored according to the hierarchical paradigm. The hierarchical model was developed before the relational model, for technology reasons. In the early days of computing, storage devices lacked the capability for maintaining the many separate files that were needed for the many relational tables. Note that this problem is avoided in the Oracle database by abstracting the physical storage (files) from the logical storage (tables): there is no direct connection between tables and files and certainly not a one-to-one mapping. In effect, many tables can be stored in a very few files. A hierarchical structure stores all related data in one unit. For example, the record for a department would include all that department’s employees. The hierarchical paradigm can be very fast and very space efficient. One file access may be all that is needed to retrieve all the data needed to satisfy a query. The employees and departments listed previously could be stored hierarchically as follows:

In this example layout, the rows and columns are of variable length. Columns are delimited with a comma, rows with a new line. Data retrieval is typically very efficient if the query can navigate the hierarchy: if one knows an employee’s department, the employee can be found quickly. If one doesn’t, the retrieval may be slow. Changes to data can be a problem if the change necessitates movement. For example, to move employee 7566, JONES from RESEARCH to SALES would involve considerable effort on the part of the database because the move has to be implemented as a removal from one line and an insertion into another. Note that in this example, while it is possible to have a department with no employees (the OPERATIONS department) it is absolutely impossible to have an employee without a department: there is nowhere to put him or her. This is excellent if there is a business rule stating that all employees must be in a department but not so good if that is not the case.
The relational paradigm is highly efficient in many respects for many types of data, but it is not appropriate for all applications. As a general rule, a relational analysis should be the first approach taken when modeling a system. Only if it proves inappropriate should one resort to nonrelational structures. Applications where the relational model has proven highly effective include virtually all Online Transaction Processing (OLTP) systems and Decision Support Systems (DSS). The relational paradigm can be demanding in its hardware requirements and in the skill needed to develop applications around it, but if the data fits, it has proved to be the most versatile model. There can be, for example, problems caused by the need to maintain the indexes that maintain the links between tables and the space requirements of maintaining multiple copies of the indexed data in the indexes themselves and in the tables in which the columns reside. Nonetheless, relational design is in most circumstances the optimal model.
A number of software publishers have produced database management systems that conform (with varying degrees of accuracy) to the relational paradigm; Oracle is only one. IBM was perhaps the first company to commit major resources to it, but their product (which later developed into DB2) was not ported to non-IBM platforms for many years. Microsoft’s SQL Server is another relational database that has been limited by the platforms on which it runs. Oracle databases, by contrast, have always been ported to every major platform from the first release. It may be this that gave Oracle the edge in the RDBMS market place.
A note on terminology: confusion can arise when discussing relational databases with people used to working with Microsoft products. SQL is a language and SQL Server is a database, but in the Microsoft world, the term SQL is often used to refer to either.

The relational paradigm models data as two-dimensional tables. A table consists of a number of rows, each consisting of a set of columns. Within a table, all the rows have the same column structure, though it is possible that in some rows some columns may have nothing in them. An example of a table would be a list of one’s employees, each employee being represented by one row. The columns might be employee number, name, and a code for the department in which the employee works. Any employees not currently assigned to a department would have that column blank. Another table could represent the departments: one row per department, with columns for the department’s code and the department’s name.
A note on terminology: what Oracle refers to as a table may also be called a relation or an entity. Rows are sometimes called records or tuples, and columns may be called attributes or fields. The number of “rows in the table” is the “cardinality of the tuples.”
Relational tables conform to certain rules that constrain and define the data. At the column level, each column must be of a certain data type, such as numeric, date-time, or character. The character data type is the most general, in that it can accept any type of data. At the row level, usually each row must have some uniquely identifying characteristic: this could be the value of one column, such as the employee number and department number in the preceding examples, which cannot be repeated in different rows. There may also be rules that define links between the tables,such as a rule that every employee must be assigned a department code that can be matched to a row in the departments table. Following are examples of the tabulated data definitions:
Departments table:Employees table:
The tables could contain these rows:
Departments:Employees:

Critical to an understanding of SQL is an understanding of the relational paradigm and the ability to normalize data into relational structures. Normalization is the work of systems analysts, as they model business data into a form suitable for storing in relational tables. It is a science that can be studied for years, and there are many schools of thought that have developed their own methods and notations.

This is a paper-based exercise, with no specific solution.
Attempt to identify the user processes, application servers, and database servers used in your environment. Try to work out where the SQL is being generated and where it is being executed. Bear in mind that usually the user processes used by end users will be graphical and will frequently go through application servers; the database administration and development staff will often prefer to use client-server tools that connect to the database server directly.
Development Tools and Languages
The Oracle server technologies include various facilities for developing applications,some existing within the database, others external to it.
Within the database, it is possible to use three languages. The one that is unavoidable, and the subject of this book, is SQL. SQL is used for data access, but it cannot be used for developing complete applications. It has no real facilities for developing user interfaces, and it also lacks the procedural structures needed for manipulating rows individually. The other two languages available within the database fill these gaps. They are PL/SQL and Java. PL/SQL is a third-generation language (3GL) proprietary to Oracle. It has the usual procedural constructs (such as if-then-else and looping) and facilities for user interface design. In the PL/SQL code,one can embed calls to SQL. Thus, a PL/SQL application might use SQL to retrieve one or more rows from the database, then perform various actions based on their content, and then issue more SQL to write rows back to the database. Java offers a similar capability to embed SQL calls within the Java code. This is industry standard technology: any Java programmer should be able write code that will work with an Oracle database (or indeed with any other Java-compliant database.)
Other languages are available for developing client-server applications that run externally to the database. The most commonly used are C and Java, but it is possible to use most of the mainstream 3GLs. For all these languages, Oracle Corporation provides OCI (Oracle Call Interface) libraries that let code written in these languages establish sessions against an Oracle database and invoke SQL commands. Many organizations will not want to use a 3GL to develop database applications.
Oracle Corporation provides rapid application development tools as part of the Oracle Developer Suite, and there are many third-party products. These can make programmers far more productive than if they were working with a 3GL. Like the languages, all these application development tools end up doing the same thing: constructing SQL statements that are sent to the database server for execution.

Critical to the concept of Grid computing is virtualization. This means that at all levels there is a layer of abstraction between what is requested and what is provided. End users ask for an application service and let the Grid work out which clustered J2EE application server can best provide it. Application servers ask for a database service and let the Grid work out from which RAC node the data can best be served. Within the Grid there is a mapping of possible services to available service providers, and there are algorithms for assigning the workload and resources appropriately. The result is that end users have neither the need nor the capacity to know from where their computing resources are actually being provided. The analogy often drawn is with delivery of domestic electricity: it is supplied on demand, and the home owner has no way of telling which power station is currently supplying him.
The Grid is not exclusive to Oracle. At the physical level, some operating system and hardware vendors are providing Grid-like capabilities. These include the ability to partition servers into virtual machines and dynamically add or remove CPU(s) and RAM from the virtual machines according to demand. This is conceptually similar to Oracle’s approach of dynamically assigning application server and database server resources to logical services. There is no reason why the two approaches cannot be combined. Both are working toward the same goal and can work together. The result should be an environment where adequate resources are always available on demand, without facing the issues of excess capacity at some times and under-performance at others. It should also be possible to design a Grid environment with no single point of failure, thus achieving the goal of 100 percent uptime that is being demanded by many users.
The SQL application developer need not know how the Grid has been implemented. The SQL will be invoked from an application server and executed by an instance against a database: the Grid will take care of making sure that at any moment pools of application servers and instances sized appropriately for the current workload are available.

The increasing size and complexity of IT installations makes management a challenging task. This is hardly surprising: no one ever said that managing a powerful environment should necessarily be simple. However, management tools can make the task easier and the management staff more productive.
Oracle Enterprise Manager comes in three forms:
Database Control
Application Server Control
Grid Control
Oracle Enterprise Manager Database Control is a graphical tool for managing one database, which may be a RAC clustered database. It consists of a Java process running on the database server machine. Administrators connect to Database Control from a browser, and Database Control then connects to the database server.
Database Control has facilities for real-time management and monitoring, running scheduled jobs, and reporting alert conditions interactively and through e-mail. Oracle Enterprise Manager Application Server Control is a graphical tool for managing one application server instance or a group of instances. The grouping technology is dependent on the version. Up to Oracle Application Server 10g release 2, multiple application servers were managed as a “farm,” with a metadata repository (typically residing in an Oracle database) as the central management point. From release 3 onward, the technology is based on J2EE clustering, which is not proprietary to Oracle.
Oracle Enterprise Manager Grid Control globalizes the management environment. A management repository (residing in an Oracle database) and one or more management servers manage the complete environment: all the databases and application servers, wherever they may be. Grid Control can also manage the nodes, or machines, on which the servers run, as well as (through plug-ins) a wide range of third-party products. Each managed node runs an agent process, which is responsible for monitoring the managed target on the node: executing jobs against them and reporting status, activity levels, and alert conditions back to the management server(s).
Grid Control gives a holistic view of the environment and, if well configured, makes administration staff far more productive than they are without it. It becomes possible for one administrator to manage effectively hundreds of targets.