SQL*Plus

This tutorial will introduce you to SQL and Oracle, teach you how to logon to Oracle at UBC, and show you how to use SQL*Plus, an interactive SQL command interpreter. SQL*Plus will be useful to you when you are doing your homework, as you will often need to see the changes that your program will make to your database. Let us begin with a brief description of SQL and Oracle.
 

SQL/Oracle

Oracle, on the other hand, is a database management system (DBMS). Users interact with a relational DBMS primarily through SQL. Without a DBMS, it would be very difficult to efficiently organize, store, secure, maintain, and query data, especially when supporting many concurrent users. Thus, a DBMS is a collection of programs to facilitate all of these functions (and more).

Besides Oracle, there are a number of different relational database management systems in the industry, including IBM's DB2, Microsoft's SQL Server, Sybase, Informix, and others. Microsoft's Access is another relational database system, but it is for smaller-sized applications, and is meant to be used by one user at a time.

Oracle comes with a number of products, a few of them being:
 

• SQL*Plus

An interactive front-end program for entering SQL statements and Oracle commands. You will be using this for your homework.

• SQL*Graph

A graphing utility which uses results based on SELECT statements

• SQL*Calc

A spreadsheet, compatible with Lotus 1-2-3, which holds results of SELECT statements

• Easy*SQL

A utility for inexperienced SQL users who do not know SQL syntax

• SQL*Forms

A form generating utility for use with SQL applications

The most useful tool for our purposes would probably be SQL*Plus, which is an interactive SQL command interpreter. With SQL*Plus, you can enter an SQL statement at the terminal and immediately see the results of the command. We will teach you how to use SQL*Plus in this tutorial. You will need to use this product to see the results of changes you have made to your database in your assignments.
 

Logging In

Unlike most UNIX applications, Oracle requires each user to have an Oracle account, complete with a userid and password. You have been assigned an Oracle account userid and password.  Your Oracle account is not the same as your UNIX account. 

If your Unix account is  a1b2  and your student number is 12345678  then:

•  your oracle account is  ora_a1b2   (note the 'ora_' before the ugrad account )
•  and your  password is  a12345678    (note the 'a' before the student number) .

To log into the "ug" (undergrad) database with SQL*Plus, type the following at the prompt:
 

> sqlplus <userid>@ug

where <userid> represents your Oracle login ID.  You will be asked to enter your password.

Note: if you cannot log in and the system complains that it cannot find the Oracle server, it means that some of your environment variables are not set properly. Set these variables  first by typing:

export ORACLE_SID=ug
export ORACLE_HOME=/home/o/oracle

and then try again. If the problem persist you should email  help&ugrad.cs.ubc.ca  and explain the problem (include the lab and machine you use and the error message).

In UBC's undergrad environment, only two connections to Oracle are allowed per user per session. Ideally, one would use one connection to run JDBC (more about JDBC in later tutorials), and another to run SQL*Plus. Therefore, if you are having problems logging in, check first that you have at most one other connection to Oracle running.

Once Sql*Plus is started, you will see the prompt:

SQL>

SQL*Plus

The other distinction between SQL*Plus commands and SQL statements is that in SQL*Plus, an SQL statement can span multiple lines, and needs to be followed by a semicolon, whereas an SQL*Plus command does not need to be followed by a semicolon, and can only span multiple lines if each line is ended with the "-" character.

We are now ready to create some tables, since we cannot do anything else until we define our database. (A database consists of one or more related tables, and possibly many other objects.) We will first create the branch relation. Type the following at the SQL prompt, being sure to press carriage return after each line:

SQL> create table branch
   2 ( branch_id integer not null PRIMARY KEY,
   3 branch_name varchar(20) not null,
   4 branch_addr varchar(50),
   5 branch_city varchar(20) not null,
   6 branch_phone integer );

For a complete discussion of datatypes in Oracle, refer to the Datatypes section in the CS304 Oracle reference.

The create table statement above creates the relation branch in our database.  You can check whether or not the branch table has been created by typing at the prompt:

SQL> select table_name from user_tables;

The branch table should be listed.  You might be wondering what user_tables is.  User_tables is a view in your database's data dictionary that contains a description of all your tables.  Oracle stores information on each user database in a data dictionary, which just consists of tables and views.

To view the schema for the branch relation, type at the prompt:

SQL> describe branch

which outputs the following:

 Name                            Null?    Type
 ------------------------------- -------- ------------
 BRANCH_ID                       NOT NULL NUMBER(38)
 BRANCH_NAME                     NOT NULL VARCHAR2(20)
 BRANCH_ADDR                              VARCHAR2(50)
 BRANCH_CITY                     NOT NULL VARCHAR2(20)
 BRANCH_PHONE                             NUMBER(38)

The SQL*Plus command, DESCRIBE <table_name> outputs the schema of a relation, showing the table name, attribute (also called field or column) names, whether or not the attribute can be left blank (i.e., NOT NULL means the field is required to have a value at all times), and the domain (datatype) for the attribute. By the way, either upper or lower case characters are acceptable for SQL*Plus input.

We can now insert a few tuples into the branch relation. Type at the prompt:

SQL> insert into branch values
   2 (10, 'Main', '1234 Main St.', 'Vancouver', 5551234);

SQL> insert into branch values
   2 (20, 'Richmond', '23 No. 3 Road', 'Richmond', 5552331);

To view the data we have just created, type at the prompt:

SQL> select * from branch;

You should get the following:

 BRANCH_ID BRANCH_NAME       BRANCH_ADDR                 BRANCH_CITY       BRANCH_PHONE
---------- ----------------- --------------------------- ----------------- ---------------
10         Main              1234 Main St.               Vancouver         5551234
20         Richmond          23 No.3 Road                Richmond          5552331

If you get multiple header lines in your output (lines containing the BRANCH_ID, BRANCH_NAME, etc. titles), then it might be that your pagesize is set too small. To change your pagesize, type at the prompt:

SQL> set pagesize 30

which should give you approximately 30 lines of output before the field name header gets repeated. You can experiment with this feature of SQL*Plus by changing the value 30 to any other value.

Now, to illustrate more complex operations such as table joins, we need more than just the branch relation. Instead of having you manually type in all the SQL statements to create the Motor Vehicles Branch (MVB) database, we can instead create a file with the corresponding SQL statements. Copy the following SQL statements into a new file called "something.sql".
 

drop table exam;
drop table license;
drop table branch;
drop table driver;

create table branch
( branch_id integer not null PRIMARY KEY,
branch_name varchar(20) not null,
branch_addr varchar(50),
branch_city varchar(20) not null,
branch_phone integer );

create table driver
( driver_sin integer not null PRIMARY KEY,
driver_name varchar(20) not null,
driver_addr varchar(50) not null,
driver_city varchar(20) not null,
driver_birthdate date not null,
driver_phone integer );

create table license
( license_no integer not null PRIMARY KEY,
driver_sin integer not null,
license_type char not null,
license_class integer,
license_expiry date not null,
issue_date date not null,
branch_id integer not null,
foreign key (driver_sin) references driver,
foreign key (branch_id) references branch );

create table exam
( driver_sin integer not null,
branch_id integer not null,
exam_date date not null,
exam_type char not null,
exam_score integer,
PRIMARY KEY (driver_sin, branch_id, exam_date),
foreign key (driver_sin) references driver,
foreign key (branch_id) references branch );

insert into branch values
( 10, 'Main', '1234 Main St.', 'Vancouver', 5551234 );

insert into branch values
( 20, 'Richmond', '23 No.3 Road', 'Richmond', 5552331 );

insert into branch values
( 30, 'West Creek', '251 Creek Rd.', 'Sechelt', 5552511 );

insert into branch values
( 40, 'Blenheim', '1342 W. 22 Ave.', 'Burnaby', 5551342 );

insert into driver values
( 111111111, 'Bob Smith', '111 E. 11 St.', 'Vancouver', TO_DATE('01-JAN-1975','DD-MM-YYYY'), 5551111 );

insert into driver values
( 222222222, 'John Walters', '222 E. 22 St.', 'Burnaby', TO_DATE('02-FEB-1976','DD-MM-YYYY'), 5552222 );

insert into driver values
( 333333333, 'Troy Rops', '333 W. 33 Ave.', 'Richmond', TO_DATE('03-MAR-1970','DD-MM-YYYY'), 5553333 );

insert into driver values
( 444444444, 'Kevin Mark', '444 E. 4 Ave.', 'Vancouver', TO_DATE('04-APR-1974','DD-MM-YYYY'), 5554444 );

insert into exam values
( 111111111, 20, TO_DATE('02-DEC-1997','DD-MM-YYYY'), 'L', 97 );

insert into exam values
( 222222222, 30, TO_DATE('09-MAY-1996','DD-MM-YYYY'), 'L', 25 );

insert into exam values
( 222222222, 40, TO_DATE('10-JUN-1996','DD-MM-YYYY'), 'L', 51 );

insert into exam values
( 111111111, 20, TO_DATE('25-MAY-1997','DD-MM-YYYY'), 'D', 79 );

insert into exam values
( 333333333, 20, TO_DATE('27-JUN-1997','DD-MM-YYYY'), 'L', 49 );

insert into exam values
( 222222222, 40, TO_DATE('29-AUG-1996','DD-MM-YYYY'), 'D', 81 );

insert into exam values
( 333333333, 10, TO_DATE('07-JUL-1997','DD-MM-YYYY'), 'L', 45 );


insert into exam values
( 444444444, 10, TO_DATE('27-JUL-1997','DD-MM-YYYY'), 'L', 71 );

insert into exam values
( 444444444, 20, TO_DATE('30-AUG-1997','DD-MM-YYYY'), 'D', 65 );

insert into exam values
( 333333333, 20, TO_DATE('27-JUL-1997','DD-MM-YYYY'), 'L', 61 );

insert into license values
( 1, 111111111, 'D', 5, TO_DATE('25-MAY-1999','DD-MM-YYYY'), TO_DATE('25-MAY-1997','DD-MM-YYYY'), 20 );

insert into license values
( 2, 222222222, 'D', 5, TO_DATE('29-AUG-1998','DD-MM-YYYY'), TO_DATE('29-AUG-1996','DD-MM-YYYY'), 40 );

insert into license values
( 3, 333333333, 'L', 5, TO_DATE('27-DEC-1997','DD-MM-YYYY'), TO_DATE('27-JUN-1997','DD-MM-YYYY'), 20 );

insert into license values
( 4, 444444444, 'D', 5, TO_DATE('30-AUG-1999','DD-MM-YYYY'), TO_DATE('30-AUG-1997','DD-MM-YYYY'), 20  );

Side Note on Foreign Key Integrity Constraints

Oracle supports only the following actions for foreign key constraints:

• on update/on delete no action  (default)
• on delete cascade
• on delete set null

Now, before we run these SQL statements, we have to delete the branch table from our account (since we can only create a table if the table does not already exist). To do this, type:

SQL> drop table branch;

which will drop all tuples and schema of the branch relation.

Alternatively we can issue a drop statement just before we create a new table in the file.  This will remove any old table that may have the same name with the new table we want to create.

Now type:

SQL> start something

The SQL*Plus command "START <filename>" reads filename.sql, and processes all the SQL commmands in the file (assuming that they are valid).

The contents of the relations we just created can be viewed by typing in the SQL queries select * from <table-name> to view the tables.

Suppose now we want to see, for each driver who lives in Vancouver, their sin, name, license number, and license expiry date. We can type the following:

SQL> select driver.driver_sin, driver_name,
   2 license_no, license_expiry
   3 from driver, license
   4 where driver.driver_sin = license.driver_sin
   5 and driver_city = 'Vancouver';

which would give us:

DRIVER_SIN DRIVER_NAME          LICENSE_NO LICENSE_E
---------- -------------------- ---------- -----------
 111111111 Bob Smith                     1 25-MAY-1999
 444444444 Kevin Mark                    4 30-AUG-1999

Oracle ensures integrity by disallowing operations which violate integrity constraints. Suppose we wanted to delete Bob Smith's record from the driver relation. We should not be able to do this because Bob Smith has some records in the exam relation, as well as a record in the license relation. To test this with Oracle type:

SQL> delete driver where driver_name = 'Bob Smith';

You should get the following message from Oracle:

ERROR at line 1:
ORA-02292: integrity constraint (OPS$E1H1.SYS_C0053277) violated - child record
found

SQL> select * from driver, exam;

As you will learn from the lecture, if we do not set the equality constraints on the fields being joined in the WHERE clause, a statement like the one above results in a cartesian product (i.e., each row in one relation is concatenated with each row of the other relation). If your window is not outrageously large, then you will see that SQL*Plus cannot tell when there are too many lines to fit on the screen, so it continues to output answers until all you can see is the last few records. You can tell SQL*Plus to prompt you (at the end of each page) for the output by typing:

SQL> set pause "<Hit Return to Continue...>"
SQL> set pause on

If you rerun your previous query, you should now notice that the output pauses when it reaches the top of the page, at which time SQL will prompt you with the text that you entered at the SET PAUSE command. This is similar to UNIX's "more" command. Remember that you first have to type in the text for the prompt with the SET PAUSE command, and then set the pause to ON by typing SET PAUSE ON. You can set the pause back to OFF by typing SET PAUSE OFF.

Another useful feature that you can set is the amount of space between the columns in the output. set space <integer> will set integer spaces between each column. For example, type:

SQL> set space 5

and rerun your query. You should now notice the difference. (Reset spacing to 1 when done).

For a list of common SQL statements, see the List of Commands section in the CS304 Oracle reference.
 
 

SQL*Plus Commands

Re-executing Commands

SQL*Plus keeps the most recent SQL command in a command buffer. Type at the prompt:

SQL> /

You should see the results of the last SQL statement which you have typed (the current command in the command buffer). Now, type RUN at the prompt, and you should see both the SQL command and the results of the query.

Both the "/" and the "RUN" commands re-execute the last SQL statement, with the difference being that RUN displays the statement before executing it.
 

Changing Statements

SQL> select from driver;

Oracle will return you the error message:

ERROR at line 1:
ORA-00936: missing expression

SQL> change /from/* from/

The format change /something/something else/ replaces the text string something with the text string something else. The effect of the above change, as outputted by Oracle, is:

1* select * from driver
 

We can now execute the statement with the RUN command.

The above was only a simple example, since it would not take much effort to re-type a single line. However, for longer statements, which can span multiple lines, the CHANGE command can become very useful. Type at the prompt:

SQL> select driver.driver_sin, driver_name,
   2 license_no, branch_name, branch_city
   3 from driver, license, branch
   4 where driver.driver_sin = license.driver_sin and
   5 license.branch_id = branches.branch_id and
   6 license_type = 'D';

Oracle should return the following error statement:

ERROR at line 5:
ORA-00904: invalid column name

SQL> 5

This displays line 5 and makes line 5 current. We can now apply the correction. Type:

SQL> change /branches.branch_id/branch.branch_id/

Now apply the query by typing RUN at the prompt.
 
 

Saving

SQL> save somethingelse.sql

This saves the last statement into a file called somethingelse.sql.  To append the statement to an existing file, type save <filename> append.  If we wanted to examine the contents of the file, we can use the GET <filename> command. To actually run the file, type:

SQL> start somethingelse

or

SQL> @ somethingelse

to run the SQL statement.

To save the output of the current SQL*Plus session in a file, type:

SQL> spool <filename>

Everything that is displayed on the screen after this command is entered will be saved in the given file.  If you do not provide a file extension for the filename, a default extension (on most systems .LST or .LIS) will be added to the filename.

To stop writing to the file, type:

SQL> spool off

Other Useful Commands

SQL> host <host command>

For example,  typing host ls *.sql executes ls *.sql.

To edit the SQL statement in the command buffer using the host operating system's default text editor (for us, it will probably be emacs), type:

SQL> edit

You will see the '/' symbol at the end of the file; just leave it there.  Note: you cannot have more than one SQL statement in the file.  After you save the changes and exit the editor, the new SQL statement will be placed in the command buffer.  Use the / or run command to execute it.

Naming Rules

Database object names, such as column and table names, that are not enclosed with double quotation marks

• cannot contain quotation marks or spaces
• are not case sensitive
• must begin with an alphabetic character
• can contain only alphanumeric characters and the underscore (_) sign
• cannot be a reserved word (click here and here for a list of reserved words)

• can contain spaces and symbols
• are case sensitive
• can be a reserved word
• must be enclosed with double quotation marks whenever they are used

Text and character literals are enclosed with single quotation marks, and they are case sensitive.  For example, where branch_name = 'Main' is not the same as where branch_name = 'main'.  To specify an apostrophe use two single quotes e.g. 'Richard''s car'.

SQL statement key words, such as SELECT and CREATE, are not case sensitive.  For example, SELECT is the same as SeLeCt.

Summary

SQL*Plus
Last updated 18/07/2012