Oracle SQL

Oracle is a company that provides database, A Database is an organised

collection of structured information or data.
Data in a database is organised in a table form, in which the columns are called
fields and the rows are records. Tables are called entity/relation that’s why SQL
is called ‘Relational Database Management System (RDBMS)’.
DBMS- It is an acronym of Database Management System that is a system
software responsible for creating, retrieval, updating & management of the
database.
Difference Between RDBMS & DBMS-
Both are applications whose function is to manage the database but DBMS
applications store data as file while RDBMS applications store data as entity
i.e., called table or relation.
DBMS (Database Management Relational Database Management
System) System (RDBMS)

• DBMS Application store data • RDBMS applications store data

as file. in a tabular form.
• Normalization is not present in • Normalization is present in
DBMS. RDBMS.

Most modern database management systems like MySQL, Microsoft SQL

Server, Oracle, IBM DB2 etc.
SQL (Structured Query Langauage)-
SQL is an acronym of Structured Query Language, which is used to interact
Oracle Data Base with our System, it lets you access & manipulate data base
object like we want to create an object to fetch an object to manipulate data
for that we need a Structured Query Language.
Columns Are called Fields.
Rows Are called Records
Tables Are called Entity/Relation.
Structured Fixed Data Model.
Oracle Latest version 19c but we are working on ‘11g/12c’.
 -Select Statement-
Select statement is used to select & access a particular database object as per
your convenience.
Applications-
• To Present the Data dictionary-
Any database object that oracle itself manages is called data dictionary.
Select * From Tab;
It will give the result of tables & views.
Select * From tabs;
Select * From user_tables;
It will just render the tables with the columns.
• To Present a Particular Column of a Table-
Select columnname1, columnname2 from table name;
To select multiple columns, we have to use comma (,). Here it is necessary to
take care of the comma otherwise it will take another column as Alias.
• To Alias a Column-
Select column_name new_columnname from table name;
Select column_name as “New columnname” from table name;
We mainly use under score (_) between two strings when naming a column but
we can use double inverted comma (“”) to use space instead of underscore.

Here it is necessary to take care of the comma & semi colon.

‘As’ indicate a column alias.
• To make a Concatenation-
Combining the contents of two or more columns is known as Concatenation.
If you wanted to do this then its syntax could be like this
Select column1 || ‘quote ‘ ||column2 as newcolumnname from tablename;

‘ ‘ String Literal- it is used to indicate the exact set of characters including

spaces to be displayed.
 • To Solve Arithmetic Operation- SQl follow the law of BODMAS.

Select salary, commission_pct, (salary*commission_pct /100) +salary as

“Total Salary” from employees;
Note: If any value in an arithmetic operation is null, then result will be null.
For Ex- salary is 50000 and commission is null then the result of arithmetic
operation will be null.
• To View without Duplicate data in a table/To view Unique data in a
table-
We can use the select statement with Distinct & Unique
Select distinct column_name from table_name;
Select unique column_name from table_name;

-Describe Statement-
It is used to display the database object structure like column & data-type.
Describe table_name;

DDL (Data Definition Language)

It is used to define database structure, under which some statements come
like CREATE, ALTER, DROP, TRUNCATE, FLASHBACK etcetera. It works on whole
database object & it is auto Committed so we can’t perform explicit commit or
rollback statement on it to make the changes permanent that’s why it fast.
1-CREATE Statement- It is used to create an object like tables, views, index,
synonyms sequencing etc.
To create a table-
Create table table_name (Coulmn_name Data-Type (Length));
There are some different types of data-type like number, varchar, varchar2,
date, clob, blob, long, raw etc.
To create a Table with default constraint/Value-
Create table table_name (column_name Data-Type (Length) default
0/sysdate );
To Create a Table with Virtual Column-
Create table table_name (Coulmn1 Data-Type (Length),
Coulmn2 Data-Type (Length) default 0,
Virtual_Column as (Coulmn1 + Coulmn2) );
Virtual Column- Whose value is automatically computed using other columns
values.
2-Insert Statement-
Note-Insert Statement is a DML (Data Manipulation Statement) but we are
using it here for practice purpose.
To Insert the data- After Creating a database object, the insert object is used
to insert data into it.
Insert into table_name values (number data-type, ‘varchar2
datatype’, ‘date datatype’);

To Insert Data according to default value-

Insert into table_name (coulmn1, Coulmn2, Coulmn3) values (number data-
type, ‘varchar2 datatype’, ‘date datatype’);
While defining the value of any table, null is not to be written in single quote
for varchar2 data-type.
For Ex- If data type is varchar2 then the null value of data records will not be
assigned in single quote.
Insert into table_name (coulmn1, Coulmn2, Coulmn3) values (number data-
type, varchar2 data-type null, ‘date datatype’);

3-Alter Statement- Alter Statement is used to modify the existing database

object like adding column and modify.
To add a new column in an existing table-

Alter table table_name add (Column_name data-type (length) default value

);
Note:
1. Default Value-We never add the default value while adding a new
column, because some value is already assigned.
2. Length-If you have any inserted data in your column whose length is
longer than modify length then you can’t modify the length of that
column. Therefore, the length of your column should always be less than
the length of the modified column.
3. Datatype-You cannot change the datatype of the date but you can
change the data type of the varchar2 & number because varcahr2
accepts the number & also depend upon the inserted data.
4. Columns-You can add multiple columns at a time.
Alter table table_name add (column1 datatype(length),coulmn2
datatype(length));

To Modify a column- If you want to change the data type, strength & default
value of a column then you can use modify statement under alter statement
for that.
Alter table table_name modify (column_name change_datatype
(change_strength) change_default value );

To Rename a Column- If you want to change the name of a column then you
can use rename statement under alter statement for that.
Alter table table_name rename column column_name to
new_coulmn_name;

To Drop a Column- if you want to delete a column then you can use drop
statement under alter statement for that like this
Alter table table_name drop column column_name;
Note- You can only delete one column at a time.
To Set unused a Column- It is used to hide a column that is unused
but we cannot use it again. If once a column sets unusable then it will
have to be dropped because this statement is used to drop a column
in the future.
Alter table table_name set unused column column_name;
A data dictionary is used to find the unused column which is as follows.
Select * from user_unused_col_tabs;

To delete an unused column, we will use drop statement under alter

statement as like.
alter table table_name drop unused column;

4-Drop Statement-Drop Statement is used to delete the database structure

like delete a table, view, index, sequencing etc.
Drop table table_name;

Drop table table_name purge;

It is used to delete a table permanent.

5-Truncate Statement- it is used to remove all the data records at once but it
retains data base structure.
Truncate table table_name;

To show dropped data- To show dropped data we use the statement as like
show recyclebin;

There is also a data dictionary to show the recyclebin i.e., like this
select * from recyclebin;
6-Flashback Statement- It used to retrieve dropped database object.
Flashback table table_name to before drop;
Note- If we have two database objects of the same name in the recyclebin
then two database objects of the same name cannot be flashbacked
simultaneously, the latest database object will be flashback only. If you want to
flashback the previously dropped same name object first, then you have to use
object name for that instead of table name which you will get from the data
dictionary of recyclebin.
select * from recyclebin;
Flashback table “object name” to before drop;

7-Purge Statement-It is used to Purge a table or it is used to emptying

recyclebin, In simple words it is used to eliminate drop database object.
Purge recyclebin;
Drop table table_name purge;
It is used to delete a table permanent.
6-Rename Statement-It is used to Rename a table.
Rename table_name to new_table_name;

Constraints
Constraints is a restriction to a row while executing DML statements on it,
actually it is used to specify the rules concerning data in the table, it can be
applied for single & multiple fields during the creation of the table or after
creating using the alter table commands.
There are some types of constraint like-
Primary Key, Unique, not null, Check, Foreign Key etc.
We can use two types of constraint while creating tables. Which are as follows.
1)Table Level Constraint
Create table table_name (Column_name1 datatype (length)
primary key,
Column_name2 datatype (length)
unique key);
2)Column Level Constraint
Create table table_name (column_name1 datatype (length),
column_name2 datatype (length),
Constraint table_column_pk primary key
(Column_name1),
Constraint table_column_pk primary key
(Column_name2));

1-Primary Key-
Each table has a column in which only unique data is taken, that makes it
different from other column.
For Example-If we take the table of a school in which the name of student’s
column, marks & their Roll Number column are mentioned, so Roll Number
column is the most unique in this type table because the name & marks of a
student may be same but their roll number may not be same.
So, we put a restriction on the column named Roll number so that duplicate
data cannot be inserted in it. For that we use primary key.
Primary Key doesn’t allow duplicate values as well as null values, we can
create one primary on a single table.
Create table table_name (Column_name datatype (strength)
Constraint table_columname_pk primary key(column_name));
Note: You can also talk about the problem of duplicate data in your
interview.
A common challenge we faced last month was when data was not getting
inserted in our database object, we noticed an error in logfiles, we found out
that it was duplicate data because it was showing the unique constraint
violated in the log files which means the data was already inserted. I read it &
asked my data quality team to correct and that’s how the problem was
resolved.
composite Primary key-
As we know that we can create one primary key on a single table but we can
create primary key on more than one column as composite Primary key.
Create table table_name (column_name datatype (length),
column_name datatype (length),
Constraint table_column_pk primary key (Column_name, Column_name));
2-Unique-
Unique key doesn’t allow duplicate values but allow null values, we can create
more than one unique on a single table.
Create table table_name (Column_name datatype (strength)
Constraint table_columname_uk unique (column_name));
Note: As we know that Unique Constraint allow null values but if under unique
constraint you have given ‘Null value’ before, then it will accept that Null value
when you give again, you can insert any number of null values in a unique
constraint because null just means empty so how will the duplicates ones
compare.
Interview Question- How many null values can be given in unique constraint?
→Answer- ‘N’ number of null values can be given in the unique constraint
because the meaning of null is blank which cannot be compared with last given
null value.
3-Not null-
Not null doesn’t allow only null values, null mean blank or empty it doesn’t
contain any space.
Create table table_name (Column_name datatype (strength)
constraint table_columname_null not null);
Note1: ‘Not null’ actually belongs to Check Constraint.
Note2: Not Null Constraint is always considered as column level constraint.
Primary Key=Unique+Not Null
Because we can use one primary key on a single table but in some special
circumstances, we can also use multiple primary key in this way mean we can
use ‘Unique’ and ‘Not Null’ constraint together to make the primary key
constraint.
4-Check-
It restricts data records based on condition.
Create table table_name (Column_name datatype (strength)
constraint table_columname_check check (Condition) );
For example, if we want to apply check constraint on date column then syntax
could be like this –
Create table table_name (Column_date date
constraint table_date_uk unique (‘column_date’>‘date’) );
5-Default Constraint- It is used to set a default value to a column. If you want
to set a default value to a column then syntax could be like this.
Create table table_name (column_name Data-Type (Length) default
0/sysdate );
6-Foreign Key-
Foreign Key is used to create a connection between two or more tables.
Create Table table_name (column name datatype (length)
Constraint table_culumnamne_fk foreign key (column_name)
references table_name (column_name);
In order to apply a foreign key, there must be two tables and they must also
have a relationship between them, similar to the relationship between a
parent and a child, hence these tables are referred as parents table & child
table.
The Relationship between them something like this.
1)One to One
2)Many to One
3)Many to Many
The Primary key is always applied to the parent table while the foreign key is
always applied to the child table.
You can understand this relationship by the following tables which establish
relationship between a parent & a foster child.
You can divide these above tables into three parts, like this
Parent Table Child-Parent Table Child Table
Customers Orders Orderitems
Author Books BookAuthor
Publisher
Note: Here the table of Promotion is no way related to Parent and Child, mean
promotion is not referenced.
You have to build the table in this order.
Now you have to make the tables in the Oracle-SQL on the basis of their
relations.
Here the table named customer is related to the table named order as father
as child. Similarly, we can understand the relation of other tables as follows.

Table name Table Type Uniqueness References Constraints

Key
Customers Parent Table Customer# - Primary
Key
Orders Parent & Child Order# Customer# Primary &
Table Foreign
Orderitems Child Table Item, Order# Order# Composite
ISBN Primary &
Foreign
Book Parent & Child ISBN PubID Primary &
Table Foreign
Publisher Parent Table PubID - Primary
Key
BOOKAUTHOR Child Table - ISBN Primary
AuthorID Key
Foreign
Key
AUTHOR Parent Table AuthorID - Primary

Constraint Data Dictionary-

To find out the constraints on a table we can use some Constraint Data
Dictionary.
Select * from user_constraints;
Select * from user_constraints where table_name=‘table_name’;
Select * from user_cons_columns where table_name=‘table_name’;
How to alter Constraints-
1)To Add a Constraint- If you want to add the constraint then you can use
syntax like this
To Add Primary Key
Alter table table_name add constraint constraint_name primary key
(column_name);
To Add Unique
Alter table table_name add constraint constraint_name unique
(column_name);
To Add check Constraint
Alter table table_name add constraint constraint_name check (Condition));
For Ex-
alter table store_reps add constraint store_repid_check check (comm in
('Y','N'));
To add Foreign Key-
Alter table table_name add constraint constraint_name foreign key
(column_name) refrences table_name(column_name);

Note: You can’t put a ‘not null’ key & ‘default value’ by alter statement
because that is column level constraint key, when we modify a column in a
table, we always modify the column for that, so to add ‘not-null’ as well we
have to modify it.
Alter table table_name modify column_name not null;

Alter table table_name modify column_name datatype(length) default

'value';

2)To remove a Constraint- if you want to remove the constraint, then for this
you can use ‘Alter Drop Statement’ which syntax will be like this.
Alter table table_name drop constraint constraint_name;
We will also use same syntax for the ‘not null’ constraint but we will have to
give the name of not null constraint.
But to remove the primary key we have to remove the foreign key which is
referenced to the primary key in another table, for this we can use the
following syntax-
Alter table table_name drop primary key;
Since there is only one primary key in a table, we will write here the primary
key directly instead of constraint name.
But if this primary key is related to another table’s foreign key, then it will give
error like this.
“This Unique /Primary key is Referenced by some foreign key”
For this you have to remove the foreign key from another table.
Firstly
Alter table table_name drop foreign_constarint_name;
then
Alter table table_name drop primary key;

3)To Enable/Disable a Constraint

Alter table table_name disable constraint constraint_name;
Alter table table_name enable constraint constraint_name;

On delete cascade/on delete set null-

While inserting data into a relational table i.e., a table with foreign key, it is
necessary to keep in mind that only the data present in the table can be
inserted into the child table, while the data present in the parent table is
removed from the child table when the data has been deleted.
On delete cascade is used with foreign key during the creating a table or
altering a table, it also deletes the data present in the child table when the
data in the parent table is deleted.
Whereas ‘set null’ null insert data in the child table on deleting the data
present in the parent table.
Alter table table_name add constraint constraint_name foreign key
(column_name) references table_name(column_name) on delete cascade;

Alter table table_name add constraint constraint_name foreign key

(column_name) references table_name(column_name) on delete set null;
 -Where Clause-
Where clause is used to filter the data records from a table based on specified
condition. Mainly It is used with select, delete & update statement. It is used
before the group by clause & can also be used without group by clause.
Where Clause with Select Statement-
Where clause with select Statement is often used with the following operators.
Sr. Mathematical Comparison Operators Sign
1 Equal to Operator =
2 Not equal to Operators <>,!=,^=
3 Less than Operator <
4 Greater than Operator >
5 Less than equal to Operators <=
6 Greater than equal Operators >=
Sr. Other Comparison Operators Description
1 Is [Not] null Null isn’t any value.
2 [Not] Between For Range.
3 [Not] In For any two or three
values.
4 [Not] like To search a pattern with
Meta character.
Sr. Logical Operators Description
1 And Operator combines two
conditions together.
2 Or Search only one
condition b/w two.
Equal To Operator-
It is used to filter the specific data record from a table based on equal to
operator condition.
Select * from table_name
where column_name= ‘SPECIFIED_CONDITION’;
Less than, Greater than, Less than equal to, Greater than equal to Operator-
It is used to filter the specific data record from a table based on less than and
greater than operator condition.
Mainly these operators are used for numbers and dates, these operators are
not used with a string, but can also be used with a string in certain
circumstances.
This situation may be as follows;
Select * from table_name
where column_name < ‘G’;

Not Equal to operator-

It is used to filter the specific data record from a table based on not equal to
operator condition.
Select * from table_name
where column_name <> ‘SPECIFIED_CONDITION’;
Operators can be used in different situations as like
1-Use of operator to filter the data records in a particular column-
Select column_name from table_name
where column_name > ‘SPECIFIED_CONDITION’;

2-Use of operator to filter the mathematical expression data records in a

particular column
Select (column_name*column_name/100) from table_name
where column_name <= ‘SPECIFIED_CONDITION’;

Note: Alias’s data is not filtered in Where clause. To filter any data, you need to
enter the same condition that you entered while inserting the data in the
table.
For Example, Character, Data Type and Length should be the same as you used
while creating the table & inserting the data.
Is [not] null- It is used to filter the specific data record from a table based on
null condition. Null is not a value so it can’t be used to compare against a value
hence it can’t be used with any operator.
So, we use ‘Is null or not null’ to filter it.
Select column_name from table_name
where column_name is not null;
Select column_name from table_name
where column_name is null;
[Not] Between operator- It is used to filter the specific data record from a
table based on Range condition.
Select column_name from table_name
where column_name between first_interval and second_interval;
For example, we have to filter the income between 10,000 to 15,000.
Select income from employees
where income between 10000 and 15000;

[Not] in operator- It is used to filter the specific data record from a table based
on two or three data record condition.
Select column_name from table_name
where column_name in (‘first_column’ ,‘second_column’);
For example, we have to filter the two countries name from customer name
table.
Select name, country from customers
where country in (‘India’, ‘Pakistan’);

[Not] like operator- It is used to filter the specific data record from a table
based on pattern with meta characters.
Select column_name from table_name
where column_name like ‘%_a’;
For example, we have to filter the pattern of “Himanshu’’ from customer name
table.
Select name, country from customers
where name like ‘H__%’;
Note: Here % is used to any number of characters while underscore (_) is used
to represent exactly one character in the indicated position.
Logical Operators- It is used to filter the specific data record from a table
based on two or more conditions.
There are two logical operators that are used to filter the specific data record
from a table based on two or more conditions.
And Operators- It is used to filter the specific data record from a table to
combine the two conditions together.
select column_name from table_name
where condition1 and condition2;
For example, List the title and publish date of any computer book published in
2005.
select TITLE, PUBDATE, CATEGORY from books
where CATEGORY='COMPUTER' and pubdate like '%_%_05';

Or Operators- It is used to filter the specific data record from a table to search
the only one conditions between two or more conditions.
select column_name from table_name
where condition1 or condition2;
For Example, list the customers live in Georgia or New Jersey.
select customer#, lastname, state from customers
where state='GA' or State='NJ'

Copying The Table

Mainly, for this we use subquery.
Subquery- When two statements are used together and one statement
depend on another statement, then it is called subquery. In short using a query
within another query is called subquery.
There are two query named inner query and outer query. First the inner query
is run after that outer query gives its result based on that.
Copying a table with data records-
Create table table_name as select * from table_name;
If a table has column level constraints, it is copied, for example Not null and
Default constraints, but if a table has table level constraints such has primary
key, foreign key and unique constraints, it is not copied.
In fact, to copy a table, the output of the select statement is only copied. For
example, if you select only two columns of a table, then only those columns
will be copied as its output.
Create table table_name as select column1,column2 from
table_name;

Copying a table without data records-

If you wanted to copying a table without data records so you have to give a
false condition with where clause.
create table table_name as select * from table_name where
1=2;

DML (Data Manipulation Language)

DML stands for Data Manipulation Language which is used to manage data
records stored in database object. Under which some statements come like
insert, update, delete, merge etc. it works on selected data records of a table
based on where clause. It is manual so we need to perform explicit commit or
rollback statement on it to make the changes permanent or revert back. It is
slower in comparison to DDL statement because it is used undo segment.
Insert Statement- After creating a database object, the insert statement is
used to insert the data into it.
Insert into table_name values (number data-type, ‘varchar2
datatype’, ‘date datatype’);

1)To Insert Data according to default value/Virtual column-

Virtual Column- Whose value is automatically computed using other columns
values. This column does not require us to assign values because its value
depends on the value of the other column.
Insert into table_name (coulmn1, Coulmn2, Coulmn3) values (number data-
type, ‘varchar2 datatype’, ‘date datatype’);
 While defining the value of any table, null is not to be written in single quote
for varchar2 data-type.
For Ex- If data type is varchar2 then the null value of data records will not be
assigned in single quote.
Insert into table_name (coulmn1, Coulmn2, Coulmn3) values (number data-
type, varchar2 data-type null, ‘date datatype’);

2) To insert data with apostrophe-

insert into table values (' varchar2
datatype’,'Apastrophe''s',number datatype);

3)To insert data using a subquery-

To insert data into a table by subquery, the number of columns of the table
selected by the inner query and the type of data must be the same as the
number of columns and type of data of the table selected by the outer query.
insert into table_name select column1,column2 from tablename;

Update Statement- It is used to update the wrong data by mistake inserted

into an object.
1)To update wrong data records-
Update table_name set column_name=‘Exact Data’
Where unique condition;

2)To update multiple column data records- If you want to update multiple
column data records then you have to used comma as a separator between
them.
Update table_name set column_name=‘Exact Data’,
column_name=‘Exact Data’
Where unique condition;
Note: if there are any constraints on any column of a table then the data
records of that column cannot be updated.
Why we use Where Clause with Update statement?
If we don’t use where clause with update statement then it will update entire
column of table with same data records which is set during the query.
To avoid this type of problem, always run the select statement before the
update statement and then copy it and use it with update statement by paste.
3)To update column data records based on mathematical expression-
For example, we have an employee’s name table in which we have to increase
their salary column data records based on their increment.
Update employees set salary=salary+increment;
update employees set salary=salary+200
where salary > 15000;

Delete Statement- It is used to delete one or more data records based on

where clause.
Delete from table_name where unique condition;
Delete table_name where unique condition;
Similarly Update Statement, if we don’t use where clause with delete
statement then it will delete entire column data records but remain database
structure.
Delete from table_name ;
To avoid this type of problem, always run the select statement before the
delete statement and then copy it and use it with delete statement by paste.
So here a question arises that what is the difference between Truncate &
Delete statement while both remain the database structure by deleting the
data records.
Truncate table table_name;
Vs.
Delete from table_name ;
Truncate is a data definition language so it defines the database structure. It
can be deleting all the data records in one go, like if the data records are more
than 100 or 1000 in our table then truncate will be delete all the data records
at once, there is no option to delete the selected data records here.
Whereas delete statement has option to delete the selected data records here.
 (TCL)Transactional Control Language
it is mainly work on DML statement to make the changes permanent, some
statements come under like COMMIT, ROLLBACK, SAVEPOINT etcetera.
Commit Statement: It is used to save executed different DML statement like
insert, update, delete etc.
commit; or shortcut key (fn + f11)

Rollback Statement: It is used to undo the wrong statements. If you have run
the commit statement before this it will not rollback.
rollback; or shortcut key (fn + f12)
Whenever you start executing a DML statement on a database object, a
transaction is started which ends with the commit or rollback statement.
Transaction: Set of Multiple DML Statement.
If you don’t commit then this transaction will not be saved i.e., changes made
by you like insert, update, delete etc. will not be saved permanently. If you are
doing this work on a server then these changes will be limited to you only, it
will not be saved for any other user until you commit or rollback your
transaction, once you commit or rollback it will be saved for other users.
There are two types of Commit Statement.
1) Explicit commit statement: commit that is done by manually is called
explicit commit.
2) Implicit Commit Statement: Commit that happen automatically are
called implicit commits. Whenever you run efficient or wrong DDL or
DCL statement during transaction it gets automatically committed.
Note: DDL & DCL is preceded by a commit and followed by another
commit.
Commit;
DDL or DCL Statement
Commit;
For example,
select * from test;
update test set name='Himansh',Surname='Gaur'
where phno='55456';
alter table abc add xyz varchar(12); (wrong ddl statement)
Save Point Statement: It is used to partial rollback.
For example, we have a table named test,
insert into test values ('Abhishek','Parjapati',785645);

update test set surname='Singh' where name like 'A%';

Savepoint a;
insert into test (name,surname,phno) values ('Vikas','Kumar',5645);
delete from test where name like '%\%' escape '\';
update test set phno=55555 where phno like '55%';
Savepoint b;
delete from test where phno like '56%';
Rollback a;
Here the partial rollback transaction will take place below the save point a. if
we do a partial rollback from the save point b then all the transactions below
the save point b will rollback.
Deadlock Issue: It refers to a situation in which two sides cannot reach on a
decision that what to do?
Like, when two users on the same server work on the same data records of the
same database object, then in that case the problem of deadlock arises, so the
first user who ran the query on the data records is required to commit or
rollback first.
 Sequence
It is an independent database object in itself, which is used to generate unique
numbers. It works like a primary key constraint under which duplicate and null
values are not allowed.
Mainly it is placed on unique data records column like employee id data
records, serial number data records, Roll number records etc.
There are following conditions to create a sequence-
1)To create a sequence by unique default value:
The value of sequence is inserted in any table with a fixed value and with a
fixed increment which is called default value.
create sequence sequence_name;

2)To create a sequence with a unique fixed start and increment:

If you want to start the sequence from the middle, then for that you can give
some conditions like this.
create sequence sequence_name;
start with number increment by number;
for example; If you want the sequence number in the table named ATMS to
start from 10 and increase by 1, then for that you would give the following
CREATE statement with condition while creating the sequence.
create sequence ATMST start with 10 increment by 1;
Using of Sequence-
1)To insert sequence with unique value in table:
The sequence object is used when inserting data into a table. Its main use is to
avoid duplicate data while inserting data into a table.
Insert into table_name values ( sequence_name.nextval, ‘aspl’ );
Note: Here nextval refers to next value.
For example, if you want to insert serial number values in table named ATMS
to increase by 1, for that you will give following insert statement while
inserting sequence.
insert into ATMS values ([Link],'Prakhar Singh’, ‘Raj Singh','BSR');
Note: if you have used a sequence on one table and use the same sequence
again on another table, then this sequence will start from the next sequence
number in the table itself because the sequence itself is independent object.
Insert into Table 1 (seq. nextval); Insert into Table 2 (seq. nextval);
1 6
2 7
3 8
4 9
5 10

2)To insert sequence with duplicate value in table:

if you want to insert a duplicate value into a sequence, then you can use
currval per pseudo for that like this
Insert into table_name values ( sequence_name.currval, ‘aspl’ );
Note: Here currval refers to current value.
It works like this 1,2,2,2,3,4,4,5,6,7,8
3)To Create a sequence with the unique ending:
You can also give maximum value while creating sequence which requires
query as follows.
create sequence sequence_name;
start with number increment by number
maxvalue number;
After reaching the maximum value the sequence ends, you can no longer use
this sequence on any other table, so you have to drop this sequence.
Error: sequence SEQUENCENAME exceeds MAXVALUE and cannot be
instantiated.
for example; If you want the sequence number in the table named ATMS to
start from 10, increase by 2 and the maximum inserted sequence number to
be 16 then for that you will give following CREATE statement with condition
while creating sequence.
create sequence ATMST start with 10 increment by 2 maxvalue 16;
4)To create sequence cycle:
If you want to run the sequence as a cycle, you need to follow the following
query.
create sequence sequence_name;
start with number increment by number
maxvalue number
cycle nocache;
Cycle sequence is mainly used on a column of a table that does not have any
primary key constraint.
It works like this 1,2,3,4,5,1,2,3,4,5,1,2,3,4,5
create sequence sequence_name;
start with number increment by number
maxvalue number
cycle ;
Cache: means that it already generates the 20 values of the next sequence in
order to maintain the quickness of the select statement. It is generally used
only with cycle to maintain next 20 upcoming numbers.
5)To create sequence with range:
While creating a sequence with its maximum value and minimum value we can
determine the limit of it. For that you need to follow the following query.
create sequence sequence_name;
start with number increment by number
minvalue number
maxvalue number
cycle nocache;
Here the sequence will start with a specified number and the value of the
sequence will increase with a determined number, also here the limit of that
sequence will be determined with maximum and minimum value, whole
statement will run in a cycle sequence.
Sequence data dictionary: Any database object that oracle itself manages is
called data dictionary.
Select * from user_sequences;
For example, if we create the following sequence
create sequence ATMST maxvalue 5 minvalue 2 cycle nocache;
select * from user_sequences;
the sequence data dictionary would look like this
SEQUENCE_NME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZ LAST_NUMBER
E
ATMST 2 5 1 Y N 0 6

Cache: means that it already generates the 20 values of the next sequence in
order to maintain the quickness of the select statement. It is generally used
only with cycle to maintain next 20 upcoming numbers.
create sequence ATMST maxvalue 21 minvalue 1 cycle; select
* from user_sequences;
SEQUENCE_NME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZ LAST_NUMBER
E
ATMST 1 21 1 Y N 20 1

How to drop sequence: The way we drop the table. Similarly, we can also drop
the sequence. Sequence cannot be retrieved after it is dropped but after
dropping the table, we can flashback from it.
Drop sequence sequence_name;

How to alter sequence: if you want to change the cycle, increment value,
maximum and minimum value of the sequence then you can use alter
statement for this.
Alter sequence sequence_name minvalue 2;

Interview Questions:
What are the per pseudo columns of sequence objects?
-nextval and currval
 Index
An index is a pointer to the data records in a table, it is used to retrieve the
data records faster, it means index is used in select statement to fetch the data
records faster, it is created on a particular column of a table, basically we
create index on that column of a table on which SELECT statement is used
most frequently with WHERE clause, an index in a database is very similar to an
index in the back of the book.
On which column of table should we create an Index?
→ On which SELECT statement is used most frequently with WHERE clause.
→ In which the number of null values is less.
How to create an index?
There are some types of indexes like Btree index, Bitmap index, function-based
index.
1)Btree Index: we create B Tree index on high selectivity of data mean where
data records are mostly unique. It creates leaves.
Create index index_name on table_name (column_name);

2)Bitmap Index: we create Bitmap Index on low selectivity of data mean where
data records are mostly duplicate like gender, age, region etc. it creates binary
values (1, 0).
Create bitmap index index_name on table_name (column_name);

3)Function-Based Index: we create function-based index on functions that is

not created on a particular column of a table.
Create index index_name on table_name (functions);
Functions like virtual column.
Srn Employee_name Salary Commission_pct
Total salary = (salary*commission_pct/100) + salary
Create index index_name on table_name ((salary*commission_pct/100) +
salary);
4)Unique Index: whenever we put a primary or unique key constraint on a
table, it creates a unique index.
This way a unique index can be applied when a table does not have primary
key & unique constraints.
Create unique index index_name on table_name (column_name);

INDEX Data Dictionary-

Any database object that oracle itself manages is called data dictionary.
Select * from user_indexes;
Select * from user_ind_columns;

Interview Question-
What happens to the constraints and indexes when you drop a table?
→ Whenever we drop a table, the constraints and indexes on it drop along
with it.
Dropping an Index: You can drop any index expect the Unique index because
unique index is related to a column of a table on which Primary key or unique
constraint is applied. whenever we put a primary or unique key constraint on a
table, it creates a unique index.
 Synonym
It is used to create the synonym for a table & it does not contain any data,
actually it is fetched the data from the original table. We can create multiple
synonyms of a table.
Whenever you insert data into a synonym table it inserts the same data into
the original table as well because it’s just another name for the original table.
create synonym synonym_name for table_name;
There are two types of synonyms.
1)Private Synonym: By default, private synonym creates itself.
2)Public Synonym: It is created for all the users present in the database, it is
made by the following query.
Create Public synonym synonym_name for table_name;

The Purpose of Creating a Table:

The main purpose of making synonym is related to security. Which has the
following security purpose.
1) Dropping, Altering & renaming the synonym table has no effect on the
original table, this means that running any DDL statement on the synonym
table has no effect on the original table.
2) Inserting, Updating and deleting the synonym table has same effect on the
original table, this means that running any DML statement on the synonym
table has effect on the original table.
Synonym Data Dictionary: Any database object that oracle itself manages is
called data dictionary.
Select * from user_synonyms;
 Join
Join is a query that is used to combine the data records from two or more
tables or views. There are some types of joins like Equi-Join, Non-Equi Join,
Left-Outer Join, Right-Outer Join, Self-Join, Cross-Join etc.
1)Cross Join: When we join two tables & don’t use the where clause in that
case cross-join will be formed. Cross join can be defined as Cartesian product
of the two tables included in the join.
There are two ways to write a join query.
1) Traditional Method Select column1, Column2 from table1, table2;
2) Join Method Select column1, column2 from table1 cross join table2

Suppose you have 3 data records in table named employee and also 3 data
records in another table named department, if we join these two tables
together without join condition then the oracle returns their 9 data records as
a cartesian product or cross join table.
Select name, dept from employee, department;
Emp Dept.
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

Note: Whenever the names of any two columns in any two tables are same,
then while writing those columns in the query '[Link]' has
to indicate which column belongs to which table. For this we can also create
table alias. We can write this query as follows.
Select [Link], [Link] from table1 a , table2 b;
Select [Link], [Link] from table1 a cross join table2 b;
2)Equi Join: It is used to retrieve all the data records from two or multiple
tables, where we use join condition with Equal Operators. It is also called inner
join or natural join.
In Equi Join, the data of any two tables must be common though the column
names may or may not be common. Thus, we can say that the data of any two
columns of two tables must be same in equi join.
1) Traditional Method
select column1, column2 from table1 alias1, table2 alias2
where alias1.common_data_column=alias2.common_data_column;

2) Join Method
select column1, column2 from table1 alias1 join table2 alias2
on alias1.common_data_column=alias2.common_data_column;
If all the data in both the table is common & sure i.e., common column, then
you can use the ‘using clause’ in place of ‘on clause’, under which we will not
use equal operator.
select column1, column2 from table1 join table2
using (common_data_column);

EmpID EmpID
A538 A538
A539 A539

select name,department from employee, department

where empID= empID;
select name,department from employee join department
on empID= empID;
select name,department from employee join department
using (empID);
Using Equi-Join to establish a relationship between multiple tables:
When we establish a relation between any two tables through different tables
then it is called relational equi-join.
Traditional Method:
Select column1, column2 from table1 alias1, table2 alias2, table3 alias3
Where alias1.common_column=alias2.common_column
and alias2.common_column= alias3.common_column;

Join Method:
Select column1, column2
from table1 alias1 Join table2 alias2 on
(alias1.common_column=alias2.common_column)
join table3 alias3 on (alias2.common_column= alias3.common_column);
Select column1, column2
from table1 join table2 using (common_column)
join table3 using (common_column);

On the basis of above tables, we have to extract the names of the customers
who have purchased some category title book of the different writers.
Traditional Method:
select firstname,lastname,title,lname,fname,
firstname || lastname || ' is ordered ' || title || ' which is written by ' ||
fname || lname as quote
from customers c,orders o,orderitems oi,books b,bookauthor ba,author a
where [Link]#=[Link]#
and [Link]#=[Link]#
and [Link]=[Link]
and [Link]=[Link] and [Link]=[Link];
Join Method:
select firstname,lastname,title,fname,lname,
firstname || lastname || ' is ordered '
|| title || ' which is written by ' || fname || lname as quote
from customers c join orders o on ([Link]#=[Link]#)
join orderitems oi on ([Link]#=[Link]#)
join books b on ([Link]=[Link])
join bookauthor ba on ([Link]=[Link])
join author a on ([Link]=[Link]);

select firstname,lastname,category,title,fname,lname,
firstname || lastname || ' is ordered ' || category ||
' category book named ' || title || ' which is written by '
|| fname || lname as quote
from customers join orders using (customer#)
join orderitems using (order#)
join books using (isbn)
join bookauthor using (isbn)
join author using (authorid)
where Category='COMPUTER';
Interview Question:
If you are joining 5 tables using equi-join then how many conditions will you
have to give in that?
→(T-1)=C so 5-1=4 conditions
Non-Equi Join: It is used to retrieve all data records from two or more tables
where we do not use join condition with equal operator but it can be any other
operator instead of equal operator condition.
1) Traditional Method
select column1, column2 from table1 , table2
where column3<> column4;
2)Join Method
select column1, column2 from table1 alias1 join table2 alias2
on column3<>column4;

Empid Empid
A540 A537
A540 A535
A540 A534
A536 A537
A536 A535
A536 A534
null A534

Example: Suppose we have two tables named Books table and Promotions
table, if we want to extract data of gifts between maximum and minimum
retail price, while maximum and minimum retail price column come under
promotion table and retail price column comes under Books table. Then we
can extract the data by Non Equi Join Query like this.
select title, retail, gift
from books join promotion
on retail between minretail and maxretail;

Self-Join: When a table is joined by itself then it’s called Self-Join that’s mean
when we join a table with the same table it is called self-join. In self-join we are
required to give alias to the table, because the same column of the same table
can also be used under this.
 1) Traditional Method
select [Link], [Link] from table
alias1 table alias2
where [Link]=[Link];
2)Join Method
select [Link], [Link] from table alias1 join
table alias2 on [Link]=[Link];

EmpId (A) EmpId (B)

A530 A530
A531 A531
A532 A532
A533 A533
A534 A534
A535 A535
A536 A536
A537 A537
A538 A538

Suppose we have a table named Customers, if we want to extract the data of

the customers who have referred the customer for purchase while customer id
and referred id columns come under same table, we can extract the data by
self-joining query like this.
select [Link] Customer,[Link] referred from customers a join
customers b on [Link]#=[Link];
Outer Join: It is used to extract the data records from one or more table by
creating join condition. There are some types of outer Join like Left Outer Join,
Right Outer Join, Full outer join.
Left-Outer Join: It is used to retrieve all the data records from left hand side
table & matching data records from right hand side table.
Right-Outer Join: It is used to retrieve all the data records from right hand side
table & matching data records from left hand side table.
Full-Outer Join: It is used to retrieve all the data records from two or more
tables matching data as well as non-matching data.
 1) Traditional Method
select column1, column2 from table1 alias1, table2 alias2
where alias1.common_data_column=alias2.common_data_column(+);

2) Join Method
select column1, column2 from table1 alias1 left outer join table2 alias2
on alias1.common_data_column=alias2.common_data_column;
select column1, column2 from table1 alias1 left outer join table2 alias2
using (common_data_column);

EmpID EmpID
A534 Null
A536 Null
A538 A538
A540 A540

EmpID EmpID
Null A532
Null A533
A538 A538
A540 A540
 EmpID EmpID
A532 Null
A533 Null
A538 A538
A540 A540
Null A534
Null A536

Let suppose we have two table named customers and orders, if we want to
extract the data of those customers who ordered some items, then we can
extract the data by left outer join or right outer join.
select c. customer#, Firstname,lastname,[Link]#
from customers c,orders o
where [Link]#(+)=[Link]#;

Set Operators
Set Operators are used to join the result of two or more select statements.
There are some types of set-operators like Union, Union-All, Minus & Intersect
etc.
Union: It will give unique shorted data records from the result set of two or
more select statements.
select column_name from table_name1
union
select column_name from table_name2;
A = (1,2,3,4) B= (1,2,5,6)
(A Union B) = (1,2,3,4,5,6)
Union All: It will give all the data records from the result set of two or more
select statements.
select column_name from table_name1
union all
select column_name from table_name2;
A = (1,2,3,4) B = (1,2,5,6)
(A Union All B) = (1,2,3,4,1,2,5,6)
Intersect: It will give common shorted data records from the result set of two
or more select statement.
select column_name from table_name1
intersect
select column_name from table_name2;
A = (1,2,3,4) B = (1,2,5,6)
(A Intersect B) = (1,2)
For Example, Let suppose we have two tables named Customers and Orders, if
we want to extract the data of customers who have placed an order for an
item, we can use the Intersect set operators for that.
Minus: It will give the data records from first select statement which is not
present in second select statement.
select column_name from table_name1
minus
select column_name from table_name2;
A = (1,2,3,4) B = (1,2,5,6)
(A Minus B) = (3,4)
For Example, Let suppose we have two tables named Customers and Orders, if
we want to extract the data of customers who have not placed an order for an
item, we can use the minus set operators for that.
Example: If we have two tables named authors and books, if we want to
extract the data of authors who have written books of both categories of
children and family life, then we can use union set operators like this.
select fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category='FAMILY LIFE'
Union
select fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category='CHILDREN';
You can extract the data by another way like this,
select distinct fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category in ('FAMILY LIFE','CHILDREN');

Using set operators with more than two select statements:

select column_name from table_name1
union
select column_name from table_name2
union
select column_name from table_name3
;

For Example,
select fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category='FAMILY LIFE'
Union
select fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category='CHILDREN'
Union
select fname || ' ' || Lname as Writer,title,Category
from books join bookauthor using(isbn)join author using (authorid)
where category='COMPUTER';
 Aggregate Functions
Aggregate is a function where the values of multiple data records are grouped
together to form a single summary value. It is also called group function.
There are some Aggregate functions like SUM, COUNT, MAX, MIN, AVG et
cetera.
Select sum(column_name) from table_name;
Select Max(column_name) from table_name;
Select Min(column_name) from table_name;
Select Avg(column_name) from table_name;
Select count(*) from table_name;
For example, suppose we have a table named Student under which there are
some columns like roll_no, name, subject, marks. If we want to extract the
total marks of the student whose roll number is 1, then aggregate function will
be used like this.
select sum(marks) from student where roll_no 1;
Note: Aggregate Function always skip the null values.
45+55+50=150 SUM Marks
45+55+null+50/3=50 AVG 45
55
45, 55, Null, 50=4 Entry Count null
Max value=55 Min Value=45 50

For example, we have a table named employees under which there are many
columns, HIRE_DATE is one of them column, if we want to extract the data of
employees who joined earlier in the organization, for that we will use
aggregate function like this.
Select min(hire_date) from employees;
Interview Question:
→ How to check how many data records are included in a table?
Select count(*) from table_name;
To Filter The Specific Data Records:
Select simple_column, sum(column_name) from table_name
where unique_condition group by simple_column;
→ GROUP BY clause is always used on the column which contains duplicate
data.
Student_id Marks
A9554 65 Student_id Total
A9554 76 A9554 206
A9553 98
A9554 65
Select student_id, sum(marks) as total
from student
where student_id= ‘A9554’
group by student_id;
having clause: Like where clause, having clause is also used to filtered the data
records but from the groups based on specified condition. It is mainly used
with select statement, it is used after the group by clause & it can’t be used
without group by clause.
For example, if we have three tables named books, authourid and author
under which there are some columns like title, authorid, fname, lname then we
want to extract the data of those author who has written n numbers books, for
that having clause will be used like this.
select fname || ' ' || lname as Author, count (*) as "Written BooK"
from books join bookauthor using (isbn)
join author using (authorid)
group by fname,lname;
select * from books;
select category,max(retail-cost) as "Profit"
from books group by category
having max(retail-cost) = (select max(retail-cost) from books);
Interview Questions:
→How to extract duplicate data from a table?
Select column_name, count (*)
from table_name group by column_name (on which we want to see duplicity)
having count (*) >1;
→how to work a query?from→where→group by→having→select→order by
 Case Conversion Functions
Lower Function: It is used to convert all inserted data records characters from
uppercase to lowercase.
Select lower (column_name) from table_name;

Upper Function: It is used to convert all inserted data records characters from
lowercase to uppercase.
Select upper (column_name) from table_name;

Initcap Function: It is used to convert the initial character of all inserted data
records to uppercase.
Select initcap (column_name) from table_name;

Note→ Case conversion functions only apply to the varchar data type.
Case conversion Functions with multiple column: case conversion functions
can be used with multiple functions with some restriction.
→Can’t use with multiple arguments: The case conversion function cannot be
used with integrating multiple columns.
Select initcap (column_name1, column_name2) from table_name;
It will give the error like this ORA-00909: invalid number of arguments.
You have to give separate case conversion for each such column.
Select initcap (column_name1) ,Upper (column_name2) from table_name;
→Can use with concatenation: The case conversion function can be used with
concatenating multiple columns.
Select initcap (column_name1 || ‘ ‘ || column_name2) as alias from
table_name;
Application of case conversion Functions: Mainly we use case conversion
functions while extracting the data records from a table via ‘where clause’,
when we extract data records these are used to deny uppercase and lowercase
characters.
select column_name from table_name
where lower(column_name) = ‘lowercharacter’
Note: It is necessary to write the query in the same case the function we are
using to extract the data.
select column_name from table_name
where upper(column_name) = ‘uppercharacter’

Case Manipulation Function

The Data type does not matter in the case manipulation function, it can be
applied to any data type.
There are some type of case manipulation function like Substr i.e. substring,
Instr i.e., instring, length etc.

Substr: It is used to extract a particular cut part of the data records inserted.
Select substr(column_name,cutting_postion,Number_of_arguments);
For Example, select substr(first_name,1,3) from employees;
We can practice on dummy table also; dummy table is one which does not
have columns it is managed by oracle database.
Positive Cutting: It is used to extract a particular cut off part of the data record
from the beginning.
→By Passing Three Arguments:
select substr('Himanshu Gaud',1,7) from dual;
Output:Himansh
→By Passing Two Arguments: Substrs work like this when we don't pass a
third number argument.
select substr('Himanshu Gaud',7) from dual;
Output: hu gaud
Note: It is mandatory to pass two arguments during substr.

Negative cutting: It is used to extract a particular cut off part of the data
record from the end.
→By Passing Two Arguments
select substr('Himanshu Gaud',-4) from dual;
Output: hu gaud
→By Passing Three Arguments
select substr('Himanshu Gaud',-4,3) from dual;
Output: hu gau
Note: Cutting is always in forward form.
Note: During negative cutting, the value of third argument i.e., number of
characters should always be smaller than the second argument i.e., position of
character.
Instr: it is used to determine the position of a character.
→By Passing Two Arguments
Select instr(column_name, ‘Character’) from table_name;
For Example,select instr('Himanshu vashu Gaud','a') from dual;
Output:4
→By Passing Three Arguments
Select instr(column_name, ‘Character’, cutting_postion)
from table_name;
For Example,select instr('Himanshu vashu Gaud','a',5) from dual;
Output:11
→By Passing Four Arguments
Select instr(column_name, ‘Character’, cutting_postion,
Occurrence_of_character) from table_name;
For Example,select instr('Himanshu vashu Gaud','a',5,3) from dual;
Output:0
Because after the fifth character a does not appear for the third time.
So it can be like this, select instr('Himanshu vashu Gaud','a',5,2) from dual;
Output:17
→By Passing Negative Position Argument:
Select instr(column_name, ‘Character’, -cutting_postion,
Occurrence_of_character) from table_name;
For Example, select instr('Himanshu vashu Gaud','a',-2,3) from dual;
Output: 4

nvl function: it is used to remove null values from he

To_char Function: It is used to convert a number or date into a character or a
string.
Select to_char(column_name, ‘require string’) from table_name;
On dummy table we can easily understand it like this.
To convert a date:
select to_char(sysdate, ' Mon-Dy-dd-yyyy, hh24:mi:ss pm') from dual;
output: Feb-Tue-08-2022, 06:03:45 AM
To convert a number:
select to_char(234.45, ' $999.9999') from dual;
Output: $234.4500
For Example, we have a table named employees, if we want to extract data of
date of hire column as string then we use to_char function like this.
select hire_date, to_char(hire_date,'Mon,day dd-yyyy hh:mi:ss PM') as
Hire_Date from employees;
select salary,to_char(salary,'$99999999.99') from employees;
Decode Function: It is used to calculate or compare values like if statement
used in shell scripting.
Select
decode(column_name,data_records,change_records_value,remain_data_reco
rds_value ) from table_name;
For example, we have a table named Gender, if we want to replace the data
records from M to Male & F to Female, then we use decode function like this.
select decode(gender,'F','Female','M','Male','Transgender') from gender;
Note: If we don't give value of remaining data record then remaining data
record becomes null.
We can easily understand this function via this query-
Select decode(column_name,If→then→if→then→else) from table_name;
Here, in place of 'then' we can use string or number as well as column name.
select salary,decode(salary,24000,first_name,17000,email,salary) from
employees;
Case Function: Like the decode function, it is also used to calculate or compare
values such as if statements used in shell scripting.
Select case column_name when data_records then change_data_records
when data_records then change_data_records
else remain_data_records end
from table_name;
Select case when column_name=data_records then change_data_records
when column_name= data_records then change_data_records
else remain_data_records end
from table_name;

Difference between decode function & case function

In decode only equal condition can be compared whereas in case all conditions
like greater than, less than, not equal to etc can be compared.
select salary, case when salary > 20000 then 'Owner'
when salary < 15000 then 'Employees'
else 'Worker' end as Designation from employees;
 Subquery
When two statements are used together and one statement depend on
another statement, then it is called subquery. In short using a query within
another query is called subquery.
There are two query named inner query and outer query. First the inner query
is run after that outer query gives its result based on that.
outer query with column_name inner query with specified
condition
Select column_name from table_name where condition (Select
column_name from table_name);
For Example, we have a table named Books, if we want to find the cost of
books which are more than max price in title ‘DATABASE IMPLEMENTATION'.
select title,category,cost from books where cost > (select cost from books
where title = 'DATABASE IMPLEMENTATION');
There are some types of subqueries.
1) Single Row Subquery
2) Multiple Row Subquery
3) Co-related Subquery
4) Nested Subquery
5) Scalar Subquery or Select Clause Subquery
6) From Clause Subquery
Single Row Subquery
Under which inner query returns a single row or data record and the result of
outer query depends on that data records.
It is mainly used with where clause, having clause & select statement.
Single row subquery with Where Clause :
outer query with column_name inner query with where clause specified condition ;
select title,category,cost from books where cost > (select cost from books
where title = 'DATABASE IMPLEMENTATION');
For example, if we have a table named employees, if we want to extract the
data record of salary and name of the employee whose salary is maximum
among all the employees.
Here we can't extract employees name because we can't take group name, so
for this type of situation we can manage like this.
Firstly, break the query according to question.
→Inner Query:
Select max(salary) from employees;
(It will give single data records)
→Outer Query:
select first_name || ' ' || last_name as "Employee Name",salary
from employees;
(It will give the employees Name & their salaries.)
→Subquery:
select first_name || ' ' || last_name as "Employee Name",salary
from employees where salary=(Select max(salary) from employees);
(It will give the result according to question.)
Single row subquery with having Clause :
outer query with column_name inner query with having clause specified condition ;
For example, if we have a table named books, if we want to extract the data
record of category and average profit of the books whose average profit is less
than from the average profit of computer category.
Firstly, break the query according to question.
→Inner Query:
select avg(retail-cost) as Avg_Profit from books where category =
'COMPUTER';
(It will give average profit of computer category book.)
→Outer Query:
select category, avg(retail-cost) as Avg_Profit from books group by category;
(It will give category & average profit of all category books.)
→subquery:
select category, avg(retail-cost) as Avg_Profit from books group by category
having avg(retail-cost )<(select avg(retail-cost) as Avg_Profit from books
where category = 'COMPUTER');
(It will give the result according to question)
Single row subquery with select Clause:
outer query with column_name ,inner query with select clause from table_name;
For example, if we have a table named employees, if we want to extract the
data record of salary, difference from maximum salary and name of the
employees.
Firstly, break the query according to question.
→Inner Query:
select max(salary) from employees;
(It will give maximum salary of an employee)
→Outer Query:
Select first_name || ' ' || last_name as "Employee Name",salary from
employees;
(It will give Employees’ name & their salary)
→Subquery:
Select first_name || ' ' || last_name as "Employee Name",salary,
((select max(salary) from employees) – salary) as Difference from employees;
 Multiple Row Subquery
Under which inner query returns a multiple row or data record and the result
of outer query depends on that data records.
For Example, we have a table named Books, if we want to extract retail price
data records of all categories along with book name then we can use multiple
row subqueries like this.
Firstly, break the query according to question.
→Inner Query:
Select category, max(retail) from books group by category;
(This will result in all category of books having maximum retail price.)
→Outer Query:
Select title, category, retail from books group by category;
(This will result in all title of books with all category having retail price.)
→Subquery:
Select title, category, retail from books Where (category, retail) in (Select
category, max(retail) from books group by category);
(It will give result according to Question.)
Note: The ‘In’operator is always used in multiple queries.
Compare the result of the inner query to the whole table:
Less than & Greater than all (</>all): It gives the all largest number to the
largest or all smallest value to the smallest value.
For example, we have a table named books, if we want to extract data records
of retail price of books whose retail price is more than maximum retail price of
all books of computer category.
→Inner Query:
select retail from books where category='COMPUTER';
(This will result in computer category of books having retail price.)
→Outer Query:
Select title, category, retail from books;
(This query will give all books title, category and retail price.)
→Subquery:
Select title, category, retail from books
where retail >all (Select retail from books where category='COMPUTER');
(It will give result according to Question.)
Less than & Greater than any (</>any): It gives the all-smallest value to the
largest value or all largest value to the smallest value.
we have a table named books, if we want to extract data records of retail price
of books whose retail price is less than maximum retail price of all books of
computer category.
→Inner Query:
select retail from books where category='COMPUTER';
(This will result in computer category of books having retail price.)
→Outer Query:
Select title, category, retail from books;
(This query will give all books title, category and retail price.)
→Subquery:
Select title, category, retail from books
where retail <any (Select retail from books where category='COMPUTER');
(It will give result according to Question.)
>all It returns all the values of the outer query that are greater than the
largest value of the inner query.
For Ex: Inner Query Output- Max 75.95
Outer Query Output -Max 89.95
Subquery Output- 89.95 > 75.95
<all It returns all the values of the outer query that are less than the
smallest value of the inner query.
For Ex: Inner Query Output- Min 25
 Outer Query Output -Min 22
Subquery Output- 22 < 25
>any It returns all the values of the outer query that are greater than the
smallest value of the inner query.
For Ex: Inner Query Output- Min 25
Outer Query Output-89.95,75.95,59.95,55.95,54.5,39.95,31.95
Subquery Output- 89.95 > 25
<any It returns all the values of the outer query that are less than the
largest value of the inner query.
For Ex: Inner Query Output- Max 75.95
Outer Query Output-59.95,55.95,54.5,39.95,31.95,25.25,22,13.7
Subquery Output- 75.95 > 59.95

Outer Query Operator Inner Query

55.95
75.95 >all 89.95
25 <all 8.95, 19.95, 22
54.5

Outer Query Operator Inner Query

55.95
75.95 <any 8.95, 19.95, 22, 25, 28.75, 29.95, 30.95, 31.95,39.95,
54.5, 55.95,59.95
25 >any 28.75, 29.95, 30.95, 31.95, 39.95, 54.5, 55.95, 59.95,
75.95, 89.95
54.5

Equal To Any(=any): It works like a ‘IN’ operator.

select title,category,retail
from books where (retail,category) in
(select retail,category from books
where category='COMPUTER') ;
select title,category,retail
from books where (retail,category) =any
(select retail,category from books
where category='COMPUTER') ;
 Multiple Column Subquery
Under which inner query returns a multiple row or data records & column or
fields and the result of outer query depends on that data records & fields.
For Example, if we have a table named books, under which we want to extract
the data of the book whose retail price is highest among all their category of
books.
Firstly, break the query according to question.
→Inner Query:
select category, max(retail) from books group by category;
(It will give multiple data records)
→Outer Query:
select title, category, retail from books;
(It will give the book Name & their categories & retail.)
→Subquery:
select title, category, retail from books where (category,retail)
in (select category,max(retail) from books group by category);
(It will give the result according to question.)

Co-related Subquery
In which inner query is related with outer query, it is called correlated
subquery.
For Example, we have two tables named books & orderitems, if we want to
extract the data of
select quantity*paideach from orderitems
where isbn in (select isbn from books
where [Link]=[Link]);
select category, (select count (*) from books a where [Link]=[Link])
total from books b;

select first_name || ' ' || last_name as "Employee

Name",salary,department_id from employees a
where exists (select 1 from employees b where
a.department_id=b.department_id group by department_id
having avg([Link]) < [Link]) order by 2;
Nested Subquery
When a query is used inside the query and another query is also used inside it,
i.e., the query is used as a mesh, it is called nested subquery.
For Example, we have two tables named Customers and Orders, if we want to
extract the data of the names of customers who have ordered maximum
number of orders, we will use nested subquery like this.
→Firstly, break the query according to question.
→Select customer#, count (*) from orders group by customer#;
(It will give the customer id and their order number of orders.)
→Select max (count (*)) from orders group by customer#;
(It will give the maximum number of orders.)
→Select customer#, count (*) from orders group by customer#
having count (*) = (Select max (count (*)) from orders group by customer#);
(It will give the customer id and their maximum order number of orders.)
→ Select FIRSTNAME || ' ' || LASTNAME as "Customer Name",Customer#
from customers
where (customer#) in (select customer# from orders
group by customer#
having count (*) = (select max (count (*)) from orders group by customer#));
(It will give the result according to question.)
 Scalar Subquery or Select Clause Subquery
if we want to extract the data of the names of customers, Customer id &
Number of who have ordered maximum number of orders, we will use nested
subquery like this.
For this we will use nested subquery with select statement subquery (Scalar
Subquery) like this,
Select FIRSTNAME || ' ' || LASTNAME as "Customer Name",Customer#,
(select max (count (*)) from orders group by customer#) as No_Orders from
customers
where (customer#) in (select customer# from orders
group by customer#
having count (*) = (select max (count (*)) from orders group by customer#));
Note: In scalar subquery only single row subquery is always used.
Inline view or Form Clause Subquery
When we use subquery with 'from clause' then it is treated as table. This is also
called Inline View.
For Example, we have a table named Books, if we want to extract data record
of average retail of books along with title, we can use inline view subquery like
this.
→ select category, avg(retail) as avg_reatil from books group by category;
(It will give average retail price according to category.)
→ select title,avg_reatil from books b,
(Select category, avg(retail) avg_reatil from books group by category) bo
where ([Link]=[Link]);
(It will give result according to question.)
 Partido Column (Matching Colum)
Partido is Spanish word which is used to matching. Each table has a Partido
column that is used for matching, but it is disappear. There are two types of
Partido column.
1) Rownum: (It is variable which varies according to the situation.)
2) Rowid: (It is fixed or unique which never change.)
Rownum :
Select rownum, Salary from employees;
Select rownum, salary from employees where salary < 24000;
Here, rownum will be changed.
Rowid :
Select rowid, Salary from employees;
Select rowid, salary from employees where salary < 24000;
Here, rowid will be same.