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.