Oracle

Wha is it?

Oracle is an RDBMS.
It is known as Oracle Database, OracleDB or simply Oracle.
Developed and marketed by Oracle Corporation.
Oracle DB runs on Windows, UNIX, Linux and Mac OS.

Followings are the four editions of the Oracle database.

Standard Edition: It provides the base functionality for users that do not require Enterprise Edition's robust package.
Enterprise Edition: It is the most robust and secure edition. It offers all features, including superior performance and security.
Express Edition (XE): It is the lightweight, free and limited Windows and Linux edition.
Oracle Lite: It is designed for mobile devices.

History

Oracle was originally developed by Lawrence Ellison (Larry Ellison).
With his two friends and former co-worker in 1977.

Download & Installation

Go to Oracle official page and search for Oracle database.
Select 11g Release 2.
Accept license agreements, and click on See all.
To download these, you need to sign in with the Oracle account or sign up if you don't have.

Download both the zip files.

Unzip those files into a single folder.
To unzip, right click on the zip file > click on Extract here.

Open the folder.
Run the Setup file.
Give an email address > Next.
Select Create and configure a database > Next.
On System class, select Server Class > Next.
On Grid installation option, select Single instance database installation > Next.
For more features, select Advanced install > Next.
Select the product language as English > Next.
In database edition, select the Enterprise Edition > Next.
Set installation location.
Follow the simple steps.
Set a password for all accounts as admin > Finish.
Select the simple instructions > Close.

Oracle Datatypes

A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.
Each value which is manipulated by Oracle Database has a data type.
The following is a list of datatypes available in Oracle/PLSQL:

Character
Numeric
Date/time
LOB
Rowid

Oracle

Datatype

Character

Numeric

Data and Time

LOB

Rowid

Numeric Data Types

The following list shows the common numeric data types and their descriptions.

Date and Time Types

The MySQL date and time datatypes are as follows:

Character Types

The following list shows the common string data types and their descriptions.

Large Object Data Types (LOB) Data Types:

ROWID data type represents actual storage address of a row.
Following are ROWID datatypes in Oracle SQL.

MySQL Database

Create Database

You can create a MySQL database by using MySQL Command Line Client.
Open the MySQL console and write down the password, if you set one while installation.
Create a database by the following syntax.
Syntax: CREATE DATABASE Customers;

SELECT Database

SELECT Database is used in MySQL to select a particular database to work with.
We can use SQL command "USE" to select a particular database.
Syntax: USE Customers;

Drop Database

We can drop/delete/remove a MySQL database easily with the MySQL command.
Syntax: DROP DATABASE Customers;

MySQL Table

Create Table

CREATE TABLE command is used to create a new table into the database.
A table creation command requires three things:

Name of the table
Names of fields
Definitions for each field

Syntax: CREATE TABLE table_name (column_name column_type...);

Use the following command to see the table already created.

Use the following command to see the table already created with table structure.

Alter Table

ALTER statement is used when you want to change the name of your table or any table field.
It is also used to add or delete an existing column in a table.
To add a column in a table, syntax is:ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];

To modify column definition in the table, syntax is:
ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ];

To drop column in the table, the syntax is:
ALTER TABLE table_name DROP COLUMN column_name;

To rename a column in the table, the syntax is:
ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ]

To rename a table, the syntax is:
ALTER TABLE table_name RENAME TO new_table_name;

Truncate vs Drop Table

TRUNCATE TABLE: It removes only table data, not structure.
DROP TABLE: It will remove complete table data and structure both.

MySQL Queries

INSERT Record

INSERT statement is used to insert data in MySQL table within the database.
We can insert single or multiple records using a single query in MySQL.
For partial field, syntax: INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

Syntax for all fields: INSERT INTO table_name VALUES ( value1, value2,...valueN );

For inserting multiple records.
Syntax: INSERT INTO cus_tbl (cus_id, cus_firstname, cus_surname) VALUES (5, 'Ajeet', 'Maurya'), (6, 'Deepika', 'Chopra'), (7, 'Vimal', 'Jaiswal');

UPDATE Record

UPDATE statement is used to update data of the MySQL table within the database.
It is used when you need to modify the table.
Syntax: UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

DELETE Record

DELETE statement is used to delete data from the MySQL table within the database.
We can delete records on the basis of conditions.
Syntax: DELETE FROM table cust_table WHERE (Condition specified).

SELECT Record

SELECT statement is used to fetch data from the one or more tables in MySQL.
We can retrieve records of all fields or specified fields.
The syntax for specified fields: SELECT expressions FROM tables [WHERE condition]

For selecting all field, systax: SELECT * FROM table_name;

From multiple tables, we can use JOIN statement.
Here we will take 2 tables; Cust_table and sub table.
The syntax for getting data from both tables: SELECT officers.officer_id, students.student_name FROM students INNER JOIN officers ON students.student_id = officers. officer_id ORDER BY student_id;

Oracle

Wha is it?

Oracle is an RDBMS.

It is known as Oracle Database, OracleDB or simply Oracle.

Developed and marketed by Oracle Corporation.

Oracle DB runs on Windows, UNIX, Linux and Mac OS.

Followings are the four editions of the Oracle database.

Standard Edition: It provides the base functionality for users that do not require Enterprise Edition's robust package.

Enterprise Edition: It is the most robust and secure edition. It offers all features, including superior performance and security.

Express Edition (XE): It is the lightweight, free and limited Windows and Linux edition.

Oracle Lite: It is designed for mobile devices.

History

Oracle was originally developed by Lawrence Ellison (Larry Ellison).

With his two friends and former co-worker in 1977.

Download & Installation

Go to Oracle official page and search for Oracle database.

Select 11g Release 2.

Accept license agreements, and click on See all.

To download these, you need to sign in with the Oracle account or sign up if you don't have.

Download both the zip files.

Unzip those files into a single folder.

To unzip, right click on the zip file > click on Extract here.

Open the folder.

Run the Setup file.

Give an email address > Next.

Select Create and configure a database > Next.

On System class, select Server Class > Next.

On Grid installation option, select Single instance database installation > Next.

For more features, select Advanced install > Next.

Select the product language as English > Next.

In database edition, select the Enterprise Edition > Next.

Set installation location.

Follow the simple steps.

Set a password for all accounts as admin > Finish.

Select the simple instructions > Close.

Oracle Datatypes

A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.

Each value which is manipulated by Oracle Database has a data type.

The following is a list of datatypes available in Oracle/PLSQL:

Character

Numeric

Date/time

LOB

Rowid

Oracle

Datatype

Character

Numeric

Data and Time

LOB

Rowid

Numeric Data Types

The following list shows the common numeric data types and their descriptions.

Date and Time Types

The MySQL date and time datatypes are as follows:

Character Types

The following list shows the common string data types and their descriptions.

Large Object Data Types (LOB) Data Types:

Large Object Data Types (LOB) Data Types:

ROWID data type represents actual storage address of a row.

Following are ROWID datatypes in Oracle SQL.

MySQL Database

Create Database

You can create a MySQL database by using MySQL Command Line Client.

Open the MySQL console and write down the password, if you set one while installation.

Create a database by the following syntax.

Syntax: CREATE DATABASE Customers;

SELECT Database

SELECT Database is used in MySQL to select a particular database to work with.

We can use SQL command "USE" to select a particular database.

Syntax: USE Customers;

Drop Database

We can drop/delete/remove a MySQL database easily with the MySQL command.

Syntax: DROP DATABASE Customers;

MySQL Table

Create Table

CREATE TABLE command is used to create a new table into the database.

A table creation command requires three things:

Syntax: CREATE TABLE table_name (column_name column_type...);

Use the following command to see the table already created.

To add a column in a table, syntax is:ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];

To modify column definition in the table, syntax is:

We can insert single or multiple records using a single query in MySQL.

The syntax for getting data from both tables: SELECT officers.officer_id, students.student_name FROM students INNER JOIN officers ON students.student_id = officers. officer_id ORDER BY student_id;