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.
button, Programs, Oracle for Windows 95 and then
SQL*Plus. The SQL*Plus login screen will appear
after roughly 15 seconds.
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 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.
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-89 and SQL-92. Over the years, each vendor of relational databases has introduced new commands to extend their particular implementation of SQL. Oracle's implementation of the SQL language conforms to the basic SQL-92 standard and adds some additional commands and capabilities.
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-54', '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 empid = 101 ;
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 adding a new column to an existing table:
| 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 ; |
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 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.
The column definitions for a table can be
displayed using the DESCRIBE command
in SQL*Plus:
More detailed metadata can be retrieved from
the tables COL and
user_constraints.
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:
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
Finally, a list of Views the user owns can be displayed by querying the USER_VIEWS table:
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
The following table lists the major pseudo columns:
YYYY is a 4 digit year.NM 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.
TO_CHAR (number, format) - Converts
a numeric column to a string of characters. format
is a set of number formatting codes where:
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.
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, a GROUP BY clause must be added to the SELECT statement.
Examples of functions are given in the following section.
Example Table STUDENTS:
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
SELECT name, gpa FROM students WHERE gpa = ( SELECT MAX(gpa) FROM students ); NAME GPA -------- ---------- Tom 3.5
Another option is to enclose some text in quotes and concatenate that text with the output of the SQL statement:
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
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.):
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
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.
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:
In MS Access:
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
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.
SELECT name, grade FROM students WHERE grade = ( SELECT MAX(grade) FROM students );
This assumes the subquery returns only one
tuple as a result.
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
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.
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
Show departments with 3 or more employees:
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
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YY')
AS Sentence
FROM employee;
SENTENCE
----------------------------------------
The oldest employee was born on 10/11/27
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.
DELETE employee;
DELETE employee WHERE salary > 50000;
DELETE employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dlocation = 'HOUSTON');
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;
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.
SQL> SELECT tname, tabtype
2 FRO
3 tab;
FRO
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
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.
SQL> 2 FROM SQL> RUN TNAME TABTYPE ------------------------------ ------- MACHINE TABLE EMPLOYEE TABLE
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.
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
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.
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
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:
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
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
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.
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 a:
drive), then the START command can be used
as follows:
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 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
- Chang