Oracle Database and its Important Sql Queries Part -1

What is Oracle
Oracle database is a relational database management system. It is known as Oracle database, OracleDB or simply Oracle. It is produced and marketed by Oracle Corporation.

Oracle database is the first database designed for enterprise grid computing. The enterprise grid computing provides the most flexible and cost effective way to manage information and applications.

Different editions of Oracle database
Following are the four editions of the Oracle database.

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.
The Oracle Corporation
Oracle Corporation is the largest software company in the field of database business. Its relational database was the first to support SQL which has since become the industry standard.

Oracle database is one of the most trusted and widely used relational database engines. The biggest rival of Oracle database is Microsoft’s SQL Server.

History of Oracle
Oracle was originally developed by Lawrence Ellison (Larry Ellision) and his two friends and former co-worker in 1977. Oracle DB runs on the most major platforms like Windows, UNIX, Linux and Mac OS.

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:

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],

column_n datatype [ NULL | NOT NULL ]
);
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.

CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50)
);
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
CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
What is Primary key
A primary key is a single field or combination of fields that contains a unique record. It must be filled. None of the field of primary key can contain a null value. A table can have only one primary key.

For versions 8i, 9i, 10g, and 11g, the limit is 1,000 columns per table. Prior to that,Oracle 7 had a 254 column limit

CREATE TABLE AS Statement
The CREATE TABLE AS statement is used to create a table from an existing table by copying the columns of existing table.

Note: If you create the table in this way, the new table will contain records from the existing table.
Syntax:

CREATE TABLE new_table
AS (SELECT * FROM old_table);
Create Table Example: copying all columns of another table
In this example, we are creating a “newcustomers” table by copying all the columns from the already existing table “Customers”.

CREATE TABLE newcustomers
AS (SELECT * FROM customers WHERE customer_id < 5000);
Table created.
This table is named as “newcustomers” and having the same columns of “customers” table.

Create Table Example: copying selected columns of another table
Syntax:
CREATE TABLE new_table
AS (SELECT column_1, column2, … column_n
FROM old_table);
Let’s take an example:

CREATE TABLE newcustomers2
AS (SELECT customer_id, customer_name
FROM customers
WHERE customer_id < 5000);
The above example will create a new table called “newcustomers2”. This table includes the specified columns customer_id and customer_name from the customers table.

Create Table Example: copying selected columns from multiple tables
Syntax:
CREATE TABLE new_table
AS (SELECT column_1, column2, … column_n
FROM old_table_1, old_table_2, … old_table_n);
Let’s take an example: Consider that you have already created two tables “regularcustomers” and “irregularcustomers”.

The table “regularcustomers” has three columns rcustomer_id, rcustomer_name and rc_city.

CREATE TABLE “regularcustomers”
( “RCUSTOMER_ID” NUMBER(10,0) NOT NULL ENABLE,
“RCUSTOMER_NAME” VARCHAR2(50) NOT NULL ENABLE,
“RC_CITY” VARCHAR2(50)
)

The second table “irregularcustomers” has also three columns ircustomer_id, ircustomer_name and irc_city.

CREATE TABLE “irregularcustomers”
( “IRCUSTOMER_ID” NUMBER(10,0) NOT NULL ENABLE,
“IRCUSTOMER_NAME” VARCHAR2(50) NOT NULL ENABLE,
“IRC_CITY” VARCHAR2(50)
)

In the following example, we will create a table name “newcustomers3” form copying columns from both tables.
Example:

CREATE TABLE newcustomers3
AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name
FROM regularcustomers, irregularcustomers
WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id
AND regularcustomers.rcustomer_id < 5000);
Oracle ALTER TABLE Statement
In Oracle, ALTER TABLE statement specifies how to add, modify, drop or delete columns in a table. It is also used to rename a table.

How to add column in a table
Syntax:

ALTER TABLE table_name
ADD column_name column-definition;
Example:

Consider that already existing table customers. Now, add a new column customer_age into the table customers.

ALTER TABLE customers
ADD customer_age varchar2(50);
Now, a new column “customer_age” will be added in customers table.

How to add multiple columns in the existing table
Syntax:

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,

column_n column_definition);
Example

ALTER TABLE customers
ADD (customer_type varchar2(50),
customer_address varchar2(50));
Now, two columns customer_type and customer_address will be added in the table customers.
How to modify column of a table
Syntax:

ALTER TABLE table_name
MODIFY column_name column_type;
Example:

ALTER TABLE customers
MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values.
How to modify multiple columns of a table
Syntax:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,

column_n column_type);
Example:
ALTER TABLE customers
MODIFY (customer_name varchar2(100) not null,
city varchar2(100));
This will modify both the customer_name and city columns in the table.
How to drop column of a table
Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;
Example:

ALTER TABLE customers
DROP COLUMN customer_name;
This will drop the customer_name column from the table.
How to rename column of a table
Syntax:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
Example:

ALTER TABLE customers
RENAME COLUMN customer_name to cname;
This will rename the column customer_name into cname.
How to rename table
Syntax:

ALTER TABLE table_name
RENAME TO new_table_name;
Example:

ALTER TABLE customers
RENAME TO retailers;
This will rename the customer table into “retailers” table.
Oracle DROP TABLE Statement
Oracle DROP TABLE statement is used to remove or delete a table from the Oracle database.

Syntax

DROP [schema_name].TABLE table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];
Parameters
schema_name: It specifies the name of the schema that owns the table.

table_name: It specifies the name of the table which you want to remove from the Oracle database.

CASCADE CONSTRAINTS: It is optional. If specified, it will drop all referential integrity constraints as well.

PURGE: It is also optional. If specified, the table and its dependent objects are placed in the recycle bin and can?t be recovered.

If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error and Oracle will not drop the table.
DROP TABLE Example
DROP TABLE customers;
This will drop the table named customers.
DROP TABLE Example with PURGE parameter
DROP TABLE customers PURGE
This statement will drop the table called customers and issue a PURGE so that the space associated with the customers table is released and the customers table is not placed in recycle bin. So, it is not possible to recover that table if required.
Oracle Global Temporary tables
Temporary tables generally contain all of the features that ordinary tables have like triggers, join cardinality, information about rows and block etc. the main difference is that the temporary tables can’t have foreign keys related to other tables.

Syntax

CREATE GLOBAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],

column_n datatype [ NULL | NOT NULL ]
);
Parameters
table_name: The parameter table_name specifies the global temporary table that you want to create.

column1, column2, … column_ n: It specifies the column that you want create in the global temporary table. Every column must have a datatype and should be defined as NULL or NOTNULL. If the value is left blank, it is by default treated as NULL.

Example
The following example specifies how to create a global temporary table

CREATE GLOBAL TEMPORARY TABLE students
( student_id numeric(10) NOT NULL,
student_name varchar2(50) NOT NULL,
student_address varchar2(50)
);
This will create a global temporary table called students
Oracle Local Temporary tables
In Oracle, local temporary tables are distinct within modules. These tables are defined and scoped to the session in which you created it.

Declare local temporary table
Syntax

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],

column_n datatype [ NULL | NOT NULL ]
);
Parameters
table_name: The parameter table_name specifies the local temporary table that you want to create.

column1, column2,… column_ n: It specifies the column that you want create in the local temporary table. Every column must have a datatype and should be defined as NULL or NOTNULL. If the value is left blank, it is by default treated as NULL.

 

Continue Oracle Part -2

Original Source : https://www.javatpoint.com