Once you are in SQL*Plus, you need to copy the tables available for your use into your tablespace by following the instructions below. If you are not familiar with line editing, see Error Correction in SQL*Plus. When you want to quit working and leave SQL*PLUS, type quit.

NOTE: Throughout this document capital letters will be used when issuing either an ORACLE command or an SQL*Plus command. ORACLE SQL is not case sensitive and this convention has only been used for convenience and clarification. Case matters only when SQL*Plus or ORACLE is checking a string value for equality in the database. For example, 'SMITH' is not equal to 'Smith'.

Items shown inside <> are required. Items inside square brackets [] are optional. When three periods (… an ellipsis) follow an expression, it indicates that the previous expression may be repeated as many times as needed.

Line numbers are supplied by the ORACLE system every time the return key is pressed. For ease of reading, line numbers are left out of this document. When you issue these commands yourself, you will see them appear on your screen.

To create a table, use the SQL CREATE TABLE command. The basic format for that is:

The above example is for one column definition. To define more columns separate the column definitions with commas. Column names must be unique within the table and can be up to 30 characters in length. All table names and column names must start with a letter but may also include numbers and underscores within their names.

The [not null] is optional; columns can be null or not null. If you do not specify 'NOT NULL' the default is 'NULL' and ORACLE will allow the column to contain a null value.

Notice the semicolon at the end of the command. It is called a command terminator and is required at the end of most SQL*Plus queries.

The data types used in our sample tables are NUMBER and CHAR.

CHAR(n) data types can be up to 255 characters in length in ORACLE V7.2. The number in parentheses following the CHAR data type name specifies that the data is of fixed length, with maximum length n.

Columns defined as NUMBER can be up to 40 digits, plus space for a decimal point and sign. Numbers may be expressed in two ways: first, with the numbers 0 to 9, the signs + or -, and a decimal point(.); second, in scientific notation, such as l.85E3 for 1850. When a NUMBER is defined as NUMBER(s,d), the number is of maximum length s with d digits after the decimal. In the salary column of the table below, the number definition is specified as NUMBER(8,2). This means that the total length of the field is equal to 8 and that 2 positions will be to the right of the decimal.

The primary key of a table consists of the column(s) used to uniquely identify a row of information in the table. The primary key column(s) must be not null. The primary key for the EMPLOYEE table will be the EMPNO field.

The following illustration is a description of how a table is defined in ORACLE. To view the table definition for the employee table, use the SQL*Plus DESCRIBE command as follows:

You should see the following description of the employee table.

Name Null? Type

------------------------------- -------- ----

EMPNO NOT NULL NUMBER(4)

NAME NOT NULL CHAR(8)

JOB CHAR(4)

SALARY NOT NULL NUMBER(8,2)

COMM NUMBER(8,2)

DEPTNO NOT NULL NUMBER(4)

SEX CHAR(1)

Notice the absence of the semi-colon in the above command? Because DESCRIBE is a SQL*Plus command rather than an SQL Language command it is not needed here, but it doesn't hurt to use it.

Use the following command to create the EMPLOYEE table within your tablespace. At the SQL prompt type in the command; returning after each line is not necessary but does promote readability. SQL will automatically number your statement lines starting with line 2. When you have finished typing in the last line including the ';' then hit return and the table will be created.

A 'table created' message should come back to you. You will be the owner of this table and you are the only person (other than a DBA) who can access the table.

To confirm the creation of the table, type

The table definition should be identical to the one above. If you identified a primary key during table creation, it does not appear in the table description.

Use the following SQL command to copy rows into your new table:

SQL> INSERT INTO EMPLOYEE ç your table name

SELECT * ç selecting all the rows

FROM IS331.EMPLOYEE; ç from the table available to you

You should receive a message confirming the creation of your rows. To test that your table has been populated properly, type

SQL> SELECT *

You should have the following 12 rows of data in the table:

Follow the procedure below to copy the DEPT and JOBS tables from the IS331 tablespace into your tablespace. Set the primary key in DEPT to DEPTNO and the primary key in JOBS to JOB.

For you to try: 

1. Get the description of the table by typing

2. Create an identical table in your tablespace.

3. Copy the contents of the original table into your table using the INSERT command.

Last revised on 12/4/97 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to Oracle/SQL Tutorial Contents Page | Return to Previous Section | Go to SQL Exercise 2 - Selection