Oracle WebServer User's Guide

Contents Index Home Previous Next

What is SQL?

SQL (Structured Query Language) is the language you use to issue instructions to the Oracle7 Server. It is, in fact, the standard language used by all major relational database vendors, and Oracle complies at Entry Level with SQL92, the most recent ISO (International Standards Organization) standard. There are several aspects of SQL that may differ from computer languages that you are familiar with, such as the following:

Retrieving Data

Suppose you wanted to pull from the Customers table the information on customers named "Peel". This is called making a query. To do it, you could issue the following statement:

SELECT *
	FROM Customers
	WHERE LNAME = 'Peel';

This produces the following:

CNUM	FNAME		LNAME	ADDRESS
4005	Julia		Peel	197 Myrtle Court, Brisbane, CA

Oracle interprets the statement as follows: Any number of spaces and/or line breaks are equivalent to one space or line break. These are delimiters, and the extra spaces and line breaks are for readability: all are equivalent "white space". Likewise, case is not significant, except in literals like the string you are searching for ('Peel').

SELECT is a keyword telling the database that this is a query. All SQL statements begin with keywords. The asterisk means to retrieve all columns; alternatively, you could have listed the desired columns by name, separated by commas. The FROM Customers clause identifies the table from which you want to draw the data.

WHERE LNAME = 'Peel' is a predicate. When a SQL statement contains a predicate, Oracle tests the predicate against each row of the table and performs the action (in this case, SELECT) on all rows that make the predicate TRUE. This is an example of set-at-a-time operation. The predicate is optional, but in its absence the operation is performed on the entire table, so that, in this case, the entire table would have been retrieved. The semi-colon is the statement terminator.

Nulls and Three-Valued Logic

With predicates, you should be aware of three-valued logic. In SQL, the basic Boolean values of TRUE and FALSE are supplemented with another: NULL, also called UNKNOWN. This is because SQL acknowledges that data can be incomplete or inapplicable and that the truth value of a predicate may therefore not be knowable. Specifically, a column can contain a null, which means that there is no known applicable value. A comparison between two values using relational operators--for example, a = 5--normally is either TRUE or FALSE. Whenever nulls are compared to other values, however, including other nulls, the Boolean value is neither TRUE nor FALSE but itself NULL.

In most respects, NULL has the same effect as FALSE. The major exception is that, while NOT FALSE = TRUE, NOT NULL = NULL. In other words, if you know that an expression is FALSE, and you negate (take the opposite of) it, then you know that it is TRUE. If you do not know whether it is TRUE or FALSE, and you negate it, you still do not know. In certain cases, three-valued logic can create problems with your programming logic if you have not accounted for it. You can treat nulls specially in SQL with the IS NULL predicate, as explained in Chapter 3 of the Oracle7 Server SQL Reference.

Creating Tables

This is how you create tables in SQL. You can use the following SQL statement to create the Customers table:

CREATE TABLE Customers
(cnum		integer NOT NULL PRIMARY KEY,	
FNAME		char(15) NOT NULL,
LNAME		char(15) NOT NULL,
ADDRESS		varchar2 );

After the keywords CREATE TABLE come the table's name and a parenthesized list of its columns with a definition of each. Integer, char, and varchar2 are datatypes: all of the data in a given column is always of the same type (char means a fixed and varchar2 a varying length string). For more information on SQL datatypes, see Chapter 2 of the Oracle7 Server SQL Reference.

NOT NULL and PRIMARY KEY are constraints on the columns they follow. They restrict the values you can enter in those columns. Specifically, NOT NULL forbids you from entering nulls in the column. PRIMARY KEY prevents you from entering duplicate values into the column and makes the column eligible to be the parent for some foreign key. For more information, see "CREATE TABLE" and "CONSTRAINT clause" in Chapter 4 of the Oracle7 Server SQL Reference.

Ownership and Naming Conventions

Note that when you create a table in SQL, you own it. This means you generally have control over who has access to it, and that it is part of a schema that bears your Oracle username. A schema is a named collection of database objects under the control of a single Oracle user. Schemas inherit the names of their owners. When other users refer to an object you have created, they have to precede its name by the schema name followed by a dot (no spaces). SQL utilizes a hierarchical naming convention with the levels of the hierarchy separated by dots. In fact, you sometimes have to precede column names by table names to avoid ambiguity, in which case you also use a dot. The following is an example in the form schemaname.tablename.columnname:

scott.Customers.LNAME

You can simplify references like this by using synonyms, which are aliases for tables or other database objects. Synonyms can be private, meaning that they are part of your schema and you control their usage, or public, meaning that all users can access them. For example, you can create a synonym "Cust" for scott.Customers as follows:

CREATE SYNONYM Cust FOR scott.Customers;

This would be a private synonym, which is the default. Now you could rewrite the example above like this:

Cust.LNAME

You still have to refer to the column directly. Synonyms can only be for tables, not table components like columns.

For more information on synonyms, see "CREATE SYNONYM" in Chapter 4 of the Oracle7 Server SQL Reference. For more information on SQL naming conventions, see Chapter 2 of the Oracle7 Server SQL Reference. For more on schemas, see "CREATE SCHEMA" in Chapter 4 of the Oracle7 Server SQL Reference.

Inserting and Manipulating the Data

Which SQL statements determine the actual data content? Chiefly, three--INSERT, UPDATE, and DELETE. INSERT places rows in a table, UPDATE changes the values they contain, and DELETE removes them.

The INSERT Statement

For INSERT, you simply identify the table and its columns and list the values, as follows:

INSERT INTO Customers (cnum, FNAME, LNAME)
	VALUES (2004, 'Harry', 'Brighton');

This statement inserts a row with a value for every column but ADDRESS. Since you did not, in your CREATE TABLE statement, place a NOT NULL constraint on the ADDRESS column, and since you did not give that column a value here, Oracle sets this column to null. If you are inserting a value into every column of the table, and you have the values ordered as the columns are in the table, you can omit the column list. You optionally can put a SELECT statement in place of the VALUES clause of the INSERT statement to retrieve data from elsewhere in the database and duplicate it here. For more information on the INSERT and the SELECT statements, see "INSERT" and "SELECT", respectively, in Chapter 4 of the Oracle7 Server SQL Reference.

The UPDATE Statement

UPDATE is similar to SELECT in that it takes a predicate and operates on all rows that make the predicate TRUE. For example:

UPDATE Customers
	SET ADDRESS = null
	WHERE LNAME = 'Subchak';

This sets to null all addresses for customers named 'Subchak'. The SET clause of an UPDATE command can refer to current column values. "Current" in this case means the values in the column before any changes were made by this statement. For more information on the UPDATE statement, see "UPDATE" in Chapter 4 of the Oracle7 Server SQL Reference.

The DELETE Statement

DELETE is quite similar to UPDATE. The following statement deletes all rows for customers named 'Subchak':

DELETE FROM Customers
	WHERE LNAME = 'Subchak';

You can only delete entire rows, not individual values. To do the latter, use UPDATE to set the values to null. Be careful with DELETE that you do not omit the predicate; this empties the table. For more information on DELETE, see "DELETE" in Chapter 4 of the Oracle7 Server SQL Reference.

Querying Multiple Tables Through Joins

Even though it only retrieves data, SELECT is the most complex statement in SQL. One reason for this is that you can use it to query any number of tables in one statement, correlating the data in various ways. One way to do this is with a join, which is a SELECT statement that correlates data from more than one table. A join finds every possible combination of rows, such that one row is taken from each table joined. This means that three tables of ten rows each can produce a thousand rows of output (10 * 10 * 10) when joined. Typically, you use the predicate to filter the output in terms of some relationship. The most common type of join, called a natural join, filters the output in terms of the foreign key/parent key relationship explained earlier in this chapter. For example, to see the people in the Customers table coupled with their various phone numbers from the Customers_Phone table, you could enter the following:

SELECT a.CNUM, LNAME, FNAME, PHONE, TYPE
	FROM Customers a, Customer_Phone b
 	WHERE a.CNUM = b.CNUM;

In the above, a and b are range variables, also called correlation variables. They are simply alternate names for the tables whose names they follow in the FROM clause, so that a = Customers and b = Customers_Phone. You can see that here you need the range variables to distinguish Customers.CNUM from Customers_Phone.CNUM in the SELECT and WHERE clauses. Even when not needed, range variables are often convenient.

Here is the output of the natural join:

CNUM	LNAME		FNAME	PHONE		TYPE
4005	Peel		Julia	375-296-8226	home
4005	Peel		Julia	375-855-3778	beeper
4008	Lopez		Emilio	488-255-9011	home
4008	Lopez		Emilio	488-633-8591	work
4011	Lim		Kerry	577-936-8554	home

This output represents every combination of rows from the two tables where both rows have the same CNUM value.

Outer Joins

Notice in the preceding example that people from the Customers table who did not have phones (namely, CNUM 4007) were not selected. If a row has no match in the other table, the predicate is never true for that row. Sometimes, you do not want this effect, and you can override it by using an outer join. An outer join is a join that includes all of the rows from one of the tables joined, regardless of whether there were matches in the other table. Such a join inserts nulls in the output in whichever columns were taken from the table that failed to provide matches for the outer-joined table. Here is the same query done as an outer join:

SELECT a.CNUM, LNAME, FNAME, PHONE, TYPE
	FROM Customers a, Customer_Phone b
 	WHERE a.CNUM = b.CNUM (+);

This is the output of the above:

CNUM	LNAME		FNAME	PHONE		TYPE
4005	Peel		Julia	375-296-8226	home
4005	Peel		Julia	375-855-3778	beeper
4007	Subchak	Terry	NULL		NULL
4008	Lopez		Emilio	488-255-9011	home
4008	Lopez		Emilio	488-633-8591	work
4011	Lim		Kerry	577-936-8554	home

Notice that the only difference in the query is the addition of (+) to the WHERE clause. This follows the table for which nulls are to be inserted. The output from the query, then, includes at least one row for each row of the table that did not have (+) appended in the predicate.

You can also use SELECT statements to produce values for processing within queries (these are called subqueries), and you can perform standard set operations (UNION, INTERSECTION) on SELECT statement output. For more information on the SELECT statement, subqueries, and joins, see "SELECT" in Chapter 4 of the Oracle7 Server SQL Reference.

Where to Look for More Information

Oracle7 SQL is a very complex subject, and we have been able only to scratch the surface of it here. To make it easier for you to find the specific information you need to perform the task at hand, we provide the following table, which identifies where in the Oracle7 Server documentation set you can find information on specific SQL topics. Unless otherwise noted, find the headings in Chapter 4 of the Oracle7 Server SQL Reference.

To Find Out About Look Under
aggregate data (totals, counts, averages, and so on) SQL Functions in Chapter 3 of the Oracle7 Server SQL Reference.
changing user passwords ALTER USER
connecting to the database CONNECT
constraints CONSTRAINT clause; CREATE TABLE; ENABLE clause
controlling user access to objects and user actions GRANT; REVOKE; CREATE ROLE; SET ROLE; see also Chapters 17 and 18 in the Oracle7 Server Concepts Manual
creating databases CREATE DATABASE
creating users CREATE USER
functions that change simple values SQL Functions in Chapter 3 of the Oracle7 Server SQL Reference.
linking databases at different locations CREATE DATABASE LINK; see also "Distributed Databases" in the Oracle7 Server Concepts Manual.
making changes to the data permanent COMMIT; SET TRANSACTION; SAVEPOINT
making SQL statements execute more quickly CREATE INDEX; see also "Indexes" in the Oracle7 Server Concepts Manual.
monitoring database usage AUDIT
reversing (undoing) changes to the data ROLLBACK; SET TRANSACTION; SAVEPOINT
Table 4 - 3. Guide to Further Information on SQL

Contents Index Home Previous Next