Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Creating Tables

Oracle SQL SELECT Statement

Formatting Output in SQL * Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result

Oracle SQL Functions

Number Functions (Math Functions)
Character Functions
Miscellaneous Functions
Aggregate Functions
Date and Time Functions

Oracle Join Queries, (Inner Join, Outer Join, Self Join)

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions, WITH, CASE Operators

Oracle Data Manipulation Language (INSERT, UPDATE, DELETE, INSERT ALL, MERGE)

Oracle Data Definition Language (CREATE, ALTER, DROP, TRUNCATE, RENAME)

Oracle Transaction Control Language (COMMIT, ROLLBACK,SAVEPOINT)

Data Control Language (GRANT, REVOKE)

Oracle Integrity Constraints (PRIMARY KEY, NOT NULL, CHECK, FOREIGN KEY, UNIQUE)

DEFAULT Values
Dropping Constraints
Disabling and Enabling Constraints
Differing Constraints Check
Viewing Information about Constraints

Oracle Date Operators, Functions

Managing Oracle Views

Managing Oracle Sequences

Managing Oracle Synonyms

Managing Indexes and Clusters

Oracle Table Partitioning and Creating Partitioned Tables

Altering Partition Tables
Dropping Partitions
Merging Partitions
Splitting Partitions
Coalescing Partitions

Oracle Objects and Object Types

Introduction to Databases.

DATABASE

A database is a collection of Data (Information).  Examples of databases, which we use in our daily life, is  an Attendance Register, Telephone Directory, Muster Rule.

Database Management System(DBMS): A database management system is a collection of programs written to manage a database. That is, it acts as a interface between user and database.

RDBMS

A Database Management System based on Relational Data Model is known as Relational Database Management System (RDBMS).

 Relational Data Model was developed by Dr. E.F. CODD. He developed the relational data model by taking the concept from Relational Algebra in  June - 1970.

Relational Data Model has some 12 Rules which are named after Codd as Codd Rules. According to Codd a package can be called as RDBMS only if it satisfies the Codd Rules.

ORACLE

Oracle is an Object-Relational Database Management System. It is the leading RDBMS vendor worldwide. Nearly half of RDBMS  worldwide market is owned by Oracle. 

ORACLE DATABASE

Every Oracle Database Contains Logical and Physical Structures. Logical Structures are tablespaces, Schema objects, extents and segments. Physical Structures are Datafiles, Redo Log Files, Control File.

A database is divided into logical storage units called tablespaces, which group related logical structures together. Each Tablespace in turn consists of one are more datafiles.

In relational database system all the information is stored in form of tables.  A table consists of rows and columns

All the tables and other objects in Oracle are stored in tablespace logically, but physically they are stored in the datafiles associated with the tablespace.

Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database's redo log. A redo log is made up of redo entries (also called redo records).

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, the changes can be obtained from the redo log so work is never lost.

Every Oracle database has a control file. A control file contains the database name and locations of all datafiles and redo log files.

Every Oracle database also has a Parameter File. Parameter file contains the name of the Database, Memory Settings and Location of Control file.