SQL

What Is DBMS:-
DBMS is the abbreviated form of DataBase Management System.
Database management system is a computer software component introduced during
1960’s. It is used for controlling various databases in the desktop computer or server.
It was also termed as Navigational Database Management System. During 1970’s
RDBMS or Relational database management system came into existence.

Database management system is the means of controlling databases either in the

hard disk in a desktop system or on a network. Database management system is used
for creating database, maintained database and provides the means of using the
database. DBMS doesn’t consider relationship between the tables. Instead it will take
the approach of manual navigation. This has led to severe performance problems
when dealing with complex and high amount of data.

DBMS has several components. Some of the major components are external
interface, database language engine, query optimizer, database engine, storage
engine, DBMS management component, etc..
What Is RDBMS
RDBMS is the abbreviated form of Relational DataBase Management System.

Relational database management system was introduced in 1970’s. RDBMS avoided

the navigation model as in old DBMS and introduced Relational model. The relational
model has relationship between tables using primary keys, foreign keys and indexes.
Thus the fetching and storing of data become faster than the old Navigational model.
So RDBMS is widely used by the enterprises and developers for storing complex and
large amount of data.

*** SoftwareDevelopment Life cycle:

Software Development Life Cycle (SDLC) is a process used by the software industry to
design, develop and test high quality softwares. The SDLC aims to produce a high-
quality software that meets or exceeds customer expectations, reaches completion
within times and cost estimates.

SDLC is the acronym of Software Development Life Cycle.

It is also called as Software Development Process.
SDLC is a framework defining tasks performed at each step in the software
development process.

ISO/IEC 12207 is an international standard for software life-cycle processes. It aims to

be the standard that defines all the tasks required for developing and maintaining
software.

What is SDLC?
SDLC is a process followed for a software project, within a software organization. It
consists of a detailed plan describing how to develop, maintain, replace and alter or
enhance specific software. The life cycle defines a methodology for improving the
quality of software and the overall development process.

The following figure is a graphical representation of the various stages of a typical

SDLC.
A typical Software Development Life Cycle consists of the following stages −

Stage 1: Planning and Requirement Analysis

Requirement analysis is the most important and fundamental stage in SDLC. It is
performed by the senior members of the team with inputs from the customer, the
sales department, market surveys and domain experts in the industry. This
information is then used to plan the basic project approach and to conduct product
feasibility study in the economical, operational and technical areas.

Planning for the quality assurance requirements and identification of the risks
associated with the project is also done in the planning stage. The outcome of the
technical feasibility study is to define the various technical approaches that can be
followed to implement the project successfully with minimum risks.

Stage 2: Defining Requirements (Analysis)

Once the requirement analysis is done the next step is to clearly define and
document the product requirements and get them approved from the customer or
the market analysts. This is done through an SRS (Software Requirement
Specification) document which consists of all the product requirements to be
designed and developed during the project life cycle.

Stage 3: Designing the Product Architecture

SRS is the reference for product architects to come out with the best architecture for
the product to be developed. Based on the requirements specified in SRS, usually
more than one design approach for the product architecture is proposed and
documented in a DDS - Design Document Specification.

This DDS is reviewed by all the important stakeholders and based on various
parameters as risk assessment, product robustness, design modularity, budget and
time constraints, the best design approach is selected for the product.

A design approach clearly defines all the architectural modules of the product along
with its communication and data flow representation with the external and third
party modules (if any). The internal design of all the modules of the proposed
architecture should be clearly defined with the minutest of the details in DDS.

Stage 4: Building or Developing the Product

In this stage of SDLC the actual development starts and the product is built. The
programming code is generated as per DDS during this stage. If the design is
performed in a detailed and organized manner, code generation can be accomplished
without much hassle.

Developers must follow the coding guidelines defined by their organization and
programming tools like compilers, interpreters, debuggers, etc. are used to generate
the code. Different high level programming languages such as C, C++, Pascal, Java and
PHP are used for coding. The programming language is chosen with respect to the
type of software being developed.

Stage 5: Testing the Product

This stage is usually a subset of all the stages as in the modern SDLC models, the
testing activities are mostly involved in all the stages of SDLC. However, this stage
refers to the testing only stage of the product where product defects are reported,
tracked, fixed and retested, until the product reaches the quality standards defined in
the SRS.

Stage 6: Deployment in the Market and Maintenance

Once the product is tested and ready to be deployed it is released formally in the
appropriate market. Sometimes product deployment happens in stages as per the
business strategy of that organization. The product may first be released in a limited
segment and tested in the real business environment (UAT- User acceptance testing).

Then based on the feedback, the product may be released as it is or with suggested
enhancements in the targeting market segment. After the product is released in the
market, its maintenance is done for the existing customer base.

SDLC Models
There are various software development life cycle models defined and designed
which are followed during the software development process. These models are also
referred as Software Development Process Models". Each process model follows a
Series of steps unique to its type to ensure success in the process of software
development.

Following are the most important and popular SDLC models followed in the industry
−

Waterfall Model
Iterative Model
Spiral Model
V-Model
Big Bang Model

*** ACID properties of DBMS:

Atomicity
Consistency
Isolation
Durability.

Atomicity :-
The atomicity acid property in SQL. It means either all the operations (insert, update,
delete) inside a transaction take place or none. Or you can say, all the statements
(insert, update, delete) inside a transaction are either completed or rolled back.

Consistency :-
This SQL ACID property ensures database consistency. It means, whatever happens in
the middle of the transaction, this acid property will never leave your database in a
half-completed state. If the transaction completed successfully, then it will apply all
the changes to the database.
If there is an error in a transaction, then all the changes that already made will be
rolled back automatically. It means the database will restore to its initial state that it
had before the transaction started.
If there is a system failure in the middle of the transaction, then also, all the changes
made already will automatically rollback.

Isolation:-
Every transaction is individual, and One transaction can’t access the result of other
transactions until the transaction completed. Or, you can’t perform the same
operation using multiple transactions at the same time.

Durability :-
Once the transaction completed, then the changes it has made to the database will
be permanent. Even if there is a system failure, or any abnormal changes also, this
SQL acid property will safeguard the committed data.
 Structured Query Language

SQL Commands

SQL commands are instructions. It is used to communicate with the database. It is

also used to perform specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table,
modify the table, set permission for users.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DQL, TCL,DCL.

1. Data Definition Language (DDL)

DDL changes the structure of the table like creating a table, deleting a table, altering
a table, etc. All the command of DDL are auto-committed that means it permanently
save all the changes in the database.

CREATE
ALTER -alter,- Modify, -add, -rename
DROP
TRUNCATE

2. Data Manipulation Language

DML commands are used to modify the database. It is responsible for all form of
changes in the database.
The command of DML is not auto-committed that means it can't permanently save all
the changes in the database. They can be rollback.

INSERT
UPDATE
DELETE
MARGE

3. Data Query Language

DQL is used to fetch the data from the database.

SELECT

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only. These operations are automatically committed in the database that's why they
cannot be used while creating tables or dropping them.
COMMIT
ROLLBACK
SAVEPOINT

5. Data Control Language

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:

GRANT
REVOKE
SETROLL

**** SQL Datatype

SQL Datatype is used to define the values that a column can contain.
Every column is required to have a name and data type in the database table.

Oracle has a number of built-in data types illustrated in the following table:

Character Data Types

CHAR: The CHAR data type specifies a fixed-length character string. If you insert a
value that is shorter than the column length, then Oracle blank-pads the value to
column length and if the value is too long for the column, then Oracle returns an
error.
The CHAR data type can store a character string with the size from 1 to 2000 bytes

The default value of length is 1 if you skip it like the following Ex:
column_name CHAR ()

Test table
Name A M O L
Vitthal BLANK PADDED

VARCHAR2: To store variable-length character strings, you use the Oracle VARCHAR2
data type. A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It

Test table A M O L
Name
Vitthal BLANK PADDING NOT GENERATED
When you create a table with a VARCHAR2 column, you must specify the maximum
string length: VARCHAR2(max_size CHAR)

If you store a character string whose size exceeds the maximum size of of the
VARCHAR2 column, Oracle issues an error.
For Ex, if you define a VARCHAR2 column with a maximum size is 20. In a single-byte
character set, you can store up to 20 characters. If you store 21 characters or more,
Oracle returns an error.

In addition, if you store 10 characters in a VARCHAR2(20) column, Oracle uses only 10

bytes for storage, not 20 bytes. Therefore, using VARCHAR2 data type helps you save
spaces used by the table.

---- Differences: CHAR vs VARCHAR vs VARCHAR2

Let’s take a look at the differences between these three data types.

-VARCHAR and VARCHAR2 are exactly the same. CHAR is different.

-CHAR has a maximum size of 2000 bytes, and VARCHAR/VARCHAR2 has a maximum
size of 4000 bytes (or 32,767 in Oracle 12c)
-CHAR does not need a size specified and has a default of 1. A size needs to be
specified with VARCHAR/VARCHAR2 columns.
-CHAR will pad spaces to the right of strings to match the length of the column, while
VARCHAR/VARCHAR2 does not.

---- What is difference between Varchar and Varchar2 ?

Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of
memory space. Varchar will occupy the space for null values whereas varchar2 can
not occupy the space for null values. So varchar2 is good to use not to face
performace related problems.varchar2 is faster than varchar datatype.

Number data type :

The Oracle NUMBER data type is used to store numeric values that can be negative
or positive.
syntax : NUMBER (precision)

--Ex- create teable test (sno number(10));

Syntax: NUMBER (precision , scale)

--Ex- create table test (sno number(10), salary number (10,2));

NOTE:- Whenever we are using number datatype with any column then we are not
allowed to insert values more than (p - s) numbers of digits before decimal point.
--Ex- number (p,s) => number (7,2)
7-2= 5
Insert into test values (12345.6789); --------valid insert value

Insert into test values (1234567.12); --------invalid insert value

ERROR: Value larger than specified precision allowed for this column.

Insert into test values(12345.67) ---------this is right

The precision is the number of digits in a number. It ranges from 1 to 38.

The scale is the number of digits to the right of the decimal point in a number. It
ranges from -84 to 127.
If you don’t specify the precision, the column can store values including fixed-point
and floating-point numbers. The default value for the scale is zero.

Date datatypes :

[Link] : The DATE data type allows you to store point-in-time values that include
both date and time with a precision of one second. The DATE data type stores the
year, the month, the day, the hours, the minutes, and the seconds.

The standard date format for input and output is DD-MON-YY e.g., 01-JAN-17 which
is controlled by the value of the NLS_DATE_FORMAT parameter.

The following statement shows the current value of the NLS_DATE_FORMAT

parameter:

SELECT * FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT';

In our Oracle Database system, the value of NLS_DATE_FORMAT is:

DD-MON-RR The following statement returns the current date with the standard
date format by using the SYSDATE function.

SELECT sysdate FROM dual;

01-AUG-17
Suppose, you want to change the standard date format to YYYY-MM-DD, you use the
ALTER SESSION statement to change the value of the NLS_DATE_FORMAT parameter
as follows:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

TIMESTAMP : The TIMESTAMP data type allows you to store date and time data
including year, month, day, hour, minute and second. In addition, it stores the
fractional seconds, which is not stored by the DATE data type.
Syntax: column_name TIMESTAMP[(fractional_seconds_precision)]
**** COMMANDS –SQL STATEMENTS-

Diff Bitt DDL and DML

Diff Bitt DBMS and RDBMS

1. Data Definition Language (DDL):-
CREATE
ALTER
DROP
TRUNCATE

[Link] :- T o create a new table (Any DB Object) in Oracle Database, you use the
CREATE TABLE statement. The following illustrates (explain)the basic syntax of the
CREATE TABLE statement:

CREATE TABLE table_name ( col1 data_type,col2 data_type, ... );

--Ex- create table test (sno number(10), sname varchar2 (20));

SELECT * FROM test;

NOTE: that you must have the CREATE TABLE system privilege to create a new
table in your schema and CREATE ANY TABLE system privilege to create a new
table in another user’s schema.

Naming Rules while using CREATE statement

Rules to follow before specifying names(Object names, Column Names and Variable
Names).
i) Each name should begins alphabet
ii) Valid character set is a-z,A-Z,0-9,@,$,# and _ (underscore)
Ex: Emp123 , Emp_o11
iii) Names are not case sensitive
iv) Already existed names are not allowed
v) Pre defined keywords (ReservedWords) are not allowed as names.
vi) Blankspace within a name is not allowed
vii) Max length of any DB object name is 32 chars

[Link] :-
To modify the structure of an existing table, you use the ALTER TABLE statement. The
following illustrates the syntax:
ALTER

ADD MODIFY DROP RENAME

The ALTER TABLE statement allows you to:

 Add one or more columns
 Modify column definition
 Drop one or more columns
  Rename columns
 Rename table

** To add a new column to a table :

ALTER TABLE table_name ADD col1 datatype constraint;

--Ex-
To add multiple columns to a table at the same time
ALTER TABLE table_name ADD
(col1 datatype constraint, col2 datatype constraint, ... );

** To modify the attributes of a column :

ALTER TABLE table_name

MODIFY col1 datatype constraint_name;

To modify multiple columns, you use the following syntax:

ALTER TABLE table_name MODIFY

(col1 datatype constraint, col2 datatype constraint, ... );

** To drop an existing column from a table :

ALTER TABLE table_name

DROP COLUMN column_name;

To drop multiple columns at the same time, you use the syntax below:

ALTER TABLE table_name

DROP (column_1,column_2,...);

** To rename a column as follows:

ALTER TABLE table_name
RENAME COLUMN Old_column_name TO new_column_name;

** To give a table a new name :

RENAME table_name TO new_name;

NOTE:- When you rename a table, Oracle automatically transfers indexes,

constraints, and grants on the old table to the new one. In addition, it invalidates
all objects that depend on the renamed table such as views, stored procedures,
function, and synonyms.
[Link] :-To move a table to the recycle bin or remove it entirely from the database,
you use the DROP TABLE statement:

DROP TABLE table_name [CASCADE CONSTRAINTS | PURGE];

----- Recycle Bin

Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped
[Link] any table in Oracle 10g is dropped then any associated objects to this table
such as indexes,constraints and other dependant objects are simply renamed with a
prefix of BIN$$.

A user drops a very important table--accidentally, of course--and it needs to be

revived as soon as possible so we need recyclebin.

Restore dropped object : - FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;

Enable/Disable Recycle Bin

ALTER SESSION SET recyclebin = OFF/ON;

ALTER SYSTEM SET recyclebin = OFF / ON;

Show recycle bin- SELECT * FROM USER_RECYCLEBIN;

DROP TABLE test ---drop one table

SELECT * FROM USER_RECYCLEBIN; --- check recycle bin
FLASHBACK test TO BEFORE DROP; ---- Restore table from recylebin
SELECT *FROM test ---- table restored

It is possible to restore the table in to different name by using the following SQL
Command,

FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New
Table Name >>;

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST_1;

[Link] :-
When you want to delete all data from a table, you use the DELETE statement
without theWHERE clause as follows:

DELETE FROM table_name;

For a table with a small number of rows, the DELETE statement does a good job.
However, when you have a table with a large number of rows, using the DELETE
statement to remove all data is not efficient.

Oracle introduced the TRUNCATE TABLE statement that allows you to delete all rows
from a big table.

Syntax: TRUNCATE TABLE table_name [CASCADE]

By default, to remove all rows from a table, you specify the name of the table that
you want to truncate in the TRUNCATE TABLE clause:

TRUNCATE TABLE table_name;

[Link] COLUMN :-
A virtual column is a table column whose values are calculated automatically using
other column values, or another deterministic expression.
Syntax: column_name [data_type] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

This statement shows how to define a virtual column in the CREATE TABLE
statement:

CREATE TABLE table_name (...,virtual_column_name AS (expression));

--Ex- create table test (sno number(10), sname varchar2(20),

marks number(10), percentage AS (marks/6) )

And this statement illustrates how to add a virtual column to an existing table using
the ALTER TABLE statement:

ALTER TABLE table_name ADD (virtual_column_name AS (expression));

alter table test add (grade as (case when marks <=300 then ‘C-grade’
when marks >300 and marks <=then 500 then ‘B-grade’
marks <=600 then ‘A-grade’ end));

To show virtual columns of a table, you query from the all_tab_cols view:

SELECT column_name, virtual_column, data_default FROM all_tab_cols

WHERE owner = '<owner_name>' AND table_name = '<table_name>';

You can see the virtual column calculation in DATA_DEFAULT column after execution
above query.

** Restrictions on virtual column :-

1). Indexes defined against virtual columns are equivalent to function-based indexes.
2). Virtual columns can be referenced in the WHERE clause of updates and deletes,
but they cannot be manipulated by DML.
3). Tables containing virtual columns can still be eligible for result caching.
4). Virtual columns are not supported for index-organized, external, object, cluster,
or temporary tables.
5). virtual column definition cannot refer to another virtual column by name.
6). virtual column definition can only refer to columns defined in the same table.

2. Data Manipulation Language (DML)

INSERT
UPDATE
DELETE

[Link] / INSERT INTO SELECT /INSERT ALL:-

This will be used to insert the records into table.

We have two methods to insert.

---- By value method
---- By address method

a) USING VALUE METHOD

Syntax: insert into table_name values (value1, value2, value3 …. Valuen);

--Ex- create table students (sno number(10), sname varchar2(20));

insert into students (1,’Vitthal’);

To insert a new record again you have to type entire insert command, if there are lot
of records this will be difficult. This will be avoided by using address method.

b) USING ADDRESS METHOD

Syntax: insert into <table_name> values (&col1, &col2, &col3 …. &coln);

This will prompt you for the values but for every insert you have to use forward slash.
--Ex- insert into students values (&no, '&sname');

To insert a new row into a table, you use the Oracle INSERT statement as follows:

INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD

Syntax: insert into <table_name>(col1, col2, col3 … Coln) values (value1, value2,
value3 …. Valuen);

--Ex- insert into students (sno) values (2);

Sometimes, you want to select data from a table and insert it into another table. To
do it, you use the Oracle INSERT INTO SELECT statement as follows:

INSERT INTO target_table (col1, col2, col3)

SELECT col1, col2, col3 FROM source_table WHERE condition;

The Oracle INSERT INTO SELECTstatement requires the data type of the source and
target tables match.

If you want to copy all rows from the source table to the target table, you remove the
WHERE clause. Otherwise, you can specify which rows from the source table should
be copied to the target table.

--Ex- create table test (empno number(10), ename varchar2(20),sal number);

Insert into test (empno,ename,sal) select empno,ename,sal from emp;

Insert into test (empno,ename,sal) select empno,ename,sal from emp where

deptno=&number;

Insert into test (empno,ename,sal) select empno,ename,sal*12 from emp;

INSERT ALL statement

** Insert multiple rows into a table :-
To insert multiple rows into a table, you use the following Oracle INSERT ALL
statement:

INSERT ALL
INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
SELECT * FROM dual;

** Insert multiple rows into multiple tables :-

Besides inserting multiple rows into a table, you can use the INSERT ALL statement to
insert multiple rows into multiple tables as shown in the following syntax:

INSERT ALL
INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
SELECT * FROM dual;

[Link] :-
To changes existing values in a table, you use the following Oracle UPDATE
statement:

UPDATE table_name SET col1 = value1, col2 = value2, col3 = value3,…

WHERE condition;

update multiple columns of a single row

--Ex-
update emp set sal=900,ename=‘VITTHAL’,job=‘CEO’ where empno=7369;

--- If you need to update a column in one table based on the values in another,
In my Ex I have emp_1 table which is having empno,ename,sal columns as emp table
having
Create table emp_1 as select empno,ename,sal from emp;

first add that column to your dummy table emp_1 as follows:-

Alter table emp_1 add comm number (10);
Now populate comm column data in dummy table emp_1 from original table emp.

UPDATE emp_1
SET comm = (SELECT comm FROM emp WHERE empno = emp_1.empno);

--- Multi-row update using select

UPDATE emp_1
SET (salary,hiredate,projno) = (SELECT MAX(salary),sysdate,projno FROM emp_1)
where empno like '74%';

--- Update a full select statement-

--If my table added with new column emp_time and if you want to update that
column then use below query-

UPDATE (SELECT * FROM emp1 WHERE empno = 7369 AND deptno = 20)
SET emp_time = 20;

[Link] :- Delete one or more rows from a table, you use the DELETE statement:

DELETE FROM table_name WHERE condition;

DELETE FROM emp; --- delete all rows of table

--Delete duplicate data Correlated query delete (1 of 2)

DELETE FROM emp e1 WHERE [Link] NOT IN (SELECT MAX(empno)FROM emp e2
WHERE [Link] = [Link]);
Delete cascade :- In practice, you often delete a row from a table which has a foreign
key relationship with rows from other tables.
However, this is unnecessary if you know how to setup table’s constraint correctly.

In this case, when you create the order_items table, you define a foreign key
constraint with the DELETE CASCADE option as follows:

CREATE TABLE order_items (order_id NUMBER( 12, 0)

CONSTRAINT fk_order_items_orders FOREIGN KEY( order_id )
REFERENCES orders( order_id ) ON DELETE CASCADE);

By doing this, whenever you delete a row from the orders table, for Ex:

DELETE FROM orders WHERE order_id = 1;

All the rows whose order id is 1 in the order_items table are also deleted
automatically by the database system.

***Difference between Truncate,Delete and Drop:-

Truncate Delete Drop

Truncate is DDL Command Delete is DML Command Drop is also DDL Command

DELETE is executed using a

Truncate is executed using
row lock, each row in the The DROP command removes
table lock. Whole table is
table is locked for deletion. a table from the database.
locked while removing the
records.

We can use where clause with

We cannot use Where DELETE to filter & delete We cannot use Where clause
clause with TRUNCATE. specific records. with Drop.

The DELETE command is

used to remove rows from a All the tables’ rows, indexes
TRUNCATE removes all table based on WHERE and privileges will also be
rows from a table. condition. removed.

Minimal logging in
transaction log, so it is It maintains the log, so it It maintains the log, so it
performance wise faster. slower than TRUNCATE. slower than TRUNCATE.

Truncate cannot be rolled User can roll back the deleted

The operation cannot be
back. data before committing it.
rolled back.
-truncate free the memory -dele not free the memory
space of table space of that table
  Why truncate is faster than delete
Ans- truncate not store log file information
Where delete maintening the log file details

Q. How to find out the table was last modified in oracle

select INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP
from dba_tab_modifications
where TABLE_NAME = 'EMP'
and TABLE_OWNER = 'HR';

3. Data Control Language (DCL)

Data Control Language(DCL) is used to control privileges in Database. To perform any
operation in the database, such as for creating tables, sequences or views, a user
needs privileges. Privileges are of two types,

System: This includes permissions for creating session, table, etc and all types of
other system privileges.
Object: This includes permissions for any command or query to perform any
operation on the database tables.
In DCL we have two commands,

GRANT: Used to provide any user access privileges or other priviliges for the
database.
REVOKE: Used to take back permissions from any user.

Allow a User to create table : To allow a user to create tables in the database, we
can use the below command,

GRANT CREATE TABLE TO username;

Grant all privilege to a User : sysdba is a set of priviliges which has all the
permissions in it. So if we want to provide all the privileges to any user, we can simply
grant them the sysdba permission.

GRANT sysdba TO username ;

Grant permission to create any table : Sometimes user is restricted from creating
come tables with names which are reserved for system tables. But we can grant
privileges to a user to create any table using the below command,

GRANT CREATE ANY TABLE TO username

Grant permission to drop any table

As the title suggests, if you want to allow user to drop any table from the database,
then grant this privilege to the user,

GRANT DROP ANY TABLE TO username

To take back Permissions

And, if you want to take back the privileges from any user, use the REVOKE
command.

REVOKE CREATE TABLE FROM username

[Link] Control Language (TCL)

TCL commands deals with the transaction within the database. Transaction Control
Language can be defined as the portion of a database language used for maintaining
consistency of the database and managing transactions in database.

COMMIT– commits a Transaction.

ROLLBACK– rollbacks a transaction in case of any error occurs.
SAVEPOINT–sets a savepoint within a transaction.
SET TRANSACTION–specify characteristics for the transaction.

1). Commit
The main use of Commit command is to make the transaction permanent. If there is a
need for any transaction to be done in the database that transaction permanent
through commit command.
Syntax : COMMIT;

--Ex- UPDATE STUDENT SET STUDENT_NAME = ‘Maria’

WHERE STUDENT_NAME = ‘Meena’;

COMMIT;

By using the above set of instructions, you can update the wrong student name by
the correct one and save it permanently in the database.

2). Rollback
Using this command, the database can be restored to the last committed state.
Additionally, it is also used with savepoint command for jumping to a savepoint in a
transaction.
Syntax : Rollback to savepoint-name;

--Ex UPDATE STUDENT SET STUDENT_NAME = ‘Manish’

WHERE STUDENT_NAME = ‘Meena’;

ROLLBACK;
This command is used when the user realizes that he/she has updated the wrong
information after the student name and wants to undo this update.

3). Savepoint
The main use of the Savepoint command is to save a transaction temporarily. This
way users can rollback to the point whenever it is needed.
Syntax : savepoint savepoint-name;

--Ex- Following is the table of a school class

Use some SQL queries on the above table and then watch the results
INSERT into CLASS VALUES (101, ‘Rahul);
Commit;
UPDATE CLASS SET NAME= ‘Tyler’ where id= 101
SAVEPOINT A;
INSERT INTO CLASS VALUES (102, ‘Zack’);
Savepoint B;
INSERT INTO CLASS VALUES (103, ‘Bruno’)
Savepoint C;

Select * from Class;

The result will look like

Now rollback to savepoint B

Rollback to B;

SELECT * from Class;

Now rollback to savepoint A

rollback to A;

SELECT * from class;

[Link] Retrival Language(DRL)/Data Query Language (DQL)

SELECT

[Link] Statement
To retrieve data from one or more columns of a table, you use the SELECT statement
with the following syntax:

SELECT column_1, column_2, ... FROM table_name;

Note - the SELECT statement is very complex that consists of many clauses such as
ORDER BY, GROUP BY, HAVING, JOIN.

A) query data from a single column

To get the customer names from the employees table

SELECT ename FROM emp;

B) Querying data from multiple columns

To query data from multiple columns

SELECT ename, job, salary FROM emp;

C) Querying data from all columns of a table

The following Ex retrieves all rows from all columns of the emp table:

Select empno,…..deptno from emp;

To make it handy, you can use the shorthand asterisk (*) to instruct Oracle to return
data from all columns of a table as follows:

Select * from emp;

a table may have more or fewer columns in the future due to the business changes. If
you use the asterisk (*) in the application code and assume that the table has a fixed
set of columns, the application may either not process the additional columns or
access the removed columns.

**** column alias

To better describe the data displayed in the output, you can substitute a column alias
for the column name in the query results.
Instead of using ename, you might want to use ‘Full Name’ for display names of emp.

Select ename AS full_name from emp;

The “AS” keyword is used to distinguish between the column name and the column
alias. Because the AS keyword is optional, you can skip it as follows:

Select ename full_name from emp;

If you want to change the letter case of the column heading, you need to enclose it in
quotation marks (“”).

Select ename “full_name” from emp;

If the column alias consists of only one word without special symbols like space, you
don’t need to enclose it in quotation marks. Otherwise, you must enclose the column
heading in quotation marks or you will get an error.

Select ename full name from emp; ---- Invalid query

Select ename “full name” from emp; ---- valid query

Besides making the column headings more meaningful, you can use the column alias
for an expression, for Ex:

Select first_name || ‘ ‘ || last_name from employees;

we concatenated the first name, space, and the last name to construct the full name
using concate (||) operator.

Select first_name || ‘ ‘ || last_name as “full name” from employees;

Similarly select ename, sal, sal*12 as “Annual_sal” from emp;

**** WHERE clause

The WHERE clause specifies a search condition for rows returned by the SELECT
statement. The following illustrates the syntax of the WHERE clause:
Syntax:- SELECT col1,col2,.. FROM table_name WHERE search_condition

Besides the SELECT statement, you can use the WHERE clause in the DELETE or
UPDATE statement to specify which rows to update or delete.
--Ex- select * from emp where deptno=10;

** Operators used in Select Statement:-

Oracle provides you with many other comparison operators illustrated in the
following table Arathmatic, relational, logical operators :

Operator Description
= Equality
!= , <> Inequality
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
AND For a row to be selected all the
specified conditions must be true.
OR For the row to be selected at least
one of the conditions must be true.
NOT For a row to be selected the
specified condition must be false.
--Ex- WAQ to display the employees except JOB as CLERK from emp table.
Select * from emp where job <> ‘CLERK’;

--Ex- WAQ to display employee who are getting more than 2000 sal
Select * from emp where sal > 2000;

--Ex- WAQ whose is CLERK and having sal more than 2000 from emp.
Select * from emp where job=‘CLERK’ and sal > 2000;

--Ex- WAQ whose job is CLERK or sal more than 2000 from emp.
Select * from emp where job=‘CLERK’ or sal > 2000;

--Ex –WAQ whose job is CLERK or SALESMAN and sal is more than 2000 from emp
table.
Select * from emp where (job=‘CLERK’ or job=‘SALESMAN’) and sal > 2000;
--Ex- WAQ to display employees details from deptno 10,20,30;
 select * from emp where deptno=10 or deptno=20 or deptno=30;

NOTE- Whenever we using AND operator then database server filter the data from
result set.
Whenever we using OR operator then database return the data based on each and
every individual condition from table.

*** NULL :-
Null value is a special value which defines Nothing or you can say it as ‘No Value’.
If there are null values in SQL then user will find wrong results [Link] counting
null values are very confusing.

In so many interview question you will find out the different scenarios with Null in
[Link] most basic scenario i will explain so that user can get information about the
interview questions related to Null in SQL.

Create table Stud1(Roll_No Number(10));

Insert into Stud values (1);

Insert into Stud values(2);
Insert into Stud values(null);
commit;

Select * from Student where roll_no != 1;

o/p:- 2
Select * from Student where roll_no != 1 or roll_no is null;
o/p:- 2, null
Select * from Student where roll_no != 1 or roll_no=null;
o/p:- 2
Select * from Student where roll_no != null;
o/p:- zero/blank
Select * from Student where roll_no is not null;
o/p:- 1,2

In all DB whenever we are using arithmatic operation and NULL value then it will
become NULL;

---Ex- NULL+50 = NULL

--Ex- WAQ to display ename,sal,comm,sal+comm from emp table where ename is

‘SMITH’.

Select ename,sal,comm,sal+comm from emp where ename=‘SMITH’;

 ename sal comm Sal+comm
SMITH 2200 null null

To overcome this problem oracle provided NVL () function.

** NVL () :-
NVL() is function which is used to replace (or) substitute user_define value in place of
nulll value.

Syntax:- NVL (exp1 , exp2)

Here exp1 ,exp2 must belong to same datatype.
If exp1 evaluates to null, then NVL() function returns exp2. If exp1 evaluates to
not-null value, the NVL() function returns exp1
--Ex- NVL (null, 20)  20
NVL (10, 20)  10
NVL (1, ‘A’)  Datatype mismatch error/datatype must be same

Select ename,sal,comm,sal+nvl(comm,0) from emp where ename='SMITH';

Ename sal comm Sal+comm

SMITH 800 null 800

** NVL2 () :-
The Oracle NVL2() function is an extension of the NVL() function with different
options based on whether a NULL value exists.
The Oracle NVL2() function accepts three arguments. If the first argument is not null,
then it returns the second argument. In case the second argument is null, then it
returns the third argument.

Syntax:- NVL2 (e1, e2, e3)

In this syntax, the first argument e1 can be a value of any data type. The second and
third arguments can be values of any data types but should be same datatype.
--Ex- NVL2 (NULL, 10, 20);  20
NVL (10, null, null);  null
NVL2 (10, 20, 30);  20
NVL2 (null,null,null)  null
NVL2 (1,’ABC’,’XYZ’)  ABC
NVL2(1, 222, 'XYZ')  e2 and e3 are must be same datatype

---Ex- WAQ to show total sal (sal+comm) from emp table.

SELECT empno, comm, sal, NVL2(comm, sal + comm, sal)"Total_sal" FROM emp;
** NULLIF() :
The Oracle NULLIF() function accepts two arguments. It returns a null value if the two
arguments are equal. In case the arguments are not equal, the NULLIF() function
returns the first argument.

Syntax: NULLIF(e1, e2);

In this syntax, the e1 cannot be a NULL.

If both expressions evaluates to numeric values, then Oracle determines the

argument with the higher numeric precedence, implicitly converts the other
argument to that data type, and returns a value of that data type.

In case both expressions evaluate to non-numeric values, then they must be of the
same data type, otherwise, Oracle issues an error.

first argument equals the second one output will be null

SELECT NULLIF(100,100) FROM dual;
o/p- null

SELECT NULLIF ('vitthal','vitthal') FROM DUAL ;

O/P- NULL

two arguments are different output will be first expression.

SELECT NULLIF(100,200) FROM dual;
o/p- 100
SELECT NULLIF ('vitthal','poonam') FROM DUAL ;
o/p- vitthal

SELECT NULLIF(NULL,100) FROM dual;

o/p- ERROR: inconsistent datatypes: expected NUMBER got CHAR

SELECT NULLIF(100,NULL) FROM dual;

o/p- 100

SELECT NULLIF(10,'20') FROM dual;

Error: inconsistent datatypes: expected NUMBER got CHAR

Difference between NVL, NVL2, NULLIF :

NVL NVL NULLIF

It has two parmeters It has tree parameters It has two parameters
Compare two expr and if decide which value to Compares two
expr1 is null then it return, based on whether a expressions and returns
replace NULL value with specified expression is null null if they are
expr2 value or not. equal,returns the first
expression if they are
not equal.
NVL(EXPR1,EXPR2). NVL2(expr1,expr2, expr3) NULLIF(EXPR1,EXPR2)
Output comes out from Output comes out from Output comes out from
source string specified strings in source string otherwise
exp2,exp3 null value

*** Special Operators:

IN NOT IN
BETWEEN NOT BETWEEN
IS NULL IS NOT NULL
LIKE NOT LIKE

[Link] / NOT IN :-
The Oracle IN operator determines whether a value matches any values in a list.
Generally we also use IN operator in place of OR operator.
NOT IN exclude the values which are in list.

Syntax:- select * from table_name where column_name IN (value1,value2,...)

--Ex- select * from emp where deptno IN (10,20,30);

Select * from emp where ename IN (‘SMITH’, ‘ALLEN’, ‘MILLER’);

Select * from emp where deptno IN (null, 10, 20);

Select * from emp where deptno NOT IN (10, 20);

Select * from emp where deptno NOT IN (null,10, 20);

NOTE:- In all DB “NOT IN” operator doesn’t work with NULL values.
2. BETWEEN / NOT BETWEEN :-
The BETWEEN operator allows you to specify and retrive a range of value. only rows
whose values are in the specified range are returned.
The NOT BETWEEN operator excludes the values which are specify in range.

Syntax :- Select * from table_name where column_name BETWEEN low AND high

--Ex- WAQ to display the employees who are getting sal between 2000-5000.
Select * from emp where sal between 2000 and 5000;
--Ex- WAQ to display the employees who are not getting sal bet 2000-5000
Select * from emp where sal not between 2000 and 5000;

--Ex- WAQ to dipaly records from '1981-12-01' to DATE '1981-12-31'

Select * from emp where hiredate BETWEEN '1981-12-01' AND
DATE '1981-12-31'

In NOT BETWEEN operator sal is equal to low range value or high range value then
those matched values are excluded from output.
In BETWEEN operator if sal is equal to low range value or high range value then it will
include that in output.

[Link] NULL / IS NOT NULL :-

IS NULL and IS NOT NULL operators to check if a value in a column or an expression is
NULL or not.
NULL is special in the sense that it is not a value like a number, character string, or
datetime, therefore, you cannot compare it with any other values like zero (0) or an
empty string (”). Generally speaking, NULL is even not equal to NULL.

Syntax:- select * from table_name where column_name is null/is not null ;

--Ex- WAQ to disaplay employees details those who are not getting commission.
Select * from emp where comm is null;

--Ex- WAQ to disaplay those employees details who are getting commission.
Select * from emp where comm is not null;

[Link] / NOT LIKE :-

LIKE operator to test whether values in a column match a specified character pattern.
Along with LIKE operator we are using two special operators these are ‘%’ ,’_’.
LIKE operator is case sensative.

Syntax:- select * from table_name where column_name like ‘character_pattern’;

select * from table_name where column_name not like ‘character_pattern’;

--Ex- WAQ to display employee details whos name start with ‘M’ from emp table.
Select * from emp where ename like ‘M%’ ;

--Ex- WAQ to display employees whose name having ‘M’ in any position within
ename column from emp table.
Select * from emp where ename like ‘%M%’ ;

--Ex- WAQ to display employees names whoes names 4th letter is ‘M’ from emp table
Select * from emp where ename like ‘_ _ _M%’ ;
--Ex- WAQ to display employees detail who are join in the month of DEC from emp
table.
Select * from emp where hiredate like ‘%DEC%’ ;

--Ex- WAQ to display those employee details who joined in year 81 from emp table
Select * from emp where hiredate like ‘%81’ ;

*** Creating a new table from another table with data:-

CREATE TABLE newtable_name AS SELECT * FROM existing_table_name;

--Ex- create table emp_1 as select * from emp;

Select * from emp_1;
NOTE:- In all DB whenever we are copying a table from another table internally
(primary key, foreign key…) are never copied only not null constraint will be copy.

*** Creating a new table from another table without data:-

CREATE TABLE newtable_name AS SELECT * FROM existing_table_name
WHERE 1=2;

We have given false condition in WHERE clause so data will not copy only structure of
old table will copy.

** Concatination Operator ( || ) :-
|| or concatenation operator is use to link columns or character strings. We can also
use a literal. A literal is a character, number or date that is included in the SELECT
statement.

--Ex- select empno, ename || sal, deptno from emp ;

NOTE: Here above we have used || which is known as Concatenation operator which
is used to link 2 or as many columns as you want in your select query and it is
independent of the datatype of column.

We can also use literals in the concatenation operator.

--Ex- select empno , ename ||' has salary of '|| sal from emp ;

We can use number as literal as well-

--Ex- select empno, ename || 100 || deptno from emp ;

** Dual table
DUAL is a table automatically created by Oracle Database along with the data
dictionary.
DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users.
It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with
a value X. Selecting from the DUAL table is useful for computing a constant
expression with the SELECT statement.
It is best example of public synonyms.

1. SELECT * FROM DUAL;

Output: X

2. To displays the number of rows of DUAL table :

SELECT COUNT(*) FROM DUAL;
Output: 1

3. To displays the string value from the DUAL table :

SELECT 'ABCDEF12345' FROM DUAL;
Output: ABCDEF12345

4. To displays the numeric value from the DUAL table :

SELECT 123792.52 FROM DUAL;
Output: 123792.52

select 1+2 from dual; ---Numeric value

Output: 3

select '1'+’2’ from dual; ---Character value

Output: 3

5. tries to delete all rows from the DUAL table :

DELETE FROM DUAL;
Output: ERROR at line 1: ORA-01031: insufficient privileges

6. tries to remove all rows from dual.

TRUNCATE TABLE DUAL;

Output : ERROR at line 1: ORA-00942: table or view does not exist

7. To select two rows from dual.

SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL;
Output-X
X

8. You can also check the arithmetic calculation from the DUAL table
SELECT 155*5/5 FROM DUAL;
 Output: 20

9. Following code display the numbers 1..10 from DUAL :

SELECT level FROM DUAL CONNECT BY level <=10;

10. In the following code, DUAL involves the use of decode with NULL.
SELECT decode(null,null,1,0) FROM DUAL;
Output: 1

11. DUAL table also use to fetch sequence value like [Link] and nextval.

select rownum ||'* 5 = '||rownum*5 from dual connect by level <=10;

select rownum*5 from dual connect by level<= 10;
select level*5 from dual connect by level<=10;

12. Decode cannot be called directly in the plsql block. to call the decode statement
we need select statement.

SELECT decode(null,null,1,0) FROM DUAL;

o/p: 1

13. to show current username

select user from dual;
Output: HR

--Ex- select length(NULL||'abc') from dual;

o/p:- 3

--Ex- select 'vitthal'||null||'mandlik' from dual;

o/p- vitthalmandlik

--Ex- select length(null) from dual;

o/p:- null

FUNCTIONS
Functions are used to solve particular task.
Functions return values.

Oracle having 2 types of functions-

[Link] functions
[Link] function

[Link] Function:- Predefine functions are 4 types

[Link] function
[Link]/string function
[Link] function
[Link] function (OR) Aggregate function

[Link] Function:-
These function operate over number/integer datatype.

** ABS (): It returns the absolute value of a number. It is used to convert –ve to +ve
number.

--Ex- select abs (-50) from dual;

--Ex- select abs ('a') from dual; ---Invalid identifier.

** sign () : The number to test for its sign.

Returns
If number < 0, then the SIGN function returns -1.
If number = 0, then the SIGN function returns 0.
If number > 0, then the SIGN function returns 1.

--Ex-
select SIGN (-23) , SIGN (-0.001) , SIGN (0) , SIGN (0.001), SIGN (23), SIGN (23.601)
from dual;
SIGN (-23) SIGN(-0.001) SIGN (0) SIGN (0.001) SIGN (23) SIGN (23.601)
-1 -1 0 1 1 1

** mod (value 1, value 2) : It gives reminder after dividation m/n.

--Ex- select mod (10,3) a, mod(10,-3) b, mod(-10,3)c, mod(-10,-3)d from dual;

o/p:
a B c D
1 1 -1 -1

** sqrt ( +value ): This will give the square root of the given value.
In sqrt function value must be positive.

--Ex- select sqrt(4) a, sqrt(0) b, sqrt(null) c, sqrt(1) d from dual;

A b c D
2 0 null 1
** ceil (value) : ceil returns nearest greater integer.
--Ex-
select ceil(5) , ceil(5.1) , ceil(-5) , ceil(-5.1) , ceil(0) , ceil(null) from dual;

Ceil(5) Ceil(5.1) Ceil(-5) Ceil(-5.1) Ceil(0) Ceil(null)

 5 6 -5 -5 0 Null

** floor (value): floor returns nearest lowest integer.

--Ex-
select floor(5) a, floor(5.1) b, floor(-5) c, floor( -5.1) d, floor(0) e, floor(null) f
from dual;
floor(5) floor(5.1) floor(-5) floor(-5.1) floor(0) floor(null)
5 5 -5 -6 0 Null
** ROUND (m, n):
This will rounds numbers to a given number of digits of precision.
Syntax: round (value, precision)

--Ex- select round (1.7) a, round (1.2) b from dual;

a b
2 1

--Ex- select round(123.2345) a, round(123.2345,1) b, round(123.2345,2) c,

round(123.2354, 2) d from dual;
A b c D
123 123.2 123.23 123.24

--Ex- select round(123.2345,-1) a, round(123.2345,-2) b, round(123.2345,-3) c,

round(123.2345,-4) d from dual;
A B c D
120 100 0 0

--Ex- select round(123,0) a, round(123,1) b, round(123,2) c from dual;

A B C
123 123 123

--Ex- select round(-123,0) a, round(-123,1) b, round(-123,2) c from dual;

A B C
-123 -123 -123

--Ex- select round(123,-1) a, round(123,-2) b, round(123,-3) c, round(-123,-1) d,

round(-123,-2) e, round(-123,-3) f from dual;
A B c D E f
120 100 0 -120 -100 0

--Ex- select round(null,null) a, round(0,0) b, round(1,1) c, round(-1,-1) d,

round(-2,-2) e from dual;
a B c D E
null 0 1 0 0

**trunc (m, n) : This will truncates or chops off digits of precision from a number.
Trunc function doesn’t compare with 50% of value.
--Ex- select trunc (1.8) a, trunc (-1.8) b from dual;
a B
1 -1

--Ex- select trunc(123,0) a, trunc(123,1) b, trunc(123,2) c from dual;

a B c
123 123 123

--Ex- select trunc(-123,0) a, trunc(-123,1) b, trunc(-123,2) c from dual;

a B c
-123 -123 -123

--Ex- select trunc(123.2345) a,trunc(123.2345,1) b, trunc(123.2345,2) c,

trunc(123.2345,3) d from dual;
A B c D
123 123.2 123.23 123.234

--Ex- select trunc(123.2345,-1)a, trunc(123.2345,-2) b, trunc(123.2345,-3) c,

trunc(123.2345,-4) d, trunc(123.2345,-0) e from dual;

a B c D E
120 100 0 0 123

--Ex- select trunc(123,-1) a, trunc(123,-2) b, trunc(123,-3) c, trunc(-123,-1) d,

trunc(-123,2) e, trunc(-123,-3) f from dual;
a B c d E F
120 100 0 -120 -123 0

--Ex- select trunc(null,null) a, trunc(0,0) b, trunc(1,1) c, trunc(-1,-1) d, trunc(-2,-2) e

from dual;
a B c d E
null 0 1 0 0

** greatest (value1,value2,value3,….n) : This will give the greatest number from list
of values.
--Ex- select greatest(1, 2, 3) a, greatest(-1, -2, -3) b from dual;
a B
3 -1
--Ex select greatest (0,0,0) a, greatest (null,null,null) b, greatest (1,2,null) c
From dual;
a B c
0 null null

 If all the values are zeros then it will display zero.

 If all the parameters are nulls then it will display nothing.
 If any of the parameters is null it will display nothing.
--Ex-
select ename,sal,comm,greatest (sal,comm) from emp where comm is not null;

** least (value1,value2,value3,….n): This will give the least number from list of
values.

--Ex- select least(1, 2, 3), least(-1, -2, -3) from dual;

a B
1 -3

--Ex- select least (0,0,0) a, least (null,null,null) b, least (1,2,null) c From dual;
a b c
0 null null

 If all the values are zeros then it will display zero.

 If all the parameters are nulls then it will display nothing.
 If any of the parameters is null it will display nothing.

** coalesce (value1, value2, value3 … valuen):

This will return first not-null value from the given list of values.

--Ex- select coalesce(1,2,3) a, coalesce(null,2,null,5) b from dual;

a b
1 2

[Link] / string Function:

** initcap ( ) : This will capitalize the initial letter of the string.

Syntax: initcap (string)

--Ex- select initcap('vitthal mandlik') from dual;

--Ex- select initcap(ename), sal from emp;
--Ex- select initcap (‘@p#m$dm:pi_z’) from dual;
o/p:- @P#M$Dm:Pi_Z
** upper ( ) : This will convert the string into uppercase.
Syntax: upper (string)

--Ex- select upper('vitthal mandlik') from dual;

--Ex- select uppder (ename), sal from emp;

** lower ( ) : This will convert the string into lowercase.

Syntax: lower (string)

--Ex- select lower('VITTHAL MANDLIK') from dual;

--Ex- select lower (ename) from emp;

** length ( ) : This will give total length of the string/number including spaces.
Syntax: length (string)
--Ex- select length('vitthal mandlik') from dual;
--Ex- select length(' ') from dual;
--Ex- select length(123456) from dual;

** lpad (string, length [, padding_char]) :

This will allows you to pad the left side of a column with any set of characters.
Default padding character was blank space.

--Ex- select lpad('vitthal',15,'*') a, lpad('vitthal',15,'*#') b from dual;

a B
***********vitthal *#*#*#*#*#*vitthal

--Ex- select lpad (‘ABC’, 2 ,’*’) from dual;

o/p :- AB

NOTE: if we give the parameter length less than string length then it will show the
same output as we given in string but length of that output will be same as
parameter.

** rpad (string, length [, padding_char]) :

This will allows you to pad the right side of a column with any set of characters.
Default padding character was blank space.
--Ex- select rpad('vitthal',15,'*') a, rpad('vitthal',15,'*#') b from dual;
a b
vitthal*********** vitthal*#*#*#*#*#*

--Ex- select rpad (‘ABC’, 2, ‘*’) from dual;

o/p: AB
 NOTE: if we give the parameter length less than string length then it will show
the same output as we given in string but length of that output will be same as
parameter.

** ltrim (string [,unwanted_chars]) :

This will trim off unwanted characters from the left end of string.

--Ex- select ltrim('computer','co') a, ltrim('computer','com') b from dual;

a b
mputer puter
--Ex- select ltrim ('computer') , ltrim (‘computer’, ‘om’) from dual;
o/p : - computer , computer

If you haven’t specify any unwanted characters it will display entire string.

--Ex- select ltrim('computer','co') a, ltrim('c omputer','com') b,

ltrim('comcomputer','com')c ,ltrim('computer','om') d from dual;
a B c d
mputer _omputer Puter computer

** rtrim (string [,unwanted_chars]) :

This will trim off unwanted characters from the right end of string.

--Ex- select rtrim('computer','er') a, rtrim('comput er','ter') b ,

rtrim('computerer','er') c, rtrim('computer','te') d from dual;
a b C d
comput Comput_ comput computer

** trim (unwanted_chars from string) :

This will trim off unwanted characters from the both sides of string.
trim can only accept one character only as an unwanted character.
By default, the TRIM () function removes leading and trailing spaces from a string.

--Ex- select trim (‘ india ‘) from dual; ---removes the space from both side
o/p:- ‘india’

--Ex- select trim( 'i' from 'indiani') a, trim( 'i' from 'iindianii') b,
trim( 'i' from 'i indiani') c from dual;
A b c
Ndian ndian indian

--Ex- select trim( leading 'i' from 'iiindiani') from dual; -- this will work as LTRIM
o/p- ndiani
 NOTE: TRIM will trim the single character and if trim character are repetative then
it will trim consecutively until it get different character as showed in above ex.

--Ex- select trim( leading 'ab' from 'abindiani') from dual;

o/p- error- ORA-30001: trim set should have only one character

--Ex- select trim( trailing 'i' from 'indianiii') from dual; -- this will work as RTRIM
o/p- indian

--Ex- select trim ('ab' from 'abindiai') from dual;

o/p- error- ORA-30001: trim set should have only one character

** translate (string, old_chars, new_chars) :

This will replace the set of characters, character by character.
In translate all three parameter are mandatory, if you skip any of it and try to execute
with two parameter it will through error.
Character value possible to transalate into number value.

--Ex- select translate('india','in','xy') a, translate ('india','i',' ') b,

translate ('india','dia',1) c from dual;
A B c
xydxa _nd_a n1

--Ex- select translate(00100,0,'XY') a, translate (123.456,'.',',') b,

translate ('India','d',null) c from dual;
A B C
1XX 123,456 Null

** replace (string, old_chars [, new_chars]) :

This will replace the set of characters, string by string.
In replace two first parameter are mandatory, if you skip the 3 rd parameter then it
will replace the value with default “Blank Space”.
Character value possible to replace into number value.
--Ex- select replace('india','in','xy') a, replace('india','in') b , replace ('india','dia',1) c
from dual;
a B c
xydia dia in1

--Ex- select replace('india','in',12) a, replace('india','nd',null) b from dual;

A B
12dia Iia

** substr (string, start_chr_count [, no_of_chars]) :

[,no_of_chars] parameter must be positive.
SUBSTR function returns the substring from a string starting from the specified
position and having the specified length (or until the end of the string, by default if
3rd parameter is not mentioned).
In substr 2 parameter are mandatory and one is optional.

--Ex- select substr('computer',2) a, substr('computer',2,5) b,

substr('computer',3,7) c from dual;
a b c
omputer omput mputer

--Ex- select substr('computer',-4,2) a, substr('computer',-9,2) b,

substr('computer',-4) c from dual;
a b c
ut null uter

--Ex- select substr('computer',0,0) a, substr('computer',null,0) b,

substr('computer',9,3) c , substr('computer',0,null) d from dual;
a b c d
null null null null

 If no_of_chars parameter is negative then it will display nothing.

 If both parameters except string are null or zeros then it will display nothing.
 If no_of_chars parameter is greater than the length of the string then it ignores
and calculates based on the orginal string length.
 If start_chr_count is negative then it will extract the substring from right end.

** instr (string, search_str [, start_chr_count [, occurrence] ]) :

This will allows you for searching through a string for set of characters.
INSTR function returns the position of a substring in a string, and allows you to
specify the start position and which occurrence to find.

If start_chr_count is +ve integer then search will start from left side of string.
If start_chr_count is –ve integer then search will start from right side of string

--Ex- select instr('information','o',4,1) a, instr('information','o',4,2) b,

instr('information','o',-4,2) c , instr('information','o',-4,1) d from dual;
a b c d
4 10 0 4

--Ex- select instr (‘ABC*D’,’*’) from dual ;

o/p :- 4
print o/p left to right
--Ex- select (‘ABCDEFGHCDIJKLMCDMNP’, ‘CD’, -6, 2) from dual;
 searching from right to left
12 3 -6 -5 -4 -3 -2 -1
ABCDEFGHCDIJKL C D M N P
o/p :- 3

--Ex- select (‘AB*CD*EF’, ‘*’, -3, 1) from dual;

o/p: 6

** greatest (strng1, string2, string3 … stringn) :

This will give the greatest string.

--Ex- select greatest('a', 'b', 'c') a, greatest('satish','srinu','saketh')b from dual;

a B
c srinu
 If all the parameters are nulls then it will display nothing.
 If any of the parameters is null it will display nothing.

** least (strng1, string2, string3 … stringn)

This will give the least string.

--Ex- select least('a', 'b', 'c') a, least('satish','srinu','saketh') b from dual;

A B
A saketh
 If all the parameters are nulls then it will display nothing.
 If any of the parameters is null it will display nothing.

** coalesce (strng1, string2, string3 … stringn)

This will gives the first non-null string.

--Ex- select coalesce('a','b','c') a, coalesce(null,'a',null,'b') b from dual;

A B
A A

**Difference between REPLACE and TRUNCATE :

REPLACE TRANSLATE
REPLACE(‘i/p_str’,’find_str’,’replace_str’) TRANSLATE(string, from_str , to_str)
Replaces entire string at a time Replaces character one-to-one basis
Returns string if no match found Returns null if no match found
Ex: select replace ('sql','abc','sequel') as Ex: select translate('sqlandplsql','sql','')
replace_string from dual; as str from dual;
o/p- ‘sql’ o/p- null
Difference between SUBSTR and INSTR:

SUBSTR INSTR
It extract a part of the string from the INSTR function extract the position of
whole source string. the string from source string.
The output datatype of SUBSTR is The output datatype of INSTR is number
number for numeric input and character irrespective of the datatype of the input
for date and character input
If the start position of SUBSTR is greater If the start position of the INSTR
than the total length of the input, the function is greater than the total length
output will be returned as NULL of the input, the output will be returned
as 0
In SUBSTR function if the start position is In INSTR function if the start position is
passed as 0 (zero) then by default the passed as 0 (zero) then output is
start position is taken as 1 returned as 0 (zero)
If the “length” argument is not passed, If the “appearance” argument is not
then the output will be the whole input passed, then by default value 1 is
from the start position considered for the appearance of the
string pattern

Difference between ROUND and TRUNC:

ROUND TRUNC
ROUND function rounds the number to a TRUNC used to truncate/delete the
specified number of decimal places number from some position
It compare with 50% of given value and It doesn’t compare given value with its
round the given value based on 50% value.
compairsion result.
It sometime take greater value OR it always takes the lesser value.
sometime take same value.

***Regular Expressions

REGEXP_INSTR - Similar to INSTR except it uses a regular expression rather than a

literal as the search
string.

REGEXP_LIKE - Similar to LIKE except it uses a regular expression as the search string.
REGEXP_LIKE is really an operator, not a function.

REGEXP_REPLACE - Similar to REPLACE except it uses a regular expression as the

search string.
REGEXP_SUBSTR - Returns the string matching the regular expression. Not really
similar to SUBSTR.

REGEXP_COUNT - Returns the number of occurrences of the regular expression in

the string.

Usages of Regular Expression in Oracle:

Validation Purpose:
There are so many scenarios where user needs to check a certain pattern.
Ex: In email validation, check user needs to add only mails, which has ‘@’ symbol.
Avoid sensitive string:
By centralizing the pattern, matching logic user can be able to avoid the sensitive
string.
Avoid duplicate validation logic:
By using the server side regular expression, you can avoid duplicating validation logic.

Search pattern in string:

Regular expressions are used to search the specific pattern from the string.

^ Matches the beginning of a string. If used with a match_parameter of 'm', it

matches the start of a line anywhere within expression.
$ Matches the end of a string. If used with a match_parameter of 'm', it matches
the end of a line anywhere within expression.
. Matches any character except NULL.
* Matches zero or more occurrences.
+ Matches one or more occurrences.
? Matches zero or one occurrence.
| Used like an "OR" to specify more than one alternative.
[] Used to specify a matching list where you are trying to match any one of the
characters in the list.
[^ ] Used to specify a nonmatching list where you are trying to match any character
except for the ones in the list.
() Used to group expressions as a subexpression.
{m} Matches m times.
{m,} Matches at least m times.
{m,n} Matches at least m times, but no more than n times.
\n n is a number between 1 and 9. Matches the nth subexpression found
within ( ) before encountering \n.
[..] Matches one collation element that can be more than one character.
[::] Matches character classes.
[==] Matches equivalence classes.
\d Matches a digit character.
\D Matches a nondigit character.
\w Matches a word character.
\W Matches a nonword character.
\s Matches a whitespace character. (only space included)
\S matches a non-whitespace character.( alpha,num,special char are included)
\Z Matches at the end of a string.
*? Matches the preceding pattern zero or more occurrences.
+? Matches the preceding pattern one or more occurrences.
?? Matches the preceding pattern zero or one occurrence.
{n}? Matches the preceding pattern n times.
{n,}? Matches the preceding pattern at least n times.
{n,m}? Matches the preceding pattern at least n times, but not more than m times.

1). REGEXP_SUBSTR ()

REGEXP_SUBSTR
(source_string, pattern[,start_position[,occurrence[,match_parameter[,subexpr]]]])

The Oracle REGEXP_SUBSTR() function is an advanced version of the SUBSTR()

function that allows you to search for substrings based on a regular expression.
Instead of returning the position of the substring, it returns a portion of the source
string that matches the regular expression.

Ex- How to seprate user name and domain name from the email using regexp

select email,regexp_substr(email,'\w.[^@]+',1)user_name,
regexp_substr(email,'[^@]+$',1)domain from test1;

--Ex1- If you want to get the fourth word of the string

SELECT regexp_substr ('This is a regexp demo','\w+', 1, 4) 4th_word FROM dual;

-------(OR)
select regexp_substr ('This is a regexp demo','\w+',1,4) 4Th_word from dual;
-------(OR)
select regexp_substr ('This is a regexp demo','[^ ]+',1,4) "4Th_word" from dual;

o/p- regexp

--Ex- First character of the string

select regexp_substr('This is a regexp_substr demo','\w') FROM dual;
o/p:- T

--Ex2- Extract the year from below table (t1) data.

FALL 2014
2014 CODE-B
CODE-A 2014 CODE-D
ADSHLHSALK
 FALL 2004

SELECT * FROM t1 WHERE TO_NUMBER(REGEXP_SUBSTR(data, '\d{4}')) >= 2014;

--Ex- Given a source string, how do we split it up into separate columns, based on
changes of case and alpha-to-numeric, such that this.
‘ArtADB1234567e9876540’

SELECT REGEXP_SUBSTR(data, '[A-Z][a-z]+', 1, 1) col1,

REGEXP_SUBSTR(data, '[A-Z]+', 1, 2) col2,
REGEXP_SUBSTR(data, '[0-9]+', 1, 1) col3,
REGEXP_SUBSTR(data, '[a-z]+', 1, 2) col4,
REGEXP_SUBSTR(data, '[0-9]+', 1, 2) col5
FROM t1;

COL1 COL2 COL3 COL4 COL5

--------------- --------------- --------------- --------------- ---------------
Art ADB 1234567 e 9876540

--Ex- We need to pull out a group of characters from a "/" delimited string, optionally
enclosed by double quotes. The data looks like this in table.

978/955086/GZ120804/10-FEB-12
97/95508/BANANA/10-FEB-12
97/95508/"APPLE"/10-FEB-12

SELECT REGEXP_SUBSTR(data, '[^/"]+', 1, 3) AS element3 FROM t1;

--Ex- How to extract string from source.

Below query will extract the first word/string of sentence.

select regexp_substr ('Surya is,very [Link]','\w+',1,1)"First_word" from dual;

o/p- ‘Surya’

-- Below query will extract the second word/string of sentence.

select regexp_substr ('Surya is,very [Link]','\w+',1,2) "Second_word"from

dual;
o/p- ‘is’

NOTE- if your sentence having any special character then it will difficult the
query to find out exact word string from the source so use carot ^ in bracket to
negate the special characters.
--Ex- How to mask mobile number (dynamicaly). First 2 digit and last 2 digit will be
display and rest of digit will replace by “*”.

select phone_number, rpad(regexp_substr (phone_number,'^\d{2}'),

length (regexp_substr (phone_number,'\d(.*)'))-2,'*')||
regexp_substr (phone_number,'\d{2}$')
from employees;

--Ex- How to mask email of employees. (First letter and last letter will display).

select first_name,email, rpad(regexp_substr (email,'^\w{1}'),

length (regexp_substr (email,'\w(.*)'))-1,'*')||regexp_substr (email,'\w{1}$')
from employees;

2). REGEXP_INSTR ()
The REGEXP_INSTR() function enhances the functionality of the INSTR()
function by allowing you to search for a substring in a string using a regular
expression pattern.

REGEXP_INSTR(string,pattern, position, occurrence, return_option,

match_parameter);

The REGEXP_INSTR() function evaluates the string based on the pattern and returns
an integer indicating the beginning or ending position of the matched substring,
depending on the value of the return_option argument. If the function does not find
any match, it will return 0.

--Ex- We have a specific pattern of digits (9 99:99:99) and we want to know the
location of the pattern in our data.
1 01:01:01
.2 02:02:02
..3 03:03:03'
We know we are looking for groups of numbers, so we can use "[0-9]" or "\d" or
[[:digit:]]. We know the amount of digits in each group, which we can indicate using
the "{n}" operator, so we simply describe the pattern we are looking for.

SELECT REGEXP_INSTR(data, '[0-9] [0-9]{2}:[0-9]{2}:[0-9]{2}') AS string_loc_1,

REGEXP_INSTR(data, '\d \d{2}:\d{2}:\d{2}') AS string_loc_2 FROM t1;

--Ex- How to know sapce positions from source string.

select 'vitthal bhanudas mandlik'"Name",

regexp_instr('vitthal bhanudas mandlik', '\s+', 1, 1) "First_space",
regexp_instr('vitthal bhanudas mandlik', '\s+', 1, 2) "Second_space",
 regexp_instr('vitthal bhanudas mandlik', '\s+', 1, 3) "third_space" from dual;

Name First_space Second_space Third_space

vitthal bhanudas 5 15 0
mandlik

--Ex- How to know words starting position

select 'vitthal bhanudas mandlik'"Name",

regexp_instr('vitthal bhanudas mandlik', '\w+', 1, 1) "First_word",
regexp_instr('vitthal bhanudas mandlik', '\w+', 1, 2) "Second_word",
regexp_instr('vitthal bhanudas mandlik', '\w+', 1, 3) "third_word" from dual;

Name First_word Second_word Third_word

vitthal bhanudas 1 6 16
mandlik
--Ex- How to know the special characters on which position

select '[Link]' "Name",

regexp_instr('[Link]', '\W', 1, 1) "1_special_char",
regexp_instr('[Link]@@@mandlik', '\W', 1, 2) "2_special_char",
regexp_instr('[Link]@@@mandlik', '\W', 1, 3) "3_special_char",
regexp_instr('[Link]@@@mandlik', '\W', 1, 4) "4_special_char" from
dual;

Name 1_cahr 2_char 3_char 4_char

Vitthal bhanudas 5 14 15 16
mandlik

NOTE: If we mention ‘+’ sign with match parameter ‘\W’ then it will show the
position of string but if we removed it then it will show the position of each
special character in the source string.

--Ex- How to know the special characters on which position

select '[Link]'"Name",
regexp_instr('[Link]', '\W+', 1, 1) "1_special_char",
regexp_instr('[Link]@@@mandlik', '\W+', 1, 2) "2_special_char",
regexp_instr('[Link]@@@mandlik', '\W+', 1, 3) "3_special_char",
regexp_instr('[Link]@@@mandlik', '\W+', 1, 4) "4_special_char" from
dual;

Name 1_char 2_char 3_char 4_char

[Link] 5 14 0 0
 mandlik

3). REGEXP_REPLACE ()

REGEXP_REPLACE
(source_string, search_pattern [,replacement_string [,start_position
[,nth_occurrence [,match_parameter ]]]])

--Ex- We need to take an initcap string and separate the words

SocialSecurityNumber
HouseNumber

We need to find each uppercase character "[A-Z]". For each match, we want to
replace it with a space, plus the matching character.
The space is pretty obvious, but we need to use "\1" to signify the text matching the
first sub expression. So we will replace the matching pattern with a space and itself,
"\1". We dont want to replace the first letter of the string, so we will start at the
second occurrence.

SELECT REGEXP_REPLACE(data, '([A-Z])', ' \1', 2) AS hyphen_text FROM t1;

--Ex- SELECT REGEXP_REPLACE('VitthalMandlik', '([A-Z])', ' \1', 2) AS name FROM

dual;
o/p- Vitthal Mandlik

--Ex- Removing special characters from a string

Th♥is∞ is a dem☻o of REGEXP_♫REPLACE function

SELECT
REGEXP_REPLACE('Th♥is∞ is a dem☻o of REGEXP_♫REPLACE function','[^a-z_A-Z ]')
FROM dual;

o/p- This is a demo of REGEXP_REPLACE function

--Ex- Masking sensitive information

The following statement hides the middle part of a credit card for security purposes.
You can apply this technique in E-commerce, Banking, and other Financial
applications that require strict security.

SELECT
regexp_replace ( '4024007187788590','(^\d{3}) (.*) (\d{4}$)', '\1**********\3' )
credit_card FROM dual;
output: 402**********8590

--Ex- Removing redundant spaces

The following statement removes redundant spaces, the space character that
appears more than one, in a string:

SELECT
regexp_replace( 'This line contains more than one spacing between
words', '( ){2,}', ' ' ) regexp_replace FROM dual;

Output: This line contains more than one spacing between words

-- Ex- Replace the fullstop by space

Select
REGEXP_REPLACE('10...taxo.....court,...near..tuff.......mountain', '(\.){1,}', ' ') from
dual;

o/p: 10 taxo court, near tuff mountain

--Ex- Replace multiple comma (,) by single comma

select
regexp_replace ('ABC,,,DDDD,,,,,,DDE,,2LMDL3EME,CEWEC,,,,,,','(\,){1,}', ',')"STRING"
from dual;

O/p:- ABC,DDDD,DDE,2LMDL3EME,CEWEC,

--Ex- Match on First Word

Lets start by using the REGEXP_REPLACE function to replace the first word in a string.
‘TechOnTheNet is a great resource' to 'CheckYourMath'

SELECT
REGEXP_REPLACE ('TechOnTheNet is a great resource', '\w+','CheckYourMath',1,1)
FROM dual;

o/p: CheckYourMath is a great resource

--Ex- replace the digits with # from source string.

‘2, 5, and 10 are numbers in this’

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this Ex', '\d', '#')
FROM dual;

o/p: '#, #, and ## are numbers in this Ex'

--Ex- replace the two digit or more than two digits with # from source string.
‘2, 5, 10, 100 are numbers in this’

SELECT
REGEXP_REPLACE ('2, 5, 10 and 100 are numbers in this Ex', '(\d){2,}', '#')
FROM dual;

o/p- 2, 5, # and # are numbers in this Ex

--Ex- Match on more than one alternative

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G') FROM dual;

o/p: 'AndGrsGn'
It will replace the source string (name) with G wherever it founds ‘a’,’e’,’i’,’o’,’u’ in his
source string

** Match on nth_occurrence
The nth_occurrence parameter allows you to select which occurrence of the pattern
you wish to replace in the string.

-- First Occurrence
Lets look at how to replace the first occurrence of a pattern in a string.
For Ex: SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 1, 'i')
FROM dual;

Result: 'TZchOnTheNet'
This Ex will replace the second character ('e') in 'TechOnTheNet' because it is
replacing the first occurrence of a vowel (a, e, i, o, or u) in the string.

-- Second Occurrence
Next, we will extract for the second occurrence of a pattern in a string.
For Ex: SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 2, 'i')
FROM dual;

Result: 'TechZnTheNet'

This Ex will replace the fifth character ('O') in 'TechOnTheNet' because it is

replacing the second occurrence of a vowel (a, e, i, o, or u) in the string.

-- Third Occurrence
For Ex: SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 3, 'i')
FROM dual;

Result: 'TechOnThZNet'
This Ex will replace the ninth character ('e') in 'TechOnTheNet' because it is replacing
the third occurrence of a vowel (a, e, i, o, or u) in the string.

--Ex- The following Ex examines country_name.

Oracle puts a space after each non-null character in the string.

select REGEXP_REPLACE(loc, '(.)', '\1 ') "REGEXP_REPLACE" from dep ;

NEW YORK
DALLAS
CHICAGO

--Ex- set the phone format in given format 1 – (XXX) XXX - XXXX

select name,
REGEXP_REPLACE(phone, '(\d) (\d{3}) (\d{3}) (\d{4}) ','\1 - (\2) \3 - \4') as phone
FROM customers;

4). REGEXP_LIKE ()

The Oracle REGEXP_LIKE() function is an advanced version of the LIKE operator.

The REGEXP_LIKE() function returns rows that match a regular expression pattern.

REGEXP_LIKE (source_string/columnname, search_pattern [, match_parameter]);

--Ex- The following statement returns employee names that contain the letter ‘c’:

SELECT ename FROM emp WHERE REGEXP_LIKE(ename, 'c' ) ORDER BY ename;

--Ex- WAQ to returns employees whose first names start with the letter A.

SELECT ename FROM emp WHERE REGEXP_LIKE( ename, '^a', 'i' ); --case insensitive

SELECT * FROM Emp WHERE regexp_like (ename, ‘^Am|^Su’,’c’); --- case sensitive

--Ex- WAQ to return emp name whos start with ‘s’ and end with ‘h’.

select ename from emp

where regexp_like (ename,'^s','i') and regexp_like (ename,'h$','i');

--Ex- WAQ to returns the employee names that end with letter y

SELECT ename FROM emp WHERE REGEXP_LIKE( ename, 'y$', 'i' ) ORDER BY ename;

--Ex- WAQ to returns employees whose names start with either letter m or n.
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, '^m|^n', 'i' ) ;

NOTE: If user does not know the spelling of amit whether it is Amit or Ameet.

SELECT * FROM Employee WHERE regexp_like (name, ‘A(mi|mee)t’);

--Ex- WAQ to returns the first names that contain exactly two letters L or 'l'.

SELECT ename FROM emp WHERE REGEXP_LIKE( ename, 'l{2}', 'i' ) ;

(OR)
SELECT ename FROM emp WHERE REGEXP_LIKE( ename, 'l{1,2}', 'i' ) ;

--Ex- WAQ to match the multiple character from names which have preceding
charcter ‘T’ and ‘L’.

SELECT ename FROM emp WHERE REGEXP_LIKE(ename, '([TL])\1','i');

(OR)
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, '([A-Z])\1','i');

--Ex- WAQ retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed
by any character, followed by the letter ‘a’.

SELECT * FROM emp WHERE regexp_like (ename , ' [B-G] . [A] ') ;

--Ex- find the ename from emp whos name start with 'a' and contain another ‘a’ in it.

select * from emp where regexp_like (ename,'[a].[a]','i');

--Ex- WAQ to extract those names who have 'O' in his names 1 or more time.

select * from emp where regexp_like (ename, 'O {1,}' , 'i' ) ;

--Ex- WAQ to retrieve all names that contain the letters ‘j’ or ‘z’.

SELECT * FROM emp WHERE regexp_like (ename , '[jz]') ;

NOTE: use the Square Brackets to specify a matching list that should match any one
of the expressions represented in it.

--Ex- We need to identify invalid email addresses.

Table data is- T1

me@[Link]
me@Ex
@[Link]
[Link]@[Link]
[Link]@ [Link]
[Link]@[Link]

SELECT data FROM t1

WHERE NOT REGEXP_LIKE(data, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', 'i');

--Ex- WAQ to create a table to check email validation

CREATE TABLE t11 (ename varchar2 (20),

email varchar2(50)
CHECK (REGEXP_LIKE(email, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}', 'i')));

--Ex- WAQ to returns the first and last names for those employees with a first name
of Steven or Stephen

SELECT first_name, last_name FROM employees

WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

*** Masking :- Telephone number mask

The another best use of regular expression is telephone number mask.
Following select statement is used for telephone number mask:

create table t1 (ename varchar2 (20),phone number

check (REGEXP_LIKE(phone,‘^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$’))));
-----------------(OR)-----------------------
create table t2 (ename varchar2(20),phone varchar2(15)
check ((REGEXP_LIKE(phone,'^\d{3}-\d{3}-\d{4}$'))));

ALTER TABLE Employee ADD (CONSTRAINT ph_no_format CHECK

(REGEXP_LIKE(Employee_phone,‘^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$’)));

REGEXP_LIKE operator is used by different cards for checking [Link]

are the formats of different cards.

Visa credit card:

4[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}d

MasterCard:
5[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

5). REGEXP_COUNT ()
The REGEXP_COUNT() function complements the functionality of the
REGEXP_INSTR() function by returning the number of times a pattern occurs in a
string.

REGEXP_COUNT(string,pattern,position,match_parameter)

--Ex- WAQ to to return the number of numbers in the string.

'An apple costs 50 cents, a banana costs 10 cents.'

SELECT REGEXP_COUNT('An apple costs 50 cents, a banana costs 10 cents.','\d+')

result FROM dual;

--Ex- WAQ to count the number of times the character 't' appears in a string.
'TechOnTheNet is a great resource'

SELECT REGEXP_COUNT ('TechOnTheNet is a great resource', 't') FROM dual ;

--Ex- WAQ to match on a multi-character pattern appears in string.

'The Ex shows how to use the REGEXP_COUNT function'

SELECT REGEXP_COUNT ('The Ex shows how to use the REGEXP_COUNT function',

'the', 1, 'i') FROM dual;

Match on more than one alternative

The next Ex that we will look at involves using the | pattern.
The | pattern is used like an "OR" to specify more than one alternative.

--Ex- WAQ to count the ‘S’ and ‘L’from employee names.

SELECT ename, REGEXP_COUNT (ename, 'S|L') FROM emp

WHERE REGEXP_COUNT (ename, 'S|L')>=1 ;

Since we did not specify a match_parameter value, the REGEXP_COUNT function will
perform a case-sensitive search

SELECT ename, REGEXP_COUNT (ename, 'S|L', 1, ‘i’) FROM emp

WHERE REGEXP_COUNT (ename, 'S|L')>=1 ;

--Ex- WAQ to count the number of words from source string.

SELECT regexp_count(‘I love [Link] is lovely language’, ‘\w+’) from dual;

o/p- 7

The search pattern ‘[^ ]’checks for characters other than the space character.
SELECT regexp_count(‘I love [Link] is Lovely Language 114 7’, ‘[^ ]+’,1) FROM dual;
o/p- 8

--Ex- WAQ to find the number of vowels from the statement

SELECT regexp_count(‘I love [Link] is lovely language 114 7’, ‘[aeiou]’) FROM dual;
o/p- 9

--Ex- WAQ to count the number of character in the statement

SELECT regexp_count(‘I love [Link] is Lovely Language 114 7’, ‘(.)’,1) FROM dual;
o/p- 39

--Ex- WAQ to count the commas from the given statement

SELECT regexp_count(‘I love SQL,SQL is Lovely, Language 114 7’, ‘,’,1) FROM dual;
o/p- 2
--Ex- WAQ to calculate the number of dots in the statement

SELECT regexp_count(‘I love [Link] is Lovely Language.’, ‘\.’,1) FROM dual;

o/p- 2

[Link] conversion functions

Date conversion functions:-
To_char ()
To_date ()
1. to_char (date, format)
It is used to convert oracle date datatype in character datatype i.e it converts date
type into date string.
To_char is case sensitive function.

The available date formats as follows.

D -- No of days in week
DD -- No of days in month
DDD -- No of days in year
MM -- No of month
MON -- Three letter abbreviation of month
MONTH -- Fully spelled out month
RM -- Roman numeral month
DY -- Three letter abbreviated day
DAY -- Fully spelled out day
Y -- Last one digit of the year
YY -- Last two digits of the year
YYY -- Last three digits of the year
YYYY -- Full four digit year
SYYYY -- Signed year
Y, YYY -- Year with comma
YEAR -- Fully spelled out year
CC -- Century
Q -- No of quarters
W -- No of weeks in month
WW -- No of weeks in year
IW -- No of weeks in year from ISO standard
HH -- Hours
MI -- Minutes
SS -- Seconds
FF -- Fractional seconds
AM or PM -- Displays AM or PM depending upon time of day
A.M or P.M -- Displays A.M or P.M depending upon time of day
FM -- Prefix to month or day, suppresses padding of month or day
TH -- Suffix to a number
SP -- suffix to a number to be spelled out
SPTH -- Suffix combination of TH and SP to be both spelled out
THSP -- same as SPTH

--Ex- select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;

output- 24 december 2006 02:03:23 pm sun

--Ex- select to_char(sysdate,'dd month year') from dual;

Output- 24 december two thousand six

--Ex- select to_char (sysdate,’JSP’) from dual ; ---(sysdate- 01-01-2021)

o/p- TWO MILLION FOUR HUNDRED FIFTY-NINE THOUSAND TWO HUNDRED
SEVENTEEN

--Ex- select (sysdate ,’J’) from dual;

Output- 01022021

--Ex- select to_char(sysdate,'dd fmmonth year') from dual;

Output- 24 december two thousand six

--Ex- select to_char(sysdate,'ddth DDTH') from dual;

Output- 24th 24TH

--Ex- select to_char(sysdate,'ddsp Ddsp DDSP ') from dual;

Output- twenty-four Twenty-Four TWENTY-FOUR
--Ex- select to_char (sysdate, ‘hh24:mi:ss’) from dual;
Output- 14:51:22

Default oracle time format has 12 hours (dd-mon-yyyy)

--Ex- select to_char ('20-jun-06' , DD/MONTH/YY) from dual;

o/p- error because default format is yyyy and we used yy as input date ‘06’.

NOTE: Whenever we are using to_char function always 1st parameter must be
oracle date type (dd-mon-yyyy) otherwise oracle server returns error

2. to_date (date)
This will be used to convert the date string into oracle date data type.

--Ex- select to_date (‘12/june/05’) from dual;

o/p- 12-jun-05

--Ex- select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day') from

dual;
Output- 24 * december * Sunday

-- If you are not using to_char oracle will display output in default date format.

--Ex- select to_date (‘12/06/05’) from dual;

o/p- error
solution:- we have to use format that we want the date string into date data type
select to_date (‘12/06/05’,’dd/mm/yy’) from dual;
o/p- 12-06-05

--Ex- WAQ to add 5 days into given date ‘09-feb-10’.

Select to_date(‘09-feb-10’)+5 from dual;
o/p- 14-feb-10

--Ex- WAQ to display given 05-jun-2005 date in character format(DAY/MONTH/YYYY).

Select to_char (‘05-jun-2005’,’DAY/MONTH/YYYY’) from dual;
o/p- error

Select to_char(to_date (‘05-jun-2005’),’DAY/MONTH/YYYY’) from dual;

o/p- THURSDAY/JUNE/2005

--Ex- WAQ to display the employees who are joining in DEC month from emp table.
Select * from emp where to_char(hiredate,’mm’)=12;
(OR)
Select * from emp where to_char(hiredate,’MON’)=‘DEC’;
NOTE: In oracle whenever we are passing date string into predefined date
function. Then oracle server automatically converts date string into date type.
That’s why in this case TO_DATE function explicitly , but here passed parameter
must be in oracle format otherwise oracle server returns error.

--Ex- select last_day (‘15-aug-2019’) from dual;

o/p- 31-aug-2019

Explicit conversion
Select last_day (‘15-08-2019’) from dual;
o/p – error

select last_day (to_date(‘15-08-2019’,’dd-mm-yyyy’)) from dual;

o/p- 31- aug- 2019

Date Functions :
1.add_months (date, no_of_months) :
This will add the specified months to the given date.

--Ex- select add_months(to_date('11-01-1990','dd-mm-yyyy'), 5) from dual;

O/p- 11-JUN-90

--Ex- select add_months(to_date('11-01-1990','dd-mm-yyyy'), -5) from dual;

O/p- 11-AUG-89

--Ex- select add_months(to_date('11-01-1990','dd-mm-yyyy'), null) from dual;

O/p- null

--Ex- select add_months(to_date('11-01-1990','dd-mm-yyyy'), 0) from dual;

O/p- 11-jan-1990

If no_of_months is zero then it will display the same date.

If no_of_months is null then it will display nothing.

ADD_MONTHS always shifts the date by whole months. You can provide a fractional
value for the month_shift parameter, but ADD_MONTHS will always round down to
the whole number nearest zero.

--Ex- select ADD_MONTHS ('28-FEB-2005', 1.5) from dual

o/p- 31-Mar-2005

2.months_between (date1, date2) :

This will give difference of months between two dates.

--Ex- select months_between(to_date('11-aug-1990','dd-mon-yyyy'),

to_date('11-jan-1990','dd-mon-yyyy')) from dual;
O/p- 7

--Ex- select months_between(to_date('11-jan-1990','dd-mon-yyyy'),

to_date('11-aug-1990','dd-mon-yyyy')) from dual;

o/p -7

--Ex- Calacualte the employees experience in “year | months |day” format

select ename,
trunc(months_between(sysdate, hiredate) / 12) "Years",
trunc(mod(months_between(sysdate, hiredate), 12)) "Months",
trunc(mod(months_between(sysdate, hiredate) * 30, 30)) "Days"
from emp;

**Important point to remember

MONTHS_BETWEEN calculates the fractional component of the number of months by

assuming that
each month has 31 days. Therefore, each additional day over a complete month
counts for 1/31 of a month, and:
1 divided by 31 = .032258065

3. next_day (date, day) :

This will produce next day of the given day from the specified date based on week

--Ex- select next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;

o/p-- 31-DEC-06
-- If the day parameter is null then it will display nothing.

4. last_day (date) :
This will produce last day of the specified month

--Ex- select last_day (sysdate) from dual;

o/p – 31/01/2021 13:05:49

--Ex- select last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;

o/p- 31-12-2006

5. extract ((year | month | day | hour | minute | second), date) :

This is used to extract a portion of the date value.
--Ex- select extract(year from sysdate) from dual;
o/p-- 2006

NOTE: You can extract only one value at a time.

6. greatest (date1, date2, date3 … daten) :

This will give the greatest date.

--Ex- select greatest(to_date('11-jan-90','dd-mon-rr'),

to_date('11-mar-90','dd-mon-rr'),
to_date('11-apr-90','dd-mon-rr')) from dual;
o/p-- 11-04-1990

7. least (date1, date2, date3 … daten) :

This will give the least date.

--Ex- select least(to_date('11-jan-90','dd-mon-rr'),

to_date('11-mar-90','dd-mon-rr'),
to_date('11-apr-90','dd-mon-rr')) from dual;

o/p-- 11-01-1990

[Link] (date, (day | month | year)):

Round will rounds the date to which it was equal to or greater than the given date.
 If the second parameter was year then round will checks the month of the
given date in the following ranges. JAN -- JUN & JUL -- DEC
 If the month falls between JAN and JUN then it returns the first day of the
current year.
 If the month falls between JUL and DEC then it returns the first day of the next
year.

--Ex- select round(to_date('24-dec-2004','dd-mon-yyyy'),'year') A,

round(to_date('11-oct-2006','dd-mon-yyyy'),'year') B from dual;

A B
01-JAN-2005 01-JAN-2006

 If the second parameter was month then round will checks the day of the given
date in the following ranges. 1 – 15 & 16 -- 31
 If the day falls between 1 and 15 then it returns the first day of the current
month.
 If the day falls between 16 and 31 then it returns the first day of the next
month.
--Ex- select round(to_date('11-jan-2004','dd-mon-yyyy'),'month') A,
round(to_date('18-jan-2004','dd-mon-yyyy'),'month') B from dual;

A B
01-JAN-2004 01-FEB-2004

 If the second parameter was day then round will checks the week day of the
given date in the following ranges. SUN -- WED & THU -- SUN
 If the week day falls between SUN and WED then it returns the previous
sunday.
 If the weekday falls between THU and SUN then it returns the next sunday.

--Ex- select round(to_date('26-dec-2006','dd-mon-yyyy'),'day') A,

round(to_date('29-dec-2006','dd-mon-yyyy'),'day') B from dual;

A B
24-DEC-2006 31-DEC-2006

If the second parameter was null then it returns nothing.

--Ex- select round(sysdate,null) from dual;
o/p- null

If the you are not specifying the second parameter then round will resets the time to
the begining of the current day in case of user specified date.

--Ex- select to_char(round(to_date('24-dec-2006','dd-mon-yyyy')) ,'dd mon yyyy

hh:mi:ss am') from dual;
o/p- 24 dec 2006 12:00:00 am

If the you are not specifying the second parameter then round will resets the time to
the begining of the next day in case of sysdate.

--Ex- select round(sysdate) from dual; ---sysdate 28/12/2020

o/p- 29/12/2020

9. trunc (date, (day | month | year))

Trunc will chops off the date to which it was equal to or less than the given date.
If the second parameter was year then it always returns the first day of the current
year.
--Ex- select trunc(to_date('24-dec-2004','dd-mon-yyyy'),'year') A,
trunc(to_date('11-mar-2006','dd-mon-yyyy'),'year') B from dual;

A B
01-01-2004 01-01-2006
If the second parameter was month then it always returns the first day of the current
month.

--Ex- select trunc(to_date('11-jan-2004','dd-mon-yyyy'),'month') A,

trunc(to_date('18-jan-2004','dd-mon-yyyy'),'month') B from dual;

A B
01-01-2004 01-01-2004

If the second parameter was day then it always returns the previous sunday.

--Ex- select trunc(to_date('26-dec-06','dd-mon-yyyy'),'day'),

trunc(to_date('29-dec-06','dd-mon-yyyy'),'day') from dual;

A B
26-12-2006 26-12-2006

If the second parameter was null then it returns nothing.

--Ex- select trunc(to_date(sysdate,'dd-mon-yyyy hh:mi:ss'),null) from dual;

o/p- Null

If the you are not specifying the second parameter then trunk will resets the time to
the begining of the current day.

--Ex- select trunc(to_date(sysdate,'dd-mon-yyyy hh:mi:ss')) from dual;

o/p- 28/12/0020 --- invalid o/p

Solution: using rrrr instead of yyyy

select trunc(to_date(sysdate,'dd-mon-rrrr hh:mi:ss')) from dual;
o/p- 28/12/2020

NOTE: RRRR accepts a four-digit input (although not required), and converts two-digit
dates as RR does. YYYY accepts 4-digit inputs but doesn't do any date converting
Essentially, your first Ex YY will assume that 81 as 2081 whereas the RR one assumes
1981.

--Ex- select to_char(trunc(to_date('24-dec-2006','dd-mon-yyyy')),

'dd mon yyyy hh:mi:ss am')from dual;
o/p- 24 dec 2006 12:00:00 am

10. coalesce (date1, date2, date3 … daten) :

This will give the first non-null date.
--Ex- select coalesce('12-jan-90','13-jan-99') A,
coalesce(null,'12-jan-90','23-mar-98',null) B from dual;
A B
12-jan-90 12-jan-90

--Ex- Get the first day of the current year. Sysdate

select round(to_date(sysdate),'year') from dual; 28-dec-2020

o/p- 01/01/2020

--Ex- Get first day of the current month.

select trunc(to_date(sysdate),'month') from dual;

o/p- 01/12/2020

--Ex- Get the last day of previous year.

select round(to_date(sysdate),'year')-1 from dual;

o/p- 31/12/2019

--Ex- Get the last day of the current year from given date 01-feb-2020

select round(to_date(add_months(‘01-feb-2020’,5),'dd-mm-yyyy'),'yyyy')-1
from dual;
o/p- 31/12/0020

--Ex- Get the remaining days of the month.

select to_char(last_day(sysdate),'dd') - to_char(sysdate,'dd')from dual;

o/p- 3

--Ex- Get the total no of day of current month.

select to_char(last_day(sysdate),'dd') from dual;

o/p- 31

--Ex- Get the first Saturday of current month

select next_day(trunc(sysdate,'mm'),'sat') from dual;

o/p- 02/01/2021

--Ex- Get the 2 nd Saturday of current month

select next_day(trunc(sysdate,'mm'),'sat')+7 from dual;
0/p- 09/01/2021

--Ex- Get all Saturday from the current month (sysdate: 19-01-2021)

select next_day(trunc(sysdate,'mm'),'saturday')+7*(level-1) from dual connect by

level<=5;
o/p -- 02/01/2021, 09/01/2021, 16/01/2021, 23/01/2021, 30/01/2021

--Ex- Get the count of all sturday of the current month.

select count(cnt_sat)as Count_Day,to_char('SATURDAY')as DAY

from (select next_day(trunc(sysdate, 'MM'), 'SAT') + 7 * (level - 1) as cnt_sat
from dual connect by level <= 5) ;
o/p:- 5

** Oracle SYSDATE :
The Oracle SYSDATE function returns the current date and time of the Operating
System (OS) where the Oracle Database installed.

SYSDATE + 1 is tomorrow
SYSDATE - 7 is one week ago
SYSDATE + 7 is next one week
SYSDATE + (10/1440) is ten minutes from now.

The following table illustrates the arithmetic of the SYSDATE function:

SYSDATE Math Description
WHERE (date) > SYSDATE - 8/24; Past 8 hours
WHERE (date) > SYSDATE - 30; Past 30 days
WHERE (date) > SYSDATE - 30/1440; Past 30 minutes
8/24 8 hours
15/24/60/60 15 seconds
1/24/60 One minute
1/24 One hour
TRUNC(SYSDATE+1/24,'HH') 1 hour starting with the next hour

NOTE:
The Oracle SYSDATE function cannot be used in the condition of a CHECK constraint.

--Ex- WAQ to find First day of Current Week, i.e. Sunday

Select TO_CHAR (trunc (SYSDATE, ‘DAY’), ‘D’) ,

TO_CHAR (trunc (SYSDATE,’DAY’), ‘DAY’) ,
trunc (SYSDATE, ‘DAY’) AS day_date from DUAL;
--Ex- WAQ to find First day of Next Week, i.e. Sunday

SELECT TO_CHAR (TRUNC (SYSDATE,’DAY’), ‘DAY’) AS DAY_IN_WORDS,

TRUNC (SYSDATE+7, ‘DAY’) AS DAY_DATE FROM DUAL;

--Ex- WAQ to find First day of Current Month

SELECT TRUNC (SYSDATE,’MM’) FROM DUAL;

--Ex- WAQ to find First day of Previous Month

SELECT TRUNC (TRUNC (SYSDATE,’MM’)-1, ‘MM’) FROM DUAL;

(OR)
SELECT TRUNC (ADD_MONTHS (SYSDATE,-1),’MM’) FROM DUAL;

--Ex- WAQ to find First day of Next Month?

SELECT LAST_DAY(SYSDATE)+1 FROM DUAL;

(OR)
SELECT TRUNC (ADD_MONTHS (SYSDATE, +1),’MM’) FROM DUAL;

--Ex- WAQ to find First day of Previous Year

SELECT TRUNC (TRUNC (SYSDATE,’YYYY’)-1, ‘YYYY’) FROM DUAL;

--Ex- WAQ to find First day of Next Year

SELECT TRUNC (ADD_MONTHS (SYSDATE, 12),’YYYY’) FROM DUAL;

(OR)
SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘YYYY’), 12) FROM DUAL;

--Ex- WAQ to find Last day of Current Month

SELECT LAST_DAY (SYSDATE) FROM DUAL;

--Ex- WAQ to find Last day of Previous Month

SELECT TRUNC (SYSDATE, ‘MM’)-1 FROM DUAL;

--Ex- WAQ to find Last day of Current Year

SELECT TRUNC (ADD_MONTHS (SYSDATE, 12),’YYYY’)-1 FROM DUAL;

--Ex- WAQ to find Last day of Previous Year

SELECT TRUNC (SYSDATE, ‘YYYY’)-1 FROM DUAL;

--Ex- WAQ to Get number of days in Current Month

SELECT TO_CHAR (LAST_DAY (SYSDATE),’DD’) FROM DUAL;

--Ex- WAQ to find Get number of days left in Current Month

SELECT TO_CHAR (LAST_DAY (sysdate),'DD')- TO_CHAR(sysdate,'DD') FROM DUAL;

SELECT LAST_DAY (sysdate) AS LAST_DAY, SYSDATE,

LAST_DAY (sysdate) - sysdate AS DAYS_LEFT FROM DUAL;

--Ex- WAQ to get first day of all months of current year.

SELECT TO_CHAR (add_months ('01-JAN-2016', LEVEL-1),'DD-MON-YY') FROM DUAL

connect by LEVEL <= 12)

--Ex- WAQ to Display each month Start and End date upto last month of the year

Select TO_CHAR (LAST_DAY (all_months),’DD-MON-YY’) AS END_DATE_MONTH

FROM (SELECT TO_CHAR (add_months (‘01-JAN-2016′, LEVEL-1),’DD-MON-YY’) AS
all_months FROM DUAL connect by LEVEL <= 12);

--Ex- WAQ to find Get number of seconds passed since today (since 00:00 hr.)

Select (SYSDATE – TRUNC (SYSDATE)) * 24 * 60 * 60 seconds FROM DUAL;

--Ex- WAQ to find Get number of minutes passed since today (since 00:00 hr.)

Select (SYSDATE – TRUNC (SYSDATE)) * 24 * 60 minutes FROM DUAL;

--Ex- WAQ to find Get number of hours passed since today (since 00:00 hr.)

Select (SYSDATE – TRUNC (SYSDATE)) * 24 hours FROM DUAL;

--Ex- WAQ to get how many number of hours are left today (till 23:59:59 hr.)

SELECT (TRUNC (SYSDATE+1) – SYSDATE) *24 AS HRS_LEFT FROM DUAL;

**** GROUP FUNCTIONS :

Group functions will be applied on all the rows but produces single output.
Sum ()
Avg ()
Max ()
Min ()
Count (columnname)
Count (*)

NOTE: In all DB by default all group functions ignores null value except count(*)

sum (column):
This will give the sum of the values of the specified column.

--Ex- select sum(sal) from emp;

o/p- 38600

avg (column) :
This will give the average of the values of the specified column.

--Ex- select avg(sal) from emp;

o/p- 2757.1428

max (column) :
This will give the maximum of the values of the specified column.

--Ex- select max(sal) from emp;

o/p- 5000

--Ex- select max (hiredate) from emp;

o/p- 23-may-87

--Ex- select max (ename) from emp;

o/p- WARD

min (column) :
This will give the minimum of the values of the specified column.

--Ex- select min(sal) from emp;

o/p- 800

--Ex- select min (hiredate) from emp;

o/p- 17-dec-80

--Ex- select min (ename) from emp;

o/p- ADAMS

count (column) / count (*) :

This will give the count of the values of the specified column.
Count() function count the null value also.

--Ex- select count(sal) A, count(*) B from emp;

A B
14 14

--Ex- select count(comm) from emp;

o/p- 4

--Ex- How to calculate null values from comm column of emp table.
Select count(*) - count (comm) from emp;
o/p- 10
--Ex- select * from emp where sal=min(sal);
o/p- error

--Ex- Get the count of columns from emp table

select count(column_name) from user_tab_columns where table_name='EMP';
o/p- 8

NOTE: in all database we are not allowed to use group function in WHERE caluse.
To use group function in filter condition oracle provided HAVING clause.

--Ex- To check the aggregate function working behaviour.

TABLE temp

IDs in table will be – 1, 2, 3, 4, 5, 6, 7

SELECT SUM(1), SUM(2), SUM(3), SUM(4), SUM(1) FROM temp;
o/p: 7, 14, 21, 28, 7

SELECT MIN(1), MIN(2), MIN(3), MIN(4), MIN(1) FROM temp;

o/p:- 1, 2, 3, 4, 1

SELECT MAX(1), MAX(2), MAX(3), MAX(4), MAX(1) FROM temp;

o/p:- 1, 2, 3, 4, 1

SELECT AVG(1), AVG(2), AVG(3), AVG(4), AVG(1) FROM temp;

o/p:- 1, 2, 3, 4, 1

SELECT COUNT(1), COUNT(2), COUNT(3), COUNT(4), COUNT(1) FROM temp;

o/p:- 7, 7, 7, 7, 7

**** GROUP BY clause:

Group by clause is used to arrange similar data items into set of logical group.
Whenever we are using group by clause database server selects similar data items
from table column and then reduces [Link] data item in each group.

The GROUP BY clause is often used with aggregate functions such as AVG(), COUNT(),
MAX(), MIN() and SUM(). In this case, the aggregate function returns the summary
information per group.

Rule: Other than group function column specified in select statement must be
specified in the group by clause otherwise server returns error “not a GROUP BY
expression”.

Syntax: SELECT column_list FROM T GROUP BY column_names;

--Ex- WAQ to display no. of employees from each department from emp table.
Select deptno, count (*) from emp group by deptno ;

--Ex- WAQ to display no. of employees in each JOB from emp table.
Select job, count (*) from emp group by job;

--Ex- select count(*), min (sal), max(sal) from emp group by sal;

NOTE: In every DB we can also use group by clause without group function.

--Ex- select deptno, sum(sal), job from emp group by deptno ;

ERROR: not a GROUP BY expression

Select deptno, sum(sal), job from emp group by deptno , job order by deptno;

NOTE: if you specified extra column names those who are not present in select
statement then its fine.

--Ex- WAQ to display count of employees per year from emp table.

select to_char (hiredate,'yyyy')Year, count(*) from emp

group by to_char (hiredate,'yyyy');
(OR)
select extract (year from hiredate) YEAR, count (*) from emp
group by extract (year from hiredate)

Execution:- In all DB whenever we are submitting group by clause then DB server

first execute after group by clause columns and also then resultes are stored in result
set table then only DB servers select that result set table based on specified columns
after select statement.

ROWS GROUPS RESULT

FROM,WHERE GROUP BY, HAVING ORDER BY, SELECT

NOTE: In all DB whenever group function are used then we must have to use
GROUP BY clause.
NOTE: We cant not use group functions in WHERE clause to filter data, if we want
to filter grouped data then we must use HAVING Clause.

--Ex- WAQ to display those department no who having more than 3 employees.

Select deptno, count(*) from emp having count (*) > 3;

**** HAVING Clause :

After group by clause we are not allowed to use WHERE clause in place of that we are
using HAVING clause.

The HAVING clause is an optional clause of the SELECT statement. It is used to filter
groups of rows returned by the GROUP BY clause. This is why the HAVING clause is
usually used with the GROUP BY clause.

If you use the HAVING clause without the GROUP BY clause, the HAVING clause
works like the WHERE clause.

NOTE: Note that the HAVING clause filters groups of rows while the WHERE
clause filters rows. This is a main difference between the HAVING and WHERE
clauses.

--Ex- WAQ to display those deptno those sum of salary is more than 9000 from emp
table
Select deptno, sum (sal) from emp group by deptno having sum(sal) > 9000;

--Ex- WAQ to display year, no. of employees per year in which more than 1 employee
was hired from emp table.

Select to_char (hiredate , ‘YYYY’) “Year”, count (*) from emp

group by to_char (hiredate , ‘YYYY’) having count (*) >1;

**** ORDER BY Clause :

The SQL ORDER BY clause is used to sort the data in ascending or descending order
based on our requirement, It is based on one or more columns. In some cases
databases sort the query results in an ascending order by default.
Syntax:
SELECT column-list FROM table_name
[WHERE condition]
[GROUP BY columnname]
[HAVING condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever
column you are using to sort that column should be in the column-list.

--Ex- select deptno , count (*) from emp where sal >1000 group by deptno
Having count (*) >2 order by deptno desc;

Order by clause using column’s positions number:

You don’t need to specify the column names for sorting the data. If you prefer, you
can use the positions of the column in the ORDER BY clause.

--Ex- select ename, sal, hiredate from emp order by 1,2;

In this Ex, the position of ename column is 1 and sal is 2.

In the ORDER BY clause, we used these column positions to instruct the Oracle to sort
the rows.

Order by clause with NULL values :

When sorting mixed NULL with non-NULL values, Oracle allows you to specify which
one should appear first.
For Ex, the following statement sorts the commission in ascending order and places
NULL values first.

select * from emp order by comm NULLS FIRST;

To place NULL values after the non-NULL values, you use NULLS LAST as shown in the
following statement:

select * from emp order by comm NULLS LAST; ---Asc order

select * from emp order by comm desc NULLS LAST; ---Desc order

Order by clause using function or expression :

The ORDER BY clause allows you to apply a function e.g., string function and math
function on a column and sorts the data by the result of the function.

For Ex, the following statement uses the UPPER() function in the ORDER BY clause to
sort the employee names case-insensitively:

select * from emp order by upper(ename);

select * from emp order by hiredate desc; ----Order by with date column

**Differnece between WHERE clause and HAVING clause

WHERE clasue HAVING clause

WHERE Clause is used to filter the HAVING Clause is used to filter record
records from the table based on the from the groups based on the specified
specified condition. condition.
WHERE Clause can be used without HAVING Clause cannot be used
GROUP BY Clause without GROUP BY Clause
WHERE Clause implements in row HAVING Clause implements in column
operations operation
WHERE Clause cannot contain HAVING Clause can contain aggregate
aggregate function function
WHERE Clause can be used with HAVING Clause can only be used with
SELECT, UPDATE, DELETE statement. SELECT statement.
WHERE Clause is used before GROUP HAVING Clause is used after GROUP BY
BY Clause Clause

** Difference between WHERE and GROUP BY

WHERE clause GROUP BY

WHERE Clause is used to filter the GROUP BY clause summaries identical
records from the table based on the rows into a single/distinct group
specified condition.
WHERE Clause can be used without GROUP BY clause can be used without
GROUP BY Clause WHERE clause
WHERE Clause is not mandatory if sql GROUP BY clause is mandatory if sql
query is having group function query is having group function

**Difference between ORDER BY and GROUP BY clause

ORDER BY GROUP BY
Whereas Order by statement sort the Group by statement is used to group
result-set either in ascending or in the rows that have the same value.
descending order.
While it does not use in CREATE VIEW It may be allowed in CREATE VIEW
statement. statement.
One or more columns can be used in All columns in SELECT statement must
ORDER BY clause from as mentioned in be mentioned in GROUP BY clause
 select statement.
ORDER BY clause always placed after GROUP BY clause always placed before
GROUP BY clause ORDER BY clause

** Differences between FROM Clause and WHERE Clause :

FROM clause WHERE clause
It is used to select the dataset on which It is used for checking some conditions
manipulation has to be done. to filter result
We provide some dataset into the In WHERE clause we give some
FROM clause as a input. condition as input.
FROM clause selects dataset to provide WHERE clause act as selector which
it to WHERE clause for applying filters required instances from dataset
conditions given in query. provide by FROM clause.
FROM clause is mandatory because if WHERE is optional, we use it only in
there is no dataset, no manipulation case of condition checking.
can be performed.

**** ROLLUP , CUBE :

Rollup , cube are used along with the GROUP BY clause only.
It introduce in 8i.

Syntax of rollup:
Select col1, col2, …. From tablename group by rollup (col1,col2,…);

Syntax of cube :
Select col1, col2, …. From tablename group by cube (col1,col2,…);

If we want to calculate subtotal based on the single column then we are using
ROLLUP function whereas if we want to calculate subtotal based on no. of column
then we are using CUBE.

--Ex- select deptno, job, sum (sal) from emp group by rollup (deptno, job);

--Ex- select deptno, job, sum (sal) , count(*) from emp group by rollup (deptno, job)
Order by septno, job desc;

**** WITH clause:

The SQL WITH clause allows you to give a sub-query block a name (a process also
called sub-query refactoring), which can be referenced in several places within the
main SQL query.
The clause is used for defining a temporary relation such that the output of this
temporary relation is available and is used by the query that is associated with the
WITH clause.

Syntax: With SQL_Query_Name As

(SQL query; )
Select * from New_SQL_Query_name;

Execution:
Step 1 : The SQL Query within the with clause is executed at first step.
Step 2 : The output of the SQL query is stored in to temporary relation of with clause.
Step 3 : The Main query is executed with temporary relation produced at last stage.

WITH temp as
(SELECT avg(Attr1) “alias_name” FROM Table),
SELECT Attr1 FROM Table WHERE Table.Attr1 > temp.alias_name;

In this query, WITH clause is used to define a temporary relation “temporaryTable”

that has only 1 attribute “averageValue”. averageValue holds the average value of
column “Attr1” described in relation Table. The SELECT statement that follows the
WITH clause will produce only those rows where the value of “Attr1” in relation Table
is greater than the average value obtained from the WITH clause statement.

--Ex- Find all the employee whose sal is more than the avg salary of all employees.

WITH temp as
(SELECT avg(Salary) avg from Employees)
select e.Employee_id, e.first_Name, [Link] FROM Employees e, temp
WHERE [Link] > [Link] order by [Link] desc;

--Ex- user needs to calculate Salary of the Employee with Total number of the
Employees and user needs to show it department-wise then following query is useful.

With Dep_Count As
(Select Deptno,Count(Empno)No_Of_Emp From Emp Group by Deptno)
Select Empno, Sal/No_Of_Emp From Emp E, Dep_Count C Where [Link] =
[Link];

--Ex- WAQ to display all departments with its total count of employees and also show
those departments those doesn’t have any employees.

with a as
(select department_id, count(employee_id) cnt_emp from employees
group by department_id)
select d.department_id, a.cnt_emp from departments d left join a
where d.department_id = a.department_id ;

--Ex- show below data in column level table(row to column conversion)

JOB_ID BATCH_ID ACTION ACTION_DATE

1 1 SENT 29/12/2020 12:54:19 PM
2 1 RECV 30/12/2020 12:54:19 PM
3 2 SENT 31/12/2020 12:54:19 PM
4 2 RECV 01/01/2021 12:54:19 PM
5 3 SENT 02/01/2021 12:54:19 PM

WITH col_generator AS
( SELECT t1.batch_id, DECODE([Link], 'SENT', [Link]) sent,
DECODE([Link],'RECV', [Link]) received
FROM test t1, test t2
WHERE t2.batch_id(+) = t1.batch_id)

SELECT batch_id, MAX(sent) sent, MAX(received) received

FROM col_generator
GROUP BY batch_id
ORDER BY 1;

--Ex- How to convert seconds into minutes

WITH t AS
(SELECT 100 s FROM DUAL
UNION ALL
SELECT 7201 FROM DUAL)
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S" FROM t;

S M:S
100 1:40
7201 120:1

Important Points:
 The SQL WITH clause is good when used with complex SQL statements rather
than simple ones
 It also allows you to break down complex SQL queries into smaller ones which
make it easy for debugging and processing the complex queries.
 The SQL WITH clause is basically a drop-in replacement to the normal sub-
query.

CONVERSION FUNCTIONS

Converting one datatype into another datatype is called conversion.

Two types of conversion-
1. Implicit (OR) automatic conversion
2. Explicit conversion

1. Implicit Data-Type Conversion :

In this type of conversion the data is converted from one type to another implicitly
(by itself/automatically).

FROM TO
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
DATE VARCHAR2
NUMBER VARCHAR2

--Ex- select sal + ‘100’ from emp ;

--Ex- select last_day (‘15-aug-05’) from dual;

o/p- 31-aug-05

2. Explicit Data-type conversion :

In oracle we can also convert one datetype into another datatype explicitly by using
following explicit conversion.
1). Decode () function
2). case statement
3). To_number ()
4). To_char ()
5). To_date ()

1). Decode () :-
Decode is a conversion function which is used to decoding the values.
Decode will act as value by value substitution.
Decode function is same as IF-THEN-ELSIF control statement of PL/SQL.
For every value of field, it will checks for a match in a series of if/then tests.

Syntax: decode (value, if1, then1, if2, then2, ……. else);

Decode function internally uses equality operator.

--Ex- select decode(1,1,3) A, decode(1,2,3,4,4,6) B from dual;

A B
3 6
Rules of DECODE ():-
-1- If the number of parameters are odd and different then decode will display
nothing.
--Ex- select decode (1,2,3) from dual;
o/p- null

-2- If the number of parameters are even and different then decode will display last
value.
--Ex- select decode (1,2,3,4) from dual;
o/p- 4

-3- If all the parameters are null then decode will display nothing.
--Ex- select decode (null,null,null,null) from dual;
o/p- null

-4- If all the parameters are zeros then decode will display zero.
--Ex- select decode (0,0,0) from dual;
o/p- 0

--Ex- select deptno , decode (deptno, 10, ‘ten’ , 20, ‘twenty’, ‘others’) from emp;

--Ex- update emp table commision column based on below conditions-

If job = ‘CLERK’ then update commission 10% of salary.
If job = ‘SALESMAN’ then update commission 20% of salary.
And for other jobs update commission 30% of salary.

Update emp
set comm = decode (job, ‘CLERK’, sal*0.10 , ‘SALESMAN’, sal*0.20,sal*0.30) ;

** DECODE with ORDER BY clause and group by -

select ename,job from emp order by decode ('J','S',ename);

--Ex- count the no. of employees with respect to job and department name
select [Link],
sum(decode(job,'CLERK',1,0))"Clerks",
sum(decode(job,'MANAGER',1,0))"Manager",
sum(decode(job,'SALESMAN',1,0))"Salesman" from emp e, dep d
where [Link]=[Link] group by [Link];

DNAME SALESMAN CLERKS ANALYST MANAGER

RESEARCH 0 2 2 1
SALES 4 1 0 1
ACCOUNTING 0 1 0 1

--Ex- display job wise total salary in pivot report format as shown in above Ex.

select job, sum ( decode (deptno, 10, sal, 0 )) "dep10",

sum ( decode (deptno, 20, sal, 0 )) "dep20",
sum ( decode (deptno, 30, sal, 0 )) "dep30" from emp group by job;

** DECODE() function and NULL :- NULL cannot be compared to anything even NULL.
However, DECODE() function treats two null values are equal.

SELECT DECODE(NULL,NULL,'Equal','Not equal') FROM dual;

o/p -- 'Equal'

SELECT DECODE(NULL,NULL,1,2) FROM dual;

o/p-- 1

2). CASE STATEMENT () :-

The CASE statement is SQL’s way of handling if/then logic.

You can use a CASE expression in any statement or clause that accepts a valid
expression.
For Ex, you can use the CASE expression in statements such as SELECT, UPDATE, or
DELETE, and in clauses like SELECT, WHERE, HAVING, and ORDDER BY.

Oracle case statement will give us the transformation of values in following format.

if Variable = Value1 then Value/variable/statement

else if Variable= Value2 then Value/variable/statement
else Value/variable/statement;

Rules:
 The CASE statement returns any datatype such as a string, numeric, date, etc.
(BUT all results must be the same datatype in the CASE statement.)
 If all conditions are not the same datatype, an ORA-00932 error will be
returned.
 If all results are not the same datatype, an ORA-00932 error will be returned.
 If no condition is found to be true, then the CASE statement will return the
value in the ELSE clause.
 If the ELSE clause is omitted and no condition is found to be true, then the
CASE statement will return NULL.
NOTE: You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN
clause is considered 2 comparisons.

Oracle CASE expression has two formats: the simple CASE expression and the
searched CASE expression. Both formats support an optional ELSE clause.

1). Simple case statement :-

The simple CASE expression matches an expression to a list of simple expressions to
determine the result.

Syntax : CASE column_name

WHEN value1 THEN statement1
WHEN value2 THEN statement2
WHEN valueN THEN statementN
ELSE statement_else
END;

In this syntax, Oracle compares the input expression (column_name) to each

comparison expression value1, value2, …, valueN.
If the input expression equals any comparison expression, the CASE expression
returns the corresponding result expression (statement).

If the input expression column_name does not match any comparison expression,
the CASE expression returns the expression in the ELSE clause if the ELSE clause
exists, otherwise, if “ELSE” clause is not present then it returns a null value.

--Ex- select ename, sal, case deptno

When 10 then ‘ten’
When 20 then ‘twenty’
Else ‘others’ end from emp;

--Ex- The following query uses the CASE expression to calculate the commission for
each JOB i.e., CLERKS 5%, SALESMAN 10%,and other jobs 8%

SELECT ename,job,sal,deptno,CASE job

WHEN 'CLERK' THEN ROUND(sal * 0.05, 2)
WHEN 'SALESMAN' THEN ROUND(sal * 0.1, 2)
ELSE ROUND(sal * 0.08, 2) END comm FROM emp
ORDER BY comm;

--Ex- UPDATE statement with nested CASE expressions

UPDATE emp SET sal = CASE deptno
WHEN 10 THEN sal+100
WHEN 20 THEN sal+200
ELSE sal END

2). Searched CASE statement :-

The Oracle searched CASE expression evaluates a list of Boolean expressions to
determine the result.
Syntax: CASE
WHEN e1 THEN r1
[ WHEN e2 THEN r2]
 ...
[ELSE r_else]
END

The searched CASE expression evaluates the Boolean expression (e1, e2, …) in each
WHEN clause in the order that the Boolean expressions appear.
It returns the result expression (r) of the first Boolean expression (e) that evaluates to
true. If no Boolean expression is true, then the CASE expression returns the result
expression in the ELSE clause if an ELSE clause exists; if ELSE clause is not exists the it
returns a null value.
Oracle evaluates each Boolean condition to determine whether it is true, and never
evaluates the next condition if the previous one is true.

--Ex- Display the employees salary between level 0-2000 as low, 2000 – 4000 as
medium, 4000- 6000 as high from emp table.

select ename,sal,CASE
WHEN sal > 0 AND sal < 2000 THEN 'Low'
WHEN sal >= 2000 AND sal < 4000 THEN 'Medium'
WHEN sal >= 4000 AND sal < 6000 THEN 'High'
ELSE 'Grand' END sal_level FROM emp ORDER BY ename;

--Ex- select full_name, std, (case when gender='M' then 'Male' else 'Female' end)as
Gender from students;

*** Use CASE with ‘IN’ operator

SELECT empno,deptno,sal,comm from emp
WHERE CASE
WHEN comm < 500 THEN 'A'
WHEN sal < 2000 AND deptno <> 30 THEN 'B'
END IN ('A','B')
ORDER BY empno;

*** Use CASE to get multiple counts in one pass:-

select job_id, count(*),
sum(case job_id when 'AD_PRES' then 1 else 0 end) as AD_PRES,
sum(case job_id when 'AD_VP' then 1 else 0 end) as AD_VP,
sum(case job_id when 'IT_PROG' then 1 else 0 end) as IT_PROG
from employees group by job_id;

** CASE expression in an ORDER BY clause

The following query uses the CASE expression in an ORDER BY clause to determine
the sort order of rows based on column value:

*LOCATIONS* Table having below columns

Location_id , address, city ,state, country_id

select * from locations where country_id in ('US','CA','UK')

Order by country_id, CASE country_id WHEN 'US' THEN state ELSE city END;

** CASE expression in HAVING clause

SELECT dname, deptno,COUNT(empno) FROM emp INNER JOIN dep USING (deptno)
GROUP BY dname, deptno HAVING
COUNT(CASE WHEN deptno = 10 THEN empno ELSE NULL END ) > 5 or
COUNT(CASE WHEN deptno = 20 THEN empno ELSE NULL END) > 2
ORDER BY dname;

** CASE expression in an Update command

We have same data in emp and employee table but sal column of emp table is null
So now we want to update table emp and set the sal column value equal to the sal
column in the employees table. In the following query, we need to use a CASE
expression with the update statement.

update emp set sal = ( case when [Link] IS NULL then [Link] else [Link] END )
from employee e1 INNER JOIN emp e2 ON [Link] = [Link];

** CASE statement that evaluates two different fields

select deptno, empno, ename from emp
WHERE case
when sal>2000 and deptno=10 then ename
when sal>1200 and deptno=20 then ename
when sal>1500 and deptno=30 then ename
end is not null;

In above Ex we have used the defined case statement in WHERE clause to filter not
null values.

--Ex- what will be THE output.

SELECT NULL, CASE

WHEN NULL=NULL THEN NULL
ELSE
'notnull'
END FROM dual;

O/p- NULL ,notnull

** Difference between DECODE function and CASE statement

 DECODE CASE
Decode is oracle system function Case is oracle statement not a
function
Decode can work only with = Equal to Case statement can work other than
operator equal to operator (<,>,>=,<=)
Decode Statement can not work with Case statement works with sub-
the Sub-queries. queries.
Decode can only be used in function Case statement can be used in PL SQL
inside SQL only. blocks
Decode can compare null = null Case can’t compare null=null
Decode cant not work with RANGE of Case can be work with RANGE of
value value using BETWEEN operator
Datatype miss match are accepted Each when condition datatype must
be same and all then condition also
datatype same

Difference between DECODE and TRANSLATE:

TRANSLATE DECODE
Translate is string function Decode is conversion function
Translate will replace character one-to- Decode replace the whole string by
one basis. string on basis of compairison.
If no match found for translate it will If no match found then it will show
show source string as it is. NULL value as output.
Ex: select translate ('vitthal','z','1') from Ex: select decode ('vitthal','z','a') from
dual; dual;
o/p: - vitthal o/p:- null

3). to_number () :-
The Oracle TO_NUMBER function is used to convert a text value to a number value.
It works similar to the TO_DATE and TO_CHAR functions but converts the values to a
number.
It takes many different data types: CHAR,VARCHAR2,NCHAR,NVARCHAR2

The TO_NUMBER returns a value in the NUMBER data type. Also, the number will be
rounded to the specified number of digits, which may cause undesired effects.

Syntax: TO_NUMBER( input_value, [format], [nls_parameter] )

input_value (mandatory): This is the value that is to be converted to a number. It can

be one of the many data types mentioned earlier but is most commonly provided as
a string.
format_mask (optional): This is the format that the output value should be displayed
as. It must be a valid number format, which is explained below.
nls_parameter (optional): This value is used to determine how the output value is
displayed, such as currency symbols and numeric characters.

Format elements:
G  group seperator
D  decimal indicator
$  dollar sign
0  leading zero
L  local currancy
,  group seperator
.  deciaml
G  represending a number

Default Format
This Ex converts a simple string to a number value.

SELECT TO_NUMBER('5467.12') FROM DUAL;

Specified Format with Decimals

This Ex converts the same string into a number, using the specified format.

SELECT TO_NUMBER('5467.12', '999999.99') FROM DUAL;

Format No Decimals
This Ex converts a number which has no decimal places in it.

SELECT TO_NUMBER('4687841', '9999999') FROM DUAL;

Format with Currency

This Ex treats the dollar sign as a currency figure and converts it to a number.

SELECT TO_NUMBER('$65.169', 'L99.999') FROM DUAL;

Format with Thousand Group Markers

This Ex handles the commas that are used as thousand group markers.

SELECT TO_NUMBER('123,456,789', '999,999,999') FROM DUAL;

Uses NLS Parameter

This Ex takes a complicated string, determines that it is in a certain format, and
specifies the numeric characters to use

SELECT
TO_NUMBER('$17 218,00', 'L999G999D00',' NLS_NUMERIC_CHARACTERS='', ''')
FROM DUAL;

How to Use Oracle TO_NUMBER With Dates

You can’t supply a date value as the parameter directly: TO_NUMBER(datevalue)
But, you can convert it to a string value and then use that as a parameter, to use
Oracle TO_NUMBER with a date: TO_NUMBER(TO_CHAR(datevalue))

--Ex- select to_number(‘$56.8’) + 3 from dual ;

ERROR : INVALID NUMBER

Whenever we are using to_number also use second parameter as same as first
parameter format by using predefine format elements.

select to_number(‘$56.8’, ‘$99.9’) + 3 from dual ;

o/p- 59.8

--Ex- select TO_NUMBER('121.23', '9G999D99') from dual ;

o/p- 121.23

--Ex- UPDATE emp SET salary = salary + TO_NUMBER('100.00', '9G999D99')

WHERE ename = 'KING' ;

4). To_char () :-
To_Char is overleading function i.e this function is used to convert number datatype
into character datatype and also used to convert date datatype into date string.

Syntax: to_char (number , ‘format’) ;

Format elements:
G  group seperator
D  decimal indicator
$  dollar sign
0  leading zero
L  local currancy
,  group seperator
.  deciaml
G  represending a number

SELECT TO_CHAR(1111,’9,999′) FROM DUAL;

The above query will convert 1111 in to format 1,111 and output will be 1,111

SELECT TO_CHAR(1111,’$9,999′) FROM DUAL;

The above query will give you output as $1111
SELECT TO_CHAR(1111,'$9,999') FROM DUAL;
o/p - $1,111

SELECT TO_CHAR(1111,'$9G999') FROM DUAL;

o/p - $1,111

SELECT TO_CHAR(1111.87,'$9,999.99') FROM DUAL;

o/p - $1,111.87

SELECT TO_CHAR(1111.87,'$9G999D99') FROM DUAL;

ERROR IF USING G THEN USE D FOR DECIMAL

SELECT TO_CHAR(1111.87,’$9G999D99′) FROM DUAL;

o/p - $1,111.87

SELECT TO_CHAR(1111.87,’9999.9′) FROM DUAL; --It will round of the decimal value
o/p - 1111.9

SELECT TO_CHAR(1,’9′) FROM DUAL;

o/p - 1

SELECT TO_CHAR(-1,’9′) FROM DUAL;

o/p- -1

SELECT LENGTH(‘-1’) FROM DUAL;

o/p- 2
These are above some important Exs of converting numbers in different characters.

--Ex- Select to_char (3456789,’99g99g999d99’) from dual;

o/p – 34,56,789.00

Local currency (L) :-

--Ex- select to_char (345, ‘L999’) from dual;
o/p - $345

NOTE: In oracle by default local currency is $ if we want to change it to then we

must use “nls_currency” clause in third parameter of the TO_CHAR function.

--Ex- Select to_char (345,’L999’,’nls_currency=Rs’) from dual ;

--Ex- select ename , to_char (sal, ‘L99g99g99g999d’, ‘nls_currency=INR’) from emp ;

--Ex- select ename, nvl (to_char(mgr), ‘No manager’) from emp;

5). To_date :-
The Oracle TO_DATE() function converts a date literal to a DATE value.

Syntax : TO_DATE (string, format, nls_language)

--Ex- select TO_DATE( '5 Jan 2017', 'DD MON YYYY' ) from dual;

--Ex- select TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS') from dual;

--Ex- select TO_DATE( '5 Jan 2017', 'DD MON YYYY' )+7 from dual;

** TO_DATE() function to insert data into a table:

Insert into emp(ename, job, hiredate)

VALUES('Thomas', ‘Salesman’, TO_DATE('Feb 01 1983','Mon DD YYYY'));

In this Ex, because Feb 01 2017 is not Oracle standard date format, you have to use
the TO_DATE() function to convert it to a DATE value before storing in the table.

ROW to COLUMN vice versa conversion functions

LISTAGG ()
PIVOT ()

1). LISTAGG :-
The Oracle LISTAGG() function is an aggregation function that transforms data from
multiple rows into a single list of values separated by a specified delimiter. The
Oracle LISTAGG() function is typically used to denormalize values from multiple rows
into a single value which can be a list of comma-seprated values or other human
readable format for the reporting purpose.

it’s a technique similar to concatenation, but it allows you to aggregate data in a

GROUP BY, combining strings that have matches in a value in another column.

Syntax: LISTAGG
(column_name [, delimiter]) WITHIN GROUP( ORDER BY sort_expressions );

The “sort_expressions” is a list of sort expressions to sort data in ascending (ASC) or

descending (DESC) order. Note that you can use NULLS FIRST or NULLS LAST in the
sort_expression to control the sort order of NULLs. By default, the LISTAGG() function
uses ASCENDING and NULLS LAST options.

--Ex- WAQ to show all employees names department wise in single row.
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS
employees FROM emp GROUP BY deptno ORDER BY deptno;

There are a few ways you can use this function

 If you use it without any grouping, LISTAGG operates on all rows and returns a
single row.
 If you use it with grouping, LISTAGG operates on and returns a row for each
group defined by the GROUP BY clause.
 If you use it as an analytic function, LISTAGG partitions the query result set into
groups, based on one or more expressions in the query_partition_clause.

 you can run a query using LISTAGG in Oracle and eliminate duplicates from the
output of the LISTAGG function.

--Ex- WAQ to display all employee names in single row.

select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) as LISTAGG_OUTPUT

FROM emp;

--Ex- WAQ to display department 10 employees names in single row

select deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) as

LISTAGG_OUTPUT FROM emp where deptno =10;

--Ex- WAQ to display employees names in row as per department wise.

select deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) as

LISTAGG_OUTPUT FROM emp group by deptno;

Listagg using PARTITION BY clause

SELECT job, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) OVER
(PARTITION BY job) as LISTAGG_OUTPUT FROM emp;

Notice how we did not use a GROUP BY. The GROUP BY is not needed if you use the
OVER PARTITION BY clause.

NOTE: The way to do this is to SELECT from a subquery which already removes the
duplicates, and do your LISTAGG on that.

--Ex- WAQ to display distinct sal employee details

SELECT deptno ,LISTAGG(sal, ',') WITHIN GROUP (ORDER BY deptno) as listagg_output

FROM ( SELECT DISTINCT(sal),deptno FROM emp ) group by deptno;
--Ex- WAQ to get the output as shown below from given input table.

Input table_1  Output

COL1 COL2 COL1 COL2
A 1 A 1
A 2 A 1,2
A 3 A 1,2,3
B 1 B 1
B 2 B 1,2
B 3 B 1,2,3

select x.col1, listagg (y.col2,',') within group (order by y.col2) as col2

from tab3 x join tab3 y on x.col1=y.col1 and x.col2>=y.col2
group by x.col1, x.col2;

--Ex- WAQ to display the first sal of all deptno.

SELECT deptno, LISTAGG(sal, ',') WITHIN GROUP (ORDER BY deptno) FROM

(SELECT deptno,sal, row_number() OVER(PARTITION BY deptno order by deptno) as
rn FROM emp) WHERE rn = 1 GROUP BY deptno;

--Ex- How to print * * * * * * using sql

If i give 1 then o/p should * ,
If i give 5 as i/p then o/p should ***

SELECT listagg('*',' ') WITHIN GROUP (ORDER BY 1) FROM dual

CONNECT BY level<=&no;

--Ex- WAQ to generate output like 1 2 3 4 5

Select listagg (level,’ ‘) within group (order by level) from dual connect by level<=5;

2). PIVOT () :-

Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation
queries which transpose rows into columns, aggregating data in the process of the
transposing. As a result, the output of a pivot operation returns more columns and
fewer rows than the starting data set.

Syntax: SELECT * from ( select_list FROM table_name)

PIVOT (aggregate_function() for column_name in (value1, value2,….) );

--Ex- select * from (select job, deptno,sal from emp)

Pivot (sum(sal) for deptno in (10”deptno10”,20”deptno20”,30”deptno”));
--Ex- select * from (select job, deptno from emp)
Pivot (count(*) for deptno in (10”deptno10”,20”deptno20”,30”deptno”));

--Ex- Separate the below table data into different columns, like alpha values in one
column and numeric value in another column.

Table vitthal Output

ID  ID ID
A 1 A
B 2 B
C 3 C
1
2
3
with
table1 as
(select rownum rank, id id1 from vitthal a where id between 'A' and 'Z'),
Table2 as
(select rownum rank, id id2 from vitthal a where id between '1' and '9')
Select id1, id2 from table1 a left join table2 b on [Link]=[Link] ;

Here we have created two alias table for our table and we inserted data in two
different virtual tables with using where condition to split our data and then
combined the virtual result set data by using join.

---Ex- convert the row level data in column level data for below table.
Input- Olympic
Country Medal Sport
India Bronze 100mtr
India Gold 100mtr
India Silver 200mtr
China Gold 400mtr
China Gold Cricket
Nepal Gold Basket ball

Output-
India China Nepal
Bronze - 100mtr Gold – 400mtr Gold – Basket ball
Gold – 100mtr Gold – cricket
Silver – 200mtr

With
table1 as
(select rownum rnk1, medal ||’ - ‘||sport as india from olympic where
country=‘India’),
Table2 as
(select rownum rnk2, medal ||’ - ‘||sport as china from olympic where
country=‘China’),
Table3 as
(select rownum rnk3, medal ||’ - ‘||sport as nepal from olympic where
country=‘Nepal’)
Select [Link] , [Link], [Link]
from table1 full outer join table2
On table1.rnk1 = table2.rnk2 full outer join table3 on table1.rnk1 = table3.rnk3 ;

--Ex- Now display the oputput of below column table into row level .
Input table: C3
India China Nepal
Bronze - 100mtr Gold – 400mtr Gold – Basket ball
Gold – 100mtr Gold – cricket
Silver – 200mtr

Output we want-
Country Medal Sport
India Bronze 100mtr
India Gold 100mtr
India Silver 200mtr
China Gold 400mtr
China Gold Cricket
Nepal Gold Basket ball

with olympic_tab as
(select 'india' country, regexp_substr (india,'\w+',1,1) Medal, regexp_substr
(india,'\w+',1,2) Sports from c3 where regexp_substr (india,'\w+',1,1) is not null
union all
select 'China' country, regexp_substr (China,'\w+',1,1) Medal, regexp_substr
(China,'\w+',1,2) Sports from c3 where regexp_substr (China,'\w+',1,1) is not null
union all
select 'nepal' country, regexp_substr (nepal,'\w+',1,1) Medal, regexp_substr
(nepal,'\w+',1,2) Sports from c3 where regexp_substr (nepal,'\w+',1,1) is not null)
select * from olympic_tab ;

(OR) select * from olympic_tab where country=‘india’;

(OR) select country from olympic_tab group by country having count(medal)>=3;
You can use this query without WITH clause also but if you use WITH clause then you
can use WHERE clause/HAVING clause to filter data with any condition. This is the
advantage of WITH clause.

--Ex- WAQ to display number of medal in tabular format for below table.

Country Medal Sport

India Bronze 100mtr
India Gold 100mtr
India Silver 200mtr
China Gold 400mtr
China Gold Cricket
Nepal Gold Basket ball

Output-
MEDAL India China Nepal
Silver 1 0 0
Bronze 1 0 0
Gold 1 2 1

Select * from (select country,medal from c1)

pivot (count(*)for country in('india', 'china', 'nepal' )) ;

--Ex- Differentiate the negative values and positive values from given test_1 table.
Sno O/p:- Sno Sno
-1 -1 1
-2 -2 2
-3 -3 3
1
2
3

with
a as
(select rownum rnk1, sno as negative from test_1 where sign(sno) = -1),
b as
(select rownum rnk2, sno as positive from test_1 where sign(sno) = 1)
select [Link], [Link] from a full outer join b on a.rnk1 = b.rnk2;

--Ex- Count the negative values and positive values from above Ex table.
with
a as
(select count(sign(sno)) as cnt_neg from test_1 where sign(sno)=-1),
b as
(select count(sign(sno)) as cnt_pos from test_1 where sign(sno)=-1)
select a.cnt_neg, b.cnt_pos from a ,b;

(OR)
select [Link], [Link] from
(select count(sign(sno)) Negative from test_1 where sign(sno) = -1) a1,
(select count(sign(sno)) Positive from test_1 where sign(sno) = 1) b1;

--Ex- I want to show the output like as

SELECT A1.A,B1.B,C1.C FROM
(SELECT COUNT(NAME)A FROM Ta WHERE Name='A')A1,
(SELECT COUNT(NAME)B FROM Ta WHERE Name='B')B1,
(SELECT COUNT(Name)C FROM Ta WHERE Name='C')C1

Input Table Ta  Output

COL1 ABC
A 321
A
A
B
B
C

SET OPERATORS
Set operators allow you to combine the results of multiple separate queries into a
single result set. Set operators are also called as vertical joins.
Types of set operators-
[Link]
[Link] all
[Link]
[Link]

Syntax:
Select column1…column n from table1;
union
Select column1…column n from table2;

Rules:
When selecting your columns, the number of columns needs to match between
queries, and the data type of each column needs to be compatible.

So, if you select three columns in the first query, you need to select three columns in
the second query. The data types also need to be compatible, so if you select a
number and two character types in the first query, you need to do the same in the
second query.

Also, if you want to order your results, the ORDER BY must go at the end of the last
query. You can’t add ORDER BY inside each SELECT query before the set operator.

[Link] :
Union Operator combines the result of 2 or more tables and fetches the results of
two select [Link] operator eliminates the duplicates from the table and
fetches the result.
For each duplicate row in table only one row is displayed in the [Link] considering
the performance of SQL using union is not preferable option but if there is situation
where user wants to remove the duplicate data from two or more table the use of
Union is preferable.

--Ex- Suppose we have 2 employee tables. One is for USA location and one is for
CANADA location and both tables having same columns and datatype then.
(Employee_USA and Employee_CAN)

Select Employee_Num,Employee_name,Department,Salary from Employee_USA

Union
Select Employee_Num,Employee_name,Department,Salary from employee_CAN;

--Ex- select * from emp where deptno =10

Union
Select * from emp where deptno= 20;

--Ex- select deptno , to_char (NULL) “DEP_NAME” from emp

Union
Select to_number(null), dname from dep ;

[Link] ALL :
Union ALL Operator combines the result of 2 or more tables and fetches the results
of two or more select [Link] all operator does not eliminate duplicate
[Link] shows duplicate records also.
By considering the performance of SQL using union all is preferable option because it
does not check the duplicate values so no sorting required at the time of fetching the
[Link] all operator is most widely used operator in reporting purpose where
user needs to fetch the records from different tables.

Kindly select employees from 2 different employee tables without eliminating

duplicates

Select Employee_Num,Employee_name,Department,Salary from Employee_USA

Union All
Select Employee_Num,Employee_name,Department,Salary from Employee_CAN;

Select * from emp where deptno=10

Union all
Select * from emp where deptno=20;

[Link] :
Intersect operator fetches the record which are common between 2 tables.
For Intersecting 2 tables the datatype and column name must be same between 2
tables.

Select Employee_Num,Employee_name,Department,Salary from Employee_USA

Intersect
Select Employee_Num,Employee_name,Department,Salary from Employee_CAN;

Select * from emp where deptno <=30

intersect
Select * from emp where deptno <=20;

[Link] :
When user wants to fetch the record from one table only and not the common
records between two tables user needs to use Minus [Link] operator
selects all the rows from first table but not from second [Link] eliminates duplicate
rows from first and second [Link] removes the results from second table and always
considered first table only.
In the Ex below, the first query would return departments 10, 20, 30, but
departments 10 and 20 are removed because they are returned by the second query.
This leaves a single rows for department 30.

--Ex- Select * from emp where deptno <=30

minus
Select * from emp where deptno <=20;

In below query output will null because 1st query all records are have matching with
2nd query .

--Ex- Select * from emp where deptno <=20

minus
Select * from emp where deptno <=30;

Difference between JOIN and UNION

JOIN UNION
JOIN combines data from many tables SQL combines the result-set of two or
based on a matched condition between more SELECT statements.
them.
It combines data into new columns. It combines data into same column
Number of columns selected from each Number of columns selected from
table may not be same. each table should be same.
Datatypes of corresponding columns Datatypes of corresponding columns
selected from each table can be selected from each table should be
different. same.
It may not return distinct columns. It returns distinct rows.

difference between union and union all

UNION UNION ALL

Union Set operator is used to fetch the Union all Set operator is used to fetch
records from 2 different tables which the records from 2 different tables
eliminates the duplicate records which does not eliminates the
 duplicate records
For Performance tuning Union operator Union all is preferable operator in
is not preferable as it takes time to Performance tuning.
eliminate duplicate records

Difference between INTERSECT and MINUS :

INTERSECT MINUS
It fetch the common records from the It fetch the record from one table only
two different tables and not the common records between
two tables
For Intersecting 2 tables the datatype For Minus 2 tables the datatype and
and column name must be same column name must be same between
between 2 tables. 2 tables.
It consider duplicate rows from first and It eliminates duplicate rows from first
second table. and second table.
It always keep the result from both It removes the results from second
table common records. It consider both table and always considered first table
tables result only.

JOINS
Joins are used to retrive the data from multiple tables based on values of the related
columns. The related columns are typically the primary key column(s) of the first
table and foreign key column(s) of the second table.

Types of 8i joins:-
[Link] Join
[Link] Equi Join
[Link] Join
[Link] Join

Types of 9i joins :-
[Link] Join
[Link] Outer Join
[Link] Outer Join
[Link] Outer Join

[Link] Join (OR) Inner Join :

EQUI JOIN is a specific type comparison base join which fetch matching records on
based on equality condition (=) and not allowing other comparison operator such as
<, > <= etc. And create record set result that are combining columns value from the
tables (two or more table).
When tables are having common column then only we are allowed to use equi join
and joining condition column must belong to same datatype.

Syntax: select col1,col2,… from table1,table2 where

table1.common_column=table2.common_column;

--Ex- select ename, sal, deptno, dname, loc from emp, dep
Where [Link]=[Link];

ERROR: column ambiguosly defined

For avoiding this error then we must use column alias names with join condition
using dot (.) operator.
We can create table alias name in FROM clause and we can use that table alias name
with all columns which are in select statement to define which column referneces to
which table.

Syntax: from table_name1 alias_name1, table_name2 alias_name2;

--Ex- select [Link], [Link], [Link], [Link], [Link] from emp e , dep d
Where [Link]=[Link];

--Ex- select e.*, d.* from emp e, dep d where [Link]=[Link];

--Ex- WAQ to display employees who are working in locaton “CHICAGO” from emp,
dep table using EQUI JOIN.

Select [Link], [Link] from emp e, dep d

where [Link]=[Link] and [Link]= ‘CHICAGO’ ;

NOTE: If you want to filter data after joining condition then we are using AND
operator in 8i joins where as in 9i joins we are using either AND (OR) where
clause also.

--Ex- WAQ to display the dname, sum of sal from emp , dep tables using equi join

Select [Link], sum ([Link]) from emp e, dep d where [Link]= [Link]
Group by [Link];

Select [Link], [Link], sum ([Link]) from emp e , dep d where [Link]=[Link]
Group by [Link], [Link] ;
--Ex- WAQ to display location, no. of employees, minimum salary, max salary, avg
salary from emp and dep table using equi join

Select [Link] , count (*) , min ([Link]) , max ([Link]), avg ([Link]) from emp e, dep d
Where [Link]=[Link] group by [Link];

--Ex- WAQ to display location, no. of employees, minimum salary, max salary, avg
salary from emp and dep table having sum of salary > 10000 using equi join

Select [Link] , count (*) , min ([Link]) , max ([Link]), avg ([Link]) from emp e, dep d
Where [Link]=[Link] group by [Link] having sum(sal) > 10000 ;

--Ex- WAQ to show subtotal of salary department name wise.

select [Link], sum ([Link]) from emp e, dep d where [Link]=[Link]

Group by rollup ([Link]);

[Link] Equi Join :

Based on other than Equality condition (< , > ,<=, >=, <>, between)
We are retriving data from multiple table.
--Ex- create table test_1 (empno number(10), deptno number (10));
Insert into test_1 values (100,10);
Insert into test_1 values (200,20);

create table test_2 (empno number(10), deptno number (10));

Insert into test_2 values (300,10);
Insert into test_2 values (400,20);
Insert into test_2 values (500,30);

Select a.*, b.* from test_1 a, test_2 b where [Link] > [Link];

Select a.*, b.* from test_1 a, test_2 b where [Link] <> [Link];

[Link] Join :
Joining a table to itself is called self join. In other words we can say that it is a join
between two copies of the same table.
Conditional column must belong to same datatype.
Whenever we want to compare two different column value from same table then we
must use self join. We must use alias name for both tables.

Syntax: SELECT a.column_name, b.column_name... FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

--Ex- WAQ to display ename and mgrname from emp table by using self join.
Select [Link] “employees” , [Link] “manager” from emp e1, emp e2
Where [Link] =[Link];
(OR using literal in query)
Select [Link] || ' Working for ' || [Link] "Employee working for manager"
from emp e1, emp e2 Where [Link] =[Link];

--Ex- WAQ to display the employees who are getting more salary than their manager
salary from emp table.

select [Link] "Employee", [Link], [Link] ,[Link] "Manager" ,[Link]

from emp e1, emp e2 where [Link] = [Link] and [Link] > [Link];

--Ex- WAQ to display the employees who are joining before their manager from
emp table.

Select [Link] “employees” , [Link] “manager” from emp e1, emp e2

Where [Link] = [Link] and [Link] < [Link] ;

Self-Joins Using the ON Clause: The ON clause can be used to join columns that have
different names, within the same table or in a different table.

Syntax: SELECT a.column_name, b.column_name... FROM table1 a JOIN table1 b

ON (a.common_filed = b.common_field);

--Ex- WAQ to display the employees who are getting more salary than their manager
salary from emp table using self join with ON clause.

Select [Link] "emp", [Link] "mgr" from emp e1 INNER JOIN emp e2
ON [Link] = [Link] and [Link]> [Link] ;

NOTE: You can use self join within all 9i joins as we used inner join for self join
in above Ex. You can use LEFT JOIN, RIGHT JOIN, FULL JOIN instead of inner
join

[Link] Join :
This join is used to retrive all rows from one table and matching rows from another
table.
If we want to retrive non-matching rows then we are using JOIN operator (+) within
joining condition of the EQUI JOIN this is called OUTER JOIN.
NOTE: This join operator can be used one side at a time within joining
condition.
--Ex- select [Link], [Link], [Link], [Link], [Link] from emp e, dep d
Where [Link] (+) = [Link]; (It work like right outer join)

--Ex- select [Link], [Link], [Link], [Link], [Link] from emp e, dep d
Where [Link] = [Link] (+); (It work like left outer join)

** 9i joins :
[Link] Join :
This join also returns matching rows only, here also join condition column must
belong to same datatype. When tables having one common column then only we can
use innner join.

Syntax: select * from table1 JOIN/INNER JOIN table2

ON table1.column_name = table2.column_name;

--Ex- select [Link], [Link], [Link], [Link], [Link] from emp e INNER JOIN dep d
ON [Link] = [Link] ;

--Ex- WAQ to display the employees who are working in loc “CHICAGO” from emp
table , dep table using inner join.

Select [Link],[Link],[Link], [Link],[Link],[Link] from emp e INNER JOIN dep

d
ON [Link] = [Link] and [Link]= ‘CHICAGO’ ;
NOTE: You can use WHERE clause or AND clause to define filter condition after
join condition in 9i joins.

USING Clause: In 9i joins we can also use USING clause in place of ON clause to
define the join condition. USING clause only return common column one time only.

Syntax: select * from table1 JOIN/INNER JOIN table2

USING (common_column_names) ;

--Ex- WAQ to show employees details with their department name.

Select [Link] , [Link], [Link], [Link] from emp e INNER JOIN dep d
Using ([Link]) where [Link] >2000 ;
o/p- ERROR

select ename, sal, deptno, dname from emp INNER JOIN dep using (deptno)
where sal >= 2000 ;

NOTE: We cant use alias names in join query if we used USING clause to define
join condition.
[Link] Outer Join:
This join always return all rows from left table and matching records from right side
table and also returns NULL values in place of non-matching records in another table.
Syntax:
SELECT [Link], [Link] FROM table1 LEFT OUTER JOIN table2
ON ([Link] = [Link]);

NOTE: You can use USING clause in place of ON clause to eliminate the common
column duplication.

--Ex- WAQ to display the NEW YORK loc employees details using left outer join.

Select [Link],[Link],[Link], [Link],[Link] from emp e LEFT JOIN dep d

ON [Link] = [Link] and [Link]= 'NEW YORK' ;

[Link] Outer Join:

This join always return all rows from right table and matching records from left side
table and also returns NULL values in place of non-matching records in another table.
Syntax: SELECT [Link], [Link] FROM table1 RIGHT OUTER JOIN
table2 ON ([Link] = [Link]);

NOTE: You can use USING clause in place of ON clause to eliminate the common
column duplication.

--Ex- WAQ to display the NEW YORK loc employees details using right outer join.

Select [Link],[Link],[Link], [Link],[Link] from emp e RIGHT JOIN dep d

ON [Link] = [Link] and [Link]= 'NEW YORK' ;

[Link] Outer Join:

This join returns all rows from all tables because it is combination of left,right outer
join. This join also return NULL values in place of non-matching rows.
Syntax: SELECT [Link], [Link] FROM table1 FULL OUTER JOIN table2
ON ([Link] = [Link]);

NOTE: You can use USING clause in place of ON clause to eliminate the common
column duplication.

--Ex- WAQ to display the NEW YORK loc employees details using full outer join.

Select [Link],[Link],[Link], [Link],[Link] from emp e FULL JOIN dep d

ON [Link] = [Link] and [Link]= 'NEW YORK' ;
[Link] Join:
This join also returns matching rows only. This join performance is very high as
compare to INNER join.
In this join we are not require to define join condition explicitly on common column.
Based on common column oracle internally define the joining condition .
Syntax: select * from table1 natural join table2;

--Ex- WAQ to display the NEW YORK loc employees details using full outer join.

Select * from emp NATURAL JOIN dep ;

NOTE: We are not allowed to use alias names in NATURAL JOIN because inernally
this join uses USING clause to define the join condition. And USING clause doesn’t
work with alias names.

[Link] Join :
The SQL CROSS JOIN produces a result set which is the number of rows in the first
table multiplied by the number of rows in the second table if no WHERE clause is
used along with CROSS [Link] kind of result is called as Cartesian Product.

If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Syntax: SELECT table1.column1 , table1.column2, table2.column1...

FROM table1 CROSS JOIN table2;

--Ex- select ename, sal, dname, loc from emp cross join dep ; ---cross join

--Ex- select ename,sal,dname, loc from emp , dep; --- cross join

--Ex- select e.*,d.* from emp e cross join dep d where [Link]=[Link];

(Using WHERE condition then it will work like inner join.)

Excersice:-
--Ex- create two tables A and B and perform ALL joins ON given table.

Table A Table B
ID ID
1 1 Select [Link] ,[Link] from t1 a, t2 where [Link]=[Link];
2 2 Select [Link] ,[Link] from t1 a INNER JOIN t2 b on [Link]=[Link];
3 3 Select [Link] ,[Link] from t1 a LEFT JOIN t2 b on [Link]=[Link];
Null Select [Link] ,[Link] from t1 a RIGHT JOIN t2 b on [Link]=[Link];
4 Select [Link] ,[Link] from t1 a FULL JOIN t2 b on [Link]=[Link];
5
6

EQUI JOIN INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN
ID ID ID ID ID ID ID ID ID ID
1 1 1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2 2 2
3 3 3 3 3 3 3 3 3 3
NOTE: EQUI Join and INNER join doesn’t accept Null Null Null Null
any null values for comparison. Whereas all Null 5 Null 4
other 9i joins accepts the null values for Null 6 Null 5
comparison. Null 4 Null 6

--Ex- Below example is for knowledge purpose.

T1 T2 In this tables t1 and t2 both are having same number as input so all
0 0 joins will produce “CARTESION PRODUCT” so all joins will give the
0 0 output in 16 digits as cross join (cartesion product).
0 0 Whenever you get single digit (0 to 9) in two tables always it will
0 0 produce cartesion product as o/p for all joins.

--Ex- create two tables tab4 and tab5 and perform ALL joins on given table.

Tab4 Tab5 select [Link],[Link] from tab4 a, tab5 b where [Link]=[Link];

SNO SNO select [Link],[Link] from tab4 a INNER JOIN tab5 b on
1 Null [Link]=[Link];
1 1 select [Link],[Link] from tab4 a LEFT JOIN tab5 b on
1 0 [Link]=[Link];
0 1 select [Link],[Link] from tab4 a RIGHT JOIN tab5 b on
null Null [Link]=[Link];

select [Link],[Link] from tab4 a FULL JOIN tab5 b on [Link]=[Link];

EQUI JOIN INNER JOIN LEFT OUTER RIGHT OUTER FULL OUTER
JOIN JOIN JOIN
SNO SNO SNO SNO SNO SNO SNO SNO SNO SNO
1 1 1 1 1 1 1 1 1 1
 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
0 0 0 0 0 0 0 0 0 0
null null null null Null Null
null null Null Null
null Null

--Ex- create two tab_2 and tab_3 and perform ALL joins on given table.
Tab_2 Tab_3
select a.*,b.* from tab_2 a, tab_3 b where [Link]=[Link];
NOS NOS
select a.*,b.* from tab_2 a INNER JOIN tab_3 b on
1 1
[Link]=[Link];
2 1
select a.*,b.* from tab_2 a LEFT JOIN tab_3 b on [Link]=[Link];
4 2
select a.*,b.* from tab_2 a RIGHT JOIN tab_3 b on
5 2
[Link]=[Link];
null 6
select a.*,b.* from tab_2 a FULL JOIN tab_3 b on [Link]=[Link];
7

EQUI JOIN INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN
SNO SNO SNO SNO SNO SNO SNO SNO SNO SNO
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2 2
4 null Null 6 4 Null
5 Null Null 7 5 Null
null Null null Null
null 6
null 7

** Difference between Natural JOIN and CROSS JOIN

NATURAL JOIN CROSS JOIN

Natural Join joins two tables based on Cross Join will produce cross or
join condition. cartesian product of two tables .
Join condition internally generated by Join condition is not needed
 DB implicitely
Rows return based on join condition Cartesion product base rows are
return

** difference between INNER JOIN and OUTER JOIN:

INNER JOIN OUTER JOIN

It returns the matched records It return all the records from both
between two or more tables. table (matched and unmatched)
Used clause INNER JOIN and JOIN. Used clause LEFT OUTER JOIN, RIGHT
OUTER JOIN, FULL OUTER JOIN, etc.
When any attributes are not common if any attributes are not common then
then it will return nothing. it will return non matching records.
it is used when we want detailed It is used when we want to complete
information about any specific information.
attribute.

** difference between EQUI JOIN and INNER JOIN :

EQUI JOIN INNER JOIN

It shows matched records only It shows matched records also
Join condition always define in WHERE Join condition always define in ON
clause clause or USING clause
It only work with equality operator. It can work with equality operator as
well as with <, > ,>=, <= also.

** Difference between LEFT JOIN and RIGHT JOIN:

LEFT JOIN RIGHT JOIN

It joins two or more tables, returns all It joins two or more tables, returns all
records from the left table, and records from the right table, and
matching rows from the right-hand matching rows from the left-hand
table. table.
The result-set will contain null value if The result-set will contain null value if
there is no matching row on the right there is no matching row on the left
side table. side table.
It is also known as LEFT OUTER JOIN. It is also known as RIGHT OUTER JOIN.

CONSTRAINTS

CONSTRAINTS are used to define some conditions that restrict the invalid data to
maintain valid data while inserting or updating or deleting data in the column. It will
maintain the integrity of the data.
The SQL CONSTRAINTS are used to implement the rules of the table. If there is any
violation of the constraints caused some action not performing properly on the table
the action is aborted by the constraint.

Constraints are categorized as follows.

[Link] integrity constraints
Not null
Check
[Link] integrity constraints
Unique
Primary key
[Link] integrity constraints
Foreign key

Constraints are always attached to a column not a table.

We can add constraints in three ways.

Column level -- along with the column definition
Table level -- after the table definition
Alter level -- using alter command

While adding constraints you didn’t specify the name for constraint then oracle will
internally name the constrain, name will start from SYS_.
If you want to give a name to the constraint, you have to use the CONSTRAINT clause.

*Column Level : Constraints can be specified for individual columns as part of the
column specification.

Syntax: create table table_name (col1 datatype (size) constraint type ,

Col2 datatype (size) constraint type….);

*Table Level : In this method we are defining constraints on group of columns i.e first
we specifying all columns and last only we are specifying constraint type along with
group of columns.

Syntax: create table table_name (col1 datatype(size), col2 datatype (size),..

Constraint type (col1, col2, ….));

Constraint States (Validate vs Novalidate) :-

With all of the constraints we create, there are several keywords used to put the
constraints into several different states.

DEFERRABLE & NOT DEFERRABLE

ENABLE & DISABLE
VALIDATE & NOVALIDATE

1). DEFERRABLE and NOT DEFERRABLE

These two options, DEFERRABLE and NOT DEFERRABLE, determine if checking for
constraints in future transactions can be deferred until the end of the transaction.
DEFERRABLE means it can be deferred using a SET CONSTRAINTS statement, and NOT
DEFERRABLE means it can’t. The default is NOT DEFERRABLE.
For Ex, let’s say we have an employee table that says the salary column must be
greater than 0. We run a transaction which adds a new employee and performs some
other tasks in other tables. However, the salary figure is updated at the end of the
transaction to a non-zero value.
By default, the employee could not be inserted because the salary would be set to 0
and the constraint would have failed. Even though it gets set at the end of the
transaction.
If we set the constraint to DEFERRABLE, we can delay the checking of this constraint
until the end of the transaction, at which point the salary value is greater than 0 and
the constraint passes.
You can’t change the deferrability of a constraint. This means if you create it as
DEFERRABLE then you can’t alter it to be NOT DEFERRABLE, and vice versa. You need
to drop the constraint and create it again.

2). ENABLE and DISABLE

Enabled constraints are “turned on” and are applied to the data in the table. Disabled
constraints are not applied to the data in the table, but appear in the data dictionary
and still exist.

The enabled and disabled states of a constraint can be changed by using ENABLE and
DISABLE. The default is ENABLE.

3). VALIDATE and NOVALIDATE

The behaviour of VALIDATE and NOVALIDATE depends on if the constraint is
ENABLED or DISABLED.
Essentially, VALIDATE means that the data already in the table is checked to ensure it
meets the constraint, and NOVALIDATE means the existing data is not checked.
But, it depends on if it is enabled or disabled.

ENABLE VALIDATE:
All existing and new data must meet the constraint. If any row does not meet the
constraint, the constraint remains disabled or not created.
Specifying this for a primary key will ensure that a validation process is run to ensure
NULL values do not exist. You can avoid this by specifying a NOT NULL constraint on
the column first.
ENABLE NOVALIDATE:
All new data must meet the constraint.
Existing data is not checked against the constraint.

DISABLE VALIDATE:
Disables the constraint.
Drops the index on the constraint.
Keeps the constraint valid.

DISABLE NOVALIDATE:
No effort is made to maintain the constraint or ensure data complies with it.
Optimizer can use constraints in this state.
If you specify ENABLE, then the default is VALIDATE. If you specify DISABLE, then the
default is NOVALIDATE.

[Link] NULL : (constraint type C)

A NOT NULL constraint is a type of constraint that means the specified column must
have a value (it cannot be NULL).
This is used to avoid null values but it accepts duplicate values.
We can add this constraint in column level only, We can not add this constraint on
table level but It is possible to add a NOT NULL constraint to an existing table by using
the ALTER TABLE statement.
Whenever we creating new table using this table(create table t_name as)
Then only Not Null Constraint will be copy..

COLUMN LEVEL
Define constraint without giving userdefine constraint name-
--Ex- create table student(no number(2) NOT NULL, name varchar(10),
marks number(3));

Define constraint with userdefine constraint name-

--Ex- create table student(no number(2) constraint not_null NOT NULL,
Name varchar(10), marks number(3));

ALTER LEVEL
alter table students MODIFY marks NOT NULL; --- without constraint name
alter table students MODIFY marks constraint mrk_nn NOT NULL; --With name

NOTE: In all DB whenever we are copying a data from one table to another
table except NOT NULL all other constraint are never been copied.

If we have a table with data and null values then we have to use NOVALIDATE state
with alter statement.
Alter table student MODIFY marks constraint mrk_nn NOT NULL enable novalidate;

2. UNIQUE : (constraint type u)

This is used to avoid duplicates but it allow nulls.
This constraint can define on column level , table level and alter level.
We can add this constraint in all three levels.
-Unique Constaint Acept Multiple Null values in a single column.

COLUMN LEVEL
--Ex- create table student(no number(2) UNIQUE, name varchar(10),
marks number(3));

--Ex- create table student(no number(2) constraint con_un UNIQUE,

name varchar(10), marks number(3));

TABLE LEVEL
--Ex- create table student(no number(2) , name varchar(10), marks number(3),
UNIQUE (no));

--Ex- create table student(no number(2) , name varchar(10), marks number(3),

Constraint con_un UNIQUE (no));

ALTER LEVEL
--Ex- alter table student add UNIQUE (no);

--Ex- alter table student add constraint con_un UNIQUE (no);

--Ex- If we try to add or Modify UNIQUE constraint on table column and that column
is having duplicate data then we have to use following steps as shown in Ex.
(OR) if you want to insert duplicate data into column by disabling PK/UQ constraint
for short time of period.

these two very important fundamentals:

By default, Oracle will attempt to create a Unique Index to police a PK or UK
constraint. A NOVALIDATE constraint requires a Non-Unique Index for the constraint
to really be “Novalidated”

CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 100;

Note that the ID column is populated with unique values. However, let’s now
introduce a duplicate value, 42:
INSERT INTO ziggy VALUES (42, ‘DUPLICATE’);

Now we want to add/re-enable the constraint on this table.

ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE

NOVALIDATE;

ERROR : ORA-02437: cannot validate (HR.ZIGGY_PK) – primary key violated

Because by default Oracle will attempt to create a Unique index when creating a PK
constraint. A Unique index MUST always contain unique values and so complains
when it stumbles across our duplicate 42 ID value.

However, we explicitly create a Non-Unique index, then we can proceed with

creating the NOVALIDATE constraint as required:

ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id)

USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id)) ENABLE NOVALIDATE;

If we look at the status of the constraint and the type of index used to police the
constraint

SELECT constraint_name, validated, uniqueness

FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;

3. CHECK : (constraint type c)

This is used to insert the values based on specified condition. If the expression
evaluates to true, Oracle accepts the data and carry the insert or update. Otherwise,
Oracle will reject the data and does not insert or update at all.

In oracle CHECK constraint doesn’t work with sysdate function.

We can add this constraint in all three levels.
We can add multiple CHECK condition on column.
The predicate in check constraint can hold a sub query.

COLUMN LEVEL
--Ex- create table student(no number(2) , name varchar(10),
marks number(3) CHECK (marks > 300));

--Ex- create table student(no number(2) , name varchar(10),

marks number(3) constraint con_ch CHECK (marks > 300));

TABLE LEVEL
--Ex- create table student(no number(2) , name varchar(10),
marks number(3), CHECK (marks > 300));

--Ex- create table student(no number(2) , name varchar(10), marks number(3),

constraint con_ch CHECK(marks > 300));

ALTER LEVEL
--Ex- alter table student add CHECK (marks>300);

--Ex- alter table student add constraint con_ch CHECK (marks>300);

If your table is having data and you want to assign CHECK constraint on that table
then you should have the data which fulfill the CHECK condition otherwise it will
through error but we can minimise that by skipping already inserted data by by
passing it from CHECK condition using NOVALIDATE.

--Ex- alter table student add constraint chk_con CHECK (marks>300) enable
novalidate;

4. PRIMARY KEY : (constraint type p)

This is used to avoid duplicates and nulls. This will work as combination of unique and
not null. Whenever we are creating Primary key then oracle internally create btree
indexes on those column. We can add this constraint in all three levels.

A table can only have one primary key on it. If you try to create a second primary key,
you’ll get an error.
A composite primary key can’t have more than 32 columns.
The same column or combination of columns cannot be part of a primary key and a
unique constraint.

Properties :
No duplicate values are allowed, i.e. Column assigned as primary key should have
UNIQUE values only.
NO NULL values are present in column with Primary key.
Only one primary key per table exist although Primary key may have multiple
columns.

Classified as : a) Simple primary key that has a Single column

2) Composite primary key has Multiple column.
Defined in Create table / Alter table statement.

COLUMN LEVEL
--Ex- create table student(no number(2) PRIMARY KEY, name varchar(10),
 marks number(3));
--Ex- create table student(no number(2) constraint con_pk PRIMARY KEY,
name varchar(10), marks number(3));

TABLE LEVEL
--Ex- create table student(no number(2) , name varchar(10), marks number(3),
PRIMARY KEY(no));
--Ex- create table student(no number(2) , name varchar(10), marks number(3),
Constraint con_pk PRIMARY KEY(no));

ALTER LEVEL
--Ex- alter table student add PRIMARY KEY(no);

--Ex- alter table student add constraint con_pk PRIMARY KEY(no);

** Composite Primary Key

If you want to use more than one column in your primary key, then this needs to be
defined as an out of line constraint. This is because you can’t reference more than
one column in an inline constraint.

--Ex- CREATE TABLE employee ( employee_id NUMBER(10),

first_name VARCHAR2(200), last_name VARCHAR2(200),
salary NUMBER(10), hire_date DATE,
CONSTRAINT pk_emp_flname PRIMARY KEY (first_name, last_name) );

In this Ex, we’ve created the table with a primary key on both the first_name and
last_name columns, which means that the combination of those values needs to be
unique.

--Ex- If we want to add PK constraint on table which is holding some data and that
data has duplicate value then we have to use use NOVALIDATE with constraint.

create table t (a number, constraint pk_a primary key (a));

alter table t disable constraint pk_a;

insert into t values(1);

insert into t values(1);

alter table t enable novalidate constraint pk_a;

ERROR: ORA-02299: cannot validate (HR.UNIQ_A) - duplicate keys found

By default a non-deferrable UNIQUE or PRIMARY KEY constraint will attempt to

create a unqiue index. So we have to create NON-UNIQUE index by using DEFERABLE
keyword and that will useful to assign a PRIMARY KEY on duplicate data column.
Solution: create table t (a number, constraint pk_a PRIMARY KEY(a) DEFERRABLE );

select INDEX_NAME, UNIQUENESS from user_indexes where table_name = 'T';

o/p- PK_A, UNIQUE

alter table t disable constraint pk_a;

insert into t values(1);

insert into t values(1);

alter table t enable novalidate constraint pk_a;

o/p:- Table altered.

So this is how we can alter PK and unique key constraint while table is having
duplicate data.

[Link] KEY : (constraint type r)

This is used to reference the parent table primary key/unique column which allows
duplicates and null values. Foreign key always attached to the child table.
One table foreign key must refer to another table primary key and also both column
must belong to same datatype.
We can add this constraint in column, table and alter levels.

COLUMN LEVEL
Create table dep ( deptno number (6) FOREIGN KEY references emp (deptno), dname
varchar2 (20), Loc varchar2 (20));

Create table dep ( deptno number (6)constraint con_fk FOREIGN KEY references
emp (deptno), dname varchar2 (20), Loc varchar2 (20));

TABLE LEVEL
create table emp(empno number(2), ename varchar(10), deptno number(2),
PRIMARY KEY(empno), FOREIGN KEY(deptno) references dept(deptno));

create table emp(empno number(2), ename varchar(10), deptno number(2),

constraint con_fk FOREIGN KEY(deptno) references dept(deptno));

ALTER LEVEL
alter table dept add FOREIGN KEY(deptno) references emp(deptno);

alter table dept add constraint con_fk FOREIGN KEY(deptno) references

emp(deptno);
Once the primary key and foreign key relationship has been created then you can not
remove any parent record if the dependent chileds exists.

To check the constraint,

If Parent table doesn’t have primary key and you are creating FK.
OUTPUT : Error at line 1 : referenced table does not have a primary key.

If Parent table has Primary Key of different datatype.

OUTPUT : Error at line 1 : column type incompatible with referenced column type.

Insert Operation in Foreign Key Table :

If corresponding value in foreign table doesn’t exists, a record in child table cannot
be inserted.
OUTPUT : Error at line 1 : integrity constraint violated - parent key not found.
Solution: First insert in master table then insert in child table.

Delete Operation in Foreign Key Table :

When a record in master table is deleted and corresponding record in child table
exists, an error message is displayed and prevents delete operation from going
through.
OUTPUT : Error at line 1 : integrity constraint violated - child record found.

**Deletion in MASTER Table :

Foreign Key with ON DELETE CASCADE :

The default behavior of foreign key can be changed using ON DELETE CASCADE.
When this option is specified in foreign key definition, if a record is deleted in master
table, all corresponding record in child table will be deleted .

Syntax –
create table dep(deptno varchar2(10), dname varchar2(20), loc varchar2(20),
FOREIGN KEY(deptno) references emp(deptno) ON DELETE CASCADE);

--Ex- create table mas (sno number primary key);

Insert into mas values (1);
Insert into mas values (2);
Insert into mas values (3);

Create table child (sno number(10),foreign key (sno)references mas(sno) ON DELETE

CASCADE);

Insert into child values (1);

Insert into child values (1);
Insert into child values (2);
Insert into child values (2);
Insert into child values (3);
Insert into child values (3);

Testing:- delete from mas where sno=1;

select * from mas;

Foreign Key with ON DELETE SET NULL :

A Foreign key with ON DELETE SET NULL means if record in parent table is deleted,
corresponding records in child table will have foreign key fields set to null. Records in
child table will not be deleted.
Syntax:
create table dep(deptno varchar2(10), dname varchar2(20), loc varchar2(20),
FOREIGN KEY(deptno) references emp(deptno) ON DELETE SET NULL);

create table mas (sno number primary key);

Insert into mas values (1);
Insert into mas values (2);
Insert into mas values (3);

Create table child (sno number(10),

foreign key (sno)references mas(sno) ON DELETE SET NULL);

Insert into child values (1);

Insert into child values (1);
Insert into child values (2);
Insert into child values (2);
Insert into child values (3);
Insert into child values (3);

Testing:- delete from mas where sno=1;

select * from mas;

NOTE: Generally when we are truncatinting master table by using truncate table
table_name then DB server returns error to overcome this problem oracle 12c
provided CASCADE clause along with truncate table table_name.
Syntax: truncate table master_tablename CASCADE;

Before we are using this command we must use ON DELETE CASCADE clause along
with foreign key while creation of table or u can alter the foreign key at alter level.

To check constraint on any table :-

Select * from USER_CONSTRAINT where table_name = ‘TABLE_NAME’ ;

--Ex- select * from USER_CONSTRAINT where table_name=‘EMP’;

If you want to view column names along with constraints then we are using
USER_CONS_COLUMNS data dictionary.
Select * form USER_CONS_COLUMNS where table_name =‘EMP’;

If you want to count the no. of columns of table then-

Select count (*) from user_tab_columns where table_name =‘EMP’;

[Link] :
This constraint is used to provide a default value for the fields. That is, if at the time
of entering new records in the table if the user does not specify any value for these
fields then the default value will be assigned to them.

COLUMN LEVEL
Create table student (ID number(6),NAME varchar2(10),AGE number DEFAULT 18 );

ALTER LEVEL
Alter table Student MODIFY ID DEFAULT 100;

If you want to view default value of any column then we are using -
Select * from user_tab_columns where table_name= ‘STUDENT’ ;

In result window you can check the default condition in DATA_DEFAULT column.

Drop Default Constraint

ALTER TABLE table_name modify COLUMN_NAME DEFAULT null;

You can not drop DEFAULT value constraint from table you can set it to null only
otherwise if you need to DROP it then you may have to recreate your table.

Use below query to make your table copy so default constraint will get removed.

Create table student_1 as select * from student;

Select * from user_tab_columns where table_name= ‘STUDENT_1’ ;

Drop table student;

Rename students_1 to student;

When you create the copy of your base table then all constraints will not copied only
NOT NULL constraint will get copy. This is how you can remove DEFAULT value from
your table column.

OPERATIONS WITH CONSTRAINTS

Possible operations with constraints as follows.
Enable ,Disable , Enforce, Drop

ENABLE :- This will enable the constraint. Before enable, the constraint will check the
existing data.
--Ex- alter table student enable constraint un;

DISABLE :- This will disable the constraint.

--Ex- alter table student disable constraint un;

ENFORCE :- This will enforce the constraint rather than enable for future inserts or
updates. This will not check for existing data while enforcing data.

--Ex- alter table student enforce constraint un;

--DROP :- This will drop the constraint of the table

Method1: alter table table_name drop constraint constraint_name;
Method2: alter table table_name drop primary key ;
(OR)
Alter table table_name drop unique (col1, col2..);

NOTE: If we want to drop primary key along with referenced foreign key then we
are using CASCADE clause along with alter drop.

Alter table table_name drop primary key/unique key cascade;

Alter table emp drop primary key ;

ERROR: this unique key/primary key is referenced by some foreign key.

Solution: alter table emp drop primary key CASCADE;

If you don’t know the constraint name then first we have to find it-
Select * from user_cons_columns Where table_name=‘EMP’;

Alter table emp drop constraint SYS_COO5580;

**Dropping a column from table has these effects on its constraints:

 All single-column constraints are dropped.
 All referential constraints that reference the column are dropped.
 All check constraints that reference the column are dropped.
 If the column is part of a multiple-column primary-key or unique constraint,
the constraints that are placed on the multiple columns are also dropped.
For Ex, if the dropped column is a unique or primary key that is referenced in other
tables, those referential constraints also are dropped. Therefore, the structure of
those other tables is also altered.

**NO VALIDATE clause:-

if table column has 1000 values and those are duplicate and we need to make that
column as PRIMARY KEY to prevent future duplication then it is possible by using “NO
VALIDATE” keyword. When we use NO VALIDATE at that time NON-UNIQUE index
must be present on that column otherwise it will through error.

--Ex- create table tab1 (sno number (10));

Insert into tab1 values (&sno); --- values 10,20,10,10,1020,10
Create index ind_1 on tab1 (sno) ;
Alter table tab1 add primary key (sno) NOVALIDATE;

Q. Suppose you have the table T1 and it has relation with many tables and its
primary key constraint name is "pk_t1" you want to disable these foreign keys.

BEGIN
FOR I IN (SELECT table_name, constraint_name FROM user_constraint
WHERE r_constraint_name='pk_t1') LOOP
EXECUTE IMMEDIATE ' alter table ' || I.table_name || ' disable constraint ' ||
i.constraint_name;
END LOOP;
END;

difference between Primary Key and Foreign Key:

Primary Key Foreign Key

A primary key is used to ensure data in A foreign key is a column or group of
the specific column is unique. [Link] provides a link between
data in two tables.
It uniquely identifies a record in the It refers to the field in a table which is
relational database table. the primary key of another table.
Only one primary key is allowed in a Whereas more than one foreign key
table. are allowed in a table.
It is a combination of UNIQUE and Not It can contain duplicate values
Null constraints.
It does not allow NULL values. It can also contain NULL values.
We can insert a value to a primary key We can’t insert a value to a foreign
attribute, even if the referencing key, if that value is not present in the
foreign key does not have that value in referenced primary key column.
its column.
Before you delete a primary key value, You can delete a value from foreign
 make sure that value is not still present key column without bothering,
in the referencing foreign key column of whether that value is present in
referencing table. referenced primary key column of
referenced relation.

Differences Between Primary key and Unique key:

Primary Key Unique Key

Used to serve as a unique identifier Uniquely determines a row which isn’t
for each row in a table. primary key.
Cannot accept NULL values. Can accept one NULL value.
Only one primary key per table More than one unique key per table can
be used

Difference between UNIQUE and DISTINCT :

UNIQUE constraint DISTINCT function

It is a constraint It is a SQL function
It is assigned with column It can not be used in table specifications
specifications at table creation time
It avoid the duplication of data while It is used with SELECT statement to
insertion operation. retrive unique data
It store in DB with table structure It doesn’t store in database.

Sub Queries
In SQL a query within another query is called as Subquery. In other words we can say
that a Subquery is a query that is embedded in WHERE clause of another SQL query.

A subquery is a query within another query. The outer query is called as main query
and inner query is called as subquery.
The subquery generally executes first, and its output is used to complete the query
condition for the main or outer query.

You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING
clause, FROM clause.
Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with
expression operator. It could be equality operator or comparison operator such as =,
>, =, <= and Like operator.
ORDER BY command cannot be used in a Subquery. GROUPBY command can be used
to perform same function as ORDER BY command.
Used primarily for solving complex use cases and increasing the performance or
speed of a DBMS operation.
You can use a subquery in many places such as:
  With the IN or NOT IN operator
 With comparison operators
 With the EXISTS or NOT EXISTS operator
 With the ANY or ALL operator
 In the FROM clause
 In the SELECT clause

DB systems having 2 types of subqueries:-

1..Non-correlated subqueries
2..corelated subqueries

In non-corelated subqueries child queries are executed first then only parent queries
executed.
Whereas in correlated subqueries parent queries are executed first and then only
child queries are executed.

1..Non-correlated Subqueries :
Non-correlated subqueries in two parts – child queries and parent query.
Child query: A query which provides values to the another query is called child query.
Parent query: A query which receives value from another query is called parent query
4 types of Non correlated subqueries-
Single row subqueries
Multiple row subqueies
Multiple column subqueries
Inline view subqueries

Syntax 1: Subquery in WHERE Clause

SELECT col(s) FROM tab1 WHERE col {=,NOT IN,IN, <,>, etc} (SELECT
column_name(s) from tab2) ;

--Ex- Find the name of department where employee no is 7369.

select dname from dep where deptno = (select deptno from emp where
empno=7369);

Syntax 2 : Subquery in HAVING Clause

SELECT col (s) FROM table1 WHERE condition GROUP BY col (s)
HAVING aggregate_function(col) {=,<,>} (SELECT column_name(s) from table2);

--Ex- Find the departments where the total number of employees is more than the
total number of employees in 10.

select [Link], count([Link]) from emp e inner join dep d on [Link] = [Link]
group by [Link]
having count([Link]) > (select count(empno) from emp where deptno = 10);
Syntax 3 : Subquery in FROM Clause
SELECT col (s) FROM (SELECT col (s) from table_name) as table_alias
WHERE condition;

--Ex- Find the number of employees in each department.

SELECT * FROM (SELECT count(DISTINCT empno)count_emp,deptno FROM emp

GROUP BY deptno) emp_summary ORDER BY count_emp;

--Ex- calculate the average of average salary of departments.

SELECT round(avg(average_salary), 0) FROM (SELECT avg(sal) average_salary

FROM emp GROUP BY deptno) department_salary;

Syntax 4 : Subquery in SELECT clause

SELECT column_name(s), (subquery) FROM table_name

--Ex- finds the salaries of all employees, their average salary, and the difference
between the salary of each employee and the average salary.

SELECT employee_id, first_name, last_name, salary,

(SELECT ROUND(AVG(salary), 0) FROM employees) average_salary ,
salary - (SELECT ROUND(AVG(salary), 0) FROM employees) difference
FROM employees
ORDER BY first_name, last_name;

[Link] Row Subquery:

When Query within a query or subquery returns only one row then these type of
queries are called as single row subqueries.
The most widely used operator for single row subquery is Equal to operator(=).
Here We need to make sure that the query is returning only one [Link] we are
able to use Max,Min,AVG like fuctions which will return only one value.

--Ex- WAQ to find highest salaried Employee from Employee table.

Select empno, ename from Emp where Sal=(Select max(Sal) from Emp);

--Ex- WAQ to display the employees details who are getting more sal than avg sal.

select * from emp where sal > (select avg(sal) from emp);

--Ex- WAQ to display the employees who are working in SALES department.
select * from emp where deptno (select deptno from dep where dname='SALES');

--Ex- WAQ to display most senior employee from emp table

Select * from emp where hiredate = (select max(hiredate) from emp);

NOTE: Generally we are not allowed to use child query table column with
parent query because SubQuery always return parent query table column in
output, To overcome this problem we must use joins within parent query.

--Ex- WAQ to display the employees who are working in SALES department.

Select [Link], [Link] from emp e, dep d where [Link]=[Link]

And [Link] = (select deptno from dep where dname=‘SALES’);

--Ex- WAQ to display those employee who are working same as ‘SMITH’ job.

Select * from emp where deptno= (select deptno from emp where ename=‘SMITH’);

--Ex- WAQ to display the employee details who are getting more salary than highest
salary paid employee of department 20.

Select * from emp where sal> (select max(sal) from emp where deptno=20);

--Ex- WAQ to display department name of highest paid employee from emp, dep
table

Select dname from dep where deptno=(select deptno from emp

where sal = (select ,max(sal) from emp ));

--Ex- WAQ to display 2nd highest salary from emp.

Select max(sal) from emp where sal < (select max(sal) from emp);

--Ex- WAQ to display 3rd highest salary employee details.

Select * from emp where sal= (select max(sal) from emp

where sal < (select max(sal) from emp where sal < (select max(sal) from emp)));

--Ex- WAQ to display lowest average salary JOB from emp table.

Select job, avg(sal) from emp group by job

Having avg(sal) = (select min(avg(sal)) from emp group by job ) ;

NOTE: Whenever in query we use nested group function then we must use group by
clause in child query.

--Ex- WAQ to display which job avg salary having more than CLERK job avg sal.

Select job, avg(sal) from emp group by job

Having avg(sal) > (select avg(sal) from emp where job=‘CLERK’) ;

--Ex- WAQ to display the employees who are working for BLAKE as manager.

Select * from emp where mgr = (select empno from emp where ename=‘BLAKE’);

2. Multi Row Subqueries :

If the output of Inner query count is more than 1 then these subqueries are called as
multi row [Link] need to use ANY,IN,EXIST Operator in outer query of multi
row subqueries because output of outer query is not a single value.

--Ex- Fetch the list of Employees which is assigned to SALES and ACCOUNTING
Department.

Select Empno, Ename from Emp where Deptno in

(Select Deptno from dep where dname in (‘SALES’,’ACCOUNTING’));

--Ex- WAQ to display employee details who are getting highest salary in each
Departments.

Select * from emp where sal in (select max(sal) from emp group by deptno) ;

--Ex- WAQ to display the employees who are working in ‘SALES’ or ‘RESEARCH’
Department

Select * from emp where deptno in (select deptno from emp

where dname=‘SALES’ or dname= ‘RESEARCH’);

--Ex- find all employees who do not locate at the location ‘DALLAS

Select * from emp where deptno not in (select deptno from dep where loc=‘DALLAS’)

Multi row subqueries with the ANY and ALL operator :

The ANY and ALL operators are used with a WHERE or HAVING clause.
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition.

Whenever resource table is having large amount of data and also child query having
max or min aggregate function and also we are compairing values by using relational
comparator (<,>,<=,>=,=) then those types of query degrades the performance of the
application. To overcome this problem for improvement of query SQL provided
subquery special operators these are – ALL , ANY operators

These operators are used along with relational operator in parent query WHERE
condition.
IN  It returns same values in the list of child query.
ALL  It satisfies all values in the list of child query.
ANY  It satisfies any value in the list of child query

You must place an =, <>, <, >, <=, or >= operator before ANY and ALL.
“=ANY” is Equivalent to ‘IN’ Operator.

>ALL means greater than every value--in other words, greater than the maximum
value. For Ex, >ALL (1, 2, 3) means greater than 3.
>ANY means greater than at least one value, that is, greater than the minimum. So
>ANY (1, 2, 3) means greater than 1.

--Ex- WAQ to display employees who are getting more salary than highest paid
employee of the department 20.

Using relational operator-

Select * from emp where sal > (select max(sal) from emp where deptno=20);

Using ALL operator-

Select * from emp where sal > ALL (select max(sal) from emp where deptno=20);

--Ex- WAQ to display the employees who are getting more salary then the all salary of
the CLERK from emp table.

Select * from emp where sal >ALL (select sal from emp where job=‘CLERK’);

--Ex- WAQ to find all employees whose salaries are greater than or equal to the
highest salary of every department.

Select * from emp where sal > any ( select max(sal) from emp group by deptno) ;

NOTE: If a subquery returns zero rows using ALL operator , the condition
evaluates to TRUE. In the following Ex, the subquery returns zero rows, which
means the whole expression "sal > ALL (zero rows)" evaluates to TRUE, so all
rows are displayed.

SELECT [Link], [Link] FROM emp e1

WHERE [Link] > ALL (SELECT [Link] FROM emp e2 WHERE [Link] = 100);
NOTE: If a subquery returns zero rows using ANY operator, the condition
evaluates to FALSE. In the following Ex, the subquery returns zero rows, which
means the whole expression "sal > ANY (zero rows)" evaluates to FALSE, so no
rows are displayed.

SELECT [Link], [Link] FROM emp e1

WHERE [Link] > ANY (SELECT [Link] FROM emp e2 WHERE [Link] = 100);

NOTE: Whenever we are using ALL operator in subquery then internally server
uses logical operator ‘AND’ operator. When we use Any operator in subquery
then internally server uses logical ‘OR’ operator.

--Ex- select * from emp where deptno >all (20,30);

o/p- 30

--Ex- select * from emp where deptno >any (20,30);

o/p- 20 and 30

[Link] Column Subqueries

In all DB we can also compare multiple columns values of child query table with
multiple column values of parent query table, these type of subqueries are called as
multiple column subqueries.

Syntax: select * from table_name where (col1,col2..) IN (select col1,col2..) from

Where condition ;

--Ex- WAQ to display the employees whos job ,mgr match with the job,mgr of the
employee ‘SCOTT’ from emp table.

Select * from emp where (job,mgr) in (select job,mgr from emp where
ename=‘SCOTT’) ;

--Ex- WAQ to display the employee who are getting highest salary from emp table

Select deptno, sal, ename from emp where (deptno, sal) IN (select deptno, max(sal)
from emp group by deptno ) ;

--Ex- WAQ to display sal,comm matching with sal,comm of the employees who are
working at location ‘DALLAS’

Select * from emp e , dep d where [Link]=[Link] and (sal, nvl(comm,0)) IN

(select sal, nvl(comm,0) from emp e , dep d
where [Link]=[Link] and [Link]= ‘DALLAS’ );

--Ex- WAQ to display senior employees for each department wise

Select job,hiredate,ename from emp

where (job,hiredate) IN (select job, max(hiredate) from emp group by job) ;

2..Correlated Subqueries

Correlated subquery - In correlated subquery, inner query is dependent on the outer

query. Outer query needs to be executed before inner query.
Generally in non-correlated child query is executed once for parent query wheras in
correlated query child query is executed for every row of parent query table.

In correlated subqueries we must give alias names for parent query and then pass
alias name in child query WHERE condition.
It does not use IN and NOT IN clause.

Syntax: select * from table_name aliasname where Column_name = (select * from

table_name where columname=[Link]) ;

If we want to modify 1 table column value based on another table column then only
we are using CORRELATED UPDATES.

Syntax: update table1 Alias1 set

Column1 = (select column2 from table2 alias2
where alias1.Common_column = alias2.common_column);

If we want to delete 1 table column value based on another table column then only
we are using CORRELATED DELETE.

Syntax: DELETE FROM table1 alias1

WHERE column1 = (SELECT expression FROM table2 alias2
WHERE [Link] = [Link]);

Whenever we are submitting co-related subqueries into the DB server then the DB
gets the candidate row from parent query table and then control passed to child
query WHERE condition and then based on evaluation values , it compares value with
parent query.

--Ex- WAQ to display 1 st highest salary employee from emp table.

Select * from emp e1

where 1 = (select count(*) from emp e2 where [Link] >= e1,sal);

--Ex- WAQ to display 2 nd highest salary employee from emp table.

Select * from emp e1

Where 2= (select count(*) from emp e2 where [Link] >= [Link]) ;

Whenever resource table is having duplicate data in column then above query
doesn’t return any result. To overcome this issue we must use DISTINCT clause.

--Ex- WAQ to display 2 nd highest salary employee from emp table.

Select * from emp e1

Where 2= (select count(distinct(sal)) from emp e2 where [Link] >= [Link]) ;

--Ex- WAQ to display 2 nd highest salary employee from emp table.

select * from emp e1 where

&no = (select count(distinct(sal)) from emp e2 where [Link]>=[Link]);

Multi row correlated subqueries with EXISTS or NOT EXISTS operator:

EXISTS operator only used in CORELATED SUBQUERIES.
The EXISTS operator checks for the existence of rows returned from the subquery. It
returns true if the subquery contains any rows. Otherwise, it returns false.

EXISTS is much faster than IN, when the sub-query results is very large,the EXISTS
operator provides a better [Link] is faster than EXISTS, when the sub-query
results is very small.

The Exists keyword evaluates true or false, but IN keyword compare all value in the
corresponding sub query column.

--Ex- WAQ to find all departments which have at least one employee with the salary
is greater than 3000.

select [Link] from dep d where exists (select [Link] from emp e where [Link] > 3000
and [Link]=[Link]) ;

--Ex- WAQ to find all departments which don’t have at least one employee with the
salary is greater than 3000.

select [Link] from dep d where exists (select [Link] from emp e where [Link] > 3000
and [Link]=[Link]) ;

--Ex- WAQ to display those department names who have employees in emp table.
Select * from dep where exists (select deptno from emp);

--Ex- WAQ to display the employees who are getting same salary as ‘SCOTT’ from
emp table.

Select * from emp e1

where exists (select * from emp e2 where [Link]=[Link] and [Link]=‘SCOTT’);

Difference Between IN and EXISTS

EXISTS IN
SQL Engine will stop the process as SQL Engine compares all values in IN
soon as it finds a single positive condition
condition in EXISTS condition
The answer of EXISTS can be TRUE or The answer of IN can be TRUE or
FALSE FALSE or NULL
EXISTS cannot compare values between IN compares values between parent
parent query and sub-query query and sub-query
It can be used to determine if any IN is used as multiple OR operator
values are returned or not
EXISTS is faster than IN if sub-query IN is faster than EXISTS if sub-query
result is large result is less
NULL can be compared using EXISTS NULL can be compared using IN
condition condition

Difference between JOIN and SUB-QUERY :

JOINS SUBQUERY
It connects two or more tables and Subquery fetch the data from one
selects data from them into a single table based on inner query output.
result set.
joins are used to return rows. Subqueries can be used to return
either a scalar (single) value or a row
set.
Join shows the data in result set from Subquery will show the data in result
both joined tables. set from outer query result set only
Join are faster when large amount of Subquery is faster when small amount
data. of data.
Join condition is needed, to place join Join condition is not needed.
between multiple table.
Joins are used in the FROM clause of subqueries used in most clauses such
the WHERE statement as SELECT , WHERE, FROM, HAVING.
Difference between correlated and noncorrelated subquery :

CORRELATED NONCORRELATED
In correlated subquery, inner query is In non-correlated query inner query
dependent on the outer query does not dependent on the outer
query
It does not use IN and NOT In clause Non-Correlated subquery are used
along-with IN and NOT IN clause
Inner query can not run alone Inner query can run alone and it's not
depended on outer query
correlated subqueries are slower They are faster than correlated
queries subqueries

Inline View
Generally we are not allowed to use order by clause in child query to overcome this
problem oracle introduced subqueries in FROM clause these type of queries are
called INLINE VIEW .

Syntax: select * from (subquery) ;

Advantages:
 We do not need to create the temporary table. This prevents the database
from having too many objects, which is a good thing as each additional object
in the database costs resources to manage.
 We can use a single SQL query to accomplish what we want.

Generally we are not allowed to use column alias names in WHERE clause if we want
to use column alias name in WHERE clause then must use INLINE VIEW.

--Ex- select ename, sal, sal*12 annsal from emp where annsal > 3000;
ERROR: “ANNSAL” : Invalid identifier

Solution: select * from (select ename, sal, sal*12 annsal from emp)
where annsal > 3000 ;

An inline view is a SELECT statement in the FROM-clause of another SELECT

statement. In-line views are commonly used to simplify complex queries by removing
join operations and condensing several separate queries into a single query.

inline view: data manipulation

following statement increases the salary of Sales department employees by 100:

update (select sal from emp INNER join dep using (deptno) where dname='Sales')
set sal = sal + 100;
following statement delete the Sales department employees whos salary is less than
1000:

delete (select sal from emp INNER JOIN dep using(deptno) where dname = 'Sales')
where sal < 1000;

Inline View using joins-

select deptno, dname, (select count(*) from emp e where [Link] = [Link])
employee_count from dep d

**** PSEUDO COLUMNS ROWID and ROWNUM in SQL

Pseudocolumns are actually associated with the table data but it has nothing to do
with table [Link] & ROWNUM are pseudocolumns which are not actual
columns in the table but behave like actual columns.
Pseudocolumns are nothing but logical columns which behaves like a physical
columns in database.

** ROWID:
 ROWID is nothing but the physical memory location on which that data/row is
[Link] basically returns address of row.
 ROWID uniquely identifies row in database.
 ROWID is combination of data object number,data block in datafile,position of
row and datafile in which row resides.
 ROWID is 16 digit hexadecimal number whose datatype is also ROWID Or
UROWID
 The fastest way to access a single row is ROWID
 ROWID is unique identifier of the ROW.

Ex:- select rowid from dual;

AAAAECAABAAAAgiAAA

** ROWNUM :
 ROWNUM is magical column in Oracle which assigns the sequence number to
the rows at the time of selection in the table.
 To limit the values in the table you can use ROWNUM pseudocolumn
 ROWNUM is nothing but logical sequence number given to the rows fetched
from the table.
 ROWNUM is logical number assigned temporarily to the physical location of
the row.
 You can limit the values in the table using ROWNUM
 ROWNUM is also unique temparary sequence number assigned to that row.
Ex: Select Rownum from dual;
Answer- 1

** difference between ROWID & ROWNUM

ROWID ROWNUM
ROWID is nothing but Physical memory ROWNUM is nothing but the
allocation sequence which is allocated to that
data at the time of selection.
ROWID is permanant to that row which ROWNUM is tempararily allocated
identifies the address of that row. sequence to the rows.
ROWID is 16 digit Hexadecimal number ROWNUM is numeric sequence
which is uniquely identifies the rows. number allocated to that row
temporarily.
ROWID returns PHYSICAL ADDRESS of ROWNUM returns the sequence
that row. number to that row.
ROWID is automatically generated ROWNUM is an dynamic value
unique id of a row and it is generated at automatically retrieved along with
the time of insertion of row. select statement output.
ROWID is the fastest means of ROWNUM is not related to access of
accessing data. data.

Top-N analysis
Top-N queries are useful in scenarios where the need is to display only the n top-
most or the n bottom-most records from a table based on a condition. This result set
can be used for further analysis. For Ex, using Top-N analysis you can perform the
following types of queries:
Top three earners in the company
Four most recent recruits in the company
Top two sales reps who have sold the maximum number of products
Top three products that have had maximum sales in the last six months

Syntax: select [column_list], ROWNUM [ROWNUM_ALIAS] from

(select [column_list] from table ORDER BY Top-N_column) where ROWNUM<=N;

A WHERE clause, which specifies the n rows to be returned. The outer WHERE clause
must use a < or <= operator.

--Ex- select rownum, ename from emp where deptno = 10;

--Ex- WAQ to display first row from emp table using rownum.

Select * from emp where rownum=1;

--Ex- WAQ to display second row from emp table.

Select * from emp where rownum=2;

o/p- No rows selected

Generally rownum doesn’t work with more than 1 positive integer i.e it works only
with < ,<= operators.

--Ex- WAQ to display first five rows from emp table

Select * from emp where rownum <= 5;

--Ex- WAQ to display first five highest salary employees from emp table.

Select * from (select * from emp order by sal desc) where rownum<=5;

--Ex- WAQ to display TOP 3 earners names and their salaries.

select ROWNUM as RANK , ename, sal from

(select ename,sal from emp ORDER BY sal DESC) WHERE RNK<=3;

--Ex- WAQ to find the top 4 senior most employees in the emp table.

select * from
(select ename,hiredate from emp order by hiredate) where rownum <=4;

--Ex- WAQ To find 4 junior most employees in the emp table.

select * from(select ename,hiredate from emp order by hiredate desc)

where rownum <=4;

--Ex- WAQ to display 5th highest salary employee details.

select * from(select * from emp order by sal desc) where rownum <=5
minus
select * from(select * from emp order by sal desc) where rownum <=4;

--Ex- WAQ to display rows between 3 to 7 from emp table.

select * from(select * from emp order by sal desc) where rownum <=7
minus
select * from(select * from emp order by sal desc) where rownum <=3;

--Ex- WAQ to display row between 1 to 5 from emp table.

select * from(select * from emp order by sal desc) where rownum between 1 and 5;

--Ex- WAQ to display middle record of emp table.

Select * from (select rownum r, e.* from emp e)

where r = trunc ((select count(*) from emp)/2);

--Ex- WAQ to display last two records from emp table.

Select * from emp

Minus
Select * from emp where rownum <= (select count(*) – 2 from emp);

Whenever we are using alias name for ROWNUM then in inline view that alias name
works with all SQL operators.

--Ex- WAQ to display 2nd row of the emp table.

Select * from (select rownum r, e.* from emp e) where r=2;

--Ex- WAQ to display rows between 3 to 7 from emp table.

Select * from (select rownum r, e.* from emp e) where r between 3 and 7;

--Ex- WAQ to display 2nd,3rd,5th,8th,10th record of emp table

Select * from (select rownum r, e.* from emp e) where r IN (2,3,5,8,10) ;

--Ex- WAQ to display first row and last row of emp table.

Select * from (select rownum r, e.* from emp e)

where r=1 OR r=(select count(*) from emp) ;

--Ex- WAQ to display even no of records from emp.

Select * from (select rownum r, e.* from emp e) where mod(r,2)=0 ;

--Ex- WAQ to display odd no of records from emp.

Select * from (select rownum r, e.* from emp e) where mod(r,2)!=0 ;

--Ex- WAQ to display 5th highest salary employee details from emp table.

Select * from (select rownum r, ename,sal from (select * from emp order by sal
desc)) where r=5 ;
** WITH Clause and ROWNUM
--Ex- WAQ to display ‘n’th highest salary employees from emp table
WITH highest_sal AS
(select * from emp order by sal desc)
select * from highest_sal where rownum<= &no ;

--Ex- WAQ to display 3rd highest salary from emp table.

with high_3 as
(select rownum r, empno, ename, sal, deptno from (select * from emp order by sal
desc))
select * from high_3 where r=5;

--Ex- give the emp detail wos sal is greter than the avg sal
WITH temp as
(SELECT avg(Salary) avg from Employees)
select e.Employee_id, e.first_Name, [Link] FROM Employees e, temp
WHERE [Link] > [Link] order by [Link] desc;

With Dep_Count As
(Select Deptno,Count(Empno)No_Of_Emp From Emp Group by Deptno)
Select Empno, Sal/No_Of_Emp From Emp E, Dep_Count C Where [Link] =
[Link];

with a as
(select department_id, count(employee_id) cnt_emp from employees
group by department_id)

select d.department_id, a.cnt_emp from departments d left join a

where d.department_id = a.department_id ;

ANALYTIC FUNCTIONS

Probably the easiest way to understand analytic functions is to start by looking at

aggregate functions. the aggregate function reduces the number of rows returned by
the query.
Analytic functions also operate on subsets of rows, similar to aggregate functions in
GROUP BY queries, but they do not reduce the number of rows returned by the
query.

Syntax: analytical_function_name () over (partition by column order by column);

In here partition by and order by clause are optional but over () is mandatory.
Partition by clause:- The query_partition_clause divides the result set into partitions,
or groups, of data. If the query_partition_clause is omitted, the whole result set is
treated as a single partition

An aggregate function, as the name suggests, aggregates data from several rows into
a single result row.
For Ex, we might use the AVG aggregate function to give us an average of all the
employee salaries in the EMP table.

SELECT AVG(sal) FROM emp;

AVG(SAL) : 2073.21429

The GROUP BY clause allows us to apply aggregate functions to subsets of rows.

For Ex, we might want to display the average salary for each department.

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno;

DEPTNO AVG(SAL)
---------- ---------------
10 2916.66667
20 2175
30 1566.66667

In both cases, the aggregate function reduces the number of rows returned by the
query.

Analytic functions also operate on subsets of rows, similar to aggregate functions in

GROUP BY queries, but they do not reduce the number of rows returned by the
query.
For Ex, the following query reports the salary for each employee, along with the
average salary of the employees within the department.

SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) "avg_dept_sal"

FROM emp;

EMPNO DEPTNO SAL AVG_DEPT_SAL

---------- ---------- ------- ------------
7782 10 2450 2916.66667
7839 10 5000 2916.66667
7934 10 1300 2916.66667

7566 20 2975 2175

……..
 7788 20 3000 2175

7521 30 1250 1566.66667

……
7654 30 1250 1566.66667

Notice how the AVG function is still reporting the departmental average, like it did in
the GROUP BY query, but the result is present in each row, rather than reducing the
total number of rows returned.

The following query uses an empty OVER clause, so the average presented is based
on all the rows of the result set.

SELECT empno, deptno, sal, AVG(sal) OVER ( ) "avg_dept_sal" FROM emp;

Analytic Function Types-

COUNT () analytical function

MIN () analytical function
MAX () analytical function
SUM () analytical function
AVG () analytical function
FIRST_VALUE () analytical function
LAST_VALUE () analytical function
LEAD () analytical function
LAG () analytical function
ROW_NUMBER () analytical function
RANK () analytical function
DENSE_RANK () analytical function.

** COUNT ( ) analytical function :-

COUNT () analytical function will count the values based on analytic_clause without
minimizing original data.
Analytic functions allow us to return these aggregate values while retaining the
original row data.

Syntax: COUNT ( { * | [ DISTINCT | ALL ] expr } ) OVER (analytic_clause)

Omitting a partitioning clause from the OVER clause means the whole result set is
treated as a single partition. In the following Ex we display the number of employees,
as well as all the original data.

SELECT empno,ename,deptno,sal,COUNT(*) OVER () AS count FROM emp;

Adding the partitioning clause allows us to return the count within a partition.

SELECT empno,ename,deptno,sal,COUNT(*) OVER (partition by deptno) AS

count_dep FROM emp;

** MIN () analytical function :-

MIN () analytical function will find the minimum value from table based on
analytic_clause without minimizing original table data.

Syntax : MIN ( [ DISTINCT | ALL ] expr ) OVER (analytic_clause)

Using an empty OVER clause turns the MIN function into an analytic function. The
lack of a partitioning clause means the whole result set is treated as a single partition,
so we get the minimum salary for all employees, as well as all the original data.

SELECT empno,ename,deptno,sal, MIN(sal) OVER () AS min_sal FROM emp

ORDER BY deptno;

Adding the partitioning clause allows us to display the minimum salary per
department, along with the employee data for each department.

SELECT empno,ename,deptno,sal, MIN(sal) OVER (partition by deptno) AS min_sal

FROM emp ORDER BY deptno;

** MAX () analytic function :-

MAX () analytical function will find the maximum value from table based on
analytic_clause without minimizing original table data.

NOTE: MAX ( [ DISTINCT | ALL ] expr ) OVER (analytic_clause)

Using an empty OVER clause turns the MAX function into an analytic function. The
lack of a partitioning clause means the whole result set is treated as a single partition,
so we get the maximum salary for all employees, as well as all the original data.

SELECT empno, ename, deptno, sal, MAX(sal) OVER () AS max_sal FROM emp;

Adding the partitioning clause allows us to display the maximum salary per
department, along with the employee data for each department.

SELECT empno, ename, deptno, sal, MAX(sal) OVER (partition by deptno) AS max_sal
FROM emp;

** SUM () Analytic Function :-

SUM () analytic function will find the sum value from table based on analytic_clause
without minimizing original table data.

Syntax: SUM ( [ DISTINCT | ALL ] expr) OVER (analytic_clause)

Omitting a partitioning clause from the OVER clause means the whole result set is
treated as a single partition. In the following Ex we display the total salaries of all
employees, as well as all the original data.

SELECT empno, ename, deptno, sal, SUM(sal) OVER () AS sum_sal FROM emp;

Adding the partitioning clause allows us to display total salary within a partition.

SELECT empno, ename, deptno, sal, MAX(sal) OVER (partition by deptno) AS sum_sal
FROM emp;

** AVG (Mean) Analytic Function :-

AVG () analytic function will find the average values from table based on
analytic_clause without minimizing original table data.

Syntax: AVG ( [ DISTINCT | ALL ] expr) OVER (analytic_clause)

Using an empty OVER clause turns the AVG function into an analytic function. The
lack of a partitioning clause means the whole result set is treated as a single partition,
so we get the mean salary for all employees, as well as all the original data.

SELECT empno, ename, deptno, sal, AVG(sal) OVER () AS avg_sal FROM emp;

Adding the partitioning clause allows us to display the average salary per
department, along with the employee data for each department.

SELECT empno, ename, deptno, sal, AVG(sal) OVER (partition by deptno) AS avg_sal
FROM emp;

** FIRST_VALUE () analytic function :-

This analytical function uses to fetch first value from group depends on
analytic_clause without minimizing original table data.

Syntax: FIRST_VALUE { (expr) [ {RESPECT | IGNORE} NULLS ]

| (expr [ {RESPECT | IGNORE} NULLS ] ) } OVER (analytic_clause)

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing
you to return the first result from an ordered set.
SELECT empno,deptno,sal, FIRST_VALUE(sal) IGNORE NULLS OVER
(PARTITION BY deptno ORDER BY sal) AS lowest_in_dep FROM emp;

The "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when
determining results.

** LAST_VALUE () analytic function :-

This analytical function uses to fetch last value from group depends on
analytic_clause without minimizing original table data.

Syntax: LAST_VALUE { (expr) [ {RESPECT | IGNORE} NULLS ]

| (expr [ {RESPECT | IGNORE} NULLS ] ) } OVER (analytic_clause)

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing
you to return the last result from an ordered set. Using the default windowing clause
the result can be a little unexpected.

SELECT empno,deptno,sal, LAST_VALUE(sal) IGNORE NULLS OVER

(PARTITION BY deptno ORDER BY sal) AS highest_in_dept FROM emp;

As with the previous function, the "{RESPECT | IGNORE} NULLS" clause indicates if
NULLs are considered when determining results. The default action is RESPECT
NULLS.

** LEAD () analytic function :-

The LEAD function is used to return data from rows further down the result set. The
following query returns the salary from the next row to calculate the difference
between the salary of the current row and the following row.
Syntax: LEAD ( col_name , offset , default ) [ { RESPECT | IGNORE } NULLS ]
OVER ( query_partition_clause order_by_clause)

SELECT empno, ename, job, sal,

LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;

If the LEAD would span a partition boundary, the default value is returned. In the
following Ex we partition by department, so the SAL_NEXT column has a default
value of "0" for the last row in each department.

SELECT deptno, empno, ename, job, sal,

LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_next
FROM emp;

** LAG () analytic function :-

The LAG function is used to access data from a previous row. The following query
returns the salary from the previous row to calculate the difference between the
salary of the current row and that of the previous row. Notice that the ORDER BY of
the LAG function is used to order the data by salary and its mandatory.

Syntax: LAG ( col_name , offset , default ) [ { RESPECT | IGNORE } NULLS ]

OVER ( query_partition_clause order_by_clause)

SELECT empno, ename, job, sal,

LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff FROM emp;

If the LAG would span a partition boundary, the default value is returned. In the
following Ex we partition by department, so the SAL_PREV column has a default
value of "0" for the first row in each department.

SELECT deptno, empno, ename, job, sal,

LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev FROM emp;

** ROW_NUMBER(), RANK(), DENSE_RANK() analytic function:-

The ROW_NUMBER() is an analytic function that assigns a sequential unique integer

to each row to which it is applied, either each row in the partition or each row in the
result set.
Syntax: ROW_NUMBER() OVER ( query_partition_clause) order_by_clause)

The order_by_clause is required. It specifies the order of rows in each partition or in

the whole result set. The order_by_clause has the following form:
ORDER BY expression1 ,expression2,...[ASC | DESC ] [NULLS FIRST | LAST]

The query_partition_clause is optional. It distributes the rows into partitions to which

the function is applied. If you omit the query_partition_clause, the function will treat
the whole result set as a single partition.

RANK() is an analytical function that is used to calculate the rank of a value in an

ordered set of values. The RANK() function returns the same rank for the rows with
the same values. It adds the number of tied rows to the tied rank to calculate the
next rank. Therefore, the ranks may not be consecutive numbers.

The RANK() function is useful for top-N and bottom-N queries.

Syntax: RANK() OVER ( query_partition_clause order_by_clause)

The order_by_clause is required. It species the order of rows in each partition to

which the RANK() function applies.

DENSE_RANK() is an analytic function that calculates the rank of a row in an ordered

set of rows. The returned rank is an integer starting from 1.

Unlike the RANK() function, the DENSE_RANK() function returns rank values as
consecutive integers. It does not skip rank in case of ties. Rows with the same values
for the rank criteria will receive the same rank values.

Syntax: DENSE_RANK() OVER ( query_partition_clause order_by_clause)

The order_by_clause is required. It specifies the order of rows in each partition or in

the whole result set. The order_by_clause has the following form:
ORDER BY expression1 ,expression2,...[ASC | DESC ] [NULLS FIRST | LAST]

In the following Ex we assign a unique row number to each employee based on their
salary (lowest to highest). The Ex also includes RANK and DENSE_RANK to show the
difference in how ties are handled.
SELECT empno, ename, deptno, sal,
ROW_NUMBER() OVER (ORDER BY sal) AS row_num,
RANK() OVER (ORDER BY sal) AS row_rank,
DENSE_RANK() OVER (ORDER BY sal) AS row_dense_rank FROM emp;

EMPNO ENAME DEPTNO SAL ROW_NUM ROW_RANK ROW_DENSE_RANK

---------- ---------- ---------- ---------- ---------- ---------- --------------
7369 SMITH 20 800 1 1 1
7900 JAMES 30 950 2 2 2
7876 ADAMS 20 1100 3 3 3
7521 WARD 30 1250 4 4 4
7654 MARTIN 30 1250 5 4 4
7934 MILLER 10 1300 6 6 5
7844 TURNER 30 1500 7 7 6
7499 ALLEN 30 1600 8 8 7
7782 CLARK 10 2450 9 9 8
7698 BLAKE 30 2850 10 10 9
7566 JONES 20 2975 11 11 10
7788 SCOTT 20 3000 12 12 11
7902 FORD 20 3000 13 12 11
7839 KING 10 5000 14 14 12

Adding the partitioning clause allows us to assign the row number within a partition.
In the following Ex we assign the row number within the department, based on
highest to lowest salary.

SELECT empno, ename, deptno, sal, ROW_NUMBER() OVER (PARTITION BY deptno

ORDER BY sal DESC) AS row_num FROM emp;
This allows us to write Top-N queries at the partition level. The following Ex brings
back the best paid person in each department, ignoring ties.

SELECT * FROM (SELECT empno, ename, deptno, sal,

ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
FROM emp) WHERE row_num = 1;

--Ex- WAQ to display the employees details of highest salary to lowest salary and also
automatically assign RANK in each department from emp table.

Select * from (select deptno , ename, sal, row_number () over (partition by deptno
Order by sal desc)r from emp) where r <= 10;

--Ex- WAQ to display 2nd highest salary from emp table department wise.
Select * from (select deptno, ename, sal, dense_rank () over (partition by deptno
order by sal desc) r from emp) where r=2;

--Ex- WAQ to display 5th highest salary from emp table.

Select * from (select ename,sal,deptno, dense_rank () over (order by sal desc) r from
emp ) where r=5 ;

--Ex- WAQ to display nth highest salary from emp table.

Select * from (select ename, job, sal, deptno , dense_rank () over (order by sal desc) r
from emp ) where r = &no;

ANALYTIC FUNCTION USING ROWID : We can use min (), max() with ROWID.

-- Select min(rowid) from emp;

-- Select max (rowid) from emp;

In oracle by default ROWID are in ascending order.

--Ex- WAQ to display first row from emp table.

Select * from emp where rowid = (select max(rowid) from emp) ;

--Ex- WAQ to display last row from emp table.

Select * from emp where rowid = (select min(rowid) from emp) ;

NOTE: We can use rowid with order by clause

--Ex- WAQ to display 2nd row from emp table.

Select * from (select ename, sal, deptno , row_number () over (order by rowid) r
from emp) where r=2 ;

--Ex- WAQ to display last two records of emp table.

Select * from (select ename, sal, deptno, row_number ()over (order by rowid desc) r
from emp) where r <= 2;

--Ex- WAQ to display 2nd row of each department from emp table.

Select * from (select ename, sal, deptno,

row_number ()over (partition by deptno order by rowid) r from emp)
where r = 2;

--Ex- DIsplay duplicate data from emp table.

Select empno, count(*) from emp group by empno having count(*) >1;
(OR)
select * from emp where rowid in (select max(rowid) from emp group by empno);

--Ex- Delete duplicate data from emp table.

Delete from emp where rowid not in (select max(rowid) from emp group by empno);

Difference between AGGREGATE fun and ANALYTICAL fun :

AGGREGATE function ANALYTICAL function

Aggregate takes “many rows, collapse There is no row collapsing while
them into one” . Therefore , returns processing. Therefore, Return the
single summary value. number of rows as the input have
Slower than analytical function Faster than aggregate functions
GROUP BY clause is mandatory GROUP BY clause is not mandatory

Difference between RANK() and DENSE_RANK () :

RANK DENSE_RANK
RANK might have gaps in the ranking DENSE_RANK doesnt have any gaps in
value the ranking value
RANK counts each tie as a RANKED row DENSE_RANK handles tie differently

Difference between ROW_NUMBER and ROWNUM :

ROWNUM ROW_NUMBER
ROWNUM is a pseudocolumn and has ROW_NUMBER is an analytical function
no parameters. which takes parameters.
ROWNUM is calculated on all result set ROW_NUMBER is calculated as part of
the column calculation
ROWNUM is unique ROW_NUMBER can contain duplicates
ROWNUM is system defined logical ROW_NUMBER is sysdtem defined
column function.

VIEWS
 View is a database object which is used to provides authority level of security.
 Views do not contain any data
 it is just a stored query in the database that can be executed when called.
 All the data it shows comes from the base tables. One can think of a view as a
virtual table or logical table.

Whenever we are creating view then automatically view defination (select

statement) stores permanently in [Link] maximum number of columns that can be
defined in a SQL View are 1000 as in tables.

To create a view in your own schema, you must have the CREATE VIEW system
privilege. To create a view in another user's schema, you must have the CREATE ANY
VIEW system privilege.

Advantages of SQL Views:

[Link] complexity: A view can draw data from several different tables and
present it as a single table, turning multi-table queries into single-table queries
against the view.

[Link] security: Each user can be given permission to access the database only
through a small set of views that contain the specific data the user is authorized to
see, thus restricting the user's access to stored data.

[Link] Integrity: If data is accessed and entered through a view, the DBMS can
automatically check the data to ensure that it meets the specified integrity
constraints.

Disadvantages of SQL views:

[Link] usability: Oracle does not allow constraints on views

[Link]: Views create the appearance of a table, but the DBMS must still
translate queries against the view into queries against the underlying source tables.
[Link] restrictions: When a user tries to update rows of a view, the DBMS must
translate the request into an update on rows of the underlying source tables. This is
possible for simple views, but more complex views are often restricted to read-only.

Syntax: CREATE OR REPLACE VIEW view_name AS

Select statement;
[WITH READ ONLY]
[WITH CHECK OPTION]

*** Types of view-

[Link] view (OR) updatable view
[Link] view (OR) join view
[Link] view

[Link] view :- Simple view is a view which is created on only one base table those
views called as simple view.
 Contains only one single base table or is created from only one table.
 We cannot use group functions like MAX(), COUNT(), etc.
 Does not contain groups of data.
 DML operations could be performed through a simple view.
 INSERT, DELETE and UPDATE are directly possible on a simple view.
 Simple view does not contain group by, distinct, pseudo column like rownum,
columns defined by expressions.
 Does not include NOT NULL constraints(excuding not null) from base tables.

Syntax: CREATE OR REPLACE VIEW view_name AS

Select statement;

--Ex- create or replace view v_1

As
Select * from emp where deptno=10;

Select * from v_1;

DML operation ristriction on simple view:-

[Link] a simple view having group function , group by clause , rownum, distinct, set
operators, joins then we cant perform DML operation on simple view.

2. We must include base table NOT NULL column into the view only then after we are
allow to perform insertion operation through simple view.

--Ex- create or replace view v_1 AS

Select * from emp where deptno=10;
Insert into v_1 (empno, ename, sal) values (1,’Vitthal’, 30) ;
1 row inserted

Select * from emp;

--Ex- create or replace view v2 AS

Select sal, deptno from emp where deptno=10;

Insert into v2 (ename, sal, deptno) values (‘Vitthal’, 2000, 30);

ERROR: cant insert NULL into empno

NOTE: If you want to check structure of any view then we are using USER_VIEW
data dictionary.
Select * from user_views where view_name=‘V1’;
Select text from user_views where view_name=‘V1’;
Select * from USER_VIEWS_AE;

Views also used for simplifying query purpose i.e regularly used query we are putting
in view and whenever necessary select that view.
If query of view is using functions on column then we must use alias name for those
columns otherwise oracle server throughs error.

--Ex- create or replace view v3 AS

Select deptno, max(sal) from emp group by deptno;
ERROR: Must name this expression with a column alias.

Solution:- create or replace view v3 AS

Select deptno, max(sal) a from emp group by deptno;

Select * from v3;

In oracle when query having rownum it also have a alias name otherwise it oracle
server throughs error.

--Ex- create or replace view v3 AS

Select rownum, ename from emp;
ERROR: Must name this expression with a column alias.

Solution:- create or replace view v3 AS

Select rownum a, ename from emp;

Select * from v3 ;

*** constraints types that can be created on view level:

  check option
 read only
 primary key
 unique key
 foreign key

The above all constraints we can assign to complex view as well as simple view.

READ ONLY constraint to the view:

You can make a view read only by adding a READ ONLY constraint to the view.

--Ex- Create or replace view v5 as

Select * from emp WITH READ ONLY constraint my_v5_constraint;

Insert into v5 (ename,sal) values (‘Vitthal’, 5000);

ERROR : ORA-01733: virtual column not allowed here

CHECK OPTION Constraint to the view:

You can specify that DML operations on a view must satisfy the subquery by adding a
CHECK OPTION constraint to the view.

--Ex- Create or replace view v6 as Select * from emp

where sal > 3000 WITH CHECK OPTION constraint my_v6_constraint;

insert into v6 (ename,sal) values (‘Poonam’, 3100);

1 row inserted.

insert into v6 (ename,sal) values (‘Vitthal’, 2900);

ERROR: ORA-01402: view WITH CHECK OPTION where-clause violation

PRIMARY KEY constraint on view column:

Create or replace view VW1 as

Select * from emp;

To add constraint on view:-

Alter view VW1
add constraint VW1_PK primary key (empno) DISABLE NOVALIDATE;

we can use USER_CONS_COLUMNS data dictionary to check the view constraints

select * from user_cons_columns where table_name='VW1';

To drop constraint on view:-

alter view VW1 drop constraint VW1_EMPNO_PK ;
FOREIGN KEY constraint on view column:

Create or replace view VW1 as

Select * from emp;

To add constraint on view:-

Alter view VW1 add constraint VW1_FK foreign key (deptno) REFERENCES dep
DISABLE NOVALIDATE;

To drop constraint on view:-

alter view VW1 drop constraint VW1_FK ;

UNIQUE constraint on view column:

Create or replace view VW1 as

Select * from emp;

To add constraint on view:-

Alter view VW1 add constraint VW1_UN unique (empno) DISABLE NOVALIDATE;

To drop constraint on view:-

alter view VW1 drop constraint VW1_UN ;

To drop view on which constraints are applicables :-

DROP VIEW view_name CASCADE CONSTRAINTS;

view based on user-defined PL/SQL function:

create or replace view v_fun1 as
select fun_1(empno) from emp; --- Here function already created using PL/SQL.

Select * from v_fun1;

[Link] View :
Complex view is a view which is created from multiple tables.
Contains more than one base tables or is created from more than one tables.
 We can use group functions.
 It can contain groups of data.
 DML operations could not always be performed through a complex view.
 We cannot apply INSERT, DELETE and UPDATE on complex view directly.
 It can contain group by, distinct, pseudocolumn like rownum, columns defiend
by expressions.
 NOT NULL columns that are not selected by simple view can be included in
complex view.
--Ex- create or replace view v4 as
Select ename,sal,dname,loc from emp e ,dep d where [Link]= [Link] ;

Select * from v4;

Generally we cant perform DML operation through complex view on base table.

Update v4 set ename= ‘Vitthal’ where ename=‘SMITH’ ;

1 row updated.

Update v4 set dname=‘XYZ’ where dname=‘SALES’ ;

ERROR: can not modify a columns.

view by join three tables

create or replace view v1 AS select [Link],[Link],[Link],[Link],[Link] as
Designer from emp e JOIN dep d on [Link]=[Link] JOIN emp m on ([Link] =
[Link]);

select * from v1;

In oracle when we are trying to perform DML operation through complex view to
base table then some other table column are effected so it will through error but if
we perform DML operation through complex view on base table and it doesn’t effect
any other table columns then DML operation will successfully completed as explain in
above Ex.

If we want to see effected and uneffected columns of any complex view then use
USER_UPDATEABLE_COLUMNS data dictionary

Select * from USER_UPDATEABLE_COLUMNS where table_name=‘V4’;

Generally in oracle also we cant perform DML operation on table through complex
view. To overcome this problem oracle 8.0 introduced INSTEAD OF TRIGGER in pl/sql.
When we create a INSTEAD OF TRIGGER on complex view then we are allow to
perfrom DML operations on base table.
By default INSTEAD OF TRIGGER is ROW LEVEL trigger.

**** Trigger:- Trigger is also same as stored procedure and also it will
automatically invoked whenever DML operation performed on base table.
You can choose the event upon which the trigger needs to be fired and the timing of
the execution. The purpose of trigger is to maintain the integrity of information on
the database.
Trigger Syntax:-
CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER Trigger specifications
INSERT [OR] / UPDATE [OR] / DELETE} ON table_name
(FOR EACH ROW)
WHEN (condition)
DECLARE
Declaration-statements
BEGIN Trigger Body
Executable-statements
EXCEPTION
Exception-handling-statements
END;

Types of Triggers in Oracle:

Triggers can be classified based on the following parameters.

Types of trigger based on the timing:-

1). BEFORE Trigger: It fires before the specified event has occurred.
2). AFTER Trigger: It fires after the specified event has occurred.
3). INSTEAD OF Trigger: A special type. (only for DML )

Types of trigger based on the level :-

1). STATEMENT level Trigger: It fires one time for the trigger body.
2). ROW level Trigger: It fires for each record that got affected in the specified event.
(only for DML)

[Link] level trigger :- In statement level triggers , trigger body is executed only
once for DML statement.

[Link] level trigger:- In row level trigger, trigger body is executed for each row DML
statements. That’s why we are using “for each row” clause in trigger specification and
also DML transaction values are internally stored in two rollback statements
qualifiers,these are :OLD , :NEW are also called as record type variable.

Instead of trigger : Instead of trigger are row level trigger and also instead of trigger
are created on views.

Syntax: create or replace trigger trigger_name

Instead of INSERT/UPDATE/DELETE on VIEW_NAME
For each row
Begin
………
End;
--Ex- create or replace view v4 as
Select ename,sal,dname,loc from emp e ,dep d where [Link]= [Link] ;

Select * from v4;

Generally we cant perform DML operation through complex view on base table.

Update v4 set ename= ‘Vitthal’ where ename=‘SMITH’ ;

1 row updated.

Update v4 set dname=‘XYZ’ where dname=‘SALES’ ;

ERROR: can not modify a columns.

To perform update on complex view we must use INSTEAD OF TRIGGER.

Create or replace trigger trig_1

Instead of update on v4
For each row
Begin
Update dep set dname=:[Link] where dname= :[Link];
End;

Testing: update dep set dname=‘XYZ’ where dname=‘SALES’ ;

1 row updated.

Select * from USER_UPDATABLE_COLUMNS where table_name= ‘V4’ ;

NOTE: in Oracle, the VIEW continues to exist even after one of the tables (that
the Oracle VIEW is based on) is dropped from the database. However, if you try
to query the Oracle VIEW after the table has been dropped, you will receive a
message indicating that the Oracle VIEW has errors.

If you recreate the table (the table that you had dropped), the Oracle VIEW will
again be fine.

Difference between Simple and Complex View in SQL

Simple view Complex view

Contains only one single base table or is Contains more than one base tables or
created from only one table. is created from more than one tables.
We cannot use group functions like We can use group functions.
MAX(), COUNT(), etc.
Does not contain groups of data. It can contain groups of data.
INSERT, DELETE and UPDATE are DML operations could not always be
 directly possible on a simple view. performed through a complex view.
Simple view does not contain group by, It can contain group by, distinct,
distinct, pseudocolumn like rownum, pseudocolumn like rownum, columns
columns defiend by expressions. defiend by expressions.
Does not include NOT NULL columns NOT NULL columns that are not
from base tables. selected by simple view can be
included in complex view.

[Link] View :-
A view can be created even if the defining query of the view cannot be executed. We
call such a view as view with errors or force views.

For Ex, if a view refers to a non-existent table or an invalid column of an existing table
or if the owner of the view does not have the required privileges, then the view can
still be created and entered into the data dictionary.
We can create such views (i.e. view with errors) by using the FORCE option in the
CREATE VIEW command:

Syntax: CREATE OR REPLACE force VIEW view_name AS SELECT * FROM table1

Warning: VIEW created WITH compilation errors.

NOTE: When Force command is used in the view syntax then even if select
statement is invalid, VIEW gets created successfully.

--Ex- In this example we are trying to create a view using a table that does not exist in
the database.

/* dummy_table is not available in the database*/

Create or replace FORCE VIEW na_view

As
select * from dummy_table;

The advantage of force view is that in future if view script becomes valid, we can
start using the view.
force view is use basically for the situation when we create a view using a table but
the table is not created at that time we use force command;

** view alter commands :-

You cannot use ALTER VIEW for removing a column or adding column. To recreate
the view without the column, use CREATE OR REPLACE VIEW.
Compile view- alter view view_name COMPILE ;

*** How to drop all views

begin
for i in (select view_name from user_views) loop
execute immediate 'drop view ' || i.view_name;
end loop;
end;

*** How to create views on all tables

Declare
Cursor C1 is Select Table_Name From Tabs;
V_name Varchar2(100);
N Number:=0;
Begin
Open C1;
Loop
N:=N+1;
Fetch C1 Into v_name;
Exit When C1%Notfound;
Execute Immediate 'Create Or Replace View '||N||' As Select * From'||v_name';
End Loop;
Close C1;
End;

MATERIALIZED VIEW

Materialized views are also the logical view of our data-driven by the select query but
the result of the query will get stored in the table or disk, also the definition of the
query will also store in the database. MV also sync newly added data into it after
refreshing. Materialized view stores replication of remote database into loacl node.

Materialized views are useful in Data-warehousing [Link] you create a

Materialized view, Oracle Database creates one internal table and at least one index,
and may create one view, all in the schema of the materialized views.

A materialized view is a replica of a target master from a single point in time. The
master can be either a master table at a master site or a master materialized view at
a materialized view site. Whereas in multimaster replication tables are continuously
updated by other master sites, materialized views are updated from one or more
masters through individual batch updates, known as a refreshes, from a single master
site or master materialized view site, as illustrated in Figure

Materialized View Connected to a Single Master Site

Syntax:-
Create materialized view VIEW_NAME Refresh [Fast/Complete/Force]
on [Commit/Demand]
as Select ..........;

Advantages of MV:
[Link] : You can define a materialized view on a base table, partitioned table or
view and you can define indexes on materialized view

[Link] creation : Materialized Views can be created in the same database where
the base tables exists or in a different database as well.

[Link] Network Loads : one of your goals is to reduce network loads

[Link] refresh : u can do manual refresh or auto refresh on commit.

5. Secure Sensitive Data: Users can only view data that satisfies the defining query
for the materialized view.

You should have create materialized views privileges to create a Materialized views.

Grant create any materialized view to USER_NAME ;

Conn hr/hr

Create materialized view mv1

As
Select * from emp;

ERROR: insufficient priviliges

Conn system/system as dba
Grant create any materialized view to hr ;

Create materialized view mv1

As
Select * from emp;
o/p- materialized view created.

NOTE: before 10g version whenever user want to create MV on any table then that
table must have primary key but after 11g version user can create MV on any table
those don’t have any primary key at all.

--Ex- create or replace view v1 as

Select * from emp;

Create materialized view mv1 as

Select * from emp;

If we want to view MV definations then we are using USER_MVIEWS data dictionary.

Select * from USER_MVIEWS where MVIEW_NAME=‘MV1’ ;

Whenever we are creating view then automatically view defination stored

permanently in DB. Whenever we are requesting view each and every time query
defination are executed. That’s why each and every time base table is effected by
execution that’s why view doesn’t improve performance.

Whenever we are creating MV then MV defination stored permanently in DB

dictionary and also oracle stores the query results of that MV in DB within that MV.
Whenever we are requesting MV by using select statement then oracle server each
and every time retrive data from MV instead of going to base table. In this case
oracle server doesn’t execute query defination that’s why in this case base table
doesn’t affected. That’s why a MV improves the performance of the query.

Whenever we are refreshing MV then only oracle server executes MV defination

from data dictionary and in this case only base tables are effected.
Select rowid, ename from emp;

Select rowid, ename from v1;

Here in those both query both rowid are same (base table and view) that’s why view
doesn’t store any data so it is called as virtual table.

Select rowid, ename from mv1;

Here rowid of MV are different than base table rowid that’s why MV stores the data.

Select * from base;

sno Name
1 a
2 b
3 c
4 d

Update base set name=upper (name) ;

Select * from base

sno Name
1 A
2 B
3 C
4 D

Select * from v1; select * from MV1;

Sno Name
sno Name
1 a
1 A
2 b
2 B
3 c
3 C
4 d
4 D

MV also stores data same like base table but when we are changing into base table
those changes not going to reflect into MV until we refresh MV.
After refresh MV will sync the data from base table.

To refresh MV : exec dbms_mview.refresh (‘MV_name’) ;

Exec dbms_mview. Refresh (mv1) ;

Select * from MV1;
sno Name
1 A
2 B
3 C
4 D

--Oracle have two types of MV :-

1). Complete Refresh MV 2). Fast Refresh MV 3) force refresh
1). Complete Refresh MV :-
In oracle by default MV are complete refresh MV . At the beginning of a Complete
Refresh, the old data of the materialized view is deleted, Then, the new data as well
as old data is inserted again by running the underlying SQL query.

Internally ROWID are created when we are refreshing MV. Every time ROWID are
dropped and again created in MV with complete refresh. The refresh is executed
within one single transaction, i.e. with a DELETE and INSERT statement and this is the
disadvantage of MV with complete refresh.
During this time, users can still use the materialized view and see the old data. At the
end of the refresh, the transaction is committed, and the new data is visible for all
users.
The advantage of this behavior is that the users can still use the materialized view
while it is refreshing.

Syntax: create materialized view view_name

Refresh complete
As ‘select statement’ ;

Select rowid, ename, sal from emp;

Exec dbms_mview. Refresh (‘mv1’);

Here rowid are changed

2). Fast Refresh MV:-

Fast refresh MV are also called ase incremental refresh MV.

This MV performance is very high compare to complete refresh MV. Because in this
MV rowid are not changed when we are refreshing MV number of times.

Syntax: create materialized view view_name

Refresh fast
As ‘select statement’ ;

With this refresh method, only the changes since the last refresh are applied to the
materialized view.
Before we are create MV with fast refresh it need mechanism to catch any changes
made to its base tables. This refreshment is also called as ‘MV log’.

An important pre-condition for a Fast Refresh is a materialized view log on each of

the base tables that are referenced in the materialized view. All columns that are
used in the query must be added to the materialized view log.
If multiple materialized views are created, only one materialized view log per base
table is required, with all columns that are used in at least one of the materialized
views.

Syntax: create materialized view log on base_table_name ;

Select * from base ;

Sno Name
1 A
2 B
3 C
4 E

Create materialized view log on BASE ;

Create materialized view mv2

Refresh fast
As
Select * from base;

Select rowid, sno, name from base;

Update base set name=‘D’ where sno=4 ;

Exec dbms_mview.refresh (‘mv2’) ;

Select rowid, sno, name from base ;

[Here rowid are not changed]

3). Force refresh MV :-

Specify FORCE if, when a refresh occurs, you want Oracle Database to perform a fast
refresh if one is possible or a complete refresh otherwise.
In REFRESH FAST Categories we saw an insert-only materialized view which could be
fast refreshed after inserts into the base table but needed a complete refresh after
other types of DML like update an old record of base table.

FORCE Clause With these types of materialized views it is often most convenient to
let Oracle decide which refresh method is best. The REFRESH FORCE method does
just that. It performs a FAST refresh if possible, otherwise it performs a COMPLETE
refresh.

select * from t2 ;
KEY T_KEY AMT
 10 1 100
20 1 300
30 1 200
40 2 250
50 2 150

create materialized view log on t2 with primary key, rowid, sequence ( t_key, amt )
including new values;

create materialized view mv

REFRESH FORCE
as
select t_key, max( amt ) amt_max from t2
group by t_key ;

select rowid, t_key, amt_max from mv ;

ROWID T_KEY AMT_MAX

AAAWpLAAEAAAAaMAAA 1 300
AAAWpLAAEAAAAaMAAB 2 250

First let's try an insert some records of base table and then refresh the mview based
on that table.

insert into t2 values ( 5, 2, 500 );

commit;

execute dbms_mview.refresh( 'MV' );

select rowid, t_key, amt_max from mv ;

ROWID T_KEY AMT_MAX
AAAWpLAAEAAAAaMAAA 1 300
AAAWpLAAEAAAAaMAAB 2 500

Since the rowids did not change but the AMT_MAX values did we can tell that a FAST
refresh was performed.
Now let's try a delete some record from base table then refresh the mview.

delete from t2 where key = 5 ;

commit;

execute dbms_mview.refresh( 'MV' );

select rowid, t_key, amt_max from mv ;

 ROWID T_KEY AMT_MAX
AAAWpLAAEAAAAaMAAC 1 300
AAAWpLAAEAAAAaMAAD 2 250

This time with REFRESH FORCE we did not got error “ORA-32314: REFRESH FAST of
"HR"."MV" unsupported after deletes/updates” . Instead Oracle performed a
COMPLETE refresh (note how the rowids for each row changed).

Generally MVIEW are refreshed by two ways-

1). Manually
2). Automatically

1). Manually :- In manual method we are refreshing MV by using dbms_mview

package. This method is also called as ‘ON DEMAND’ method.
In oracle by default refresh method is ‘ON DEMAND’.

2). Automatically :- In oracle we can also refresh MV without using dbms_mview

package. This method is called as ‘ON COMMIT’ method.

Syntax: create materialized view view_name

Refresh complete/ refresh fast on demand/ on commit
As
Select statement ;

Select * from base;

Sno Name
1 A
2 B
3 C
4 E

Create materialized view mv3

Refresh fast on commit
As
Select * from base;

Update base set name=‘D’ where sno=4 ;

Select * from base ;

Sno Name
1 A
2 B
 3 C ----Base table is updated
4 D
Select * from mv3 ;
Sno Name --- MV is still not updated
1 A
2 B
3 C
4 E
commit;
Select * from mv3;
Sno Name ---MV is now updated because we performed COMMIT.
1 A
2 B
3 C
4 D

Checking the Status of a Materialized View

Three views are provided for checking the status of a materialized view:
DBA_MVIEWS, ALL_MVIEWS, and USER_MVIEWS. To check if a materialized view is
fresh or stale, issue the following statement:

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE

FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME STALENESS LAST_REF COMPILE_STATE

---------- --------- -------- -------------

MV1 NEEDS_COMPILE FAST NEEDS_COMPILE

MV2 FRESH FAST VALID

If the compile_state column shows NEEDS COMPILE, the other displayed column
values cannot be trusted as reflecting the true status. To revalidate the materialized
view, issue the following statement:

ALTER MATERIALIZED VIEW [materialized_view_name] COMPILE;

ALTER MATERIALIZED VIEW MV1 COMPILE;

Then reissue the SELECT statement.

**Updatable Materialized Views :-

You can make a materialized view updatable during creation by including the FOR
UPDATE clause. Updatable materialized views enable you to decrease the load on
master sites because users can make changes to the data at the materialized view
site. The following is an Ex of an updatable materialized view:

CREATE MATERIALIZED VIEW mview_1 FOR UPDATE

AS
SELECT * FROM EMP;

Update mview_1 set Sal=3000 where tempno= 7369;

The updatable MV is only update the data at MV contain data but it doesn’t update
the base table data.
Note that the changes aren't pushed to the base table. As soon as you refresh it, the
changes are lost.
We can’t not update the MV to update base table its impossible to update base table
through MV.

**Complex Materialized Views

a materialized view is considered complex when the defining query of the
materialized view contains: A CONNECT BY clause , An INTERSECT, MINUS, or
UNIONALL set operation , The DISTINCT or UNIQUE keyword, aggregate function,
joins

If you require a materialized view whose defining query is more general and cannot
observe the restrictions, then the materialized view is complex and cannot be fast
refreshed.

--Ex- usng CONNECT BY clause

CREATE MATERIALIZED VIEW mv1 AS

SELECT LPAD(' ', 4*(LEVEL-1))||ename FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

--Ex- using SET OPERATORS

CREATE MATERIALIZED VIEW mv5 AS

SELECT [Link], [Link] FROM emp e1
UNION ALL
SELECT [Link], [Link] FROM emp e2;

--Ex- using DISTINCT or UNIQUE keyword

CREATE MATERIALIZED VIEW mv6 AS

SELECT DISTINCT (deptno) FROM EMP ORDER BY deptno;

--Ex- using AGGREGATE FUNCTION

CREATE MATERIALIZED VIEW mv7 AS
SELECT AVG(salary) "Average" FROM emp;

--Ex- Using JOINS

CREATE MATERIALIZED VIEW mv8 AS

SELECT [Link], [Link], [Link] FROM emp e, dep WHERE [Link] = [Link];

Restrictions on Fast Refresh:-

In general materialized views cannot be fast refreshed if the base tables do not have
materialized view logs or the defining query:

contains an analytic function

contains non-repeating expressions like SYSDATE or ROWNUM
contains a subquery in the SELECT clause
contains a HAVING clause
contains nested queries with ANY, ALL, or NOT EXISTS
contains a CONNECT BY clause
references remote tables in different databases
There are even more restrictions for materialized views containing joins, aggregates,
UNION ALL, subqueries, etc

*** can we modify Materialized view query . Is is possible to do the same without
droping and recreating it.
To modify the Materialized view you have to drop the materialized view first and
then recreate it.
No, you cannot alter the query of a materialized view without dropping it.

The CREATE MATERIALIZED VIEW syntax does not support that feature.

The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in

one or more of the following ways:

To change its storage characteristics

To change its refresh method, mode, or time
To alter its structure so that it is a different type of materialized view
To enable or disable query rewrite

** Alter commands for MV:-

To change refresh method of MV-
ALTER MATERIALIZED VIEW mview_name REFRESH FAST/COMPLETE;

To change automatic time of MV

ALTER MATERIALIZED VIEW emp_data
 REFRESH COMPLETE START WITH TRUNC (SYSDATE+1) + 9/24
NEXT SYSDATE+7;

The START WITH value establishes the next automatic refresh for the materialized
view to be 9:00 a.m. tomorrow. At that point, Oracle Database performs a complete
refresh of the materialized view, evaluates the NEXT expression, and subsequently
refreshes the materialized view every week.

Difference between VIEW and MVIEW :-

View Mview
View is nothing but the logical Materialized views (Snapshots) are
structure of the table which will also logical structure but data is
retrieve data from 1 or more table. physically stored in database.
When we are dropping base table When we are dropping base table
then view cannot be accessible. then MV can be accessible until you
perform next complete refresh.
View doesn’t store data Materialized view store the data
It uses for security purpose Improve performance purpose
Data access is not as fast as Data retrieval is fast as compare to
materialized views simple view because data is accessed
from directly physical location
Through view we can perform DML We cant perform DML operation on
operation on base table base table through MV.
View are basically sync with base table MV are not in sync with base table so
so it is auto refresh method without they need refresh explicitely.
using commit.
Base table rowid and view rowid are Base table rowid and MV rowid are
same different

SEQUENCE

Sequence is database object which is used to generate sequence number

[Link] sequence are used to generate primary key automatically.

Syntax
CREATE SEQUENCE sequence_name ---varchar2
[START WITH start_num] ---number
[INCREMENT BY increment_num] ---number (not null)
[MAXVALUE maximum_num | NOMAXVALUE] ---number
[MINVALUE minimum_num | NOMINVALUE] ---number
[CACHE cache_num | NOCACHE] ---varchar2(1)
[CYCLE | NOCYCLE]; ---varchar2(1)
START WITH
Here you have to specify a numeric value from which you want your sequence to
start. Start with clause can not be altered. START WITH can not be less than
MINVALUE.
Create sequence s_11;

Alter sequence s_11

Start with 4;
ERROR: cannot alter starting sequence number.

NOTE:- Start with cant not be less than MINVALUE

Create sequence s_14

Start with 3
Increment by 1
Minvalue 5;

ERROR: START WITH cannot be less than MINVALUE.

INCREMENT BY
This attribute also takes a numeric value; to increment the sequence by the number
that you specify here will serve as the interval between sequence numbers.
INCREMENT BY value cannot be 0 but it can be any positive or negative value.
If this value is negative, then the sequence descends. If the value is positive, then the
sequence ascends. If you omit this clause, then the interval defaults to 1.

Create sequence s_14

Start with 10
Increment by 0
Minvalue 5 ;
ERROR: INCREMENT must be a non-zero integer

MAXVALUE / NOMAXVALUE
Using these attributes you can set the maximum upper bound for your sequence.
MAXVALUE must be equal to or greater than START WITH and must be greater than
MINVALUE attribute.
In case you don’t want to set the MAXVALUE for your sequence then you can use
NOMAXVALUE attribute.

Create sequence s_14

Start with 10
Increment by 1
Maxvalue 5;
ERROR: START WITH cannot be more than MAXVALUE

Create sequence s_14

Start with 1
Increment by 1
minvalue 8
Maxvalue 5;

ERROR: MINVALUE must be less than MAXVALUE

MINVALUE / NOMINVALUE
we use MINVALUE attribute to set the lower bound of our sequence. As a value this
attribute also accepts the numeric value and it should be less than or equal to START
WITH as well as less than MAXVALUE. In case you don’t want to set the lower bound
for your sequence then you can use NOMINVALUE attribute instead.

Create sequence s_14

Start with 1
Increment by 1
minvalue 8;

ERROR: START WITH cannot be less than MINVALUE

Create sequence s_14

Start with 10
Increment by 1
minvalue 8
Maxvalue 5;

ERROR: MINVALUE must be less than MAXVALUE

CACHE/ NOCACHE
As the value of cache attribute, you specify the number of integers to keep in
memory. The default number of integers to cache is 20. The minimum number of
integers that may be cached is 2. The maximum integer that may be cached is
determined by the formula:

CEIL (maximum_num - minimum_num)/ABS (increment_num).

Specify NOCACHE to indicate that values of the sequence are not pre-allocated. If you
omit both CACHE and NOCACHE, the database caches 20 sequence numbers by
default.
CYCLE/NOCYCLE
If you set the flag on CYCLE then your sequence continues to generate values after
reaching either its maximum or minimum value.
When you set the CYCLE flag on then you have to must specify MAXVALUE and
MINVALUE parameter.
You specify NOCYCLE flag when you do not want your sequence to generate more
values after reaching its maximum or minimum value. If in case you omit both these
flags then by default oracle engine will set the flag on NOCYCLE.

If you want to generate sequence value or access sequence value then we are using
following 2 pseudo columns-
1). Currval 2). Nextval

CURRVAL : Returns the current value of a sequence.

Syntax: sequence_name.currval

NEXTVAL : Increments the sequence and returns the next value.

Syntax: sequence_name.nextval

If we want to generate sequence value by using select statement then we must use
dual table.

Select sequence_name.currval from dual;

Select sequence_name.nextval from dual;

There is one another way you can create sequence like below-
Create sequence s_10;

Once you created the sequence and you want to check its current value then use it

This statement internally select the by default values for all clause of sequence as
below.
SEQUENCE MIN MAX INCREMENT CYCLE ORDER CACHE LAST
NAME value value BY FLAG FLAG SIZE NUMBER
S_10 1 1E28 1 N N 20 1

If you want to check sequence structure in database then we use USER_SEQUENCES

data dictionary.

Select * from USER_SEQUENCES where sequence_name = ‘S_10’;

Select s_10.nextval from dual; --execute to see next value of secuence.

After you execute above statement CACHE will generate the next 20 no lot.
SEQUENCE MIN MAX INCREMENT CYCLE ORDER CACHE LAST
NAME value value BY FLAG FLAG SIZE NUMBER
S_10 1 1E28 1 N N 20 21

Create sequence seq_01

Select s_01.currval from dual;

Unfortunately it will through one message like below

ORA-08002: sequence S_01.CURRVAL is not yet defined in this session

That means you can’t not RUN CURRVAL when sequence that doesn’t started.
You may have to RUN NEXTVAL statement first then you can run CURRVAL statement
after sequence initiated.

Select s_01.nextval from dual;

O/p: 1

Select s_01.currval from dual;

O/p: 1

How sequence increase performance:- Generally whenever we are creating a

sequence then the sequence are generated in hard disk. Whenever we are
requesting sequence value by using currval and nextval through a tool then server
process checks if requested sequence is available in cache memory area.
If it is not available in cache memory then oracle server searches requested
sequences are available in Hard disk.
If it is available then only oracle server fetches requested sequence value to cache
memory area. Then only that value is return to client tool.
Whenever we are requesting sequence number more number of time then this
process degrades performance of the sequence to overcome this issue we are
putting set of sequence number in cache memory area by using cacahe option clause.
Then only whenever we are using sequence number then server process directly
fetches sequence number from cache memory area.

When you call s_10.nextval, the values from 1-20 will taken from the sequence and
cached in the SGA. When you call it again like s_10.nextval, the value 2 will be
returned but this value 2 is not taken from Sequence, it has been taken from Cache
(SGA). It will do so till your call reaches 21. So till 2 to 20 there is no "Real" call to
sequence and hence the improvement in [Link] 21 it will generate again
next 20 numbers in CACHE.

Create sequence s_14

Start with 1
Increment by 1
maxvalue 100
minvalue 1
cache 1
cycle;

ERROR: the number of values to cache must be greater than 1.

NOTE: cache size must be greater than cycle by default value (1) i.e cache must be
atleast 2. When you are using CYCLE parameter you must have MAXVALUE
specified in your sequence.

How to use sequence with Primary Key in 11g and 12 c :-

In oracle 12 c we can also generate primary key value without using PL/SQL row level
trigger. In this case we are creating sequence in a SQL and use that sequence
[Link] along with default caluse within table at the creation of table.

Syntax: column_name datatype (size) default sequence_name.nextval primary key;

In Oracle 12c
Create sequence s_11;

Create table test

(sno number (10) default s_11.nextval primary key , name varchar2 (20)) ;

Insert into test (name) values (‘&name’) ;

Values entered: Vitthal, Sachin, Vitthal, Chandu

Select * from test;

SNO NAME
1 Vitthal
2 Sachin
3 Vitthal
4 Chandu

In Oracle 11 g
Create sequence s_12

create table test (sno number(10) primary key, name varchar2 (20));

insert into test (sno, name) values (s_10.nextval, '&name');

select * from test;

 SNO NAME
1 Vitthal
2 Sachin
3 Vitthal
4 Chandu

How to use sequence to update column.

Alter table test add orderno number (10);

Select * from test;

SNO NAME ORDER NO
1 Vitthal
2 Sachin
3 Vitthal
4 Chandu

Create sequence s_11

start with 1001;

update test set orderno= s_11.nextval ;

select * from test ;

SNO NAME ORDER NO
1 Vitthal 1001
2 Sachin 1002
3 Vitthal 1003
4 Chandu 1004

NOTE : In oracle we can change sequence parameters value by using ALTER

command but we are not allowed to change START WITH value using ALTER.

Syntax: alter sequence sequence_name Parameter_name NEW_VALUE ;

Create sequence s_12

Start with 5
Increment by 1
Minvalue 3;

Select [Link] from dual;

5
Select [Link] from dual;
6
Select [Link] from dual;
7

Alter sequence s_11

Increment by -1; ----sequence altered

Select [Link] from dual;

6
Select [Link] from dual;
5

Alter sequence s_11 Start with 4;

ERROR: can not alter starting sequence number.

NOTE:- Start with cant not be less than MINVALUE

Create sequence s_14
Start with 3
Increment by 1
Minvalue 5;

ERROR: start with cannot be less than MINVALUE.

--Ex- Following is the sequence query creating sequence in descending order.

CREATE SEQUENCE sequence_2

start with 100
increment by -1
minvalue 1
maxvalue 100
cycle;

Above query will create a sequence named sequence_2.Sequence will start from 100
and should be less than or equal to maximum value and will be incremented by -1
having minimum value 1.

--Ex- Sequence using with Trigger- creating SEQUENCE with all defaults values
CREATE SEQUENCE my_sequence;

This sequence can then be used immediately in triggers when inserting new records
in a table:

CREATE OR REPLACE TRIGGER my_trigger

BEFORE INSERT ON test
FOR EACH ROW
WHEN ([Link] is null)
BEGIN
SELECT my_sequence.nextval INTO :[Link] FROM DUAL;
END my_trigger;

How To Modify A Sequence: - There are some limitations on what you can modify in
a sequence such as:
 Create is allow in sequence creation but replace is not allowed. Sequence
cannot be rollback.
 You cannot change the start value of a sequence.
 The minimum value cannot be more than the current value of the sequence.
 The maximum value cannot be less than the current value of the sequence.
Suppose you want to modify the value of INCREMENT BY attribute from 2 to 4, so for
that ALTER SEQUENCE command will be:

ALTER SEQUENCE sq_demo INCREMENT BY 4;

Altering Sequences
To alter a sequence, your schema must contain the sequence, or you must have the
ALTER ANY SEQUENCE system privilege. You can alter a sequence to change any of
the parameters that define how it generates sequence numbers except the sequence
starting number. To change the starting point of a sequence, drop the sequence and
then re-create it.
Ex, the following statement alters the emp_sequence:

ALTER SEQUENCE emp_sequence

INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;

Alter cache size to 1000 from default 20 size-

ALTER SEQUENCE s_11 CACHE 1000

select * from user_sequences where sequence_name='S_11';

SEQUENCE MIN MAX INCREMENT CYCLE ORDER CACHE LAST
NAME value value BY FLAG FLAG SIZE NUMBER
S_11 1 1E28 1 N N 1000 21

Drop sequence-
DROP SEQUENCE sequence_name ;

NOTE: If you using sequence in your insertion operation and after that you close
the session and then sequence CACHE will be flushed so after some time if you
started new session and used same sequence again then it will start from next
 integer of flushed sequence numbers.

**** How to reset sequence :-

Suppose you create a SEQUENCE like shown below:

CREATE SEQUENCE TESTSEQ

INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER;

Now you fetch values from SEQUENCE. Let’s say I have fetched four times as shown
below.

SELECT [Link] FROM dual;

SELECT [Link] FROM dual;
SELECT [Link] FROM dual;
SELECT [Link] FROM dual;

After executing above four commands the value of the SEQUENCE will be 4. Now
suppose i want to reset the value of the SEQUENCE to 1 again. Follow all the steps in
the same order as shown below:

ALTER SEQUENCE TESTSEQ INCREMENT BY -3;

SELECT [Link] FROM dual;

ALTER SEQUENCE TESTSEQ INCREMENT BY 1;

SELECT [Link] FROM dual; --- sequence is reset successfully

--Ex- create an sequence and start value will 901.

create sequence seq_ex

start with 901
increment by 1
cache 10;

select seq_ex.nextval, seq_ex.currval from dual;

NEXTVAL CURRVAL
 901 901
And I have taken some values from the sequence and its current value (currval) is
1436. Now I wanted to reset the sequence current value to 501 (smaller value).

Resetting the value to smaller value--

Find out the difference between current value and the value to be reset.

Select 1436-501 from dual;

o/p- 935

Modify the increment by value of the sequence.

Alter sequence seq_ex increment by -935 ;

Select seq_ [Link], seq_ [Link] from dual;

o/p- 501 501

Reset the increment by to the original value (here it is 1)

alter sequence seq_ ex increment by 1;

--Ex- CREATE a SEQUENCE to generate below sequence

required output
1
-1
1
-1
1
-1

CREATE SEQUENCE seq_123

START WITH 1
INCREMENT BY 2
MAXVALUE 1
MINVALUE -1
CYCLE
NOCACHE;

Select seq_123.nextval from dual connect by level<=6;

INDEX

Index is database object which is used to retrieve data fastly from database.
Indexes are schema objects that are logically and physically independent of the data
in the objects with which they are associated. Thus, an index can be dropped or
created without physically affecting the table data. The index points directly to the
location of the rows containing that value.
Index are basically created on table columns.

SQL Indexes are nothing but way of reducing the cost of the query. More the cost of
the query less the performance of the query. The main task of query tuner is to
reduce the cost of the query using indexing, reduce the Full table scans, and reduce
the time to fetch the records from the query.

Indexes help speed up searching in the database. If there is no index on any column
in the WHERE clause, then the SQL server has to scan through the entire table and
check each and every row to find matches, which might result in slow operation on
large data.

**Advantages of Indexes:

1. It allows faster retrieval of data

2. It avoids the Full table scan so that the performance of retrieving data from the
table is faster.
3. It avoids the table access all together
4. Indexes always speeds up the select statement.
5. Indexes used to improve the Execution plan of the database

**Disadvantages of Indexes:

[Link] slows down the performance of insert and update [Link] always
we need follow best practice of disabling indexes before insert and update the table
[Link] takes additional disk space so by considering memory point indexes are
costly.

In oracle we are creating INDEX in two ways-

1). Automatically 2). Manually

1). Automatically /Implicit index :- In oracle whenever we are creating a primary key
or unique key in a table column then oracle server internally automatically B-tree
indexes on those columns.

2). Manually :- We can also create index explicitly by using following syntax-

Create INDEX index_name on table_name (column_name);

** Oracle having three types of indexes-

1). B-tree index
2). Bitmap Index
3). Function based index
There are some other index also-
1. Normal index (Oracle considered Normal indexes as B-Tree Indexes)
2. Unique Index
3. Composite Index
4. Clustered Index
5. Non-Clustered Index.
6. Local Index
7. Global Index

1). B-Tree Indexes

B-trees, short for balanced trees, are the most common type of database index. A
B-tree index is an ordered list of values divided into ranges. By associating a key
with a row or range of rows, B-trees provide excellent retrieval performance for a
wide range of queries, including exact match and range searches.

illustrates the structure of a B-tree index. The Ex shows an index on the

department_id column, which is a foreign key column in the employees table.

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks
that store values. The upper-level branch blocks of a B-tree index contain index data
that points to lower-level index blocks.
The root branch block has an entry 0-40, which points to the leftmost block in the
next branch level. This branch block contains entries such as 0-10 and 11-19. Each of
these entries points to a leaf block that contains key values that fall in the range.
A B-tree index is balanced because all leaf blocks automatically stay at the same
depth. Thus, retrieval of any record from anywhere in the index takes approximately
the same amount of time.

The leaf blocks contain every indexed data value and a corresponding rowid used to
locate the actual row. Each entry is sorted by (key, rowid). the leftmost leaf block (0-
10) is linked to the second leaf block (11-19).

[Link] key tables :

The B-tree indexes are useful when there is primary key for the table.
[Link] is needed :
The B-tree index is used when sorting is needed for improving the performance of
query.

--Ex- Create index emp_empno_idx on employees (employee_id);

**Calculate Performance of the query

(OR) testing query is searching for indexes or not :-

Step1:- use an explain plan FOR Clause in front of the query.

Syntax: explain plan for ‘select statement’ ;

Whenever we are specifying this clause oracle server internally automatically creates
plan table, which display query performance.

Step2:- (display plan table)

If we want to view plan table then we are using DISPLAY function dbms_xplan
package by using following syntax.
Syntax: select * from table (dbms_xplan.display ()) ;

--Ex- Without using Index-

Select * from emp where ename=‘SCOTT’ ;

Testing: explain plan for select * from emp where ename=‘SCOTT’ ;

select * from table (dbms_xplan.display());

--Ex- With using indexes
Create index ind_1 on emp (ename) ;

Select * from emp where ename =‘SCOTT’

Testing: explain plan for select * from emp where ename=‘SCOTT’ ;

select * from table (dbms_xplan.display());

2). Function Based Index

In oracle , whenever WHERE clause having functions or expressions then oracle

server doesn’t search for indexs if those columns having already indexes also it will
do full table scan. To overcome this issue oracle introduced extension of the B-tree
index called function based index which used to create indexes on columns along
with functions or expressions.

By default function based index are B-tree index

Syntax- CREATE INDEX index_name ON table_name (function_name(column_name));

OR stored expression
-- Ex, suppose you create the following function-based index:

CREATE INDEX emp_total_sal_idx

ON employees (12 * salary * commission_pct, salary, commission_pct);

SELECT employee_id, last_name, first_name,

12*salary*commission_pct AS "ANNUAL SAL" FROM employees
WHERE (12 * salary * commission_pct) < 30000
ORDER BY "ANNUAL SAL" DESC;

A function-based index is also useful for indexing only specific rows in a table. For Ex,
the cust_valid column in the [Link] table has either I or A as a value. To index
only the A rows, you could write a function that returns a null value for any rows
other than the A rows. You could create the index as follows:

CREATE INDEX cust_valid_idx

ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );

Create index FI_Employee on Employee(trunc(Hire_date));

--Ex- Create a function base index to show only month and year as hiredate from
emp table

Step1: create index ind_mon_yr on emp (to_char(hiredate,'mm-yyyy'));

Explain plan for

select empno,ename,sal, to_char(hiredate,'mm-yyyy')as mon_yr from emp
where to_char(hiredate,'mm-yyyy') between '12-1980' and '12-1981';

select * from table (dbms_xplan.display());

If you want to check index details then we are using USER_INDEXES dictionary.
select * from all_indexes where table_name='EMP';
select * from user_ind_columns where table_name='EMP';

--Ex- create function based index to calculate difference between two dates.
create index ind_delay
on emp (round(months_between('24/jan/2021', hiredate) / 12, 2)) ;

explain plan for select * from

(select empno,ename,job,sal,deptno,round(months_between('24/jan/2021',
hiredate) / 12, 2) as delay from emp) where delay >= 39.80;

select * from table (dbms_xplan.display());

select * from all_indexes where table_name='EMP'; --- To check index details

select * from user_ind_columns where table_name='EMP'; --To check column details

Advantages of Oracle Function Based Index :

 It is easy to use and implement.
 These Indexes provides the immediate value.
 These indexes are used to speed up the existing applications without changing
any logic.
 These indexes are used to improve the performance of complex sql queries

Disadvantages of Functional Based Index :

 Sometimes the functional based indexes degrades the performance of query.
User needs to check Explain plan of the query to check it.
 It will not be used where there are huge insert and update [Link]
select statement these indexes are more used.

3). Bit-Map Index:

Cardinality of a column= no. of distinct values /total no. of rows

Cardinality of emp table on empno column = 14/14 =1 --- High cardinality

Cardinality of emp table on job column = 5/14 =0.357 --- Low cardinality

Where to Use Bitmap Index

I will recommend you to use bitmap index when the distinct values are between 1 to
[Link] make sure that following important points before using Bitmap indexes.

When 1 to 10 distinct values then bitmap indexes are very [Link] will improve the
performance of query drastically.
When distinct values between 100 –unlimited distinct values i will recommend you
not to use bitmap indexes. It will decrease the performance of queries.

If Table contains the distinct values which are not more than 20 distinct values then
user should go for Bit map [Link] should avoid the indexing on each and every
row and do the indexing only on distinct records of the table column.
You should able to check drastic change in query cost after changing the normal
index to Bit map index.

The bit map indexes are very much useful in dataware housing where there are low
level of concurrent [Link] map index stores row_id as associated key value
with bitmap and did the indexing only distinct [Link] If in 1 million records
only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and
fetches the records from that 20 values only.

Syntax: Create bitmap index Index_name on Table_name(Columns which have

distinct values);

In bitmap structures, a two-dimensional array is created with one column for every
row in the table being indexed. Each column represents a distinct value within the
bitmapped index. This two-dimensional array represents each value within the index
multiplied by the number of rows in the table.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so
it can be rapidly scanned for matching values. These matching values are delivered to
Oracle in the form of a Row-ID list, and these Row-ID values may directly access the
required information.

The real benefit of bitmapped indexing occurs when one table includes multiple
bitmapped indexes. Each individual column may have low cardinality. The creation of
multiple bitmapped indexes provides a very powerful method for rapidly answering
difficult SQL queries.

remember that bitmap indexes are only suitable for static tables and materialized
views which are updated at night and rebuilt after batch row loading. If your tables
experience multiple DML's per second, BE CAREFUL when implementing bitmap
indexes!

 1 - 7 distinct key values - Queries against bitmap indexes with a low cardinality
are very fast.
 8-100 distinct key values - As the number if distinct values increases,
performance decreases proportionally.

Ex: CREATE BITMAP index BM_D_NAME on DEP(dname);

Ex/Real Life Scenario: “Suppose There are 2 tables which has milions of [Link]
need to improve the performance of [Link] It is taking 4 mins to fetch 1 million
Records.”

Step 1: Explain Plan select * from DEP d,EMP e where [Link]= [Link];

Output: Cost of DEP Table-20000 – table is Full Scanned

Cost of EMP tabe-20000 -- table is Full Scanned

Step 2: Check whether there are SQL Indexes on table columns:

Select * from ALL_INDEXES where Table name in (‘EMP’,’DEP’);

Output: No indexes found.

Step 3: Check description of the table and check whether the normal index where the
Unique index and where bitmap indexes are applicable.

Step 4: Creation of normal index on EMP table name column.

Create index NI_EMP_NAME on EMP(ENAME);

Step 5: EMPNO has unique values so kindly create UNIQUE INDEX ON that
[Link] has also unique values so for DEPTNO column we need to create
unique index.

CREATE UNIQUE INDEX UI1_EMP on EMP(EMPNO);

CREATE UNIQUE INDEX UI2_DEPT on DEPT(DEPTNO);
Step 6: Check for Distinct [Link] dname has 20 distinct departments so on
Department_name column create Bit-map index.

CREATE BITMAP index BM_DEP_NAME on DEP(dname);

Step 7:Check the cost of Query

Explain Plan select * from DEP d,EMP e where [Link]= [Link];

Output: Emp Table :-> 20 Fast Unique Scan

DEP Table:-> 10 Bit-map Scan

and Results will come in 10 Seconds..Hope everyone get idea about basic indexing
and how it is been used in real life scenarios.

**Bitmap Index and Unique constraint:-

You can have a bitmap index and unique constraint on different columns in a table:
create table t (c1 number unique,c2 number);

create bitmap index i2 on t(c2);

But you can't use a bitmap index to support a unique constraint:

alter table t add constraint u unique (c2) using index i2;

**Unique Index: :

Here the concept is bit [Link] needs to check the values of the table to create
unique [Link] table contains uniquely identified values in specified column then you
should use unique index.

Especially while creating the table if we specify the primary key then unique index is
automatically created on that [Link] for Unique key constaint columns you
separately need to do indexing. Kindly make sure that Unique key indexes created on
the columns which has unique values only.

Syntax: Create UNIQUE INDEX Index_name on Table_name(Unique column_name);

--Ex- CREATE UNIQUE INDEX ui_emp on EMP(EMPNO);

--Ex- CREATE UNIQUE INDEX ui_emp1 on EMP (JOB);

ERROR: cannot create UNIQUE INDEX;
Duplicate key found

--Ex- create table t_00 (sno number constraint sno_pk primary key);
Table will create unique index on primary key column and the name of the UNIQUE
index will be same as name of the PRIMARY KEY/UNIQUE constraint.

select * from user_ind_columns where table_name='T_00';

--Ex- create table t_11 (sno number constraint sno_un UNIQUE);

select * from user_ind_columns where table_name='T_11';

If you didn’t specify constraint name while table creation then index name will be
same as constraint name which will be generated by system.

--Ex- create table t_12 (sno number UNIQUE);

select * from user_ind_columns where table_name='T_12';

**Composite Index:

When 2 or more columns in single table are related which each other and used in
where condition of select statement then user should create composite index on the
columns which are created. If all columns selected by in query are in composite index
then oracle will return the values from the index without accessing the table.

Composite indexes should be avoided as they are large in size

Ex: Select [Link],[Link] from Emp e ,Dep d

where [Link]=[Link] and [Link]=[Link];

Suppose we want to do indexing on Employee and Department [Link] in above

ex Empno and Deptno are related to each other. So we can create index on Empno
and Deptno

Create index CI_ENO_DEPTNO on Employee(Empno,Deptno);

**Reverse Key Index :

Use reverse key index to breaks the index scan.

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT
statements, especially with an increasing key, like an index on an Oracle sequence
(which is used for the primary key of the target table). For large batch inserts, Oracle
reverse key indexes will greatly speed-up data loads because the high-order index
key has been reversed.

A reverse key index creates an unusal form of B-Tree index.

 Every byte in the indexed column is reversed.
  The leaf blocks include the ROWID (not reversed). However, the distribution of
the data is changed.

--Ex- For example, by using a sequence to generate a primary key, the sequence will
generate values like :
987500,
987501,
987502,
and so on.
These values are sequential, so if I were using a conventional B*tree index, they
would all tend to go to the same right-hand-side block, thus increasing contention for
that block.
With a reverse key index, Oracle will logically index :
005789,
015789,
205789,
and so on.

Oracle will reverse the bytes of the data to be stored before placing them in the
index, so values that would have been next to each other in the index before the byte
reversal will instead be far apart.
This reversing of the bytes spreads out the inserts into the index over many blocks.

**Clustered Indexes:
 The clustered indexes are indexes which are physically stored in order means it
stores in ascending or descending order in Database.
 Clustered indexes are created one for each table.
 When primary key is created then clustered index has been automatically
created in the table.
 If table is under heavy data modifications the clustered indexes are preferable
to use.

**Non Clustered Indexes:

  The clustered indexes are used for searching purpose as we can create
clustered indexes where primary is defined. But Non clustered indexes are
indexes which will be created on the multiple joining conditions,multiple filters
used in query.
 We can create 0 to 249 non-clustered indexes on single table.
 Foreign keys should be non [Link] user wants to retrieve heavy data
from fields other than primary key the non clustered indexes are useful.

Differnece between Clustered and nonclustered index:-

Clustered Index
 Only one per table
 Faster to read than non clustered as data is physically stored in index order
Non Clustered Index
 Can be used many times per table
 Quicker for insert and update operations than a clustered index

What is mean by Global and Local Index? (Types of Indexes in SQL in term of access
of Table )
When there is partition on the table and we need to apply the indexes on that table
then we need to use global indexes or local indexes. When table is partitioned then
we need to use global or local parameters/keywords in DDL of create index
statement.

**Global Index:
Usually when you create index on the table has indexed but when you are using
partitioned table we need to change the syntax of the create index and need to use
the Global index for one to many relationship. Global index is one to many
relationships which allows index partition to map to many table partitions. The global
index can be partitioned by range or hash method and it can be defined on any kind
of partitioned or non partitioned table.
Syntax: Create index Indexname On table_name(Column_name)
GLOBAL
(PARTITION Partition_name values(value_of_partition),
PARTITION Partition_name values(value_of_partition),
PARTITION Partition_name values(value_of_partition),
PARTITION Partition_other values(value_of_partition));

Ex: Create index Employee_GI on employee (Employee_num)

GLOBAL
(PARTITION Employee_1 values(1),
PARTITION Employee_2 values(2),
PARTITION Employee_other values(default));

**LOCAL INDEX:
Local indexes are indexes where there is one to one mapping between index partition
and table partition. These indexes are basically used to improve the performance of
partitioned tables. Local indexes directly uses divide and conquer approach to
generate the Fast and best execution plan of SQL Query.
Syntax: Create index indexname on table_name(Column_name)
LOCAL
(Partition Partition_name1,
Partition Partition_name2,
Partition Partition_name3….);

Alter Index:-
Rename indexes:- Alter index Index_name Rename to New_indexname;
Enable an Index: alter index index_name rebuild;
Disable an index: alter index index_name unusable;

You can check the status of index by using USER_INDEXES data dictionary.
select * from all_indexes where table_name = 'TABLE_NAME';

After run above query check the STATUS column of index if it is VALID then index is
enable if it is DIABLED means index is disabled.

Enable an Function-Based Index:-

Enable : alter index index_name ENABLE;
Disable: alter index_name DISABLE;
Drop Index: drop index index_name ;

Difference between UNIQUE and NONUNIQUE index :

UNIQUE index NONUNIQUE index

It allow only single occurrence of key It allow multiple occurance of the key
value value
In unique index, there is one ROWID per In nonunique index, there are multiple
data value. ROWID per data value
a unique index will scan all relevant leaf a non unqiue index results in Oracle
blocks of the b-tree and stop when it scanning all leaf blocks of the relevant
finds a row matching the queried key part of the b-tree, when it finds a row
value because Oracle knows the index is that matches the query it will continue
unique. to scan all further leaf blocks to see if
they are any other matching rows.
thus making use of a unique index much Nonunique index are slower than
faster. unique index
It doesn’t scan all the leaf block of b-tree It scan all the leaf block of b-tree

Difference between B-TREE and BITMAP index :

B-TREE BITMAP
B-tree indexes are the default index type To create a bitmap index you need to
of the CREATE INDEX statement specify CREATE BITMAP INDEX.
B-tree indexes are suitable for columns Bitmap indexes are suitable for columns
with a high number of distinct values with a low number of distinct values.
A b-tree index has index nodes (based A bitmap index looks like, a two-
on data block size), it a tree form. dimensional array with zero and one
(bit) values
B-Tree index can not index null values at Bitmap index can index null values
all
Use for OLTP Used in Dataware housing support
system.

Index Access Methods

Full Table Scan :

A full table scan looks through all of the rows in a table – one by one – to find the
data that a query is looking for. Obviously, this can cause very slow SQL queries if you
have a table with a lot of rows – just imagine how performance-intensive a full table
scan would be on a table with millions of rows. Using an index can help prevent full
table scans.

If a query does not have a WHERE clause to filter out the rows which appear in the
result set, then a full table scan might be performed.

Index Full scan:

There are some scenarios in which a full table scan will still be performed even
though an index is present on that table.
If a query does have a WHERE clause, but none of the columns in that WHERE clause
match the leading column of an composite index on the table, then a full table scan
will be performed.

Even if a query does have a WHERE clause with a indexed column but still full table
scan can still occur. This situation arises when the comparison being used by the
WHERE clause prevents the use of an index. Here are some scenarios in which that
could happen:
 If the NOT EQUAL (the “<>“) operator is used. An Ex is “WHERE NAME <>
‘XYZ'”. This could still result in a full table scan, because indexes are usually
used to find what is inside a table, but indexes (in general) cannot be used to
find what is not inside a table.
 If the NOT operator is used. An Ex is “WHERE NOT NAME = ‘AAAA’”
  If the wildcard operator is used in the first position of a comparison string. An
Ex is “WHERE NAME LIKE ‘%INTERVIEW%'”.
 If IS NOT NULL/ IS NULL operator used then also full table scan will happen.

Rowid Scan: Rowid scan is the fastest Access paths to retrieve a single row, because
the exact location of the row is specified and optimizer does not perform any scan.
Rowid contains as shown in below diagram :-

The rowid of a row specifies the datafile and data block containing the row and the
location of the row in that block. Locating a row by specifying its rowid is the fastest
way to retrieve a single row, because the exact location of the row in the database is
specified.
To access a table by rowid, Oracle first obtains the rowids of the selected rows, either
 from the statement's WHERE clause
 or through an index scan of one or more of the table's indexes.
Oracle then locates each selected row in the table based on its rowid.

--Ex-
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------

Index Fast Full Scan :

Index fast full scans are used when the index contains all columns in the query and at
least one column in the index key has a NOT NULL constraint, it is an alternative to
full table scans.

The scenario where this index would be invoked is, Whenever the Oracle SQL
optimizer detects that the query is executable without touching table rows, Oracle
invokes this fast full index scan and quickly reads every block of the index without
touching the table itself provided that query doesn’t contain any ORDER BY clause.
The definition for this scan is more or less similar to the index full scan but the only
difference is that former will be invoked when ORDER BY clause is not mentioned in
the query. It differs from the index full scan in another way where output won’t be in
the sorted order since ORDER BY clause is not mentioned.

The major difference between fast full index scan and full index scan is that Index
fast full scan will be invoked only if ORDER BY clause is not mentioned in the query.

Another difference between fast full index scan and full index scan is that table
data will never be accessed at any cost if the Index fast full scan is invoked.

You can use fast full index scans by setting the OPTIMIZER_FEATURES_ENABLE
initialization parameter or using the INDEX_FFS hint.

ADVANTAGE:
 If index full scan is followed, then we can eliminate full table scan completely
thereby we can reduce the execution time of query, number of data blocks to
be referred and I/O.
 Since multi index blocks read is supported by this scan (unlike index full scan
where only single index block can be read at a time), query performance would
be good and better.

--Ex- explain plan for select /*+ index_ffs(departments dept_id_pk) */ count(*) from
departments;

select * from table (dbms_xplan.display());

Index Unique Scan :

An index unique scan performs a unique scan to return a single ROWID if SQL
statement contains a UNIQUE or a PRIMARY KEY as follows.
If Oracle has to follow Index Unique Scan, then in the SQL, equality operator (=) must
be used. If any operator is used other than equality operator, then Oracle can’t
impose this Index Unique Scan.
Only on b*tree unique index, this Index Unique Scan is imposed if equality operator is
used in the query. In all other types of index (like non-unique b*tree index), Oracle
Can’t impose this Index Unique Scan though equality(=) operator is used

If your table is having primary key and that primary key is using for filter condition in
WHERE clause then it will do INDEX UNIQUE SCAN also
--Ex- explain plan for select * from emp where empno =7839;

select * from table (dbms_xplan.display());

Index Range Scan :

Optimizer will use a range scan for accessing selective data ( instead of unique row )
via using Index as follows.
Index Range Scan means the retrieval of one or more ROWIDs from an index. Indexed
values are generally scanned in ascending order.
Index Range Scan is applicable to both B*Tree Unique Index and B*Tree Non-Unique
Index unlike Index Unique Scan, where it is applicable only to B*Tree Unique Index.

If Oracle has to follow Index Range Scan, if B*Tree Unique index is created on the
table, then in the SQL, any non-equality operator must be used like <=, <, >, >=, IN,
BETWEEN. If any of these operators is used, it means more than one index record is
going to be referred in the index table and in turn which returns more than one
ROWID (because in the unique index, single index value is mapped to single ROWID.
So if 5 index records are accessed, it means 5 ROWIDs are retrieved).

If Oracle has to follow Index Range Scan, if B*Tree Non-Unique index is created on
the table, then in the SQL, it can have equality operator (=). If this is used, it means
only one index record is going to be referred in the index table and in turn which
returns more than one ROWID (because in the non-unique index, single index value is
mapped to more than one ROWID).

Index Skip Scan :

Index skip scan is performed initial (leading) column of the composite index is not
specified in a query as follows.

A composite index could only be used if the first column, the leading edge, of the
index was referenced in the WHERE clause of a statement.
However, if the leading column isn’t referenced now, Oracle can use the index
anyways via an Index Skip Scan access path

NOTE: In oracle index information stored under USER_INDEXES data dictionary.

--Ex- create INDEX ind1 on emp (ename);

Select * from USER_INDEXES where table_name = ‘EMP’ ;

NOTE: If we want to view column names along with indexname then we are using
USER_IND_COLUMNS data dictionary.

Select * from USER_IND_COLUMNS where table_name = ‘EMP’;

Index Join Scan:
Index Join Scan is a hash join of many indexes that together contain all the table
columns that are used by the query. If you want to use Index join scan, you should
use INDEX_JOIN hint in the query.

SYNONYMS

Synonyms are database object which provide security. Oracle SQL / PLSQL uses
synonym as an alias name for any database object such as tables, views, sequences,
stored procedures, and other database object.

In other words we can say that in Oracle SQL / PLSQL a synonym is an alternative
name for database objects. Generally synonyms hides another schema username,
object name.
When you grant object privileges on a synonym, you are granting privileges on the
underlying object, and the synonym only acts as an alias in the GRANT statement.
In all DB by default synonym are Private synonym .

Synonyms are two types:-

1). Public Synonyms
2). Private Synonyms

Syntax : CREATE OR REPLACE [PUBLIC] SYNONYM schema.synonym_name

FOR [Link]@dblink ;

use the OR REPLACE option if you want to re-create the synonym if it already exists.
In case the synonym does not exist, the OR REPLACE has no effect.

1). Public Synonyms :- The synonym will be accessible for all the valid users, but the
user must have the sufficient privileges for the object to use its synonym.
Syntax:
CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR [Link]@dblink ;

the best Ex of PUBLIC SYNONYM is dual table.

Before we creating public synonym DBA first give us CREATE PUBLIC SYNONYM
system privilagesto user by using following syntax otherwise oracle server returns
error insufficient privilages
Syntax: grant create public synonym to user_name ;

--Ex- create or replace PUBLIC sysnonym emp_syn for [Link];

2). Private Synonyms :- The synonym are only accessible to schema users.
Syntax:
CREATE OR REPLACE SYNONYM synonym_name FOR [Link]@dblink ;

--Ex - Suppose you have a table called employee in the schema owned by the user
HR, and you granted the SELECT privilege for the employee table to PUBLIC.
To query data from the employee table, you use the following statement:

SELECT * FROM [Link];

Notice that you must include the name of the schema and table name in the query.
To simplify this query, you can create a public synonym using the following CREATE
PUBLIC SYNONYM statement:

CREATE PUBLIC SYNONYM employee FOR [Link];

Now, you can query the table sales with a simpler SQL statement:

SELECT * FROM employee;

Notice that the employee public synonym hides the name of the employee table and
its schema HR.

To check synonyms details we use USER_SYNONYMS data dictionary-

select * from user_synonyms where table_name='EMP';

select * from user all_synonyms ;

DROP SYNONYM statements:

The DROP PRIVATE SYNONYM statement allows you to delete a synonym from the
database.
Syntax: DROP SYNONYM schema.synonym_name FORCE;

use the FORCE keyword to delete the synonym even if it has dependent tables or
user-defined types.

If you want to drop a private synonym, you must be the owner of the schema to
which the synonym belongs or you must have the DROP ANY SYNONYM privilege. In
case you want to drop a PUBLIC synonym, you must have the DROP PUBLIC
SYNONYM privilege.

To drop a public synonym, you use the PUBLIC keyword as follows:

Syntax: DROP PUBLIC SYNONYM synonym_name FORCE;

Difference between SYNONYM and ALIAS:

SYNONYM ALIAS
Synonyms are a database object type Alias are not a database object. aliases
which refer database objects are just a name to refer a table, view or
a column inside a query.
Synonyms can be created for tables, aliases are used only for views, tables
views, functions, procedures, packages, and their columns.
sequences, materialized views, object
types and triggers.
Synonyms are a database object, they Aliases valid inside the query where
are valid inside the schema (private they are being used.
synonym) or inside the database (public
synonym).
Each schema needs “create synonym” there is no any privilege to use aliases.
privilege to create synonyms.

**Alter Commands for SYNONYMS :

If you want to change any synonym name then you must have to use
CREATE OR REPLACE query –

Create or replace SYNONYM synonym_name for table_name ;

Then you must have to compile that changed SYNONYM.

ALTER SYNONYM synonym_name COMPILE; ---Private synonym
ALTER PUBLIC SYNONYM synonym_name COMPILE; ---Public synonym

Advantages of Oracle synonyms :

Synonyms allow you to change complicated and lengthy names by simplified aliases.
Synonyms provide security by hiding DB objects.

CLUSTER
Cluster is database object which contains group of table together and also shares
same data block.
In all DB cluster DB objects are used to improve performance of the joins that’s why
cluster DB objects are created by DB administrators [Link] tables must have
common column name, common column is also called as CLUSTER KEY.
Generally clusters are created at the time of table creation.
In all DB whenever we are submitting INNER JOIN or OUTER JOIN then DB server
internally checks from clause tables are available in cluster or not ?
If those tables are available in cluster then DB server retrive data fastly from the
cluster table.
When to use a cluster : The ideal places to use a cluster are when you have a group
of tables that are frequently queried together.

** Creating cluster in oracle :-

Step1 :- creating a cluster
Step2 :- create an index on cluster
Step3 :- create cluster table

Step1 :- create a cluster based on common column name, this common column name
is also called as cluster KEY.

Syntax: create cluster cluster_name (common column_name datatype (size)) ;

Step2 :- create an index on cluster

Syntax : create index index_name on cluster cluster_name ;

Step3 :- creating cluster table

Syntax : create table table_name (common column_name datatype (size)),

Col1 datatype size(), col2 datatype size(),…..)
Cluster cluster_name (common column_name) ;

--Ex- create cluster emp_dep (deptno number (10));

Create index abc on cluster emp_dep

Create table emp10 (empno number (10), ename varchar2(10), sal number (10),
deptno number (10)) cluster emp_dep (deptno);

Create table dep10 (deptno number(10), dname varchar2 (10), loc varchar2(10))
cluster emp_dep (deptno);

To see the structure of cluster table you can use USER_CLUSTERS, ALL_CLUSTERS,
and DBA_CLUSTERS data dictionary views :

select * from user_clusters where cluster_name='EMP_DEP';

select * from USER_TABLES where table_name='EMP10';

select * from USER_TABLES where table_name='DEP10';

You can check table structure so you will see the table has cluster or not.

NOTE: In oracle cluster tables having same ROWID. We cant not drop cluster, if
cluster is having tables to overcome this issue oracle 8.0 introduced including table
clause along with the drop cluster cluster_name which is used to drop cluster with
tables.

Syntax: drop cluster cluster_name including tables ;

Drop cluster emp_dep ;
ERROR: cluster not empty

Drop cluster emp_dep including tables;

o/p- cluster dropped.

HIERARCHICAL QUERIES

You can use hierarchical queries to travel along parent-child relationships in your
data. For Ex, family trees, computer directory structures, and company organization
charts.

If we want to retrive hierarchical data then we are using following clauses-

1). Level 2). Start With 3). Connect By

Syntax: select level, column_name from table_name Where condition

Start with condition connect by prior parent_colname= child_colname;

LEVEL : The position in the hierarchy of the current row in relation to the root node.
START WITH : You state which rows are the roots here. These are the rows that
appear at the "top" of the tree.

In a company org chart this is the CEO. Here that's employee_id 100, Steven King. So
you can begin the chart with him using:

START WITH employee_id = 100 (OR) START WITH manager_id is null

CONNECT BY : You state the parent-child relationship here. This links the columns
that store the parent and child values. You access values from the parent row using
the keyword prior.
In a company each employee's "parent" is their manager. Thus you need to join the
parent row's employee_id to the child's manager_id. So you connect the prior
employee_id to the current manager_id, like so:

CONNECT BY prior employee_id = manager_id

CONNECT_BY_ROOT : Returns the root node(s) associated with the current row.

SYS_CONNECT_BY_PATH : It can be useful to see values from all the rows between
the root and the current row. Sys_connect_by_path allows you to do this. It builds up
a string, adding the value from the first argument for the current row to the end of
the list. It separates these using the second argument.

Syntax: SYS_CONNECT_BY_PATH (column_name,’Delimitername’);

CONNECT_BY_ISLEAF : Indicates if the current row is a leaf node.

ORDER SIBLINGS BY : Applies an order to siblings, without altering the basic

hierarchical structure of the data returned by the query.

PRIOR : is a unary operator which is used or indicates that “father of” the records or
first record.

Bottom up hierarchy : Whenever we are using PRIOR operator infront of parent

column (manager_id) then oracle server uses BOTTOM-TOP search within tree
structure.

Top down hierarchy : Whenever we are using PRIOR operator infront of child column
(employee_id) then oracle server uses TOP-BOTTOM search within tree structure.

NOTE: Do not specify ORDER BY or GROUP BY clause with Hierarchical Queries. If

you want to order the data then use “ORDER SIBLINGS BY” clause.
ORDER SIBLING BY <column_name>
But if we want to order the data based on LEVEL then we can use simple ORDER
BY clause
ORDER BY <LEVEL_NO>

NOTE: START WITH clause always mandatory to use CONNECT BY clause.

--Ex- WAQ to show employees hierarchical structure from employees table.

select * from employees

START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id;

--Ex- WAQ to show BOSS- EMPLOYEE relationship.

select LEVEL, SYS_CONNECT_BY_PATH (first_name, ‘‘) from employees

start with manager_id is null
connect by prior employee_id = manager_id;

--Ex- WAQ to display employees who are working under ‘BLAKE’ from employee table

Select LEVEL, SYS_CONNECT_BY_PATH (first_name, ‘‘) from employees

Start with first_name=‘BLAKE’
connect by prior employee_id = manager_id;

--Ex- WAQ to count the employees at each level.

Select level, count(*) from employees

Start with manager_id is null
Connect by prior employee_id = manager_id
Group by level
Order by 1;

--Ex- WAQ to find the tree structure data of employee table in asending order.

Select level, first_name from employees

Start with manager_id is null
Connect by prior employee_id= manager_id
Order sibling by ename;

**NOCYCLE:
It's possible to store loops in your hierarchy. Usually this is a data error. But some
structures may contain loops by design.
For Ex, the following sets the CEO's manager to be a lowly programmer:

update employees set manager_id = 107 where employee_id = 100;

This leads to a circle in your data. So you could get stuck in an infinite loop. Luckily
Oracle Database has cycle detection to stop this.
If you try and build a hierarchy that contains a loop, connect by throws an ORA-1436
error:

select * from employees

start with employee_id = 100
connect by prior employee_id = manager_id;

You can avoid this using the nocycle keyword. This spots when the query returns to
the same row. The database hides the repeated row and continues processing the
tree.
To use it, place nocycle after connect by:

select * from employees

start with employee_id = 100
connect by nocycle prior employee_id = manager_id;

This is another important unary operator in oracle which will gives the basic idea
about the root of the [Link] you want to see the boss’s hierarchy then user
needs to use connect_by_root keyword .

select lpad(' ',level*3,' ')||first_name name,

connect_by_root first_name boss from Employees
start with manager_id is null
connect by prior employee_id = manager_id;

PARTITIONS TABLE
Partitioning is a divide-and-conquer approach to improving Oracle maintenance and
performance of the application in backup and recovery process.
Partition table are created by DB administrator in very large database (VLDB).

When To Partition?
There are two main reasons to use partitioning in a VLDB environment. These
reasons are related to management and performance improvement.
Partitioning offers:
 Management at the individual partition level for data loads, index creation and
rebuilding, and backup/recovery. This can result in less down time because
only individual partitions being actively managed are unavailable.
 Increased query performance by selecting only from the relevant partitions.
This weeding out process eliminates the partitions that do not contain the data
needed by the query through a technique called partition pruning.
When to Use partitioning:
 When a table reaches a "large" size. Large being defined relative to your
environment. Tables greater than 2GB should always be considered for
partitioning.
 When table performance is weak and we need to improve performance of
application.

Advantages of Partition:
[Link] Performance
[Link] availability
[Link] Simpler management

Types Of Table partitioning:

1). Range Partition
2). List Partition
3). Hash Partition

[Link] Partition:
When in the specified table the data is based on the specific date range and it is
properly divided in some range then user should go for the partitioned named as
‘Range Partition’.

This partition type is most common type of Table partitioning which is been useful for
Data warehouse to store the historical data in given date [Link] partitioning is
done in such way that the expression values lies within the specific [Link] kind of
Table partition is used when there is a particular date range available.

Syntax:
Create table Tablename (Col1 datatype(size)…..Coln datatype(size))
Partition by range(Column needs to be partitioned)
(Partition partition_name1 values less than(value1)….
Partition partition_name-n values less than(maxvalue));

--Ex- Create partition tables based on key columne ‘SAL’.

Create table Employee(emp_no number(2), Salary number(2))

partition by range(Salary)
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(maxvalue));

Insert into Employee values(‘Amit’, 40000);

Insert into Employee values(‘Rama’, 11000);
Insert into Employee values(‘Shiva’, 25000);

Selecting records from partitioned tables and from specific partition

Select * from Employee;
Select * from Employee partition(p1);

Alter commands for Range partition:-

Add partition:-
Alter table Employee add partition p5 values less than(50000);

Drop Table partition: Alter table Employee drop partition p1;

Rename Table partition: Alter table Employee Rename partition p1 to p6;

Truncate partition: Alter table Employee Truncate partition p1;

Split partition:
Alter table Employee Split partition p1 at (5000) into (partition p10,partition p11);

*** To see how many partitioned tables are there in your schema give the
following statement

select * from user_part_tables where table_name=‘table_name’;

*** To see on partition level partitioning information

select * from user_tab_partitions;

*** Creating a multicolumn range-partitioned table :

CREATE TABLE sales_demo (year NUMBER, month NUMBER,day NUMBER,

amount_sold NUMBER)
PARTITION BY RANGE (year,month)
(PARTITION before2001 VALUES LESS THAN (2001,1),
PARTITION q1_2001 VALUES LESS THAN (2001,4),
PARTITION q2_2001 VALUES LESS THAN (2001,7),
PARTITION q3_2001 VALUES LESS THAN (2001,10),
PARTITION q4_2001 VALUES LESS THAN (2002,1),
PARTITION future VALUES LESS THAN (MAXVALUE,0));

INSERT INTO sales_demo VALUES(2000,12,12, 1000);

INSERT INTO sales_demo VALUES(2001,3,17, 2000);
INSERT INTO sales_demo VALUES(2001,11,1, 5000);
INSERT INTO sales_demo VALUES(2002,1,1, 4000);

2). List Partitioning:

List partition basically based on charcter datatype column.
List partition enables you to explicitly control how the partition of tables needs to be
done by specifing list of distinct values as partition key in description of each
partition.

When there is a set of distinct values in the table which is properly divided then user
should go with list [Link] listing the distinct values user should do the partition.

Syntax:
Create table Tablename (Col1 datatype(size)….Coln datatype(size))
Partition by list (Column needs to be partitioned)
(Partition partition_name1 values (value1,value2,…)
Partition partition_name-n values (default));

Simple ex is the table storing the country data in which state is distinct [Link]
You can partition the table using list of state values.

Create table Employee(emp_no number(2),State varchar2(20))

partition by List(State)
(partition p1_MH_KA values (‘Maharashtra’,’Karnataka’),
partition p2_GJ_RJ values(‘Gujrath’,’Rajasthan’),
partition p3_BG_SM values(‘Bengal’,’Sikkim’),
partition p4_Other values (Default));

Insert into Employee values(‘Vitthal’, ‘Gujrath’);

Insert into Employee values(‘Anmol’, ‘Rajasthan’);
Insert into Employee values(‘Sumit’, ‘Karnataka’);
Insert into Employee values(‘Amit’, ‘Maharashtra’);
Insert into Employee values(‘Rama’, ‘Kerala’);
Selecting records from partitioned tables.
Select * from Employee; -- select from table
Select * from Employee partition(p1_MH_KA); ---select from specific partition

Add new partition: Alter table Employee add partition p5_Kerala values(‘Kerala’);

Drop partition: Alter table Employee drop partition p1_Maharashtra;

Rename : Alter table Employee Rename partition p1_Maharashra to p6_Maha;

Truncate partition: Alter table Employee Truncate partition p1;

** Multi-column List-Partitioned Table

Multi-column list partitioning enables you to partition a table based on list values of
multiple columns. Similar to single-column list partitioning, individual partitions can
contain sets containing lists of values.
A multi-column list-partitioned table can only have one DEFAULT partition.

--Ex-
CREATE TABLE sales_by_region_and_channel
(deptno NUMBER, deptname VARCHAR2(20), quarterly_sales NUMBER(10,2),
State VARCHAR2(2), channel VARCHAR2(1) )
PARTITION BY LIST (state, channel)
(PARTITION q1_north_direct VALUES (('OR','D'), ('WA','D')),
PARTITION q1_north_indirect VALUES (('OR','I'), ('WA','I')),
PARTITION q1_south_direct VALUES (('AZ','D'),('UT','D'),('NM','D')),
PARTITION q1_ca_direct VALUES ('CA','D'),
PARTITION rest VALUES (DEFAULT) );

3). Hash Partition:

Hash partitioning is type of partitioning where data is partitioned by using the

hashing algorithms.
Hash partition is mainly used to distribute data among the different storage
[Link] partition is easy to use and best alternative for list partition when data
is not historical.
Syntax:
Create table Table_name (Col1 datatype1…Coln datatype ‘n’)
Partition by Hash(column_name)
Partitions partition_number);

--Ex- Create table Employee (emp_no number(2), emp_name varchar(2))

partition by hash(emp_no)
partitions 5;

The Above statement will create 5 partitions named:

Sys_P1
Sys_P2
Sys_P3
Sys_P4
Sys_P5

In oracle all partitions information are stored under USER_TAB_PARTITIONS data

dictionary.

Select * from USER_TAB_PARTITIONS where table_name=‘EMPLOYEE’;

--Ex- In the following Ex, names of individual partitions, and tablespaces(ts) in which
they are to reside, are specified. The initial extent size for each hash partition
(segment) is also explicitly stated at the table level, and all partitions inherit this
attribute.

CREATE TABLE departments_hash (department_id NUMBER(4) NOT NULL,

department_name VARCHAR2(30)) STORAGE (INITIAL 10K)
PARTITION BY HASH(department_id)
(PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3,
PARTITION p4 TABLESPACE ts4);

**** Creating a Table with Subpartitions (Range + List partition):-

In Ex creating a composite range-list partitioned table with both read-only and read-
write status.

The orders_read_write_only is explicitly specified as READ WRITE, so the default

attribute of the table is read write.

The default attribute of partition order_p1 is specified as read only, so the

subpartitions ord_p1_north and order_p1_south inherit read only status from
partition order_p1. Subpartitions ord_p2_south and order_p3_north are explicitly
specified as read only, overriding the default read write status.

CREATE TABLE orders_read_write_only (order_id NUMBER (12),

order_date DATE NOT NULL, state VARCHAR2(2) ) READ WRITE
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (state)

( PARTITION order_p1 VALUES LESS THAN (01-12-2015)) READ ONLY

( SUBPARTITION order_p1_north VALUES ('OR', 'WA'),
SUBPARTITION order_p1_south VALUES ('AZ', 'UT', 'NM') ),

PARTITION order_p2 VALUES LESS THAN (01-03-2016))

( SUBPARTITION order_p2_north VALUES ('OR', 'WA'),
SUBPARTITION order_p2_south VALUES ('AZ', 'UT', 'NM') READ ONLY ) ,

PARTITION order_p3 VALUES LESS THAN (01-06-2016)

(SUBPARTITION order_p3_north VALUES ('OR', 'WA') READ ONLY,
SUBPARTITION order_p3_south VALUES ('AZ', 'UT', 'NM') ) );

NOTE: refer more Exs on below link-

[Link]

-----------------Index organized table (IOT) ----------------------------------------------

INDEX-ORGANIZED TABLE
-An index-organized table keeps its data sorted according to the primary key
column values for
the table. Index-organized tables store there data as if the entire table was stored
in an index.
An index-organized table allows you to store the entire table’s data in an index.
Ex:
create table student (sno number(2),sname varchar(10),smarks number(3)
constraint
pk primary key(sno) organization index;
-------------------------------------------------------------------------------------------------------
--WITH clouse-----CTE (Comman Table Expretion)----------

WITH temp as
(SELECT avg(Salary) avg from Employees)
select e.Employee_id, e.first_Name, [Link] FROM Employees e, temp
WHERE [Link] > [Link] order by [Link] desc;

With Dep_Count As
(Select Deptno,Count(Empno)No_Of_Emp From Emp Group by Deptno)
Select Empno, Sal/No_Of_Emp From Emp E, Dep_Count C Where [Link] =
[Link];

with a as
(select department_id, count(employee_id) cnt_emp from employees
group by department_id)
select d.department_id, a.cnt_emp from departments d left join a
where d.department_id = a.department_id ;

ex- with high_3 as

(select rownum r, empno, ename, sal, deptno from (select * from emp order by sal
desc))
select * from high_3 where r=3;