Oracle Database and its Important Sql Queries Part – 2

Oracle View
In Oracle, view is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.

A view is created by a query joining one or more tables.

Oracle CREATE VIEW
Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;
Parameters:

view_name: It specifies the name of the Oracle VIEW that you want to create.
Example:

Let’s take an example to create view. In this example, we are creating two tables suppliers and orders first.

Suppliers table:

CREATE TABLE “SUPPLIERS”
( “SUPPLIER_ID” NUMBER,
“SUPPLIER_NAME” VARCHAR2(4000),
“SUPPLIER_ADDRESS” VARCHAR2(4000)
)

Orders table:

CREATE TABLE “ORDERS”
( “ORDER_NO.” NUMBER,
“QUANTITY” NUMBER,
“PRICE” NUMBER
)

Execute the following query to create a view name sup_orders.

Create View Query:

CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = supplier_id
WHERE suppliers.supplier_name = ‘VOJO’;
Output:
View created.
0.21 seconds
You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;
Output:
SUPPLIER_ID QUANTITY PRICE
3 35 70
3 26 125
3 18 100
3 rows returned in 0.00 seconds
Oracle Update VIEW
In Oracle, the CREATE OR REPLACE VIEW statement is used to modify the definition of an Oracle VIEW without dropping it.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
Example:

Execute the following query to update the definition of Oracle VIEW called sup_orders without dropping it.

CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = supplier_id
WHERE suppliers.supplier_name = ‘HCL’;
You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;
Output:

SUPPLIER_ID QUANTITY PRICE
1 35 70
1 26 125
1 18 100
row(s) 1 – 3 of 3
Oracle DROP VIEW
The DROP VIEW statement is used to remove or delete the VIEW completely.

Syntax:

DROP VIEW view_name;
Example:

DROP VIEW sup_orders;

Oracle Queries
You can execute many queries in oracle database such as insert, update, delete, alter table, drop, create and select.

1) Oracle Select Query
Oracle select query is used to fetch records from database. For example:

SELECT * from customers;
More Details…

2) Oracle Insert Query
Oracle insert query is used to insert records into table. For example:

insert into customers values(101,’rahul’,’delhi’);
More Details…

3) Oracle Update Query
Oracle update query is used to update records of a table. For example:

update customers set name=’bob’, city=’london’ where id=101;
More Details…

4) Oracle Delete Query
Oracle update query is used to delete records of a table from database. For example:

delete from customers where id=101;
More Details…

5) Oracle Truncate Query
Oracle update query is used to truncate or remove records of a table. It doesn’t remove structure. For example:

truncate table customers;
More Details…

6) Oracle Drop Query
Oracle drop query is used to drop a table or view. It doesn’t have structure and data. For example:

drop table customers;
More Details…

7) Oracle Create Query
Oracle create query is used to create a table, view, sequence, procedure and function. For example:

CREATE TABLE customers
( id number(10) NOT NULL,
name varchar2(50) NOT NULL,
city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (id)
);
8) Oracle Alter Query
Oracle alter query is used to add, modify, delete or drop colums of a table. Let’s see a query to add column in customers table:

ALTER TABLE customers
ADD age varchar2(50);

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

SELECT expressions
FROM tables
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

SELECT *
FROM customers;

Select Example: select specific fields
Example

SELECT age, address, salary
FROM customers
WHERE age < 25
AND salary > ‘20000’
ORDER BY age ASC, salary DESC;

Select Example: select fields from multiple tables (JOIN)
SELECT customers.name, courses.trainer
FROM courses
INNER JOIN customers
ON courses.course_id = course_id
ORDER BY name;
output


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):

INSERT INTO table
(column1, column2, … column_n )
VALUES
(expression1, expression2, … expression_n );
Syntax: (Inserting multiple records using a SELECT statement):

INSERT INTO table
(column1, column2, … column_n )
SELECT expression1, expression2, … expression_n
FROM source_table
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 Insert Example: By VALUE keyword
It is the simplest way to insert elements to a database by using VALUE keyword.

See this example:

Consider here the already created suppliers table. Add a new row where the value of supplier_id is 23 and supplier_name is Flipkart.

See this example:
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(50, ‘Flipkart’);
Output:
1 row(s) inserted.
0.02 seconds
Oracle Insert Example: By SELECT statement
This method is used for more complicated cases of insertion. In this method insertion is done by SELECT statement. This method is used to insert multiple elements.

See this example:
In this method, we insert values to the “suppliers” table from “customers” table. Both tables are already created with their respective columns.

Execute this query:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT age, address
FROM customers
WHERE age > 20;
Output:
4 row(s) inserted.

You can even check the number of rows that you want to insert by following statement:

SELECT count(*)
FROM customers
WHERE age > 20;

Oracle INSERT ALL statement
The Oracle INSERT ALL statement is used to insert multiple rows with a single INSERT statement. You can insert the rows into one table or multiple tables by using only one SQL command.

Syntax

INSERT ALL
INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
Parameters
1) table_name: it specifies the table in which you want to insert your records.

2) column1, column2, column_n: this specifies the columns in the table to insert values.

3) expr1, expr2, expr_n: this specifies the values to assign to the columns in the table.

Oracle INSERT ALL Example
This example specifies how to insert multiple records in one table. Here we insert three rows into the “suppliers” table.

INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (20, ‘Google’)
INTO suppliers (supplier_id, supplier_name) VALUES (21, ‘Microsoft’)
INTO suppliers (supplier_id, supplier_name) VALUES (22, ‘Apple’)
SELECT * FROM dual;
Output:

3 row(s) inserted.

This is totally equivalent to the following three INSERT statements.

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, ‘Google’);
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, ‘Microsoft’);
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (3000, ‘Apple’);
Oracle INSERT ALL Example: (Insert into multiple tables)
The INSERT ALL statement can also be used to insert multiple rows into more than one table by one command only.

In the following example, we are going to insert records into the both “suppliers” and “customers” tables.

INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (30, ‘Google’)
INTO suppliers (supplier_id, supplier_name) VALUES (31, ‘Microsoft’)
INTO customers (age, name, address) VALUES (29, ‘Luca Warsi’, ‘New York’)
SELECT * FROM dual;
Output

3 row(s) inserted. 
Here, total 3 rows are inserted, 2 rows are inserted into the suppliers table and one row into the customers table.

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:

UPDATE table
SET column1 = expression1,
column2 = expression2,

column_n = expression_n
WHERE conditions;
Update Table by selecting rocords from another table
Syntax:

UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
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)
UPDATE suppliers
SET supplier_name = ‘Kingfisher’
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.

UPDATE suppliers
SET supplier_address = ‘Agra’,
supplier_name = ‘Bata shoes’
WHERE supplier_id = 1;
Output:

1 row(s) updated.
Oracle Update Example: (By selecting records from another table)
UPDATE customers
SET name = (SELECT supplier_name
FROM suppliers
WHERE suppliers.supplier_name = customers.name)
WHERE age < 25;
Output: 2 row(s) updated.

Here, the customers table is updated by fetching the data from “suppliers” table.

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

Syntax

DELETE FROM table_name
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
DELETE FROM customers
WHERE name = ‘Sohan’;
This statement will delete all records from the customer table where name is “Sohan”.

Oracle Delete Example: On multiple conditions
DELETE FROM customers
WHERE last_name = ‘Maurya’
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.

Oracle TRUNCATE TABLE
In Oracle, TRUNCATE TABLE statement is used to remove all records from a table. It works same as DELETE statement but without specifying a WHERE clause. It is generally used when you don?t have to worry about rolling back

Once a table is truncated, it can?t be rolled back. The TRUNCATE TABLE statement does not affect any of the table?s indexes, triggers or dependencies.

Syntax

TRUNCATE TABLE [schema_name.]table_name
Parameters
1) schema_name: This parameter specifies the name of the schema that the table belongs to. It is optional.

2) table_name: It specifies the table that you want to truncate.

Oracle TRUNCATE Table Example
Consider a table named “customers” and execute the following query to truncate this

TRUNCATE TABLE customers;
Output

Table truncated.
1.11 seconds
Now check the customers table, you will find that there is no data available in that table. It is equally similar to DELETE TABLE statement in Oracle.

Oracle DELETE Table Example
DELETE TABLE customers;
TRUNCATE TABLE vs DELETE TABLE
Both the statements will remove the data from the “customers” table but the main difference is that you can roll back the DELETE statement whereas you can’t roll back the TRUNCATE TABLE statement.
Oracle DISTINCT Clause
Oracle DISTINCT clause is used to remove the duplicate records from the result set. It is only used with SELECT statement.

Syntax:

SELECT DISTINCT expressions
FROM tables
WHERE conditions;
Parameters:
expressions:It specifies the columns that you want to retrieve.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled.

Oracle DISTINCT Example: (with single expression)
Let’s take a table “customers”

Customer table:

CREATE TABLE “CUSTOMERS”
( “NAME” VARCHAR2(4000),
“AGE” NUMBER,
“SALARY” NUMBER,
“STATE” VARCHAR2(4000)
)

Customer Table
Execute this query:

SELECT DISTINCT state
FROM customers
WHERE name = ‘charu’;
Output:

oracle distinct query 1
Oracle DISTINCT Example: (with multiple expressions)
Execute this query:

SELECT DISTINCT name, age, salary
FROM customers
WHERE age >= ’60’;
This example specifies distinct name, age and salary of the customer where age is greater than or equal to 65.
Oracle FROM Clause
FROM clause is a mandatory clause in SELECT expression. It specifies the tables from which data is to be retrieved.

Syntax:

FROM table_name…
Expressions…
Oracle FROM Clause Example: (with one table)
Let’s take an example to explain how to use FROM clause to retrieve data from one table. Consider a table “customers”.

Customer table:

CREATE TABLE “CUSTOMERS”
( “NAME” VARCHAR2(4000),
“AGE” NUMBER,
“SALARY” NUMBER,
“STATE” VARCHAR2(4000)
)

Customer Table
Execute this query:

SELECT *
FROM customers
WHERE salary >= 20000
ORDER BY salary ASC;
Output:

Oracle from example
Oracle FROM Clause Example: (with two tables)

Inner Join example:

Let’s take two tables “suppliers” and “order1”.

Suppliers:

Oracle Inner Join
Oracle Inner Join supplier
Order1:

Oracle Inner Join
Oracle Inner Join order
Execute the following query:

SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number
FROM suppliers
INNER JOIN order1
ON suppliers.supplier_id = order1.supplier_id;
Output:

Oracle from example 5
Oracle ORDER BY Clause
In Oracle, ORDER BY Clause is used to sort or re-arrange the records in the result set. The ORDER BY clause is only used with SELECT statement.

Syntax:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];
Parameters:
expressions: It specifies columns that you want to retrieve.

tables: It specifies the table name from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the records to be selected.

ASC: It is an optional parameter that is used to sort records in ascending order.

DESC: It is also an optional parameter that is used to sort records in descending order.

Oracle ORDER BY Example: (without ASC/DESC attribute)
Let’s take a table “supplier”

Supplier table:

CREATE TABLE “SUPPLIER”
( “SUPPLIER_ID” NUMBER,
“FIRST_NAME” VARCHAR2(4000),
“LAST_NAME” VARCHAR2(4000)
)

Suppliers Table
Execute this Query:

SELECT *
FROM supplier
ORDER BY last_name;
Output:

oracle order by example 1

The above example returns the first_name ordered by last_name in ascending order.

Oracle ORDER BY Example: (sorting in descending order)
If you want to sort your result in descending order, you should use the DESC attribute in your ORDER BY clause:

Execute this Query:

SELECT *
FROM supplier
ORDER BY last_name DESC;
Output
The above example returns the first_name ordered by last_name in descending order.
Oracle GROUP BY Clause
In Oracle GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns.

Syntax:

SELECT expression1, expression2, … expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, … expression_n;
Parameters:
expression1, expression2, … expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.

Oracle GROUP BY Example: (with SUM function)
Let’s take a table “salesdepartment”

Salesdepartment table:

CREATE TABLE “SALESDEPARTMENT”
( “ITEM” VARCHAR2(4000),
“SALE” NUMBER,
“BILLING_ADDRESS” VARCHAR2(4000)
)

Execute this query:

SELECT item, SUM(sale) AS “Total sales”
FROM salesdepartment
GROUP BY item;
Output

The above example will show the total sales of every individual item.
Oracle GROUP BY Example: (with COUNT function)
Let’s take a table “customers”

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

Customer table:

CREATE TABLE “CUSTOMERS”
( “NAME” VARCHAR2(4000),
“AGE” NUMBER,
“SALARY” NUMBER,
“STATE” VARCHAR2(4000)
)

Execute this query:

SELECT state, COUNT(*) AS “Number of customers”
FROM customers
WHERE salary > 10000
GROUP BY state;
Output:

Oracle GROUP BY Example: (with MIN function)
Let?s take a table “employees”

Employees table:

CREATE TABLE “EMPLOYEES”
( “EMP_ID” NUMBER,
“NAME” VARCHAR2(4000),
“AGE” NUMBER,
“DEPARTMENT” VARCHAR2(4000),
“SALARY” NUMBER
)
Execute this query:

SELECT department,
MIN(salary) AS “Lowest salary”
FROM employees
GROUP BY department;
Output:

Oracle GROUP BY Example: (with MAX function)
In this example, we are using “employees” table that is given above.

Execute this query:

SELECT department,
MAX(salary) AS “Highest salary”
FROM employees
GROUP BY department;
Output:

Oracle HAVING Clause
In Oracle, HAVING Clause is used with GROUP BY Clause to restrict the groups of returned rows where condition is TRUE.

Syntax:

SELECT expression1, expression2, … expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, … expression_n
HAVING having_condition;
Parameters:
expression1, expression2, … expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.

having_conditions: It specifies the conditions that are applied only to the aggregated results to restrict the groups of returned rows.

Oracle HAVING Example: (with GROUP BY SUM function)
Let’s take a table “salesdepartment”

Salesdepartment table:

CREATE TABLE “SALESDEPARTMENT”
( “ITEM” VARCHAR2(4000),
“SALE” NUMBER,
“BILLING_ADDRESS” VARCHAR2(4000)
)

Execute this query:

SELECT item, SUM(sale) AS “Total sales”
FROM salesdepartment
GROUP BY item
HAVING SUM(sale) < 1000;
Output:

Oracle HAVING Example: (with GROUP BY COUNT function)

Let’s take a table “customers”

Customer table:

CREATE TABLE “CUSTOMERS”
( “NAME” VARCHAR2(4000),
“AGE” NUMBER,
“SALARY” NUMBER,
“STATE” VARCHAR2(4000)
)

Execute this query:

SELECT state, COUNT(*) AS “Number of customers”
FROM customers
WHERE salary > 10000
GROUP BY state
HAVING COUNT(*) >= 2;
Output:

Oracle HAVING Example: (with GROUP BY MIN function)
Let’s take a table “employees”

Employees table:

CREATE TABLE “EMPLOYEES”
( “EMP_ID” NUMBER,
“NAME” VARCHAR2(4000),
“AGE” NUMBER,
“DEPARTMENT” VARCHAR2(4000),
“SALARY” NUMBER
)

Execute this query:

SELECT department,
MIN(salary) AS “Lowest salary”
FROM employees
GROUP BY department
HAVING MIN(salary) < 15000;
Output

Oracle HAVING Example: (with GROUP BY MAX function)
Execute this query:

SELECT department,
MAX(salary) AS “Highest salary”
FROM employees
GROUP BY department
HAVING MAX(salary) > 30000;
Output:

Continue Oracle Database and its Important Sql Queries Part – 1