Learn Oracle in One Day (एक दिन मे Oracle सिखे)

 

What is the Oracle database?

Oracle database is a relational database management system. It is also called OracleDB, or simply Oracle. It is produced and marketed by Oracle Corporation. It was created in 1977 by Lawrence Ellison and other engineers. It is one of the most popular relational database engines in the IT market for storing, organizing, and retrieving data.

Oracle database was the first DB that designed for enterprise grid computing and data warehousing. Enterprise grid computing provides the most flexible and cost-effective way to manage information and applications. It uses SQL queries as a language for interacting with the database. The following is a list of Oracle database editions in order of priority:

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

Oracle CREATE TABLE

In Oracle, CREATE TABLE statement is used to create a new table in the database.

To create a table, you have to name that table and define its columns and datatype for each column.

Syntax:

1.       CREATE TABLE table_name  

2.       (   

3.         column1 datatype [ NULL | NOT NULL ],  

4.         column2 datatype [ NULL | NOT NULL ],  

5.         ...  

6.         column_n datatype [ NULL | NOT NULL ]  

7.       );  

Parameters used in syntax

• table_name: It specifies the name of the table which you want to create.
• column1, column2, ... column n: It specifies the columns which you want to add in the table. Every column must have a datatype. Every column should either be defined as "NULL" or "NOT NULL". In the case, the value is left blank; it is treated as "NULL" as default.

Oracle CREATE TABLE Example

Here we are creating a table named customers. This table doesn't have any primary key.

1.      CREATE TABLE customers  

2.      ( customer_id number(10) NOT NULL,  

3.        customer_name varchar2(50) NOT NULL,  

4.        city varchar2(50)  

5.      );  

This table contains three columns

• customer_id: It is the first column created as a number datatype (maximum 10 digits in length) and cannot contain null values.
• customer_name: it is the second column created as a varchar2 datatype (50 maximum characters in length) and cannot contain null values.
• city: This is the third column created as a varchar2 datatype. It can contain null values.

Oracle CREATE TABLE Example with primary key

1.      CREATE TABLE customers  

2.      ( customer_id number(10) NOT NULL,  

3.        customer_name varchar2(50) NOT NULL,  

4.        city varchar2(50),  

5.        CONSTRAINT customers_pk PRIMARY KEY (customer_id)  

6.      );  

Oracle Insert Statement

In Oracle, INSERT statement is used to add a single record or multiple records into the table.

Syntax: (Inserting a single record using the Values keyword):

1.        INSERT INTO table  

2.       (column1, column2, ... column_n )  

3.       VALUES  

4.       (expression1, expression2, ... expression_n );   

Syntax: (Inserting multiple records using a SELECT statement):

1.       INSERT INTO table  

2.       (column1, column2, ... column_n )  

3.       SELECT expression1, expression2, ... expression_n  

4.       FROM source_table  

5.       WHERE conditions;   

Parameters:

1) table: The table to insert the records into.

2) column1, column2, ... column_n:

The columns in the table to insert values.

3) expression1, expression2, ... expression_n:

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

4) source_table:

The source table when inserting data from another table.

5) conditions:

The conditions that must be met for the records to be inserted.

Oracle SELECT Statement

The Oracle SELECT statement is used to retrieve data from one or more than one tables, object tables, views, object views etc.

Syntax

1.       SELECT expressions  

2.       FROM tables  

3.       WHERE conditions;   

Parameters

1) expressions: It specifies the columns or calculations that you want to retrieve.

2) tables:This parameter specifies the tables that you want to retrieve records from. There must be at least one table within the FROM clause.

3) conditions: It specifies the conditions that must be followed for selection.

Select Example: select all fields

Let's take an example to select all fields from an already created table named customers

1.       SELECT *  

2.       FROM customers;   

output

Select Example: select specific fields

Example

1.       SELECT age, address, salary  

2.       FROM customers  

3.       WHERE  age < 25  

4.       AND salary > '20000'  

5.       ORDER BY age ASC, salary DESC;  

Select Example: select fields from multiple tables (JOIN)

1.      SELECT customers.name, courses.trainer  

2.       FROM courses  

3.       INNER JOIN customers  

4.       ON courses.course_id = course_id  

5.       ORDER BY name;   

output

Oracle UPDATE Statement

In Oracle, UPDATE statement is used to update the existing records in a table. You can update a table in 2 ways.

Traditional Update table method

Syntax:

1.       UPDATE table  

2.       SET column1 = expression1,  

3.           column2 = expression2,  

4.           ...  

5.           column_n = expression_n  

6.       WHERE conditions;

Parameters:

1) column1, column2, ... column_n:

It specifies the columns that you want to update.

2) expression1, expression2, ...expression_n:

This specifies the values to assign to the column1, column2, ?. column_n.

3) conditions:It specifies the conditions that must be fulfilled for execution of UPDATE stateme.

Oracle Update Example: (Update single column)

1.      UPDATE suppliers  

2.      SET supplier_name = 'Kingfisher'  

3.      WHERE supplier_id = 2;  

This example will update the supplier_name as "Kingfisher" where "supplier_id" is 2.

Oracle Update Example: (Update multiple columns)

The following example specifies how to update multiple columns in a table. In this example, two columns supplier_name and supplier_address is updated by a single statement.

1.      UPDATE suppliers  

2.      SET supplier_address = 'Agra',  

3.          supplier_name = 'Bata shoes'  

4.      WHERE supplier_id = 1;  

Output:

1 row(s) updated.

0.06 seconds

Oracle DELETE Statement

In Oracle, DELETE statement is used to remove or delete a single record or multiple records from a table.

Syntax

1.       DELETE FROM table_name  

2.       WHERE conditions;   

Parameters

1) table_name: It specifies the table which you want to delete.

2) conditions: It specifies the conditions that must met for the records to be deleted.

Oracle Delete Example: On one condition

1.      DELETE FROM customers  

2.       WHERE name = 'Sohan';     

This statement will delete all records from the customer table where name is "Sohan".

Oracle Delete Example: On multiple conditions

1.      DELETE FROM customers  

2.       WHERE last_name = 'Maurya'  

3.       AND customer_id > 2;  

This statement will delete all records from the customers table where the last_name is "Maurya" and the customer_id is greater than 2.