![[SQL*Plus Running]](http://cimic.rutgers.edu/~holowcza/oracle/sqlplus/sqlplus_running.gif)
The Oracle Relational Database Management System (RDBMS) is an industry leading database system designed for mission critical data storage and retrieval. The RDBMS is responsible for accurately storing data and efficiently retrieving that data in response to user queries.
The Oracle Corporation also supplies interface tools to access data stored in an Oracle database. Two of these tools are known as SQL*Plus, a command line interface, and Developer/2000 (now called simply Developer), a collection of forms, reports and graphics interfaces. This technical working paper introduces the features of the SQL*Plus tool and provides a tutorial that demonstrates its salient features.
This tutorial is intended for students and database practitioners who require an introduction to SQL, an introduction to working with the Oracle SQL*Plus tool, or both.
This document is organized as follows. A brief overview of the suite of Oracle products is first presented in Section 2. In Section 3, we discuss the basics of working with the SQL*Plus tool. Structured Query Language (SQL), including data definition language (DDL) and data manipulation language (DML) is discussed in section 4. Advanced SQL*Plus commands are discussed in section 5 and a brief introduction to stored procedures and triggers is given in section 6.
Developing applications using an Oracle database requires access to a copy of the Oracle RDBMS (or a central Oracle RDBMS server), and one or more of the development tools. Third party development tools such as PowerBuilder, Visual Basic or Java can also be used for applications development.
Stand-alone development in a single user environment can be accomplished using the Personal Oracle or Personal Oracle Lite RDBMS in conjunction with Oralce Developer or a third party development tool.
Muli-user development in a shared environment can be accomplished using an Oracle RDBMS server running on a server machine. Distributed client PCs can develop the applications using any of the tools mentioned above.
Regardless of the development environment, used, the Oracle SQL*Plus utility is a convenient and capable tool for manipulating data in an Oracle database. In the following section, the SQL*Plus tool is introduced.
In the following sections, the basic functionality of SQL*Plus will be demonstrated along with sample input and output to demonstrate some of the many features of this product.
In this section, we give some general directions on how to get into the SQL*Plus program and connect to an Oracle database. Specific instructions for your installation may vary depending on the version of SQL*Plus being used, whether or not SQL*Net or Net8 is in use, etc.
Before using the SQL*Plus tool or any other development tool or utility, the user must obtain an Oracle account for the DBMS. This account will include a username, a password and, optionally, a host string indicating the database to connect to. This information can typically be obtained from the database administrator.
The following directions apply to two commonly found installations: Windows 95/98 or NT client with an Oracle server, and a UNIX installation.
![[Start]](http://cimic.rutgers.edu/~holowcza/oracle/sqlplus/win95start.gif)
button, Programs, Oracle for Windows 95 and then
SQL*Plus. The SQL*Plus login screen will appear
after roughly 15 seconds.![[SQL*Plus Login Screen]](http://cimic.rutgers.edu/~holowcza/oracle/sqlplus/sqlplus_login.gif)
In the User Name: field, type in your Oracle username.
Press the TAB key to move to the next field.
In the Password: field, type your Oracle password.
Press the TAB key to move to the next field.
In the Host String: field, type in the Service
Name of the Oracle host to connect to.
If the DBMS is Personal Oracle lite then
this string might be ODBC:POLITE.
If the DBMS is Personal Oracle8, then the host string
might be beq-local.
For Client/Server installations
with SQL*Net or Net8, this string will be the service
name set up by the SQL*Net assistant software.
Finally, click on the OK button to
complete the Oracle log in process.
SQL*Plus will then establish a session
with the Oracle DBMS and the SQL*Plus
prompt (SQL> ) will appear.
The following figure shows the
results of logging into Oracle using SQL*Plus:
There are a number of situations in which an error may occur:
In any of the above cases, an error message will be returned. If the Oracle server is not available or if you supply the wrong username or password, an error will be returned right away. If there is a networking problem, SQL*Plus may take several minutes before returning an error.
Here are some common error messages and some suggestions on how to resolve them:
Unfortunately, most versions of SQL*Plus will not re -display the login screen if your attempt to connect is unsuccessful. You should exit SQL*Plus completely by pulling down the File menu and choosing the Exit menu item. Then run SQL*Plus again from the beginning.
For users of Personal Oracle Lite, there is a default database schema created upon installation of the software. To log into Personal Oracle Lite using SQL*Plus, supply the following values on the SQL*Plus login screen:
In the User Name: field, type in OOT_SCH
In the Password: field, type in OOT_SCH
In the Host String: field, type in ODBC:POLITE.
To run SQL*Plus under UNIX, log into your UNIX account
and at the UNIX command prompt (shown as unix% below),
type the sqlplus
command followed by a carriage return. When prompted
for a username, supply your Oracle username (This may be
the same as or different from your UNIX account name). When
prompted for a password, supply your Oracle account
password (this should not be the same as your UNIX account
password).
To exit the SQL*Plus program (in any operating system), type EXIT and press Enter or carriage return:
Once a session has been established using the SQL*Plus tool, any SQL statements or SQL*Plus Commands may be issued. In the following section, the basic SQL*Plus Commands are introduced.
SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to:
The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:
Examples of these SQL*Plus commands are given in the following sections.
Note the distinction made between SQL*Plus Commands and SQL Statements. SQL*Plus commands are proprietary to the Oracle SQL*Plus tool. SQL is a standard language that can be used is just about any Relational Database Management System (RDBMS).
The following two sections describe how to invoke help in SQL*Plus under Windows 95/NT and under UNIX. The method you use to access help may differ according to how your software was installed.
To get HELP on any of the oracle tools, use the
Oracle8 or Oracle8i Documentation which is accessible through
a web browser. To access the Oracle8 Documentation,
click on the Windows 95
button, then
Programs, Oracle for Windows 95 and finally
Oracle8 Documentation: 
.
This will launch your local Web Browser (Netscape
Navigator/Communicator or Microsoft Internet Explorer)
and the Welcome to the Oracle8 Documentation Library!
screen will be displayed. From here, click on the
"TEXT VERSION" link to get to the
Oracle Product Documentation Library.
Once in the Oracle8 Documentation main screen, click on Oracle8 Enterprise Edition and then SQL*Plus Getting Started for Windows NT/95. Other documentation you may find useful are:
| Help File/Link | Contents |
|---|---|
| SQL Reference | Comprehensive syntax for all SQL statements |
| SQL*Plus Getting Started for Windows NT/95 | Specific SQL*Plus commands and options for Windows 95 and NT users. |
| SQL*Plus Quick Reference | Quick reference guide to SQL*Plus commands. |
| SQL*Plus User's Guide and Reference | Comprehensive guide to using SQL*Plus. |
Each of these can be found on the same Oracle8 Enterprise Edition page.
Note the the arrangement of help screen and on-line documentation in other versions of Oracle8 and Oracle8i may be slightly different.
HELP SELECT
as follows:
In the relational model, data is stored in structures called relations or tables. Each table has one or more attributes or columns that describe the table. In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc.
SQL statements are issued for the purpose of:
The SQL language has been standardized by the ANSI X3H2 Database Standards Committee. Two of the latest standards are SQL-92 and SQL-99. Over the years, each vendor of relational databases has introduced new commands to extend their particular implementation of SQL. Oracle8i's implementation of the SQL language conforms to the "Entry Leve" SQL-99 standards and is partially compliant with the Transitional, Intermediate, and Full levels of SQL-99. For more details on the exact compliant and non-compliant features, please refer to the Oracle8i SQL Reference.
Some examples of SQL statements follow. For all examples in this tutorial, key words used by SQL and Oracle are given in all uppercase while user-specific information, such as table and column names, is given in lower case.
To create a new table to hold employee data, we use the CREATE TABLE statement:
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1),
salary NUMBER(7) NOT NULL,
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL) ;
To insert new data into the employee table, we use the INSERT statement:
INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122',
'24-JAN-1954', '987 Western Way, Plano, TX',
'M', 42000, NULL, 5);
To retrieve a list of all employees with salary greater than 30000 from the employees table, the following SQL statement might be issued (Note that all SQL statements end with a semicolon):
To give each employee in department 5 a 4 percent raise, the following SQL statement might be issued:
UPDATE employee
SET salary = salary * 1.04
WHERE dno = 5;
To delete an employee record from the database, the following SQL statement might be issued:
DELETE FROM employee
WHERE ssn = 999887777 ;
The above statements are just an example of some of the many SQL statements and variations that are used with relational database management systems. The full syntax of these commands and additional examples are given below.
An Oracle database can contain one or more schemas. A schema is a collection of database objects that can include: tables, views, indexes and sequences. By default, each user has their own the schema which has the same name as the Oracle username. For example, a single Oracle database can have separate schemas for HOLOWCZAK, JONES, SMITH and GREEN.
Any object in the database must be created in only
one schema. The object name is prefixed by the schema name
as in: schema.object_name
By default, all objects are created in the user's
own schema.
For example, when JONES creates a database object such as a
table, it is created in her own schema. If
JONES creates an EMPLOYEE table, the full
name of the table becomes: JONES.EMPLOYEE.
Thus database objects with the same name can be
created in more than one schema. This feature allows
each user to have their own EMPLOYEE table, for example.
Database objects can be shared among several users by specifying the schema name. In order to work with a database object from another schema, a user must be granted authorization. See the section below on GRANT and REVOKE for more details.
Please note that many of these database objects and options are not available under Personal Oracle Lite. For example, foreign key constraints are not supported. Please see the on-line documentation for Personal Oracle Lite for more details.
SQL*Plus accepts SQL statements that allow a user to create, alter and drop table, view and sequence definitions. These statements are all standard ANSI SQL statements with the exception of CREATE SEQUENCE.
ALTER TABLE has the following syntax for renaming a table:
ALTER TABLE has the following syntax for adding a new column to an existing table:
Note: In earlier versions of Oracle (before Oracle8), there is no single command to drop a column of a table. In order to drop a column from a table, you must create a temporary table containing all of the columns and records that will be retained. Then drop the original table and rename the temporary table to the original name. This is demonstrated below in the section on Creating, Altering and Dropping Tables.
Finally, ALTER TABLE can also be used to add a constraint to a table such as for a PRIMARY KEY, FOREIGN KEY or CHECK CONSTRAINT. The syntax to add a PRIMARY KEY is:
where sql select statement is in the form:
Additional information on the SELECT statement and SQL queries can be found in the next section.
Note that an ORDER BY clause may not be added to the sql select statement when defining a view.
In general, views are read-only. That is, one may query a view but it is normally the case that views can not be operated on with INSERT, UPDATE or DELETE. This is especially true in cases where views joing two or more tables together or when a view contains an aggregate function.
In the following section, each of the SQL DDL commands will be discussed in more detail.
A table is made up of one or more columns (also called attributes in relational theory). Each column is given a name and a data type that reflects the kind of data it will store. Oracle supports four basic data types called CHAR, NUMBER, DATE and RAW. There are also a few additional variations on the RAW and CHAR data types. The basic datatypes, uses and syntax, are as follows:
The VARCHAR data type is a synonym for VARCHAR2. It is recommended to use VARCHAR2 instead of VARCHAR.
A column may be specified as NULL or NOT NULL meaning the column may or may not be left blank, respectively. This check is made just before a new row is inserted into the table. By default, a column is created as NULL if no option is given.
In addition to specifying NOT NULL constraints, tables can also be created with constraints that enforce referential integrity (relationships among data between tables). Constraints can be added to one or more columns, or to the entire table.
Each table may have one PRIMARY KEY that consists of a single column containing no NULL values and no repeated values. A PRIMARY KEY with multiple columns can be designated using the ALTER TABLE command. We create primary keys with NOT NULL constraints to uphold entity integrity.
Up to 255 columns may be specified per table. Column names and table names must start with a letter and may not contain spaces or other punctuation except for the underscore character. Column names and table names are case insensitive. This means that you can specify the names of columns and tables in any way you like. For example, the following three SELECT statements are all identical:
In the following example, a new table called ``employee'' is created with ten columns of a variety of types. The columns indicated by NOT NULL will be mandatory while the other columns, by default, will be optional.
The numbers 2 through 11 before each line indicate the line number supplied by the SQL*Plus program as this statement was typed in. We will omit these numbers in the rest of the examples to facilitate copying and pasting this material directly into a live SQL*Plus session.
A new table can also be created with a subset of the columns in an existing table. In the following example, a new table called emp_department_1 is created with only the fname, minit, lname and bdate columns from the employee table. This new table is also populated with data from the employee table where the employees are from department number 1.
One can also create a new table with all of the columns from the original table, but with only a subset of the rows form the original table:
SQL> CREATE TABLE high_pay_emp
AS SELECT *
FROM employee
WHERE salary > 50000 ;
Table created.
DESCRIBE is an SQL*Plus command that displays the columns of a table and their data types. The syntax for the DESCRIBE command is:
The copying of data can be suppressed by giving a WHERE clause that
always evaluates to FALSE for each record in the source table. The
following example makes a duplicate of the employee
table but does not copy any data into it.
Constraints can be added to the table at the time it is created, or at a later time using the ALTER TABLE statement. Constraints can include:
Here is an example of creating a primary key constraint on the ssn column:
Referential integrity constraints can also be added.
Referential integrity refers to the behavior of
child records when the parent record is modified
or deleted.
In the following example, the dno
column in the employee table references the
dnumber column in the
department table. If a department is deleted,
all employees that reference the department are also
deleted. This is given by the ON DELETE CASCADE
option. (be sure to DROP TABLE employee before you try the next
example).
In order to specify a foreign key constraint, the
column in the parent (or master) table (e.g., the dnumber
column in the department
table in the above example) must be either the
primary key or a unique key for the table. Thus,
the parent (or master) table must be created first
before the child (or detail) table is created using
the above constraints.
Additional CREATE TABLE constraint statements allow
the specification of what should happen when a row is
deleted or updated in a parent table. In the above example,
deleting a department causes all employees in that department
to also be deleted. Other options include
ON DELETE SET DEFAULT and
ON DELETE SET NULL. In addition, the behavior
of child tables when a parent table is updated can
also be specified using an ON UPDATE
clause.
CHECK constraints can be added to check the values for a given column. This can be used to allow only a specific set of valid values for a column. In the following example, CHECK constraints are added to limit the valid values for the sex column and to check if the salary is greater than 10,000 (be sure to DROP TABLE employee before you try the next example).
The CHECK constraints are activated when inserting a new row or when updating existing data. In the following example, the value given for sex is 'm':
In the previous examples, constraints were given names with the following prefixes:
The ALTER TABLE command can be used to add a new column to an
existing table or to change the data type of an existing column.
The following examples add a new column manager
to an existing table named emp_department_1
and then modify the data type of the fname column.
The ALTER TABLE command can also be used to change the datatype of column provided there is no data in the table. To get around this if there is data in the table, create a temporary table using all of the data from the existing table, delete the existing records from the original table, alter the datatype, and then insert the records from the temporary table back into the original table. For example, assume the emp_department_1 table has some records in it and we want to change the datatype for the MANAGER column:
The DROP TABLE command can be used to drop a table definition and
all of its data from the database. In the following example, the
table emp_department_1 created previously, is
dropped from the database.
Create another table called COURSES with the following columns and data types:
Next, use the ALTER TABLE statement to add the following column to the STUDENTS table:
Use the ALTER TABLE statement to define the StudentID and CourseNumber as the PRIMARY KEY for the COURSES table. To do this, list both of the column names separated by a comma.
Use the ALTER TABLE statement to define StudentID in the COURSES table as a FOREIGN KEY that references the StudentID in the STUDENTS table.
Finally, add some data to the STUDENTS and COURSES tables
(simply copy and paste these statements into SQL*Plus
to add the data):
employee
table is frequently accessed by superssn, an index
can be created on that column as well:
Create an index on the COURSES table for the semester and year columns (together).
employee table is
created. When a view is defined, a SQL statement is associated
with the view name. Whenever the view is accessed, the SQL
statement will be executed.
In the following example, the view
emp_dno_1 is created as a limited
number of columns (fname, lname, dno)
and limited set of data ( WHERE dno=1 ) from
the employee table.
Once the view is created, it can be queried with a SELECT statement as if it were a table.
Views can be dropped in a similar fashion to tables. The DROP VIEW command provides this facility. In the following example, the view just created is dropped.
Views can also be created to join several tables together. The following is an example of creating a view that joins two tables:
As a final example, a view can be created that contains an aggregate function. In the following example, a view is created that returns the average salary of all employees per department.
Note that in general, views are read-only as in the above cases.
To see which views are defined in a schema, submit a query to the USER_VIEWS view:
Query the view and show the output.
Create another view called V_COURSES_TAKEN based upon the following SQL SELECT statement:
Query the V_COURSES_TAKEN view and show the output.
In this example, the sequence will begin its numbering at 1 and count up (in increments of 1 which is the default) until it reaches 9999. Once the MAXVALUE is reached, accessing the sequence will return an error.
Sequences are accessed using a SELECT statement with
a special table called
Sequences can also be used in INSERT statements to
automatically provide the next value for a key.
For example, to insert a new employee row with the
next employee id in the sequence, the following
statement would be issued:
As with most database objects, Oracle Sequences can be dropped
using a DROP SEQUENCE command. Dropping a sequence and then
re-creating it has the effect of resetting the sequence to its
START WITH number. In the following example, the Employee Id
sequence created previously is dropped.
Sequences can also be altered to change the INCREMENT BY,
MAXVALUE or START WITH values. The ALTER SEQUENCE
statement achieves these changes.
Then, write an SQL INSERT statement to insert a
new record for the following person:
The actions are defined as follows:
In addition to objects such as tables, the SELECT and
UPDATE actions may also be granted on individual
columns in a table or view.
The general syntax for the GRANT statement is:
At this point, user BOB may now issue SQL SELECT
statements on the table ALICE.employee. For example,
user BOB may execute:
The REVOKE statement reverses the authorization
by removing privileges from a subject (user). The
syntax for REVOKE is:
The current authorizations in effect can be viewed
by selecting from the USER_TAB_PRIVS view. In the
following example, the columns are first formatted
(more examples of this are given in a later section),
and then the privileges for the user (table owner)
ALICE are displayed.
A quick way to generate a list of GRANT statements for
every table in your schema is to run a query on the
catalog that forms the GRANT statements:
The result of this query is something like the
following:
THen REVOKE the SELECT privilege from the
STUDENTS table. Have your group member try to
query the table after you have revoked access
and see what happens.
One problem with this method is that if the
tables move to another user's schema, all
of the references will need to change.
An alternative is to use Synonyms to
provide a pointer to the schema and database objects.
A Synonym is like a pointer in that is has a name
that is recognized in the local schema that,
when addressed, will resolve to the schema.object
name in another user's schema.
Synonyms are created with the CREATE SYNONYM
command:
For example, if Bob wishes to have access to
Alice's employee table, first, Alice would
need to GRANT access to her table using the GRANT
command, and then Bob would create a synonym
using:
Now Bob can execute the following query:
If the tables are moved to another schema such as
Abe's schema, then only the synonyms need to
be dropped and recreated. All applications
will run the same.
To generate a list of CREATE SYNONYM statements,
use the following type of query:
In this section, the SQL commands for creating, altering and
deleting tables, views and sequences, and granting and
revoking access to database objects have been introduced. A
typical database may have a dozen or more related tables with
several columns each. To facilitate the creation and deletion
of a large number of tables, the CREATE statements can be
placed into a file and executed using the SQL*Plus START
command.
SQL*Plus allows the user to enter SQL statements to select,
insert, update and delete rows in database tables. These
are all standard SQL statements.
A commit is also done implicitly when the next SQL statement is
executed or the user exits SQL*Plus.
If the WHERE clause is omitted, all rows in the table will be
deleted.
If a value for each column in the table is supplied, then the
columns do not need to be listed in the first set of parenthesis.
Values can be of 3 types: Character, Number or Date. Each one
requires a slightly different format when inserting:
All values, regardless of data type, must be separated by commas.
Another option for the INSERT statement is to pull some
data from another table. The syntax is:
For example, assume table1 and table2 have the same
number of columns and the corresponding columns
have the same data types. To insert all data currently in
table1 into table2:
The WHERE clause, GROUP BY, HAVING and ORDER BY statements
are optional. If a WHERE
clause if omitted, all rows in the table will be retrieved. If the
ORDER BY statement is omitted, there is no specific order in which
the rows will be displayed. GROUP BY and HAVING are used in
conjunction with aggregate functions (functions that operate
on more than one record). If all columns in the table are to be
retrieved, an asterisk (*) may be substituted for the entire
list of columns after the SELECT key word.
More than one table can be specified in the FROM clause.
The WHERE clause typically contains logic expressions
(such as
A more complete syntax for the SELECT statement is:
The expression can be either a single value or an arithmetic
expression including another column in the table. More than one
column can be updated at a time by adding additional column
name = expression pairs separated by commas.
If the WHERE clause is omitted, the update is applied to all
rows in the table.
For example, to give all employees in the marketing
department a 3% raise:
In the following example, a new row is inserted into the
To check the contents of the
In the next example, a row in the
In the final example, a row is deleted from the
In the final example, if the ROLLBACK command was given instead of
the COMMIT command, the rows would have been undeleted.
Note that many of these statements and commands
will not work properly under Personal Oracle Lite.
The Oracle Data Dictionary maintains a collection of
USER_ views that are accessible from each user's
schema. The following table summarizes these views:
A comprehensive list of user catalog views can be found in
the Oracle Server Reference guide.
Many of the view contain columns of type LONG.
In order to display their content, set the
SQL*Plus variable LONG to a large
number such as 4096 as follows:
To find out the names of tables you have created, use
the system view called
The column definitions for a table can be
displayed using the
More detailed metadata can be retrieved from
the tables
To get information on columns of a table,
use the following (substitute 'EMPLOYEE'
with the name of the table in question):
To see any constraints that are presently
in effect on a table, use the following
(substitute 'EMPLOYEE' with the name of the
table in question):
A list of Indexes defined on tables in the user's schema can be
displayed by querying the USER_INDEXES table:
Finally, a list of Views the user owns can be displayed
by querying the USER_VIEWS table:
The following table lists the major pseudo columns:
TO_CHAR (number, format) - Converts
a numeric column to a string of characters. format
is a set of number formatting codes where:
NVL (s, expression) - If s is
NULL, return expression. If s is not null,
then return s.
USER - Returns the username of the current user.
To use an aggregate function and a regular non-aggregated
column in the same query, a GROUP BY clause must
be added to the SELECT statement.
Examples of functions are given in the following section.
Example Table STUDENTS:
Another option is to enclose some text in quotes and
concatenate that text with the output of the SQL statement:
Note the two aliases given to the
students table: s1 and s2.
These allow us to refer to different views
of the same table.
You may wish to sort the output based on the GPA.
In this case, the output is ordered by GPA in
decending order (highest GPA will come
first, etc.):
In MS Access:
The following example prints a tree structure
modeled after the tutoring relationships
in the Students table. We will start with Mary's student id
(102) since no one tutors her.
This assumes the subquery returns only one
tuple as a result.
In the above case, the subquery returns a set of
tuples. The IN clause returns true when a tuple
matches a member of the set.
The above query shows all employees names and
salaries where there is at least one person who
makes more money (the first exists) and
at least one person who makes less money
(second exists).
The above query shows all employees for whom
there does not exist an employee who is
paid less. In other words, the highest
paid employee.
Show the departments with average salary
greater than 33000.
Show departments with 3 or more employees:
Show all employees whose name starts with 'S'
Show all employees whose name contains the letter 'E'
and the letter 'L' in that order:
Show all employees whose name contains the letter 'E'
and the letter 'L' in any order:
The DECODE function can also be used to invert a table.
For example to show the department names along a
single row we might use:
SQL*Plus has several commands to allow the user to edit or modify
SQL statements. Once a new SQL statement has been typed in (ending
with a ;) this statement is placed into a buffer and is
considered to be the current SQL statement. All of the
following commands operate on the current SQL statement in the
buffer.
SQL statements may be typed with a free format. Spaces and
<cr> characters may be used to separate key words in a SQL
statement. SQL*Plus displays line numbers in the left hand margin
indicating the current line for a SQL statement that spans multiple
lines.
In the following example, an erroneous SQL statement has been
entered. After the ; was typed, an error message was
displayed indicating the approximate location of the error and
a brief error message description.
To correct line number 2, the user can type the line number
followed by the correct portion of the SQL statement. This corrects
the SQL statement in the buffer. The last step is to execute the
SQL statement in the buffer by typing the RUN command.
The LIST command can be used to display the current contents of the
SQL buffer. An asterisk (*) is used to mark the current line
of the SQL statement within the buffer.
The current line of the SQL statement in the buffer can be appended
using the APPEND command. The syntax is: APPEND new text. In
the following example the new text ``xyz'' is appended to line
number 3 which is the current line in the buffer.
Text on a line in the SQL statement can also be replaced using the
CHANGE command. The syntax for the CHANGE command is: CHANGE /
old text / new text /
In the following example, text on the current line number 3 will be
replaced with blank text:
To move to a different line of the SQL statement in the buffer,
simply type the line number.
The DEL command can be used to delete the current line of the SQL
statement out of the buffer as in the following example.
A SQL statement in the buffer can be saved to a file for later use.
The SAVE command serves this purpose. The syntax for the SAVE
command is: SAVE filename
In this example, the current contents of the buffer are saved to a
file called query.sql:
A directory and/or drive letter (for those using MS DOS or
MS Windows) can be placed in front of the file name in
order to re-direct the file to another drive or directory.
For example, to save the current statement to a floppy disk:
A SQL statement saved in a file can then be loaded and executed
using the START command. The syntax for the START command is: START
filename
Here, the file query.sql created in the previous example is
loaded and executed using the START command:
Again, a drive letter and/or directory name can be
placed in front of the file name.
In many cases, it is easiest to create and edit
a set of text files containing the queries and
then use the START command to execute them.
Instructions for this vary depending on the
operating system. For example, under a UNIX
system, one can use a text editor such as
Under MS Windows 95 or NT, one can use the
Windows NotePad editor to create these same
types of files. If the files are stored on a
floppy disk (for example, the
When working with SQL statements and SQL*Plus
commands in a script file, be sure and make
backups of your disks and files.
The formatting commands include:
Note that none of these SQL*Plus formatting commands
changes the underlying table structures.
Perhaps the most useful command is
The
Some of the SQL*Plus variables of interest include
The following example shows an SQL*Plus script file
(
SQL*Plus has a command called
The
The
Note that some SQL*Plus commands will not show up in the
SPOOL file. To have them echo to the SPOOL file, use the
Also, when SPOOLing to a file, SQL*Plus makes each line
80 characters long by padding with spaces. This can be shortened
to fewer characters using the
The
The following example shows the prompt/accept
sequence for a query.
When this script is executed, the following output
is shown:
The first PROMPT command simply echoes out a line
to the display. The second command,
ACCEPT, accepts input from the user. In this case,
the variable that will hold the input is
called
Once the user types the department number and
presses enter, the variable
In the following example, additional commands have
been added including the SET VERIFY and REMARK
commands to improve the script.
When this script is executed, the following output
is shown:
For variables that are not numeric type, the data type
is not required in the ACCEPT command. For example,
to search for a specific employee by name:
In the above example, the UPPER() function is used
to change both the column values of lname
and the text the user supplied for the &name
variable to all upper case letters. In this way,
a match will be made even if the user types in
lower case letters for the &name. variable.
To check the execution plan for each SQL
statement, turn the AUTOTRACE
option on with the following SQL*Plus command:
To turn off AUTOTRACE, issue the following
command:
The next useful command for performance
monitoring is called TIMING.
This command starts a timer that can be
read at any interval, similar to how a stopwatch
operates. To set up a timer, issue the following
command:
To stop a timer, issue the TIMING STOP
command. Note that AUTOTRACE and TIMING should probably not
be used in conjunction as it would be difficult to separate
the execution time for the SQL statement from the
time taken to generate the plan and statistics.
The TO_CHAR and TO_DATE functions can be used to
convert dates to other formats, however, this may
become inconvenient, especially when inserting
a large number of rows.
The ALTER SESSION statement can be used to alter various
characteristics of the current SQL*Plus session including
the default date format. This statement is often used to
format dates to conform to regional customs. The
syntax of ALTER SESSION for use with changing the
default date format is as follows:
The date_format can include the following
codes:
For example, to change the default date to include a full
four digit year, issue the following ALTER SESSION
statement:
Note that this change only remains in effect for the
current session. Logging out of SQL*Plus and logging back in
(or re-connecting to the Oracle database using the
connect command) will reset the date format
back to its default.
The PL/SQL language has all of the conditional (IF ...THEN)
looping (WHILE), assignment, variable declaration and
other language constructs of a complete programming language.
SQL statements may be freely mixed in with the other programming
statements. The major change to SQL is the syntax of
the SELECT statement. All SELECT statements in PL/SQL must
use the INTO clause to redirect the rows returned by the
SELECT into variables. The syntax of the SELECT statement is:
Variables named in the INTO clause correspond to
the order of columns selected in the SELECT clause.
For example:
The above PL/SQL block declares two variables and
then executes a SELECT statement returning the salary
in PL/SQL variable empsalary and the department
number in PL/SQL variable empdepartment for
employee SMITH. If the empdepartment is equal
to 1 then an SQL UPDATE statement is executed.
It is possible that a SELECT...INTO statement can
return more than on row or record, or no records at all.
In such situations, the entire SELECT statement will fail
resulting in what is called an EXCEPTION. EXCEPTIONs in
PL/SQL must be handled (taken care of) by some code.
Most all triggers and stored procedures that use SELECT...INTO
have EXCEPTION handling code.
The EXCEPTION code the following syntax:
To continue the above example, the exception code
would appear as follows at the end of the regular
stored procedure code:
There are two main ways of storing PL/SQL code
in the Oracle database: CREATE PROCEDURE
and CREATE TRIGGER. Triggers are procedures that
are executed in response to some event. Events
include the execution of a DML statement on
a table (such as INSERT, UPDATE, DELETE, MODIFY).
Procedures are typically used to implement general
program logic that can be shared across applications,
triggers and utilities. A procedure must be explicitly
called by an application, trigger or program.
It is common practice to store general business
rule checking in procedures. This allows applications
to check data validity before a transaction is
submitted to the database. Triggers can also call
the procedures to check data at the database level.
Since the business rules are coded in a single set
of procedures, maintenance of this code is simplified.
In this section, we will introduce the syntax
for creating triggers and demonstrate the use of a
trigger to enforce a business rule.
Here is an example trigger called check_age
used to check if
an employee is over the age of 16. This trigger will
be executed in response to the events of INSERT or
DELETE on the employee table. The check_age
trigger code will be
executed BEFORE the affects of the SQL statement
are put into place. Finally, check_age will
execute FOR EACH ROW affected by the SQL statement.
Lines starting with the double minus sign --
are comments and are ignored by the trigger.
After this code has been entered in SQL*Plus,
an additional line will appear as if the SQL
statement should continue. To complete entering
the trigger code, type a forward slash /
and the code will be submitted.
One of three things will happen when a new procedure
or trigger is created:
To view the compilation errors check the
USER_ERRORS view:
To see if the trigger compiled correctly, look in
view USER_ERRORS as follows:
Alternately, use the SQL*Plus command SHOW ERRORS.
To view the trigger code:
Or, in a more compact form:
Once the trigger has been entered without
syntax errors, it can be tested.
See what happens when we attempt to
insert a new employee record where the
employee's birthdate is less than 16
years ago:
The following example implements a simple inventory system.
The Products table holds a list of products with a productid
as the key and a description. The inventory location table
holds a series of locations in the warehouse including an
identifier and the aisle, tier and bin.
Finally, the intersection of these two tables is the
inventory table which takes a locationid and a
productid and gives the quantity of the product
present at the location.
SQL Statements to create and populate tables
The SQL code to create and populate the three tables
is given below:
First step: Create three tables and add constraints
We can create a view to implement this query:
To see the output from the stored procedures, set the following
options (Note: You must do this each time you log into SQL*Plus).
Stored Procedures to Add and Remove items From inventory
Adding a new product to an existing location requires the
following:
Removing an existing product from inventory requires the
following:
Note: When creating stored procedures in SQL*Plus, you
must type a slash character ( / ) on the line after the
last END; of the procedure. This lets SQL*Plus know
to submit the CREATE PROCEDURE statement to the database.
To see any errors from the compilation of the procedure
use the show errors command.
To run the stored procedures, use the EXECUTE command followed
by the name of the procedure and any parameters.
For example, to add 10 units of product P500 to location 106,
execute the following:
To remove 10 units of procedure P500 from location 106,
execute the following:
In this section, we have shown some basic forms of triggers
and stored procedures. For additional information and examples
on the PL/SQL language, please refer to the Oracle PL/SQL
User's Guide and Reference.
Portions of this tutorial are based on the
technical working paper:
Please feel free to use these tutorials for your own personal
use, education, advancement, training, etc. However, I kindly
ask that you respect the time and effort I have put into
this work by not distributing copies, either in whole or in part,
for your personal gain. You may not under any circumstances
download, cache or host copies of these tutorials on your own
servers. Use of these tutorials for commercial training
requires a special arrangement with the author or authors.
DUAL. The
employee_id_seq
sequence is retrieved:
Exercise 4: Working with Sequences
For this exercise, start by creating an Oracle Sequence
called student_id_seq. Have the sequence start
numbering at 120 and go up to 999.
4.2.2 Grant and Revoke Statements
The GRANT and REVOKE statements allow a user to control
access to objects (Tables, Views, Sequences, Procedures, etc.)
in their schema.
The Grant command grants authorization for a subject
(another user or group) to perform some action
(SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX) on
an object (Table, View, stored procedure, sequence
or synonym).
GRANT SELECT
ON employee
TO BOB ;
REVOKE SELECT
ON employee
FROM BOB ;
Exercise 5: GRANT and REVOKE
GRANT SELECT privileges to another member of your
group. Have them query your STUDENTS table.
4.2.3 Synonyms
In many cases, a schema is created under a
single username but other users must have access
to the tables, sequences and stored
procedures. One possibility is to code
all queries and applications to specifically
access these database objects by providing a
schema name. For example;
4.3 SQL Data Manipulation Language
In this section, we discuss SQL statements that can
be used to manipulate data in tables and views.
4.3.1 Select, Insert, Update, Delete,
Commit and Rollback Data
For example: 'Bill Smith'
For example: 123, 44000.12
For example: '26-JUN-1996'
Note that different versions of Oracle accept only 2 digit years
for an INSERT statement. All newer versions (from Oracle8 on)
appear to default to using 4 digit years.
INSERT INTO table2
SELECT * FROM table1;
WHERE salary > 40000) that are
evaluated for each row in the table.
employee table. Since a value is supplied
for each column, the columns do not need to be explicitly
listed.
employee
table, a SELECT statement is done on the table.
employee
table is updated.
employee table.
4.3.2 Displaying Table Metadata (Data about the data)
Once database objects have been created, it is often useful
to query the data dictionary to see the various
characteristics of the objects. In this section, we
describe several ways to query the data dictionary
to retrieve this information.
USER View Contents Typical Query
USER_TABLES Table names and storage
details about tables a user owns
SELECT table_name FROM USER_TABLES;
CAT or TAB Brief list of tables and views for a user
SELECT * FROM CAT;
or
SELECT * FROM TAB;
COL Column names and NOT NULL constraints.
SELECT colno, cname, coltype, width, scale, precision, nulls
FROM col
WHERE tname = 'EMPLOYEE'
ORDER BY col.colno;
USER_INDEXES Indexes defined on tables the user owns
COLUMN table_owner FORMAT A12
SELECT index_name, table_owner, table_name FROM USER_INDEXES ;
USER_VIEWS View names and view definitions (queries)
a user owns
SELECT view_name, text FROM USER_VIEWS;
USER_SEQUENCES Sequence definitions and current
values for sequences a user owns
SELECT * FROM USER_SEQUENCES ;
USER_TRIGGERS Trigger names and definitions
for triggers a user owns
SELECT trigger_name, trigger_body FROM USER_TRIGGERS;
USER_ERRORS Contains information about the last error that
occurred in a user's schema due to a trigger or
procedure compilation error.
SELECT * FROM USER_ERRORS;
USER_CONSTRAINTS Constraints on tables a user owns.
Includes column constraints such as NOT NULL,
CHECK and foreign key constraints.
SELECT constraint_name, table_name, search_condition
FROM USER_CONSTRAINTS WHERE table_name = 'EMPLOYEE';
USER_OBJECTS All database objects a user owns. Includes
tables, views, sequences, indexes, procedures, triggers, etc.
COLUMN object_name FORMAT A35
SELECT object_name, object_type FROM USER_OBJECTS ;
USER_SOURCE Source code for stored procedures owned by the user.
To see which procedures exist: SELECT DISTINCT NAME from USER_SOURCE;
To see the actual code: SELECT TEXT FROM USER_SOURCE WHERE NAME = 'procedure_name' ORDER BY LINE;
Note: You may have to reduce the ARRAYSIZE variable to avoid
overflowing the bufer. e.g., SET ARRAYSIZE 2
USER_TS_QUOTAS Quotas on tablespaces accessible to a user.
SELECT * FROM USER_TS_QUOTAS ;
CAT in a
SELECT statement: SELECT * FROM cat; .
The following is an example:
TAB view was supported in
older versions of Oracle and may not be available
in future releases of Oracle. In that case, try
using the CAT view instead of
TAB.
DESCRIBE command
in SQL*Plus:
COL and
user_constraints.
SQL> COLUMN table_owner FORMAT A12
SQL> SELECT index_name, table_owner, table_name FROM USER_INDEXES ;
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------ ------------------------------
ACCOUNTS_PK HOLOWCZA ACCOUNTS
AT_PK HOLOWCZA ACCOUNT_TYPES
COURSES_PK HOLOWCZA COURSES
CUSTOMER_PK HOLOWCZA CUSTOMERS
PK_DEPARTMENT HOLOWCZA DEPARTMENT
PK_EMP HOLOWCZA EMPLOYEE
UNQ_RNAME HOLOWCZA LOGREPORT
SQL> SET LONG 4096
SQL> SELECT view_name, text FROM USER_VIEWS;
VIEW_NAME
-------------------
TEXT
--------------------------------------------------------------
VACCOUNTS
SELECT c.fname, c.lname, ac.account_number, at.account_typeid,
at.interest_rate, at.minimum_balance,
ac.date_opened, ac.current_balance
FROM customers c, accounts ac, account_types at
WHERE c.customerid = ac.customerid
AND ac.account_typeid = at.account_typeid
V_COURSES_TAKEN
SELECT name, major, coursenumber, coursename,
semester, year, grade
FROM students, courses
WHERE students.studentid = courses.studentid
4.3.3 Oracle Pseudo-Columns
The Oracle implementation of SQL adds several
pseudo columns to each table. These columns
do not exist in a physical table, yet they can be
used in any SQL statement for a variety of purposes.
Exercise 6: Dispaying Metadata
For this exercise, query the USER_ tables and
display the following metadata:
4.3.4 Oracle SQL Functions
The Oracle implementation of SQL provides a number of
functions that can be used in SELECT statements.
Functions are typically grouped into the following:
The following is an overview and brief description of
single row functions. x is some number,
s is a string of characters and c is
a single character.
The following is an overview and brief description of
multiple row (group) functions. col is the
name of a table column (or expression) of type NUMBER.
ABS (x) - Absolute Value of x
CEIL (x) - Smallest integer greater than
or equal to x.
COS (x) - Cosine of x
FLOOR (x) - Largest integer less than
or equal to x.
LOG (x) - Log of x
LN (x) - Natural Log of x
ROUND (x, n) - Round x to
n decimal places to the right of the decimal
point.
SIN (x) - Sine of x
TAN (x) - Tangent of x
TRUNC (x, n) - Truncate x to
n decimal places to the right of the decimal
point.
CHR (x) - Character for ASCII value x.
INITCAP (s) - String s with the first letter
of each word capitalized.
LOWER (s) - Converts string s to
all lower case letters.
LPAD (s, x) - Pads string s
with x spaces to the left.
LTRIM (s) - Removes leading spaces from s.
REPLACE (s1, s2, s3) - Replace
occurrences of s2 with s3 in string s1.
RPAD (s, x) - Pads string s
with x spaces to the right.
RTRIM (s) - Removes trailing spaces from s.
SUBSTR (s, x1, x2) - Return a
portion of string s starting at position x1
and ending with position x2. If x2 is
omitted, it's value defaults to the end of s.
UPPER (s) - Converts string s to
all upper case letters.
ASCII (c) - Returns the ASCII value of c
INSTR (s1, s2, x) - Returns the
position of s2 in s1 where the search
starts at position x.
LENGTH (s) - Length of s
TO_CHAR (date, format) - Converts
a date column to a string of characters. format
is a set of Date formatting codes where:
YYYY is a 4 digit year.
MM is a month number.
MONTH is the full name of the month.
MON is the abbreviated month.
DDD is the day of the year.
DD is the day of the month.
D is the day of the week.
DAY is the name of the day.
HH is the hour of the day (12 hour clock)
HH24 is the hour of the day (24 hour clock)
MI is the minutes.
SS is the seconds.
9 indicates a digit position. Blank if position value is 0.
0 indicates a digit position. Shows a 0 if the position value is 0.
$ displays a leading currency indicator.
TO_DATE (s, format) - Converts
a character column (string s to a date. format
is a set of Date formatting codes as above.
TO_NUMBER (s, format) - Converts
a character column (string s to a Number. format
is a set of Number formatting codes as above.
SYSDATE - Returns the current date (and time if
the TO_CHAR function is used) from the
system clock.
For example:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS') FROM DUAL;
DECODE (s, search1, result1, search2, result2) -
Compares s with search1, search2, etc.
and returns the corresponding result when there is a match.
In addition the COUNT group function counts instances of
values. These values can be any type (CHAR, DATE or NUMBER):
Exercise 7: Functions
For this exercise, use the various functions to
display the following:
Congratulations Bill, your grade point average is 3.45
You'll need to use the TO_CHAR function to convert the
GPA column (which is a NUMBER data type) to a set of characters.
Hint: You'll need to use the SUBSTR function on
the COURSENUMBER column to extract the first three
letters. Then compare this to 'CIS'.
4.3.5 Examples of SQL DML Statements
In this section, several examples of SQL DML statements
are given. Variations on WHERE clause, FROM clause and
using SQL functions are all demonstrated.
Basic Select Statements
CREATE TABLE students (studentid NUMBER(5,0), name VARCHAR2(25),
major VARCHAR2(15), gpa NUMBER(6,3),
tutorid NUMBER(5,0));
INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102);
INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL);
INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102);
INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106);
INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);
INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103);
INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);
Example table COURSES:
Create table courses(studentid NUMBER(5,0) NOT NULL,
coursenumber VARCHAR2(15) NOT NULL,
coursename VARCHAR2(25), semester VARCHAR2(10),
year NUMBER(4,0), grade VARCHAR2(2));
INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');
INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-');
INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B');
INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A');
INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');
StudentID Name Major GPA TutorId
101 Bill CIS 3.45 102
102 Mary CIS 3.1
103 Sue Marketing 2.95 102
104 Tom Finance 3.5 106
105 Alex CIS 2.75 106
106 Sam Marketing 3.25 103
107 Jane Finance 2.9 102
SELECT AVG(gpa)
FROM students;
AVG(GPA)
----------
3.12857143
SELECT AVG(gpa)
FROM students
WHERE major = 'CIS' OR major = 'Finance';
AVG(GPA)
----------
3.14
This is an example of a subquery
SELECT name, gpa
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME GPA
-------- ----------
Tom 3.5
SELECT 'The student with the highest GPA is ' || name
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME
------------------------------------------
The student with the highest grade is Tom
SELECT name, major, gpa
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
);
NAME MAJOR GPA
-------- ---------- ----------
Bill CIS 3.45
Tom Finance 3.5
Sam Marketing 3.25
SELECT name, major, gpa
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
)
ORDER BY gpa DESC;
NAME MAJOR GPA
-------- ---------- ----------
Tom Finance 3.5
Bill CIS 3.45
Sam Marketing 3.25
Selecting from 2 or More Tables
Example table EMPLOYEE:
FNAME MI LNAME SSN BDATE ADDRESS S SALARY SUPERSSN DNO
-------- -- ------- --------- --------- ------------------------- - ------ --------- ---
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX M 30000 333445555 5
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX M 40000 888665555 5
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX F 25000 987654321 4
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX F 43000 888665555 4
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX M 38000 333445555 5
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX F 25000 333445555 5
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX M 25000 987654321 4
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX M 55000 1
Example table DEPARTMENT:
DNAME DNUMBER MGRSSN MGRSTARTD
--------------- --------- --------- ---------
RESEARCH 5 333445555 22-MAY-78
ADMINISTRATION 4 987654321 01-JAN-85
HEADQUARTERS 1 888665555 19-JUN-71
Example Table DEPT_LOCATIONS:
DNUMBER DLOCATION
------- ---------------
1 HOUSTON
4 STAFFORD
5 BELLAIRE
5 SUGARLAND
5 HOUSTON
Example table DEPENDENT:
ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP
--------- --------------- --- --------- ------------
333445555 ALICE F 05-APR-76 DAUGHTER
333445555 THEODORE M 25-OCT-73 SON
333445555 JOY F 03-MAY-48 SPOUSE
123456789 MICHAEL M 01-JAN-78 SON
123456789 ALICE F 31-DEC-78 DAUGHTER
123456789 ELIZABETH F 05-MAY-57 SPOUSE
987654321 ABNER M 26-FEB-32 SPOUSE
SELECT employee.fname, employee.lname
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON' ;
FNAME LNAME
-------- --------
JOHN SMITH
FRANKLIN WONG
RAMESH NARAYAN
JOYCE ENGLISH
JAMES BORG
SELECT dept_locations.dlocation, department.dname,
employee.fname, employee.lname
FROM employee, department, dept_locations
WHERE employee.dno = department.dnumber
AND department.dnumber = dept_locations.dnumber
AND employee.dno = dept_locations.dnumber
ORDER BY dept_locations.dlocation, employee.lname;
Results:
DLOCATION DNAME FNAME LNAME
--------------- --------------- -------- --------
BELLAIRE RESEARCH JOYCE ENGLISH
BELLAIRE RESEARCH RAMESH NARAYAN
BELLAIRE RESEARCH JOHN SMITH
BELLAIRE RESEARCH FRANKLIN WONG
HOUSTON HEADQUARTERS JAMES BORG
HOUSTON RESEARCH JOYCE ENGLISH
HOUSTON RESEARCH RAMESH NARAYAN
HOUSTON RESEARCH JOHN SMITH
HOUSTON RESEARCH FRANKLIN WONG
STAFFORD ADMINISTRATION AHMAD JABBAR
STAFFORD ADMINISTRATION JENNIFER WALLACE
STAFFORD ADMINISTRATION ALICIA ZELAYA
SUGARLAND RESEARCH JOYCE ENGLISH
SUGARLAND RESEARCH RAMESH NARAYAN
SUGARLAND RESEARCH JOHN SMITH
SUGARLAND RESEARCH FRANKLIN WONG
16 rows selected.
SELECT MAX(employee.salary)
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON';
MAX(EMPLOYEE.SALARY)
--------------------
55000
SELECT *
FROM department, dept_locations ;
DNAME DNUMBER MGRSSN MGRSTARTD DNUMBER DLOCATION
--------------- ------- --------- --------- ------- ----------
RESEARCH 5 333445555 22-MAY-78 1 HOUSTON
ADMINISTRATION 4 987654321 01-JAN-85 1 HOUSTON
HEADQUARTERS 1 888665555 19-JUN-71 1 HOUSTON
RESEARCH 5 333445555 22-MAY-78 4 STAFFORD
ADMINISTRATION 4 987654321 01-JAN-85 4 STAFFORD
HEADQUARTERS 1 888665555 19-JUN-71 4 STAFFORD
RESEARCH 5 333445555 22-MAY-78 5 BELLAIRE
ADMINISTRATION 4 987654321 01-JAN-85 5 BELLAIRE
HEADQUARTERS 1 888665555 19-JUN-71 5 BELLAIRE
RESEARCH 5 333445555 22-MAY-78 5 SUGARLAND
ADMINISTRATION 4 987654321 01-JAN-85 5 SUGARLAND
HEADQUARTERS 1 888665555 19-JUN-71 5 SUGARLAND
RESEARCH 5 333445555 22-MAY-78 5 HOUSTON
ADMINISTRATION 4 987654321 01-JAN-85 5 HOUSTON
HEADQUARTERS 1 888665555 19-JUN-71 5 HOUSTON
15 rows selected.
SELECT DISTINCT dlocation
FROM dept_locations;
DLOCATION
---------------
BELLAIRE
HOUSTON
STAFFORD
SUGARLAND
SELECT department.dname, SUM( employee.salary )
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME SUM(EMPLOYEE.SALARY)
--------------- --------------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
SELECT department.dname, SUM( employee.salary ) AS TotalSalaries
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME TOTALSALARIES
--------------- -------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
SELECT fname, lname,
salary AS CurrentSalary,
(salary * 1.03) AS ProposedRaise
FROM employee;
FNAME LNAME CURRENTSALARY PROPOSEDRAISE
-------- -------- ------------- -------------
JOHN SMITH 30000 30900
FRANKLIN WONG 40000 41200
ALICIA ZELAYA 25000 25750
JENNIFER WALLACE 43000 44290
RAMESH NARAYAN 38000 39140
JOYCE ENGLISH 25000 25750
AHMAD JABBAR 25000 25750
JAMES BORG 55000 56650
8 rows selected.
Recursive Queries and Table Aliases
STUDENTS (studentid, name, major, grade, student_tutorid)
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid;
STUDENT TUTOR
------------------------- -----------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
(+) after the join condition to indicate
an outer join:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1 LEFT JOIN students tutors
ON s1.tutorid = tutors.studentid;
In Oracle:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid (+) ;
STUDENT TUTOR
------------------------- -------------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
Mary
SELECT s1.name AS TutorName,
COUNT(tutors.tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE s1.studentid = tutors.tutorid
GROUP BY s1.name;
TUTORNAME NUMBERTUTORED
------------------------- -------------
Mary 3
Sam 2
Sue 1
Tree Queries
Another form of recursive query is the tree query.
A tree query decomposes the table such that each
row is a node the tree and nodes are related in
levels. Consider the Students table defined above.
Using the SQL SELECT statements CONNECT BY
and START WITH clauses, we can form a set
of relationships between the rows of the
table that form a tree structure.
WHERE Clause Expressions
SELECT name, grade
FROM students
WHERE grade =
( SELECT MAX(grade) FROM students );
Typically used when aggregate functions are in the subquery.
SELECT employee.fname, department.dname
FROM employee, department
WHERE employee.dno = department.dnumber
AND department.dname IN ('HEADQUARTERS', 'RESEARCH');
FNAME DNAME
-------- ---------------
JAMES HEADQUARTERS
JOHN RESEARCH
JOYCE RESEARCH
RAMESH RESEARCH
FRANKLIN RESEARCH
SELECT employee.fname
FROM employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dept_locations.dlocation = 'STAFFORD');
FNAME
-------
ALICIA
JENNIFER
AHMAD
SELECT department.dname, AVG(salary)
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
HAVING AVG(salary) > 33000 ;
DNAME AVG(SALARY)
--------------- -----------
HEADQUARTERS 55000
RESEARCH 33250
SELECT department.dname, COUNT(employee.dno)
FROM department, employee
WHERE department.dnumber = employee.dno
GROUP BY department.dname
HAVING COUNT(employee.dno) >= 3;
DNAME COUNT(EMPLOYEE.DNO)
--------------- -------------------
ADMINISTRATION 3
RESEARCH 4
Use the LIKE operator to perform a partial string match.
Generally, the % character is used as the wild card
although in some DBMS, the * character is used.
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE 'S%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JOHN SMITH 30000
Show all employees whose name contains the letters 'AR'
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%AR%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
RAMESH NARAYAN 38000
AHMAD JABBAR 25000
Note that chatacters within quotes are case sensitive.
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%E%L%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JOYCE ENGLISH 25000
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%E%L%' OR
lname LIKE '%L%E%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JENNIFER WALLACE 43000
JOYCE ENGLISH 25000
Examples of SQL Functions
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YYYY')
AS Sentence
FROM employee;
SENTENCE
----------------------------------------
The oldest employee was born on 10/11/1927
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YY') || ' and is now' ||
TO_CHAR( (SYSDATE - MIN(bdate)) / 365, '99') ||
' years old.'
AS Sentence
FROM employee;
SENTENCE
-----------------------------------------------------------------
The oldest employee was born on 10/11/27 and is now 70 years old.
SELECT fname || ' '|| lname || ' has ' ||
DECODE(COUNT(essn),
0, 'no dependents.',
1, 'one dependent.',
2, 'two dependents.',
3, 'three dependents.')
AS Sentence
FROM employee, dependent
WHERE employee.ssn = dependent.essn (+)
GROUP BY employee.fname, lname;
SENTENCE
---------------------------------------
AHMAD JABBAR has no dependents.
ALICIA ZELAYA has no dependents.
FRANKLIN WONG has three dependents.
JAMES BORG has no dependents.
JENNIFER WALLACE has one dependent.
JOHN SMITH has three dependents.
JOYCE ENGLISH has no dependents.
RAMESH NARAYAN has no dependents.
8 rows selected.
SELECT MAX(DECODE(dnumber, 1, dname)) AS Department,
MAX(DECODE(dnumber, 4, dname)) AS Department,
MAX(DECODE(dnumber, 5, dname)) AS Department
FROM department ;
DEPARTMENT DEPARTMENT DEPARTMENT
--------------- --------------- ---------------
HEADQUARTERS ADMINISTRATION RESEARCH
Deleting Tuples with DELETE
DELETE employee;
DELETE employee
WHERE salary > 50000;
DELETE employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dlocation = 'HOUSTON');
For example, consider the
DNO attribute in the Employee
table as a Foreign Key.
Removing a department would then be
contingent upon no employees working in
that department.
This is what we call enforcing
Referential Integrity
Change Values using UPDATE
UPDATE employee
SET lname = 'SMITH'
WHERE lname = 'JONES';
UPDATE employee
SET salary = salary * 1.05
WHERE fname = 'JOYCE' AND lname = 'ENGLISH' ;
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.02
WHERE TO_NUMBER( ( SYSDATE - bdate) / 365) >= 50;
Exercise 8: Update and Delete
For this exercise, write the SQL UPDATE and DELETE
statements to:
5. Advanced SQL*Plus Commands
This section introduces some of the advanced features of
SQL*Plus including editing the SQL command buffer,
formatting output from SQL SELECT statements, saving
the output from SQL statements and collecting
performance statistics on the execution of SQL
statements.
5.1 Editing The SQL Buffer
SQL> SELECT tname, tabtype
2 FRO
3 tab;
FRO
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> 2 FROM
SQL> RUN
TNAME TABTYPE
------------------------------ -------
MACHINE TABLE
EMPLOYEE TABLE
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> APPEND xyz
3* tabxyz
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
SQL> CHANGE/xyz//
3* tab
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
VI, Emacs or Pico
to create text files with the create statements to
create the tables, insert statements to add data and
select statements to perform some queries.
a:
drive), then the START command can be used
as follows:
5.2 Formatting SQL*Plus Output
SQL*Plus contains several commands that can
alter the appearance of the output. These commands
are only in effect for the current SQL*Plus session.
They can also be included in SQL script files and
can be executed using the START command.
COLUMN
which changes the appearance of data for a given column.
The syntax for the COLUMN command is as
follows:
COLUMN column_name option1 option2 ...
Where option can be one or more of the following:
FORMAT format
- Changes the format for the column.
For example, to only display the first 10 characters of
an employee's last name (column LNAME), use the following:
COLUMN lname FORMAT A10
This indicates to format the lname column as an Ascii
column with only 10 characters.
Numbers can be formatted using "9" to indicate digits.
For example:
COLUMN salary FORMAT $9,999,990.99
HEADING heading_text
- changes the heading for a column.
JUSTIFY LEFT or JUSTIFY CENTER or
JUSTIFY RIGHT - aligns the output with the left,
center or right of the column.
NULL text - Indicates the text that
should be displayed in place of a NULL value.
WRAPPED or WORD_WRAPPED or TRUNCATED
- Indicates how text that is longer than the displayed column
width should be handled. TRUNCATED means
it will be cut off at the maximum width of the field.
WRAPPED will wrap the value down to the
next line of output. WORD_WRAPPED does the same
as Wrapped but breaks the value up on white space.
SET and SHOW commands can
also be useful. To see a listing of all of the SQL*Plus
variables, type SHOW ALL
BTITLE and TTITLE described above,
Other useful variables include:
OFF
meaning SQL*Plus commands will not be echoed.
ON.
LONG
column will be displayed. The default is 80
bytes.
PAGESIZE lines have been displayed.
The default is OFF.
LINESIZE.
The default is ON meaning the output will
be trimmed (e.g., will not be padded with spaces).
LINESIZE. The default is ON
meaning long output will be wrapped.
myquery.sql) that
utilizes some of the above formatting commands. The output
of this script is displayed afterwards.
5.3 Saving SQL*Plus Output using the
SPOOL CommandSPOOL that can send the
output from any SQL statement to a file. Indeed, anything
that is displayed in SQL*Plus can be echoed to this spool file.
SPOOL command is invoked with the
name of a file that will contain the output.
Once this has been executed, the output from
all subsequent SQL statements will be copied to
the file. To end capturing the output, issue the
SPOOL OFF command.
The following is an example:
SPOOL OFF command turns the output off.
Everything between SPOOL a:\myfile.out and
SPOOL OFF will be in the
file myfile.out. This is a simple ASCII text file
that can be read by Windows Notepad, MS Word, or
just about any word processor, e-mail package, etc.
To print, load this file into MS Word, set the font to
Courier and print as you would with any other
document.
SET ECHO ON option.
SET LINESIZE
option. For example, SET LINESIZE 70 will pad
each line of output to 70 characters.
SET TRIMOUT and SET TABS
options offer other ways to change the spooled output.
5.4 Prompting and Accepting user Input
SQL*Plus has several commands that can be used to
prompt the user for input, accept input from the
user and store it in a variable, and then use
that variable in a query.
dept. The input should be
of type NUMBER, and the PROMPT
Department Number: should be displayed.
dept
takes on the value. The following two lines
(starting with old and new) are verifying the
values used for the dept variable.
To suppress the display of this verification, use
the SET VERIFY OFF command before
running the script or as one of the first
commands in the script.
5.5 Collecting Statistics On SQL Statements
SQL*Plus has several commands that monitor the execution of
SQL statements. The commands can be used to gather
statistical information for performance monitoring
purposes.
The first command is called AUTOTRACE and
is used to trace the execution plan for an SQL
statement. To use AUTOTRACE, a special table
must be created in the schema to hold the statistical
information. Execute the following CREATE TABLE
command in your schema:
SET AUTOTRACE ON
Then execute an SQL statement:
SET AUTOTRACE OFF
5.6 Using the ALTER SESSION Statement for Date Formats
In the previous examples of SQL statements, the default format
of data of type DATE has been in the form: DD-MON-YY
YY A 2 digit year such as 98.
YYYY A 4 digit year such as 1998.
NM A month number.
MONTH The full name of the month.
MON The abbreviated month (Jan, Feb, Mar).
DDD The day of the year. For use is Julian dates.
DD The day of the month.
D The day of the week.
DAY The name of the day.
HH The hour of the day (12 hour clock)
HH24 The hour of the day (24 hour clock)
MI The minutes.
SS The seconds.
6. Stored Procedures and Triggers
The Oracle RDBMS has the ability to store procedures within
the data dictionary and execute procedures in the RDBMS.
Procedures (Program Units) are written in the PL/SQL
language (Procedural Language), which is proprietary
to Oracle. PL/SQL runs in both the database engine
as well as in many of Oracle's development tools
such as Oracle Developer.
6.1 Trigger Syntax
Creating a trigger is accomplished with the
CREATE TRIGGER statement. There are numerous
options for a trigger that specify when the trigger
should fire. These options include:
6.2 Trigger Example
SELECT * FROM USER_ERRORS
or use the SHOW ERRORS SQL*Plus command.
6.3 Stored Procedure Example
CREATE TABLE inventory_locations (
locationid NUMBER(10) NOT NULL,
aisle NUMBER(10),
tier NUMBER(10),
bin NUMBER(10) );
ALTER TABLE inventory_locations
ADD CONSTRAINT il_pk PRIMARY KEY (locationid);
CREATE TABLE products (
productid VARCHAR(10) NOT NULL,
description VARCHAR(35) );
ALTER TABLE products ADD CONSTRAINT prod_pk
PRIMARY KEY (productid);
CREATE TABLE inventory (
locationid NUMBER(10) NOT NULL,
productid VARCHAR(10) NOT NULL,
quantity NUMBER(10) );
ALTER TABLE inventory ADD CONSTRAINT inventory_pk
PRIMARY KEY (locationid, productid);
Next step: Add some data to the three tables
INSERT INTO inventory_locations VALUES (101, 1, 1, 1);
INSERT INTO inventory_locations VALUES (102, 1, 1, 2);
INSERT INTO inventory_locations VALUES (103, 1, 1, 3);
INSERT INTO inventory_locations VALUES (104, 1, 2, 1);
INSERT INTO inventory_locations VALUES (105, 1, 2, 2);
INSERT INTO inventory_locations VALUES (106, 1, 2, 3);
INSERT INTO inventory_locations VALUES (107, 2, 1, 1);
INSERT INTO inventory_locations VALUES (108, 2, 1, 2);
INSERT INTO products VALUES ('P500', 'HP LaserJet 6L');
INSERT INTO products VALUES ('P510', 'HP DeskJet 855');
INSERT INTO products VALUES ('P520', 'IBM Aptiva');
INSERT INTO products VALUES ('P530', 'Compaq Presario');
INSERT INTO inventory VALUES (101, 'P500', 5);
INSERT INTO inventory VALUES (102, 'P510', 10);
INSERT INTO inventory VALUES (103, 'P500', 10);
INSERT INTO inventory VALUES (104, 'P520', 1);
INSERT INTO inventory VALUES (105, 'P530', 5);
The following query shows the current state of the inventory:
SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid
AND i.productid = p.productid;
CREATE VIEW vinventory AS
SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid AND i.productid = p.productid;
To see the current state of the inventory, simply
query the view:
SELECT * FROM vinventory;
SET SERVEROUTPUT ON
SET ARRAYSIZE 2
The following Oracle PL/SQL implements the add_to_inventory
procedure:
CREATE OR REPLACE PROCEDURE add_to_inventory (
new_locationid IN NUMBER,
new_productid IN VARCHAR,
new_quantity IN NUMBER) AS
current_quantity NUMBER;
BEGIN
current_quantity := 0;
-- See if some quantity exists at the current location
-- If not, then raise EXCEPTION and insert a new record
-- If so, then continue on to the UPDATE statement
SELECT quantity
INTO current_quantity
FROM inventory
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
-- If we get this far, then there must already exist
-- an inventory record with this locationid and productid
-- So update the inventory by adding the new quantity.
IF (current_quantity > 0) THEN
UPDATE inventory
SET quantity = quantity + new_quantity
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
END IF;
-- If the first SELECT statement above fails to return any
-- records at all, then the NO_DATA_FOUND exception will be
-- signalled. The following code reacts to this exception.
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
-- Since an inventory record mathcing the locationid and
-- productid can not be found, we must INSERT a new
-- inventory record.
INSERT INTO inventory
(locationid, productid, quantity)
VALUES (new_locationid, new_productid, new_quantity);
END;
END;
EXECUTE add_to_inventory(106, 'P500', 10)
EXECUTE remove_from_inventory(106, 'P500', 10)
7. Acknowledgements
Adam, N. R., Holowczak, R. H., Maeng, J-H.,
ORACLE SQL*Plus and SQL*Forms: An Introduction and Tutorial
Technical Working Paper 89, CRAMTD
Project, Rutgers University.
September, 1994
File: sqlplus.html Last Update: Sat Apr 7 21:41:05 EDT 2001