In this hands-on assignment you will be expected to learn:

a. How to interactively query a database by using SQL statements and SQL*PLUS commands.

b. How to use SQL statement to implement relational operations such as SELECT, PROJECT, PRODUCT, UNION, INTERSECT, MINUS (Difference), DIVISIION, JOIN, OUTER JOIN(s), RECURSIVE QUERY, and others.

c. How to use an editor (either pico, vi, or others) within SQL*PLUS to edit query statements.

d. How to use spool command to save an interactive session within SQL*PLUS.

Before you start with your hands-on assignment, you may need to

1. Print and read the document, "INTRODUCTION TO ORACLE/SQL*PLUS," which is available from Blackboard:

From reading the document, you are expected

a. To get familiar with the syntax of the SQL statements, the relationship between the SQL statement syntax and the semantics, the correspondences between a query in English and a query in SQL.

b. To know the set of SQL commands (SQL commands and SQL statements are different).

c. To know how to spool a file.

2. Study the Instruction to Written Assignment #1, by going through the SCIS online orientation, you are expected to get familiar with the following:

a. How to use SSH from your local PC to get connected on SCIS Unix server in order to use the Oracle server. (You need to download SSH Client and install on your local PC before you connect to SCIS Unix, please revisit SCIS Online Orientation for the detail.)
b. How to use FTP to transfer files from SCIS Unix server to your local PC.
c. How to use a small set of commonly used Unix commands.

In order to complete the assignment:

1. Please do hands-on practice of all the SQL query examples (in SQL statements) in the document (from page 1 to 43). Again please read the tutorial document first before doing it.

3. After you go through all the examples of SQL statements in the document (Not the SQL*PLUS commands), you need to use the SQL*PLUS command spool to save all the session information into a file and submit the file on Blackboard.

4. To enter SQL*PLUS system, you need to do two following things:

a. login on scis

b. login on ORACLE SQL*PLUS by using the following command:

usercode@scis> oracle11g at the prompt, please enter the same usercode as the one you used for your SCIS Unix, NSU email, or Blackboard, and enter the password: mcis6301@oracle11g.

(Please see the document "INTRODUCTION TO ORACLE/SQL*PLUS.")

5. The spool command in SQL*PLUS keeps track of all the information during the session. Please make sure that you have gone through the tutorial successfully before you generate the script.

SQL> spool filename.lst (start the spooling process)

SQL> spool off (please make sure you quit spool)

SQL> exit (quit from ORACLE/SQL*PLUS)

a.) SQL> is the prompt of ORACLE/SQL*PLUS.

b.) All the query statements you enter during the session will be saved (spooled) in the file with the name filename.lst.

6. To change the password on SQL*PLUS, please type the command at the SQL> prompt:

password

7. To upload file from SCIS Unix to your desktop, for PC (MS Windows) users, select New File Transfer under Window menu in SSH, and drag the spooling file to your PC desktop;

8. Please note the difference between SQL statements and SQL commands.

Attachment:- database.zip