Very basics on DB access

1
Very basics on DB access

• Elke A. Rundensteiner

2
DBMS?

• Oracle
• Accounts already created
• Documentation http//www.oracle.com
• mySQL
• To create an account, visit http//www.wpi.edu/Aca
  demics/CCC
• Documentation http//www.mysql.com
• Many other DBMS engines out there

3
Oracle Version at WPI

• The oracle server SID is WPI11GR2 (or, CS)
• Version 11g 11.2.0.3.0 (64 bit version)
• System name is oracle.wpi.edu
• port is 1521
• Problems user names cannot have a . in them.

4
How to set up path for Oracle

• If you use CCC linux systems, then you need to
  source /usr/local/bin/csoraenv file to setup your
  shell to use oracle
• On CCC, if your shell is set to /bin/tcsh, then
  run
• source /usr/local/bin/csoraenv
• On CCC, if your shell is set to /bin/bash, then
  run
• source /usr/local/bin/oraenv
• To figure out what the type of shell that you
  use, type echo SHELL
• From your shell prompt, type
• echo PATH

5
How to set up Oracle

• Or, set environment variables by adding the
  needed settings into your .cshrc file on the
  ccc.wpi.edu machine (which gets sourced
  automatically when you log on).
• setenv ORACLE_BASE /usr/local/oracle11gr203
• setenv ORACLE_HOME /usr/local/oracle11gr203/produc
  t/11.2.0/db_1
• setenv PATH PATHORACLE_HOME/bin
• setenv ORACLE_SID WPI11GR2
• setenv TWO_TASK ORACLE_SID

6
How to set up Oracle

• Or, set environment variables by adding the
  needed settings into your .cshrc file on the
  ccc.wpi.edu machine (which gets sourced
  automatically when you log on).
• setenv ORACLE_BASE /usr/local/oracle11gr203
• setenv ORACLE_HOME /usr/local/oracle11gr203/produc
  t/11.2.0/db_1
• setenv PATH PATHORACLE_HOME/bin
• setenv ORACLE_SID WPI11GR2
• setenv TWO_TASK ORACLE_SID

7
How to set up Oracle

• If in tcsh or csh shell, your .cshrc file would
  be
• setenv ORACLE_BASE /usr/local/oracle11gr203
• setenv ORACLE_HOME
• ORACLE_BASE/product/11.2.0/db_1
• setenv PATH PATHORACLE_HOME/bin
• setenv ORACLE_SID WPI11GR2
• setenv TWO_TASK WPI11GR2
• setenv LD_LIBRARY_PATH ORACLE_HOME/lib

8
How to set up Oracle

• If in bash shell, your .cshrc file would
  be
• setenv ORACLE_BASE/usr/local/oracle11gr203
• setenv
• ORACLE_HOMEORACLE_BASE/product/11.2.0/db_1
• setenv PATHPATHORACLE_HOME/bin
• setenv ORACLE_SIDWPI11GR2
• setenv TWO_TASKWPI11GR2
• setenv LD_LIBRARY_PATHORACLE_HOME/lib

9
How to set up Oracle (contd)

• If your path is empty, then add one additional
  line, as below, on top of your .cshrc file
• setenv PATH .
• The rest is as on previous slide

10
How to set up Oracle

• After editing the file .cshrc
• Please run below or log out and back in
• source .cshrc
• Or, log out and back in.

11
Problems while setting up Oracle

• Important Set up Oracle immediately and see
  that it is working
• you may run into problems, typically due to
  simple typos or minor setup issues
• If you have identified a project partner, start
  working with him/her on this to help each other
• If you get really stuck, please send a message to
  mywpi to show a screen-dump of what happens.

12
Oracle introduction

• Connecting
• sqlplus ltuserNamegt/ltPASSWORDgt
• Change passwd using password command
• You will end up submitting your passwd
• therefore dont use password that you use for
  other purposes.

13
Oracle useful commands

• These commands can be executed from SQL shell
• SELECT FROM cat -- lists tables you have
  created
• SELECT table_name FROM user_tables -- as above.
• DESCRIBE lttableNamegt -- describes schema for
  table with name tableName
• help index -- shows list of help topics
• help start -- illustrates how to use command
  start
• exit -- exit from SQL shell

14
Using Oracle from Windows

• Multiple ways
• Use aquastudio software from aquafold.com.
  connect to --
• server oracle.wpi.edu
• port 1521 (this is the default)
• SID WPI11GR2
• Download oracle client for windows. Connect using
  sqlplus client or other tools
• sqlplus rundenst/rundenst_at_//oracle.wpi.edu1521/c
  s.wpi.edu

15
Working with the Data Server
16
Basic SQL Commands

• CREATE TABLE student(sNum INTEGER,sName VARCHAR
  (30))
  -- creates table student with two columns
• INSERT INTO student VALUES (1, Joe)
  -- insert one row into the student
  table
• SELECT FROM student
  -- select all rows from student table
• DELETE FROM student
  -- delete all rows in the student table
• DROP TABLE student
  -- drop student table
• Purge recyclebin
  -- purge recyclebin tables that get created.
• -- Only works if you are logged onto CCC1

17
Saving your Interactive Session in SQLPlus

• If you want to save your output to a file (for
  homework)
• spool ltfileNamegt
• ltexecuteCmds to create tables ...gt
• spool off