Name: Section:

Notes for Oracle

Oracle was developed by Larry Ellison, Bob Miner and Ed Oates

First name of Oracle Corporation is Software Development Laboratories

The purpose of a database is to store and retrieve related information. A database server is the key to
solving the problems of information management.

• I – means INTERNET

• G – means GRID

• C – means CLOUD

Versions of Oracle Database

Grid Architecture

• Grid computing is a new IT architecture that produces more resilient and lower cost enterprise
information systems. With grid computing, groups of independent, modular hardware and
software components can be connected and rejoined on demand to meet the changing needs of
businesses

Benefits of Grid Architecture

• Higher Quality of Service

1|Page Notes for Oracle

• Lower cost

• Greater flexibility

• Higher quality of service results from having no single point of failure

• a robust security infrastructure, and centralized, policy-driven management

• Lower costs derive from increasing the utilization of resources and dramatically reducing
management and maintenance costs.

• Rather than dedicating a stack of software and hardware to a specific task, all resources are
pooled and allocated on demand, thus eliminating underutilized capacity and redundant
capabilities.

Grid computing also enables the use of smaller individual hardware components, thus reducing the cost
of each individual component and providing more flexibility to devote resources in accordance with
changing needs.

2|Page Notes for Oracle

Common Data Types

• CHAR(length)

• The CHAR data type stores character strings of a fixed length. The length parameter
(inside the parentheses) specifies the length of the character string.

• VARCHAR2

• The VARCHAR2 data type stores character strings of a variable length. The length
parameter (inside the parentheses) specifies the maximum length of the string.

• DATE

• The DATE data type stores dates and times. It stores also all the four digits of the year,
the month, the day, the hour in 24-hour format, the minute, and even the second.

• Integer

• The INTEGER data type stores integers (whole numbers).

• Number

• The NUMBER data type stores floating point numbers (numbers with decimal point).
The precision parameter is the maximum number of digits that maybe used for the
number, while the scale parameter is the maximum number of digits to the right of the
decimal point.

Five Simple Rules in Selecting Data Types

These simple rules will guide us how to select appropriate data types to use in our columns. By following
these rules, we can conserve more storage space and increase the speed in accessing our data in the
database.

Rule Number 1. Use the smallest possible sizes. The smaller the column size, the lesser the amount of
data that Oracle Database has to store and process, thus the faster it can read and write. Moreover, the
narrower column can be sorted or indexed faster.

Rule Number 2: If we are going to use a column for frequent sorts, let us use an integer-based column
such as INTEGER data type rather than a character-based column such as the CHAR and VARCHAR2 data
types. It’s because Oracle Database sorts integer data faster than character data. Tip: To sort or index
means to arrange data.

Rule Number 3: We must use the smallest possible data type for a column that will hold our data. For
instance, if we are going to store numbers from 1 to 99 in a column, we should use INTEGER.

Rule Number 4: If we are going to store numeric data in our column, it is advisable to use a numeric data
types such as INTEGER or NUMBER, instead of using CHAR or VARCHAR2. It’s because numeric data

3|Page Notes for Oracle

types generally require less space to hold numeric values compared to character data types. This will
save storage space, and smaller columns greatly enhance the performance during searching, sorting or
joining other columns.

Rule Number 5: We should not use NUMBER data type in defining our primary keys. Instead, we can use
the INTEGER data types.

How to create a simple database

Explanation:

This Name1 table has only one column called Name. Its data type is VARCHAR2 and its respective
maximum length is 15 characters.

How to Create a Database with multiple columns.

4|Page Notes for Oracle

Explanation:

This CREATE TABLE query created a table called Employee1 with four columns in the HR database. These
four columns are: EmployeeNumber, Name, Salary, and Address. The data type of EmployeeNumber is
NUMBER, while the data type of Salary is NUMBER (6,2) which means 12 numeric characters wide and
only 2 numeric characters are displayed after the decimal point. The data type of both Name and
Address is VARCHAR2(variable-length character) and their maximum length characters are 15 and 20,
respectively.

SQL’s Data Manipulation Language (DML)

The DML is the SQL construct and commands used to manipulate the data in the table. The common
DML commands are INSERT, UPDATE, DELETE, and SELECT.

Commands What it does?

INSERT Append (add) rows of data into a table.
UPDATE Edit (modify or change) data in a table.

DELETE Erase rows of data from a table.

SELECT Retrieve rows of data from a table.

Inputting Data into Tables

There are several ways to input data into our tables using Oracle Database. We apply the SQL
commands called INSERT INTO…VALUES and INSERT INTO…SELECT. We will learn them here in action.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT column1, column2, ...

FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to
select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

5|Page Notes for Oracle

Example: This code will retrieve ALL data from the EMPLOYEE1 database.

Result:

How to Use INSERT

The INSERT INTO…VALUES option needs the column list and all the columns in the correct order.

Format:

INSERT INTO TableName VALUES (‘char_attrib_val’, ’num_attrib_val’);

How to INSERT with 1 column table

Here is the explanation of each part of the SQL script that we have executed, lately:

INSERT is the SQL command to insert or input data

6|Page Notes for Oracle

INTO is a necessary SQL keyword Name1 is the name of the existing table

VALUES is another necessary SQL keyword

‘Juan Cruise’ is the string of letters that correspond to the VARCHAR2 data type

INSERTING DATA INTO MULTIPLE COLUMNS

INSERT INTO Employee1 VALUES (1001, 'Juan Cruise', 20500, 'Project 8, Q.C.');

Note: To insert a row in the table, we should be very careful to match the column for column. In other
words, the correct data should be in order as it is entered. Like for example, the first data is an
Employee number and should be an integer data type, the next data is the Name and should be a
character data. This rule also applies to the remaining columns.

Tip: Append means to add - in the Database term.

The SQL UPDATE Command

The SQL UPDATE command is used to set or change data values in a table. We usually change or set data
in more than one row. Here is the general syntax of SQL UPDATE:

UPDATE TableName SET fieldname1, fielname2, fieldnamen…

Example

7|Page Notes for Oracle

This command will set the salary of all employee to 0;

Example

This command will set the salary of the employee who has the employee number 3 from the current
salary to 17000.

Result:

8|Page Notes for Oracle

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name
WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement.
The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records
in the table will be deleted!

Example :

Before the Delete Statement

DELETE FROM EMPLOYEE1

WHERE LASTNAME='CARDO';
After we run the SQL statement

We can see that the row where the last name is CARDO was removed.

9|Page Notes for Oracle