CSI 3317 (LAB 1)

1
CSI 3317 (LAB 1)

• Introduction to Oracle

2
Introduction to Oracle
In SITE Labs
Programs ? Database apps ? Oracle ORAHOME 92 ?
Application Development ? SQLPLUS
3
Introduction to Oracle
4
Introduction to Oracle
5
Introduction to Oracle

• Client / Server Database

6
Data Types
VARCHAR2 Variable length character data Max. 2000
characters Field size must be specified on
declaration If entered data is smaller than
specified size only entered data is
stored. Trailing blank spaces are
omitted. CHAR Fixed length character data. Max.
255 characters. If no size specification size
only one character. If size specified and the
data entered is smaller trailing spaces added.
7
Data Types Continue.
NUMBER Negative, Positive, fixed and floating
point numbers. Range 10-130 to 10126 Precision
up to 38 decimal points. Integer Number e.g sid
NUMBER(5) Can store up to 99,999 Fixed-Point
Number e.g price NUMBER(5,2) Can store up to
999.99 Floating-Point Number e.g gpa
NUMBER Notice No precision or scale Can store
2.7 as well as 3.725
8
Data Types Continued..

• DATE
• -From January 1, 4712 BC to December 31, 4712 AD
• -Stores
• Century
• Year
• Month
• Day
• Hour
• Minute
• Second
• -Default (DD-MON-YY)
• Use Mask to change.
• e.g dob DATE

9
Data Types Continued..
Other Data Types
LONG Up to 2GB Variable length character RAW and
LONG RAW Binary data
10
RDBMS
Oracle is a hybrid of Relational and object
oriented databases For Our LAB we will only
touch Relational Properties
Remember your SQL operations -create (to create
a new object or table for our lab
purpose) -select (view) -update (change) -insert
11
Oracle SQLPLUS
To Create Table use CREATE TABLE lttable namegt (
ltfield namegt ltdata typegt CONSTRAINT, ltfield
namegt ltdata typegt CONSTRAINT) To delete Table
use DROPTABLE lttable namegt
12
Oracle SQLPLUS.
DROP TABLE location CREATE TABLE
LOCATION (locid NUMBER(5) CONSTRAINT
location_locid_pk PRIMARY KEY, bldg_code
VARCHAR2(10) CONSTRAINT location_bldg_code_nn NOT
NULL, room VARCHAR2(6) CONSTRAINT
location_room_nn NOT NULL, capacity NUMBER(5)
CONSTRAINT location_capacity_nn NOT NULL)
Notice that the every constraint has a name.
13
Oracle SQLPLUS.
To view the table specifications DESCRIBE lttable
namegt To rename the table RENAME
ltoldtablenamegt ltnewtablenamegt To add Additional
Fields ALTER TABLE lttable namegt ADD ltfield namegt
ltdata declarationgt ltconstraintsgt To modify
existing Fields ALTER TABLE lttable namegt MODIFY
ltfield namegt ltnew data declarationgt
14
Oracle SQLPLUS.
Now lets populate the table INSERT INTO location
VALUES (53, 'BUS', '424', 1) INSERT INTO
location VALUES (54, 'BUS', '402', 1) INSERT
INTO location VALUES (45, 'CR', '101', 150)
15
Oracle SQLPLUS.
Remember we can we view this data with SELECT
Statement. SELECT from LOCATION
16
Oracle Navigator

• Start ? Programs ? Database apps ? Oracle
  ORAHOME 92 ? Application Development ?Oracle
  Navigator

17
Oracle Navigator

• Right-click on Database Connections ? New
• Use the connection info similar to one used for
  SQLPLUS introduction.

18
Oracle Navigator

• Click on Plus sign besides your Connection on
  left pane.
• Right-click on Table ? New
• Now use the wizard to create the same table.
  (Remember to Drop table before creating it again)