0% found this document useful (0 votes)
94 views21 pages

Oracle Is A Client/Server Relational Database

Oracle is a relational database management system with a client/server architecture, where the database server manages data storage and access. SQL is the standard language used to communicate with Oracle and other relational database servers to access, manipulate, and control data. SQL*Plus is an Oracle tool that allows users to interact with Oracle databases by issuing SQL and PL/SQL commands from a command-line interface.

Uploaded by

allextrastodo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
94 views21 pages

Oracle Is A Client/Server Relational Database

Oracle is a relational database management system with a client/server architecture, where the database server manages data storage and access. SQL is the standard language used to communicate with Oracle and other relational database servers to access, manipulate, and control data. SQL*Plus is an Oracle tool that allows users to interact with Oracle databases by issuing SQL and PL/SQL commands from a command-line interface.

Uploaded by

allextrastodo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 21

Oracle

What is Oracle?
Oracle is a Client/Server Relational Database
Management System.
Server: The database consists of physical storage
of data and the database services (such as
searching, sorting, retrieving, updating and
analyzing data). All data access occurs through
server.
Client is a software program that interacts with
the server; either requesting data (or) sending
data.
The communication between the Client and
Server: This is done through physical layer ( a
network) and a logical layer (data access
language i.e. SQL).
Application
Development Tools

SQL*Net Protocol
Client

Oracle RDBMS
Server

db1 db2
Oracle Products
Oracle Server, Personal Oracle, SQL*Plus,
Pro*Oracle, Developer (Forms, Graphics, Reports,
Procedure Builder), Designer, Discoverer, Oracle
Book, etc.,
Oracle Server:
It is a database server (or) back-end is used
to control
 Storage of data in designated database
areas
 Retrieval of data for applications
 Database Security, and the tasks permitted
for specified users.
 Consistency and Protection of data
 Communication and integrity of data where
databases are distributed across network.
SQL, PL/SQL and SQL*Plus
SQL is the language used to access a
relational data including Oracle.
PL/SQL is an Oracle’s Programming Language
for writing logic and for manipulating data.
SQL*Plus is an Oracle Product with which SQL
and PL/SQL languages can be used.
And also has its own language for controlling
the output of SQL queries.
In Simple, SQL and PL/SQL are languages
that are used in a number of Oracle Products.
SQL*Plus is just one of the product
supporting them.
Relational Databases
It is a collection of data (or) information
organized in two-dimensional matrix called as
“Tables” , consisting of columns and rows.
Before creating tables and other objects in
database, you must decide what tables you will
need to logically represent your data.
It should define:
 Tables and their names
 Column Names for each Table
 Key column for each table
 Relationships between tables
When designing th tables and relationships for a
database certain errors commonly occur.
A process called Normalization helps.
Normalization
Normalization is a process of refining tables,
keys, columns and relationships to create a
consistent database design.
Un-Normalized Form: It is a rough draft of a
data item(s).
First Normal Form:
 Separate repeating data items.
 Identify key data items.
Second Normal Form:
Each data item must depend on key
Third Normal Form:
Eliminate independent data items.
Database Objects
Tables, Data Types, Synonyms, Views, Snapshots,
Indexes, Sequences, etc.,
SQL
(Structure Query Language)
A relational database requires a language to
enable users to access data.
SQL is the language used to communicate with
server to access, manipulate, and control data.
The SQL language was developed by IBM in the
mid-1970s.
In 1979, Oracle corporation introduced the first
commercial implementation of SQL.
SQL is a non-procedural language: you specify
what information you want, not how to get it.
SQL is an English-like language.
SQL uses simple command set called as
Communication with a RDBMS
using SQL
SQL statement
is entered

SQL> SELECT
dname from DEPT;
Data
base
Data is displayed

DNAME
--------
Accounting
SQL Statements
SELECT
INSERT Data Manipulation Language
UPDATE (DML)
DELETE
CREATE
ALTER
DROP Data Definition Language (DDL)
RENAME
TRUNCATE
COMMIT
ROLLBACK Transaction Control Language(TCL)
SAVEPOINT
GRANT
REVOKE Data Control Language (DCL)
SQL*Plus
SQL*Plus is a software product from Oracle
Corporation that allows user to interactively use
the SQL commands, produce formatted reports
and support procedures to access Oracle
Database.
Logging on to SQL*Plus: To log in through a
Windows Environment :
Click Start  Programs  Oracle for Windows 
SQL*Plus 8.0 (or)
Double-click SQL*Plus icon on Desktop
Fill in username, password and host (where the
database is resided)
Once you are successfully logged in to SQL*Plus,
you will get SQL prompt.
Writing Commands
At SQL prompt you may enter SQL statements
(or) SQL*Plus commands.
Writing SQL Statements:
 SQL statements are not case sensitive
 SQL statements can be entered on one or more
lines.
 When you enter a SQL statement, it is stored in
a part of memory called the SQL Buffer, and
remains until you enter a new command.
 SQL statement terminates with semi-colon (;)
Writing SQL*Plus Commands:
 SQL*Plus commands are entered one line at a
time and are not stored in the SQL buffer.
EDIT command is used to invoke editor with
buffer contents to a file named afiedt.buf
Querying Tables
The SELECT statement retrieves information from
the database, implementing all operators of
relational algebra.
SELECT [DISTINCT] */ColumnName List
FROM tableName
[ORDER BY ColName/ColNumber/Alias [ASC/DESC]]
[WHERE condition]
[GROUP BY columnName]
[HAVING condition]

SELECT identifies what columns


FROM identifies which table
Column Heading Defaults:
Default display is UpperCase
Default justification is Left for date and chars
Right for Numeric
Tables Used in the Course
The following three tables will be used :
 EMP table, which gives details of all the
employees of a company
 DEPT table, which gives details of all the
departments of a company
 SALGRADE table, which gives details of salaries
for various grades.
Some Basic Queries:
# List the information of all employees
# List the information of all departments
# List the information of employee salary grades
# List all employee names along with salaries
# List all employee department number,
employee number and their manager number
# List department names with locations
Options used in the SELECT clause are
Arithmetic expression: + - * %
# List the name, salary and PF of all employees
# List all employee names and annual salary.
Column Aliases: You can change a column’s
headings by using an Alias (alternative heading)
Aliases are forced to display in Uppercase, unless
it in enclosed in “ “.
# List all employees, salary per annum as
ANNUMSAL.
Concatenation Operator (||) allows to be linked
to other columns.
# List ename and job as single column
Literal is any character which is not a column
# Display the output as SMITH WORKING AS
CLERK for all employees.
Preventing the Selection of
Duplicate rows
DISTINCT clause is used to eliminate duplicate
rows.
# List the different jobs available in emp table
# List the different departments in the emp table
# List the different departments along with jobs
Arranging Rows in an Order
ORDER BY clause is used to arrange the rows in
ascending or descending order
# List details of employees in order of names
# List the employee details in ascending order of
salary
# List the employee name, hiredate in descending
order of hiredate
# List employee name, salary, PF (10% of sal),
HRA(7% of sal), DA (5% of sal) and gross, order
the result in ascending order of gross.
# List the department number, job and employee
name in ascending order of department number and
salary in descending order.
Querying the desired (or)
required columns
WHERE clause is used to retrieve the rows based on
the condition.
The condition expects 3 elements :
1. A Column name
2. A comparison operator
3. A column name, constant or list of values
Comparison Operators:
= > >= < <= <>
Logical Operators are:
AND OR NOT
SQL Operators:
BETWEEN … AND IN LIKE IS NULL
# List the names, numbers, job and departments of
all clerks.
# List all managers number and names.
# List all department names with department
numbers greater than 20.
# List employees whose commission is greater than
their salary.
# List the employees whose salary is between 1000
and 2000.
# List the employees whose numbers are 7369,
7321, 7839, 7934, 7788.
# Display all clerks who earn between 1000 and
2000.
# List employee number, name, job, sal, deptno
whose salary is more than 1500 of Manager and
Salesman.
# List employee names who have joined before 30th
June 81 and after December 81.
Working with Null Values:
# List the employee names who are not eligible for
commission
# List the name of the employee and designation of
the employee who does not report to any body.
# List the employees who are eligible for
commission
Matching Pattern:
% represents a sequence of zero or characters
_ for a single character
# List all employees whose name starts with ‘S’
# List the employee names ending with ‘S’
# List all employee names whose names have
exactly 5 characters.
# List the names of employees who is having the
second character as ‘I’.
Summarizing data with Aggregate Functions:
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
# List total employees working in the company
# List the number of jobs available
# List the maximum, minimum, average and sum of
salaries
# List the average salary and number of employees
working in the department 20.
Grouping: GROUP BY clause can be used to divide
the rows in a table into smaller groups.
# Calculate the average salary for each job.
# Show the average salary for each job excluding
managers.
# Show the average monthly salary bill for each job
# Show the average salary for all departments
employing more than 3 people.
# Display only those jobs where the maximum
salary is greater than or equal to 3000.

You might also like