Starting Oracle

To use Oracle under UNIX, some preparations have to be taken care off.

1. Open the file ~/.tcshrc.
   
2. Search for a list of wi_packages. An example of such a list is: set wi_packages=(newtex netscape oracle)
   
3. Add oracle.
4. Make sure you have a username and a password. You can get a password by subscribing at the first course or by sending an email to csdb@liacs.nl.

Oracle can be launched by typing the following command in a terminal:

sqlplus username@ONW

Your username is your UNIX account name. The following message appears:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 9 16:19:17 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password:

Enter your password. By default, your password equals your username. Next, the SQL> prompt appears. Just like a UNIX command prompt, you can use this prompt to give commands. Enter the following command immediately to change your password:

alter user username identified by newpassword;

Pay attention to the semicolon at the end of the line.

Additional instructions for Den Haag

Additional to the instructions on the WEBPAGE for Den Haag I&E

Using SQL in the LIACS system via PUTTY SSH

• Open putty.exe: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
• Login on "ssh.liacs.nl" or "silver.liacs.nl"
• Follow the normal instructions

Editing files in remote locations

• Install winscp: http://winscp.net/eng/download.php
• Login with your liacs account
• Edit the files through this interface

Introduction to the commands

We introduce some commands here which you may find useful during the next exercises. We strongly encourage you to try each of them. Currently, you don't have to understand the meaning of all statements. It is our intention that you get a feeling for the system.

• Creating a table

  CREATE TABLE BranchOffice ( id CHAR(20), address CHAR(20) );

  This is an SQL-DDL statement which creates a table in the database. Here you define the columns of a table.
  
  
• Inserting tuples into a table

  INSERT INTO BranchOffice VALUES ( 10, 'Niels Bohrweg 1' );

  This will add a row to the table. As you see, a string is surrounded by single quotes (') and NOT by double quotes (").
  
  
• Viewing the contents of a table

  SELECT * FROM BranchOffice;

  Also try this:

  select * from BRANCHOFFICE;

  As you see here, capitals don't matter in ORACLE. You can use the notation that you like most, as long as you are consequent. It should be clear to us that you understand the difference between a command (SELECT) and a table (BranchOffice). Finally, also try:

  SELECT *
FROM BranchOffice;

  You can distribute your commands over multiple lines. The query has to end with a dot-comma. We prefer queries that are split up in multiple lines.
  
  
• Managing your tables
  Once you have created some tables, you may want to get an overview of all the tables that you created, similar to the UNIX `ls' command or the DOS `dir' command. In ORACLE everything is stored in a table, also the overview of all your tables. To view the names of all your tables, type:

  SELECT * FROM tab;

  As you see here, there is a special predefined table `tab' which stores all table names. If you want to see more information about a particular table, you can use the command DESCRIBE, for example:

  DESCRIBE BranchOffice;

  
  
• Removing tables
  To remove your table use:

  DROP TABLE BranchOffice;

  
  
• Exiting
  To exit Oracle's SQL*Plus, type:

  EXIT

  
  
• Commands in a file
  For your exercise, you have to deliver a file with all your commands. A nice feature of ORACLE is to execute commands that are stored in a text file. Let's put the CREATE command in a textfile with the name "createtable.sql", and save this file. Take care of the .sql suffix, which is absolutely necessary. Run sqlplus again. Now type as command:

  @createtable

  As you can see, the commands in the file are executed.