RDBMS using

Oracle
2+1
Relational Database Management System
Using Oracle

E-mail: kamran@niit.edu.pk

RDBMS Using Oracle

This course will cover….

n Oracle RDBMS Environment

n Advance Structured Query language (SQL)
n Procedural Language / Structured Query language (PL/SQL)
n Oracle Developer Forms (GUI)
n Oracle Developer Reports (GUI)

n Oracle database Administration

1
 SQL

PL/SQL

Oracle Graph &

Oracle Form Oracle Report
Procedure
Builder Builder
Builder

SQL/PLSQL
n Data Types & Oracle RDBMS limits
n Getting information, Adding, Updating & Deleting
information
n SQL Arithmetic, Group, Character and Date
functions
n Creating and using views
n Creating Index
n Functions
n Procedures
n Implicit and Explicit Cursors
n Triggers
n etc.

2
 Front-end Development
n Oracle Forms/Reports Development
• Creating front-
front -end forms and menus
• Working with form objects
• Creating buttons, text items, images, blocks etc
• Creating reports and graphs

After completion of this course you will be able to

develop complete database applications
using Oracle database and Oracle development Suit
by writing SQL and PL/SQL reusable code

3
Books

n OCA/OCP Introduction to Oracle 9i SQL

Study Guide Exam IZ0-
IZ0-007
by Chip Dawes, Biju Thomas

n Oracle 9i PL/SQL
A Developer’s Guide
By Bulusu Lakshman

n Oracle 9i Development by Example

By Dan Hatka

Reference Books

n Introduction to Oracle: SQL and PLSQL

n Build Internet Applications I by Oracle Press
n Build Internet Applications II by Oracle Press
n Oracle Reports by Oracle Press
n Oracle 8i/9i by Oracle press
(OCP track student guide)

4
Lets begin………

Installation

5
 Oracle 8/8i

Windows NT/2000/xp

n Oracle 8 for NT or Oracle8i ver

ver.. (8.1.5)
n Developer 6i

Free space required (approx. 1.2 GB)

Install Oracle and developer by giving different home

names and at different locations.

n e.g for ORACLE Home Name à ora_home

For Developer 6i Home Name à dev_home

6
 Oracle 9i
n Oracle 9i Database
n Oracle 9i Development Suite

Free space required (approx. 3 GB)

Min RAM required is 512Mb

Recommended for Personal Computers

Oracle 8/8i ver 8.1.5 or higher and Developer 6i
or
Oracle 8/8i Database and Oracle 9i Development suit

Oracle Database Architecture

Oracle Server
Instance

User Server Background Shared

User Processes Global Area
Process Process

SQL*Plus, SQL, PL/SQL

VB, …
Database

Tables, Indexes, Data Files System Files

Constraints,
Triggers, …

7
 SQL
In Oracle
RDBMS

PL/SQL

Oracle Graph &

Oracle Form Oracle Report
Procedure
Builder Builder Builder

Relational Database Management

System
Using Oracle

Lecture 1
1. Overview of the Oracle RDBMS
2. Overview of SQL* Plus
3. Data Types in Oracle SQL Plus
4. Oracle RDBMS limits
5. Getting information from a table
6. Selecting limited rows/columns
7. Comparison Operators

8
 SQL queries

n What is query?

Answer can be….

“Query is a request towards the

database in order to retrieve
information from tables”

SQL
Query

User

Query Analyzer

Output Database

9
 Database

Oracle RDBMS Limits

n Tables No Limit
n Rows No Limit
n Columns in a table 254
n Characters in a character field 240
n Number Field 105
n Tables joined in a query No limit
n Level of nested sub queries 255
n Characters in a name 30

10
 SQL
n SQL (Structural Query Language) is a language which
is used to manipulate data in any RDBMS.

n SQL is not case sensitive.

SQL Plus
SQL Plus is a program for working with an ORACLE
database by using SQL language.

v Create tables in a database

v Store , change & retrieve information in/form the table
v Perform calculations

11
 SQL commands categories

n SQL commands are divided into three

main categories

• Data Definition Language (DDL)

• Data Manipulation Language (DML)
• Transaction Control Commands (TCC)

Data definition language (DDL commands)

Commands that are performed on a table or user

v Create, Alter, Drop (Related to tables)

v Grant, Revoke (For Permissions e.g.
read, write etc..)

Data Manipulation Language (DML commands)

Commands that are performed on table data
v SELECT (Select data in rows and columns from
one or more tables.)
v DELETE (Remove rows from a table.)
v INSERT (Add new rows to a table.)
v UPDATE (Change data in a table.)

Transaction Control Commands

v Commit & Rollback à to save or undo changes

12
 ORACLE Sample tables
n EMP
n Bonus
n Dept orcl
n Salgrade

Connect Oracle SQL

user name: scott
Password: tiger
Database string: orcl

Scott is the default user of ORACLE

In you home computer use null or
your computer name as Host String.

13
 The system table “tab”

n tab: It’s the table of tables, i.e. stores

the names of all the tables created by
the user

n select * from tab;

• lists all the tables created

SELECT statement

Display all data from a table

SQL> Select * from <tablename
<tablename>;
>;
SQL> Select * from emp
emp;;

SQL> Select * from emp ;

• Displays data stored in emp table

14
 SELECT statement

Display specified data from a table

SQL> Select <column-

<column-name> from <table-
<table-name>;

e.g.
SQL> select empno from emp
emp;;

(Here empno is a column in Emp table)

SELECT statement

More Examples
SQL> select ename from emp
emp;;

SQL> select * from dept;

SQL> select deptno from dept;

15
 Table description command

SQL> desc <table

<table--name>

We use this command to display the structure

(columns & data-
data -types) of specified table.
e.g

SQL> desc emp ; (where emp is the table name)

SQL> desc dept;
………..

The WHERE Clause

WHERE clause is used when we want to
select few records from a table
(based on condition/criteria).

Suppose we have a CAR table

CarNo Color Table CAR

--------------
2344 RED CarNo Number(4)
3445 BLACK Color char(15)
3467 RED
9878 BLUE

16
 CAR table
CarNo Color
2344 RED
3445 BLACK
3467 RED
9878 BLUE
Suppose we want to select RED cars only.

For this we will write this query

SQL> select * from cars where Color = ‘RED’;

1- What will be the query if we want to

select car whose number is 2344?

2- Select car-
car-numbers from car table
whose color is RED?

Comparison Operators
n = Equal to
n ! = or <> Not equal to
n > Grater then
n >= Grater then equal to
n < less then
n <= less then equal to
n BETWEEN …AND… between two values
n IN (list) any of a list of values
n LIKE Match a character
pattern

17
 = (Equal to)

n SQL> select * from emp where sal = 800;

Display records with salary is equal to 800

! = or <> Not equal to

n SQL> select * from emp where sal <> 800;

n SQL> select * from emp where sal != 800;

Display records with salary is not equal to 800

18
 > Grater then

n SQL> select * from emp where sal > 800;

Display records with salary is grater then 800

< less then

n SQL> select * from emp where sal < 800;

Display records with salary is less then 800

19
 >= Grater then equal to

n SQL> select * from emp where sal >= 800;

Display records with salary >= 800

BETWEEN …AND

n SQL> select * from emp

where sal between 200 and 800;

Display records where salary is from 200 to 800

NOTE:
Records with salary 200 and 800 are also included.

20
 IN (list) any of a list of values

n select * from emp

where sal in (200,800,600);

Display records where salary is either

200 or 800 or 600

LIKE match a character pattern

n select * from emp where ename like ‘_dam’;

Display records where ename column has

“dam” at the end and there is one character
at the beginning of ename.

n select * from emp where ename like ‘A%’;

Display records where ename begins with ‘A’.

21
 and ……. or
n SQL> select * from emp
where sal = 800 or sal = 600

Display records where salary is either 600 to 800

n SQL> select * from emp

where sal is 800 and ename like ‘A%’;

Display records where salary is equal to 800 and

ename begins with ‘A’

Lab Practice

22
 Practice Following SQL Queries
PERFORM THE FOLLOWING QUERIES:

Part 1

n List all rows of the table emp

emp..
n Display the structure of the table emp
emp..
n Display the structure of the table dept.
n List all employees name from table emp
emp..
n List all jobs from emp table.
n List all salaries from emp table.
n List all commission, mgr from emp table.

Part 2

n Display records where salary is either 600 or 800.

n Display records where ename column has “dam” at

the end and there is one character missing at the
beginning of ename
ename..

n Display records where salary is either 200 or 800 or

600

n Write a SQL statement which selects list of

employees whose salary is between 200 to 800 or
their name ends with ‘m’.

n Write a SQL statement which selects names of the

employees whose salary is either 200 or 800 or the
character ‘d’ comes in their name.

23
 Part 3
n List all departments number from table emp
emp..

n List all employees number, employees name, jobs,

mgr from table emp
emp..

n List all employees number, employees name, jobs,

mgr, salaries from table emp
emp..

n List all employees number, employee name, jobs,

mgr, salaries, hiredate from emp table.

n Write a SQL statement which selects list of

employees whose salary is either 200 or 800 (By
using in operator) and/or their name starts with ‘A’.

Be Happy J

24