CSI 3317 LAB 1 Introduction to Oracle Introduction

CSI 3317 (LAB 1) Introduction to Oracle

Introduction to Oracle…… In SITE Labs Programs Database apps Oracle – ORAHOME 92 Application Development SQL*PLUS

Introduction to Oracle……

Introduction to Oracle…… n Client / Server Database

Data Types VARCHAR 2 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.

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

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

Data Types Continued…. . Other Data Types LONG Up to 2 GB Variable length character RAW and LONG RAW Binary data

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

Oracle SQL*PLUS To Create Table use CREATE TABLE <table name> ( <field name> <data type> CONSTRAINT, <field name> <data type> CONSTRAINT); To delete Table use DROPTABLE <table name>;

Oracle SQL*PLUS……. DROP TABLE location; CREATE TABLE LOCATION (locid NUMBER(5) CONSTRAINT location_locid_pk PRIMARY KEY, bldg_code VARCHAR 2(10) CONSTRAINT location_bldg_code_nn NOT NULL, room VARCHAR 2(6) CONSTRAINT location_room_nn NOT NULL, capacity NUMBER(5) CONSTRAINT location_capacity_nn NOT NULL); Notice that the every constraint has a name.

Oracle SQL*PLUS……. To view the table specifications DESCRIBE <table name>; To rename the table RENAME <oldtablename> <newtablename>; To add Additional Fields ALTER TABLE <table name> ADD <field name> <data declaration> <constraints>; To modify existing Fields ALTER TABLE <table name> MODIFY <field name> <new data declaration>;

Oracle SQL*PLUS……. 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);

Oracle SQL*PLUS……. Remember… we can we view this data with SELECT Statement. SELECT * from LOCATION;

Oracle Navigator n Start Programs Database apps Oracle – ORAHOME 92 Application Development Oracle Navigator

Oracle Navigator …… n Right-click on Database Connections New n Use the connection info similar to one used for SQL*PLUS introduction.

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