26 July 2002
 
                Starting SQLPLUS and logging on
                -------------------------------
 
NOTE: No submission required.  This lab is only to get you familiar
with using ORACLE.  Can be done in groups of 2 (NOT more).

The following oracle specific environment variables have
to be added to your .profile if you have not already added.
 
       TERM=vt100
       ORACLE_TERM=vt100
       ORACLE_HOME=/oracle
       ORACLE_SID=GEN
       PATH= $ORACLE_HOME/bin:/usr/ccs/bin:$PATH
       export PATH TERM ORACLE_TERM ORACLE_HOME ORACLE_SID
 
 Invoke sqlplus as follows
 
       $ sqlplus
 
 Again enter your oracle username and password at the respective
 prompts and press Enter key. 
 

Your password is same as your login name. 
*IMPORTANT* Change your password by giving following command

	SQL> password; 

(or alter user oracle_login_id identified by new_passwd;)

 SQL*Plus displays the version of ORACLE to which you connected and the
 versions of available tools . Then it displays the SQLPlus command
 prompt as follows.
 
   SQL>
 
 			Introduction to SQLPLUS
 			-----------------------

Creating tables :
-----------------

CREATE TABLE students ( rollno CHARACTER(8), name VARCHAR(30), 
		hostel INTEGER, cpi NUMERIC(3,2) );

Insert  data :
--------------

   SQL> INSERT INTO students VALUES ( '99305017', 'Sai Sundar', 11, 7.23);
   SQL> INSERT INTO students VALUES ( '99305018', 'Shyam Sundar', 11, 9.23);
   SQL> INSERT INTO students VALUES ( '99305019', 'Ram Sundar', 11, 8.23);


Running queries :
-----------------

   SQL> SELECT * FROM students;
   SQL> SELECT rollno FROM students;
   SQL> SELECT rollno, name FROM students WHERE cpi > 9.0;


Create more tables of your own, insert data, and run some queries
on single tables.  

Updates/deletes:
----------------
   SQL> UPDATE students SET cpi = 9.46 WHERE rollno = '99305018';

   SQL> DELETE students WHERE rollno = '99305018';

Runnings batch of queries :
-------------------------

- Create a file having extension as .sql.
- Write quries in the file. Queries should with a semicolon
- Execute 
      SQL> start <filename>

Getting help :
------------

SQL> help [topic]

Listing all the tables :
-----------------------

SQL> SELECT * FROM cat;

For saving output in a file :
---------------------------

	SQL> SPOOL filename

Output of any command after this will be stored in the "filename".lst

Multitable queries:
-------------------

CREATE TABLE course ( courseno CHARACTER(8), coursename VARCHAR(30));
CREATE TABLE registered ( rollno CHARACTER(8), courseno CHARACTER(8));

... insert appropriate data into each table

Query to find all course numbers with students from Hostel 11

   SELECT courseno 
   FROM students, registered 
   WHERE  students.rollno = registered.rollno  AND
          students.hostel = 11

(Course numbers may appear multiple times: use SELECT DISTINCT instead
of SELECT to get them only once.)

And try some more queries on your own:
   -- course numbers with some students with cpi < 6
   -- rollnumbers of all students registered for course CS317
And try more on your own. 

You can try more queries from the DB Concepts book or other books,
after defining appropriate tables, if you want (not essential!).