Transcript IFSM 420 - LAB

IFSM 420 - LAB
ORACLE 8
SPRING 2002
LAB1:OBJECTIVE
 COURSE INFORMATION & Materials
 ACCESS TO ORACLE ACCOUNT
 SQL* PLUS ENVIRONMENT
 ORACLE DB & ACCESSING Data
Dictionary (DD)
LAB 1
 Introduction
 COURSE INFORMATION
– Log on to the system (GL’s username and
password)
– Go to the blackboard via myUMBC
– Get Enrolled to the course (IFSM420)
– Course information, rules, etc.
– Assignments
ORACLE-RDBMS
 ORACLE 8
– Server and Client Software: NT server and
WIN 95/98/NT clients
– DBA, Developer, User
– Oracle products: SQL Plus 3.3 or 8.0;
Developer 2000
– Info at www.oracle.com: manuals, free
software, etc.
ACCESS TO ORACLE
 Accessing Oracle
– IFSM’S server (account)
– GL server (account)
 IFSM’S ORACLE ACCOUNT
– Start->Programs->SQL Plus
• Log on (dialog box)
– USERNAME, PASSWORD & Host String
• Host string: oracle.ifsm.umbc.edu
– Access to the server with various privileges is created by DBA
=> SQL prompt;
 Changing Password!!!
 Exiting SQL*Plus
SQL* PLUS ENVIRONMENT
 Oracle SQL*Plus program window
– Oracle SQL*Plus Environment
– Editing and Debugging SQL commands
• Online Editing / commands
• Using Alternative Text Editor
– Using Online Help (Look at ORA.HLP at
C:\ORANT\HELP; or menu item)
– SPOOLING
ORACLE DB & ACCESSING Data
Dictionary (DD)
 Data Dictionary or Meta-data
•
•
•
•
•
Data Dictionary (DD): stores all of the information that is used
to manage the objects in the database, and is a central source of
information for the ORACLE RDBMS itself and for all users
of ORACLE. It is automatically maintained.
desc DICT
desc DICT_COLUMNS
column Comments format a50
select count(table_name) from dict_columns;
select table_name from dict_columns where table_name LIKE
'USER%';
Catalogue: USER_CONSTRAINTS, UER_TABLES (TABS), etc.
• desc user_constraints;
 More
LAB 2: Objectives
 ASSIGNMENT 1: Page 57 & 58 no. 4, 5
and 11
 DDL
–
–
–
–
Creating tables, constraints
Viewing tables, constraints definitions
Deleting tables, constraints
Inserting data values into tables
CREATE TABLE
 EXAMPLE (Case Study One, page 13- on the
text):
CREATE TABLE customer
(custid NUMBER(5) CONSTRAINT customer_custid_pk PRIMARY
KEY,
last VARCHAR2(30) CONSTRAINT customer_last_nn NOT NULL,
first VARCHAR2(30),
mi CHAR(1),
cadd VARCHAR2(30),
city VARCHAR2(30),
state CHAR(2),
zip VARCHAR2(10),
dphone VARCHAR2(10),
ephone VARCHAR2(10));
CREATE TABLE (Contd.)
CREATE TABLE cust_order
(orderid NUMBER(8) CONSTRAINT cust_order_orderid_pk
PRIMARY KEY,
orderdate DATE CONSTRAINT cust_order_orderdate_nn
NOT NULL,
methpmt VARCHAR2(10) CONSTRAINT
cust_order_methpmt_nn NOT NULL,
custid NUMBER(5) CONSTRAINT cust_order_custid_fk
REFERENCES customer(custid),
ordersource VARCHAR2(20) [CONSTRAINT
cust_order_ordersource_fk
REFERENCES ordersource(ordersource)]);
OR: CONSTRAINT cust_order_orderid_pk PRIMARY KEY
(ORDERID)
VIEW TABLE structure (Contd.)
 EXCERSIE: CREATE the ITEM table (5 minutes)
 Note:
– Naming conventions of tables, fields, constraints
– Order of tables creation/dependences
– !! NAMES ARE IN UPPERCASE
 View the tables’ attributes/structure: name,
columns’ properties, associated constraints
(SYSTEM DATABASE TABLES in DD)
–
For detail DD info. on each table use:
• DESCRIBE/DESC table_name;
– DESC CUSTOMER;
– USER_TABLES, USER_CONSTRAINTS
• SELECT table_name FROM user_tables;
• SELECT constraint_name FROM user_constraints [where
table_name=‘CUSTOMER’];
Lab 3/4: DDL & DML
 Objectives
– How to write an SQL script and run
– How to use Date Data Type & Format masks
• Input
• Output
–
–
–
–
INSERT Data into tables
Modify/delete data rows/records from tables
Undo modification: ROLLBACK and SAVEPOINT
Modifying Database Table (see the NothWoods
university database script on blackboard)
• Altering/Changing tables’ columns, constraints
SQL Script
 Exercise 1: Write and execute the script for
creating the database tables of the
Clearwater Traders Sales Order Database
(see page 12-)
 Spool On/OFF!!!!!
 Steps
• save the “clearwat.sql” script in your working
folder referred as a PATH (S:\, S:\IFSM420 or …).
• At the SQL prompt:
– START
Path\clearwat.sql;
Input and Output Mask
 Example with Date type
 Internally Oracle Date={century(cc), year, month,
day, hour, minute, and second}
 Default:
– DD-MON-YY for Date; and HH:MI:SS A.M. for Time
for Display/Output
– No format for Input => must be specified
 TO_CHAR( date, ‘format’) – reformats Oracle
date according to the format (in Output)
 TO_DATE(string, ‘format’) converts a string in a
given format into an ORACLE date.
Input and Output Mask (Contd.)
 Examples:
select To_Date('12/10/99','dd/mm/yyyy') from
DUAL;
select To_Date('12/10/99','mm/dd/yyyy') from
DUAL;
INSERT INTO cust_order VALUES
(1061, TO_DATE('06/01/2001', 'MM/DD/YYYY'),
'CC', 179, 'WEBSITE');
DUAL – is Oracle work table with only one row and
one column in it. Used to demonstrate functions with
literals or pseudo-columns
Input and Output Mask (Contd.)
Examples:
Select SysDate from DUAL;
Select User, 2000 * 567 from DUAL;
Select User, 2000 * 567 as product from DUAL;
select orderdate from cust_order;
Select To_CHAR(orderdate,'yyyy-month-dd') from
cust_order;
SELECT TO_CHAR(CUSTID,'00999') FROM
CUSTOMER;
LAB 4/5: Objectives
 Discussion on Homework #1
 DML
– Modify/delete data rows/records from tables
– Undo modification: ROLLBACK and SAVEPOINT
– Modifying Database Table (see the NothWoods
university database script on blackboard)
•
Altering/Changing tables’ columns, constraints
 Class Exercise: Page 76, questions 4, 5, 8 and 9
 Class Exercise: Page 104, questions 10 and 11
DML: Update/Delete/Rollback
Class exercises:
1. Do exercises 4 and 5 on page 76
2. Run the NothWoods University database
script available on the blackboard
3. Do exercises 8 and 9 on page 76.
DDL: MODIFY DB TABLE structure
 Allowed not allowed! (page 52 )
 For Examples refer to
NorthWoods University database script
Class Exercise: Problem Solving Cases, (page
58-59, question 2) a to d.
MODIFY TABLE structure(Cont
SELECT constraint_name FROM user_constraints where
table_name='CUSTOMER';
SELECT constraint_name FROM user_constraints where
table_name='cust_order‘;
? ALTER TABLE cust_order
MODIFY (ordersource VARCHAR2(20) CONSTRAINT
cust_order_ordersource_fk
REFERENCES ordersource(ordersource));
(see PAGE 52-55)
? ALTER TABLE cust_order
ADD CONSTRAINT cust_order_ordersource_fk
REFERENCES ordersource(ordersource);
DROP Command
 TABLE, SEQUENCE
DROP TABLE cust_order;
DROP SEQUENCE cust_order_sequence;
 CONSTRAINTS
ALTER TABLE table_name DROP CONSTRAINT
constraint_name;
e.g.:
ALTER TABLE customer DROP CONSTRAINT
customer_custid_pk;
ALTER TABLE cust_order DROP CONSTRAINT
cust_order_ordersource_fk;
QUERY
 Class Exercise:
– On Page 103-104, review questions 2, 3,4, 6, 10
& 11, explain what the queries perform and run
for the results.
– Page 103 review question 4
– Page 104-105 problem solving cases: 15, 17, 19
and 23
Homework #2
 Attempt all questions. Write the SQL query, run,
spool to a file. Submit the printed or soft copy of
the spool file. Handwritten is not accepted. Write
your full name, section number, etc. Each has 2
point.
 Questions:
– Page 104-105 problem solving cases: 18, 20, 21, 25 and
26
 Due Date:
– 4th of March for Section 201
– 6th of March for Section 202 and 203
Lab 6: QUERY & Forms
 Objective
– Sequences
– Join Multiple Tables
– Union, Intersect or Minus
– View
– Developer 2000
• Query Builder
• Form Designer
• Report Designer
Lab 6: QUERY
 Class Exercises
– Sequences: Practice examples on page 127-132
– Problem solving cases on Page 149; questions
1,3,6 and 7.
 Homework #3:
– Problem solving cases on Page 150; questions
11,12,14,15 and 16.
– Due date: 03/11/02 for Section 201; 03/13/02
for sections 202 and 203
Forms-Developer 2000
 Form Builder
 Query Builder
 Report Builder
Lab 7: Query and Form
 Objective
– Discussions on Assignment #2
– Exercises on Form
– Page 336 question 2
– How to use Triggers
– Add the navigational buttons for the
previous form (on Page 336 question 2)
Lab 8: Form
 Using Query Builder
 Revision on Developer Form Builder
– Object Navigator & Online Help
• Two views: Ownership and Visual View
• Visual View: Canvas, Frame, Window
• Creating, Modifying and Deleting objects
– Layout Editor (LE)
• LE tool palette
• Selecting, moving, resizing, aligning, grouping, ordering
objects
• Formatting Objects
– Using Views and Sequences
• First Create at SQL Plus
Lab 8
 Using Format Masks
 Using Trigger
– With Sequences
page 338-347
– With Buttons
– With List of Value (LOV)
 LOV