CMSC4003

Lab Assignment 1: SQL*PLUS

Name:

Due: See the due date in D2L calendar.

  1. Preparation

(a) Oracle Documentation

Documentation of Oracle products can be accessed at: Go to the Web site, click on “Documentation & APIs”under “Essential Links”on the left side of the webpage. Click on “Database” under “Documentation”and then click on “Database 12c Release 1”. Click on “View Oracle Database, 12c Release 1 (12.1)” under “Oracle Database 12c Documentation”.

You have reached the webpage for “Oracle Database DocumentationLibrary” ( If you click on the“Master Book List” link on the upper-left corner of the page, you can see all the online books for Oracle 12c technologies. You can find the “SQL Language Reference” and “PL/SQL Language Reference”books to check the syntax of SQL and PL/SQL statements directly.

You may be asked for username and password when you try to access the documents. You may sign up for free by yourself. You can also use the following account: cse480/cse480

The link to SQL*Plus reference is at:

  1. Connect to Oracle Server

(a) Use your CS Server account to connect to cs2.uco.edu through SSH (e.g., PuTTY).

(b) Execute: sqlplus

(c)Login using your Oracle account (same as your CS account).

  1. Run SQL commands. Note that Oracle is case-insensitive. Also note that all SQL statements need to end with a semicolon (;).

(a) create table STUDENT (

sid number(7),

sname varchar2(30),

sdno number(5));

A table named STUDENT is created in the database. Guess what is the meaning of “number(7)” in the statement.

Answer:

(b) desc STUDENT

“desc” is an SQL*Plus command. Therefore, it does not need to end with a semicolon. Explain the function of the SQL*Plus command “desc”.

Answer:

Note that this is a quite useful command to check the definition (schema) of a table.

(c) insert into STUDENT values (30, 'John Doe', 20);

commit;

Insert a tuple into the table. Note the order of the values in the parentheses. It must correspond to the order of the columns in the table definition.

“commit” is an SQL command that makes the insertion of the tuple to be permanent in the table. Without committing, the tuple will not be visible by other users/session. It is related to transaction processing that will be discussed in future lectures. At this point, you only need to remember that after you insert/delete/update the content of some tables, type commit to make the changes permanent.

Now try:

insert into STUDENT values ('MarkSmith', 20, 10);

Write down what happened. Why?

Answer:

(d) select * from STUDENT;

Display all the columns of all the rows in the table STUDENT.

(e) insert into STUDENT values (100, 'Mark Smith', 10);

commit;

select * from STUDENT;

How many rows are there in the table?

Answer:

(f) delete from STUDENT where sid = 30;

commit;

select * from STUDENT;

Delete statement: it deletes a row whose sid is 30 from the table. Note that delete is usually used with a “where” clause to specify which rows to delete. A delete statement without a “where” clause will delete ALL the tuples in a table.

(g)select table_name from user_tables;

This select statement returns only the table_name column from the table user_tables. User_tables is a system table, which is part of Oracle data dictionary. It is automatically maintained by DBMS. It contains tuples for every table created by the user. Since user_tables has many columns (check it with desc user_tables), we only display its table_name column.

(h) drop table STUDENT;

Remove the table STUDENT. Note that all the tuples within the table will be removed. Use it with care!

  1. Running SQL from a file

Instead of using interactive command lines as above, you can also run commands from a file. You can use an editor, such as vi or nano, to create the file with SQL statements and then execute the file in SQL*Plusas follows:

@filename.sql

Note that the default file extension is “.sql”. Therefore if you name your file with the “.sql” extension, you can omit the extension name in the command:

@filename

The created file can be put in the same directory where you launch SQL*Plus. If you place the file at a different directory, path may be included as part of the file name.

In this part, you create a file named lab1.sql that contains all the above commands and then run it in SQL*Plus. Leave the file in your account for grading purpose.

Submission: Submit an electronic copy of your answers to the questions in this lab using the D2L email. The electronic copy can be this MS Word document with your answers typed in it.

Final Note: SQL*Plus is a quite inconvenient command editor, even though it provides a rich set of edit commands. My recommendation is that even if you work in interactive command line mode, you can open a Notepad and type your command in it first. You always edit your command in Notepad and then copy and paste it into SQL*Plus to run the command. It will save you time.

1