Oracle

 

     Database is an organized collection of data. The data can be textual, like order or inventory data, or it can be pictures, programs or anything else that can be stored on a computer in binary form. 

A relational database stores the data in the form of tables and columns. A table is the category of data, like Employee, and the columns are information about the category, like name or address. 

  Some databases have minimal feature sets and only store data, while others include programming languages, facilities and utilities to support enterprise-level applications like ERP and data warehousing. 

    Oracle is made up of a set of processes running in your operating system. These processes manage how data is stored and how it is accessed. I will cover these processes in detail in the future; but for now we just need to understand that Oracle is a program that is running in the background, maintaining your data for you and figuring out where it should go on your hard drive. 

     In almost all relational databases, data is accessed through SQL, or Structured Query Language, and Oracle is no exception. SQL allows you to SELECT your data, INSERT new records, UPDATE existing records and DELETE records you want to get rid of. SQL can be embedded in other languages or you can run scripts of SQL directly against the database. 

    PL/SQL is the procedural language extension to SQL. PL/SQL is a programming language like C, Java or Pascal. In the Oracle world, there is no better way to access your data from inside a program. SQL can be natively embedded in PL/SQL programs. I will be using both SQL and PL/SQL very heavily in my future articles. 

PL/SQL is a feature-rich language geared toward developing database applications. PL/SQL is the procedural language of the database, but it is also the procedural language for most of Oracle's tools. Programs that run inside the database are called stored procedures. These stored procedures are almost always PL/SQL, but can be written in Java. 

Some of Oracle's tools to access the database and create programs are: 

SQL*Plus has a command line interface. With it, you can access the database and write stored procedures, you can run SQL commands to retrieve data and you can run scripts of either SQL, PL/SQL  or built-in SQL*Plus commands, or a mixture of those three things. 

Oracle Developer is a 4GL GUI application Builder. With Developer, you can create forms, reports, and graphics. Oracle*Forms and Oracle*Reports are two components of Oracle Developer. Earlier versions created client-server applications, but the more recent versions create web applications that run under the Oracle Application Server (OAS). OAS is a web-based application server sold by Oracle. OAS is licensed separately and is very expensive (as are its closed source competitors). The current version is OAS 10g. 

HTML DB is a fairly new application builder geared toward web development (added to the DB with release 9iR2). HTML DB does not need an application server. This tool runs from the database and can be presented to the web using the Apache web server that comes with the database. Since this is not an additional license, it provides a cheaper way to develop applications. Developer is a feature-rich thick client with all of the normal GUI widgets. HTML DB is HTML-based and is very thin and limited to the HTML provided widgets. 

Oracle Enterprise Manager (OEM) is the Enterprise GUI tool to manage the database. From this tool, you can perform any action in the database that is required. This tool is primarily used for administration but can also be provided to developers for application tuning and monitoring. In Oracle 10g, OEM also provides Grid control. 

There are also a lot of third party tools for accessing the database. For our purposes, our primary tool for data access will be SQL*Plus. In the future, I will cover creating applications with HTML DB and accessing your data with some choice third-party tools. 

How about Java? Java is the current hot language, especially on the web. I will also be covering Java in the database in future articles. Java is natively supported by Oracle. Technically, you could use Java to create your stored procedures if you chose, but I am not a proponent of that. When you are working inside the database, I believe that you should only resort to Java when you cannot accomplish a task in PL/SQL. As you'll see, there is very little you cannot accomplish with PL/SQL. 

It will be hard to learn Oracle if you can't play with it. So where can you get access to this software? Oracle has a technical web site called OTN (Oracle Tech Net) that provides access to all of Oracle's software and all of the documentation for that software. There are also forums and many other tools to use to help you learn Oracle. You have to register to access it, but registration is free. Go to http://otn.oracle.com/ and get an ID today. In future articles, I will be spending a lot of time accessing this web site to download software and documentation. My next article will cover downloading Oracle 10g and installing it. 

As a side note, Oracle provides all of their software with a developer's license. This license allows you to try out the software free of charge. You may not create production applications without a paid license, but development with the tools is acceptable. I am not a lawyer, so I recommend that you review the license when you download any software from OTN. 

Also, a note about versioning in Oracle; all products released by Oracle have a version. The current version of the database is 10g Release 1, also written as 10gR1. In addition to this semantic release name, each release has a numeric version. The numeric release for 10gR1 is 10.0.1.3. 

Database Administrator

Who should go for this training?
The following professionals can go for this course:

v  Administrator

v  Analyst

v  Developer

v  Systems Administrator

What will you learn?

v v  Identify the major structural components of the Oracle Database 12c

v  Create reports of aggregated data

v  Write SELECT statements that include queries

v  Retrieve row and column data from tables

v  Run data manipulation statements (DML) in Oracle Database 12c

v  Create tables to store data

v  Utilize views to display data

v  Control database access to specific objects

v  Manage schema objects

v  Display data from multiple tables using the ANSI SQL 99 JOIN syntax

v  Manage objects with data dictionary views

v  Write multiple-column sub-queries

v  Employ SQL functions to retrieve customized data

v  Use scalar and correlated sub-queries

v  Create reports of sorted and restricted data

v  Learn about the Oracle Database Architecture.

v  Create and manage an Oracle Database Instance.

v  Create and manage Storage Structures.

v  Configure the Oracle Network Environment.

v  Create and manage users.

v  Monitor the database and manage performance.

v  Develop backup and recovery procedures to address your business needs.

v  Implement backup and recovery settings and perform backup operations to disk and tape.

v  Employ Oracle Database recovery procedures to recover from media and other failures.

v  Diagnose and repair data failures.

v  Use Flashback Technologies and data duplication to complement backup and recovery procedures.

v  Secure the availability of your database by appropriate backup and recovery strategies.

v  Understand the multitenant architecture.

v  Create and manage a multitenant container database and pluggable databases.

v  Understand regular and application pluggable databases.

v  Manage storage within a multitenant container database and pluggable databases.

v  Manage security within a multitenant container database and regular and application pluggable databases

 Database technology and its integration into most aspects of commercial, scientific and academic computing. As you read about the various database certification programs, keep these job roles in mind: 

v  Database administrator (DBA): Responsible for installing, configuring and maintaining a database management system (DBMS). Often tied to a specific platform such as Oracle, MySQL, DB2 or SQL Server. 

v  Database developer: Works with generic and proprietary APIs to build applications that interact with a DBMS (also platform-specific, like DBA roles).

v  Database designer/database architect: Researches data requirements for specific applications or users, and designs database structures and application capabilities to match.

v  Data analyst/data scientist: Responsible for analyzing data from multiple disparate sources to discover previously hidden insight, determine meaning behind the data and make business-specific recommendations.

v  Data mining/business intelligence (BI) specialist: Specializes in dissecting, analyzing and reporting on important data streams, such as customer data, supply chain data, and transaction data and histories.

v  Data warehousing specialist: Specializes in assembling and analyzing data from multiple operational systems (orders, transactions, supply chain information, customer data, etc.) to establish data history, analyze trends, generate reports and forecasts, and support general ad hoc queries. 

      In the era where data is being generated in humongous amounts, there is a constant need to handle data in databases. Relational databases are one of the most popular databases, and SQL is the basis of relational databases. Therefore Sql Skills are indispensable in most of the job roles. In this article on SQL Commands, I will discuss the top commands and statements that you need to understand in SQL.

The topics covered in this blog are mainly divided into 4 categories:

v  Data Definition Language(DDL) – Consists of commands which are used to define the database.

v  Data Manipulation Language(DML) – Consists of commands which are used to manipulate the data present in the database.

v  Data Control Language(DCL) – Consists of commands which deal with the user permissions and controls of the database system.

v  Transaction Control Language(TCL) – Consist of commands which deal with the transaction of the database.

Apart from the above commands, the following topics will also be covered in this article:

v  Comments in SQL

v  Different Types Of Keys In Database

v  Constraints Used In Database

v  Nested Queries

v  Joins

v  Set Operations

v  Dates & Auto Increment

v  Views

v  Stored Procedures

v  Triggers

 

SQL and working with Oracle Database. It covers the following topics:

v  Module 1: Tables

v  Module 2: Columns and Data Types

v  Module 3: Data Modeling

v  Module 4: Tables, Columns and Modeling Review

v  Module 5: Select and Where

v  Module 6: Joins

v  Module 7: Aggregates and Group By

v  Module 8: Select, Joins and Grouping Review

v  Module 9: Insert and Commit

v  Module 10: Update and Transactions

v  Module 11: Delete and Truncate

v  Module 12: Course Review