homework help on oracle

1
Homework Help On Oracle
2
What is Oracle ?

• Oracle is a name of a organization which has
  developed many software, Operating Systems and
  Databases.
• Here we are going to discuss about Oracle
  Database or Oracle DB.
• Oracle database is a relational database
  management system in which data are treated as a
  unit. It is the most trusted and widely-used
  relational database engines.
• The system of oracle database is built around a
  relational database framework in which data
  objects may be directly accessed by users through
  SQL (Structured Query Language).

3
History of Oracle

• 1979 Oracle Release 2
• 1986 client/server relational database
• 1989 Oracle 6
• 1997 Oracle 8 (object relational)
• 1999 Oracle 8i (Java Virtual Machine)
• 2000 Oracle Application Server
• 2001 Oracle 9i database server
• 2004 Oracle 10g is released (the g stands for
  Grid).
• 2007 Oracle 11g is released.
• 2013 Oracle 12C is released which is capable of
  providing cloud services with Oracle Database.

4
Oracle Family

• Personal Oracle- for single users. Used to
  develop systems
• Oracle Standard Edition- (Entry level Workgroup
  server)
• Oracle Enterprise edition- Extended
  functionality
• Oracle Lite- (Oracle mobile) single users using
  wireless devices.

5
Developer Tools

• Oracle Forms Developer
• Oracle Reports Developer
• Oracle Jdeveloper
• Oracle Designer
• OEPE , Oracle Enterprise Pack for Eclipse.

6
Oracle Architecture
D000
SMON
RECO
S000
PMON
P000


Total SGA Size
1700

Mbyte


Fixed Size
SGA

70
Kbyte

Variavle
Size

490
MByte
TL-812
KByte
KByte
KByte
KByte
4,000,000
KByte
2,100
1,200,000
LGWR
CKPT
DBW0
ARCH
Archive Log Mode(50M)
7
Memory Structure Shared Pool
Shared Pool
Shared Pool Contents - Text of the SQL or
PL/SQL statement - Parsed form of the SQL or
PL/SQL statement - Execution plan for the SQL
or PL/SQL statements - Data dictionary
cache containing rows of data dictionary
information Library Cache - shared SQL area
- private SQL area - PL/SQL procedures and
package - control structures lock and library
cache handles Dictionary Cache - names of all
tables and views in the database - names and
datatypes of columns in database tables -
privileges of all Oracle users SHARED_POOL_SIZE

Library Cache
Dictionary Cache
Control Structures for example Character
Set Conversion Memory Network Security
Attributes and so on ..
Reusable Runtime Memory
8
TableSpaces

• A database is divided into logical storage units
  called Tablespaces.
• logical construct for arranging different types
  of data.
• An Oracle database must have at least a system
  tablespace.
• It is recommended to have different tablespaces
  for user and system data.

9
Tablespaces

• a logical structure

Data1
Data2
Data1_01.dbf
Data2_01.dbf
Data2_02.dbf
The DATA1 Tablespace One datafile
The DATA2 Tablespace Two datafiles
10
Create Tablespace

• CREATE TABLESPACE test
• DATAFILE '\oraserv\ORADATA\a.dbf'
• SIZE 10M
• AUTOEXTEND ON
• NEXT 10M MAXSIZE 100M
• CREATE TABLE cust(id int,name varchar2(20))
• TABLESPACE test

11
SQL(Structured Query Language)

• SQL is a standard language for accessing
  databases.
• Both an ANSI and ISO standard
• Types of commands
• Data Definition Language (DDL) Create, Alter,
  Drop, Rename, Truncate
• Data Manipulation Language (DML) Insert, Delete,
  Update
• Data Retrieval Select
• Transaction Control Commit, Rollback, Savepoint
• Data Control Language (DCL) Grant, Revoke

12
STUDENT
SID Name Age
10 Mark 18
20 Smith 22
30 John 19
40 Lee 21
COURSE
TAKES
Course ID Department
1 Commerce
2 Computers
3 Commerce
4 Accountant
5 Computers
SID Course ID
1 5
2 2
3 1
4 5
13
Data Definition Language

• CREATE TABLE table
• ( column datatype DEFAULT expr
• column_constraint ...
  table_constraint
  
• , column datatype DEFAULT expr
• column_constraint ...
• )
• ALTER TABLE table
• ADDMODIFY column datatype DEFAULT expr
  column_constraint
  
• DROP drop_clause
• DROP TABLE table cascade constraints
• DESC table

14
Example

• CREATE TABLE Students
• (sid INTEGER,
• name VARCHAR(20),
• age INTEGER)
• CREATE TABLE Courses
• ( courseid CHAR(6),
• department CHAR(20))
• CREATE TABLE takes
• ( sid CHAR(9),
• courseid CHAR(6))

15
Data Manipulation Language

• INSERT INTO table view (column , column
  ...)
• VALUES (expr,expr ...)
  
• UPDATE table view
• SET (column , column expr
• WHERE condition
• DELETE FROM table view WHERE condition

16
Example

• INSERT INTO Students VALUES (10,Mark,18)
• INSERT INTO Students VALUES (20,smith,22)
• INSERT INTO Students VALUES (30,John,19)
• INSERT INTO Students VALUES (40,Lee,21)

17
Data Retrieval

• SELECT DISTINCT ALL tableview
• FROM table view
• WHERE condition
  
• GROUP BY expr , expr
• ORDER BY expr ASC DESC

18
Example

• select from student
• select name from student where sid'10'
• select courseid from course where
  departmentcomputer
• Select a.sid from takes a, student b where
  a.sidb.sid

19
Transaction Control

• COMMIT
• ROLLBACK to savepoint
• SAVEPOINT name
• commit
• savepoint point5
• rollback to point5

20
Data Control Language

• GRANT privileges
• ON object TO userpublic
• WITH GRANT OPTION
• REVOKE privileges
• ON object TO userpublic
• CASCADE CONSTRAINTS
• grant select,update on student to XYZ
• revoke update on student to XYZ

21
For Further Informatiom

• Read more about oracle Database
• http//www.urgenthomework.com/oracle-10g-and-11g.p
  hp