SQL LANGUAGES lang-

 DDL – Data definition language

 DML – Data manipulation language
 TCL – Transaction control language
 DCL – Data control language

DDL-
DDL, or Data Definition Language, is a set of SQL commands used to define and
manage the structure of database objects

CREATE - Used to create new database objects such as tables, indexes, views, and sequences.

ALTER - Modifies the structure of existing database objects, such as adding, modifying, or dropping
columns in a table.

DROP - Permanently Delete database objects such as tables, indexes, or views from the database.

TRUNCATE: Removes all data from a table, but retains the table structure.

RENAME: Change the table name.

DML
DML stands for Data Manipulation Language in SQL. It's a set of SQL commands
used to manage and manipulate data within database tables.

INSERT - Adds new rows of data into a table.

UPDATE - Modifies existing data within a table.

DELETE- Delete the particular values in the table.

MERGE – INSERT and UPDATE

DCL
DCL stands for Data Control Language in SQL. It comprises commands used to
manage access permissions and control access to database objects.

GRANT: Provides specific privileges to database users or roles. These privileges can include SELECT,
INSERT, UPDATE, DELETE, and others, allowing users to perform certain actions on database objects.
REVOKE: Revokes previously granted privileges from users or roles. It removes specific access rights that
were previously granted using the GRANT command.

TCL -- Transaction Control Language

COMMIT: Finalizes and permanently applies changes made during the current transaction to the
database. Once committed, changes become visible to other transactions.

ROLLBACK: Undoes all changes made during the current transaction and restores the database to its
state before the transaction began.

SAVEPOINT: Establishes a named point within a transaction to which you can later rollback. It allows you
to create intermediate points for rollback within a transaction.

DROP & TRUNCATE & DELETE

DROP-

- DROP is a DDL statement

- If we drop a table then it will be permanently removed from the database
- We cannot recover the dropped table
- Its auto commit
TRUNCATE-

- TRUNCATE will remove the data from the table

- And keep the structure of the table
- Truncate will be faster than delete
- But we cannot able to delete specific rows from a table because we cannot use where clause in
truncate
- Truncate is auto commit since it’s the ddl
DELETE-

- Delete is a DML statement

- We can able to delete a specific row or the entire rows from a table using DELETE
- Delete requires commit to save the transaction
-
 - Delete structure will remain the same
- It will delete the entire adata from the table
- If we want to delete specific row we can use delete
-
- Truncate delete data from the memory and delete will check the table count and deletion
- We will receive message like table truncated in truncate
- And in delete we will get number of rows deleted
- Delete is a DML ans truncate is the DDL
- So delete will not be an autocommit and truncate is the auto commit statement
- With delete we can able to delete a specific

JOINS-

 Joins are used to get the data from more than one table.
 To fetch the data from different table with primary key and foreign key relation we will go for
joins
Types

o Inner Join (simple join, equal join).

o Left outer Join
o Right Outer Join
o Full Outer Join
o Self-Join
o Cross Join (cartesian product)

INNER JOIN

Will bring matched records between the joining tables

LEFT JOIN – it will display all matched and unmatched records from left table and matched records from
right table, remaining records from right table will come as null.

RIGHT JOIN – it will display all matched and unmatched records from RIGHT table and matched records
from LEFT table, remaining records from LEFT table will come as null.

FULL OUTER JOIN – It will display all matched and unmatched records from the both tables, Unmatched
record come as NULL value.

SELF JOIN– If we want to join two different columns in same table then we have to use self-join.By using
table and column alias will join two columns in same table.
CROSS JOIN - It will do the cartesian product, if we missed to mention join condition then it will take
cross join.
Ex . T1 table have 10 rows, T2 have 5 rows , we can get 50 rows.

CONSTRAINTS

 Constraints are nothing but the column level rules

 Constraints are used to limit the type of data that can go into a table
 This ensures the accuracy and reliability of the data in the table. If there is any violation on the
constraint, the action will be aborted.
 To restrict the duplicate and to restrict the null
And to have the relation between tables

NOT NULL -- Not accept the null values but accept the multiple duplicate values.

UNIQUE -- Not accept the duplicate values but accept the multiple null values.

Primary key – Not accept the null values and not accept the duplicate values. One table using only one
primary key

CHECK- CONSTRAINT

 This constraint works based on the condition given while creating the constraint
FOREIGN KEY CONSTRAINT

 Foreign key should always refers a primary key

 It’s like the child data will always refer the parent data
 When there is a need to establish relationship between two tables then we can do it
with the help of Foreign Key and primary key
 Foreign Key will accept null
 no restriction for Foreign Key creation in a table
 Foreign Key will accepts duplicates as well.

PK vs- Unique

PRIMARY KEY (PK) UNIQUE KEY

It does not accept duplicates values;it
PRIMARY KEY does not accept duplicates & null.
accepts the null values
We can mention only one PRIMARY KEY for a In a table we can have any number of
table. unique key
 when we create both these constraints a unique index will gets created automatically in
the same name of constraint.

PK vs- FOREIGN Key

PRIMARY KEY (PK) FOREIGEN KEY (FK)

PRIMARY KEY does not accept duplicates & null. FOREIGEN KEY accepts duplicates & null.
We can mention only one PRIMARY KEY for a FOREIGEN KEY Reference of primary key (or)
table. unique key.
We cannot drop parent table directly.
We have to drop child table then have to -
drop parent table.if the key primary key

Union Vs Union ALL

Both are set operators

UNION UNIONALL
union will not show duplicates.it give only unique
union all will show dupliactes
records

union will Records will be order in ascending

order.

Delete Vs Truncate

DELETE TRUNCATE
delete can remove specific records in a In truncate all records will be
table. (Using where condition) removed.keep the structure of the table.
Delete is DML command Truncate is DDL command
delete can rollback truncate cannot be rollback
delete is not an auto commit truncate is an auto commit
If we delete the records, records space truncate will free up the space ,after the
not removed data get deleted

Delete Vs Truncate Vs Drop

Delete Truncate Drop
Delete is DML, delete can Truncate id DDL, In truncate Drop is DDL statement. Even
remove specific records in all records in a table will be table structure will also
a table by using where removed. We cannot remove from database.
class. mention where class.
delete can rollback truncate cannot be rollback Drop cannot be rollback
delete is not an auto commit truncate is an auto commit Drop is an auto commit
delete tablespace will not be truncate tablespace will be
removed removed

Char- Vs Varchar-
 Char is the fixed length
 it will not release the free space
 Varchar is the flexible length
 it will release the free space

Varchar- Vs Varchar2-
 varchar is a variable-length data type, we can change the size of the character at the time of the
execution.
 VARCHAR is ANSI compatibility. It will accept Empty Strings.

 varchar2 is a variable-length data type, we can change the . size of the character at the
time of the execution.
 VARCHAR2 is ORACLE compatibility. It will not accept Empty Strings.

CASE- scase-
 CASE statement goes through conditions and returns a value.
 When the first condition is met . Then it will return the value for that condition and return the
result.
 Means once a condition is true, it will stop reading and return the result.
 In case if no conditions are true, it returns the value in the ELSE clause.
 Syntax : case when condition then result
 When condition2 then result 2 ELSE result 3 end

CASE- Vs DECODE-
 CASE is a statement and DECODE is a function
 We can use the CASE in the where clause and can not use the DECODE in the where
clause.
 DECODE can check equality operators only where as CASE can support all relational
operators
  DECODE can be used in sql only where as CASE can be used in SQL AND PL/SQL CASE is
better than DECODE.

CASE DECODE
CASE is a statement DECODE is a function and its specific to SQL
In case we can able to use any relational In DECODE only equals operator is possible
operators Means it will perform equality check only
CASE expects data consistency But DECODE is not
CASE handles null differently DECODE handles null as null
Means CASE will return FALSE if we compare Means it will return TRUE if we compare null to
null to null null

Common Table Expression -CTE

 Common table expression is used to store the data into the single variable
 and that variable can be accessed multiple time in the same sql
 common table expression is denoted by WITH clause
 CTE can be more readable

CTE vs- Subquery

 CTE is reusable by design

 We can store the result set data into a variable and access it multiple times inside the same SQL
multiple times.
 This will decrease the number of DB hits as well.
 Subquery is query with in a query.
 It allows us to use the result of another query in the outer query
 We can narrow down the records selection in the inner query and process then in the outer
query faster.
 There are Normal and Corelated Subqueries
 In Normal subquery the inner most query will get executed first followed by the next outer layer
query
 In Corelated - subquery references columns in the parent query, then its result will be
dependent on the parent query.
 We usually use exists and not exists in corelated subquery
 We have to relate the main query and te sub query with the common column between the
tables used in the main and subquery
 Basically, we will use the primary and foreign key columns
 We use the keywords like exists, not exists
 In the where clause
Where Vs Having- wh-
 Where clause is used to filter records in base table or physical table.
 having clause is used to filter records in result of group by clause
 we cannot use the having clause without group by
 having we can use aggregate function
 in where clause we can’t use aggregate functions
 group by is used to grouip the data

Null Function
NVL-

 NVL is the two arguments

 IF first argument is null it will replace it with the second argument
 If the first argument is not null then it will return the same value

NVL2-

 NVL2 is the 3 parameters ,

 if 1st one is not null then it returns 2nd parameter,
 if 1st parameter is null then it prints 3rd parameter

NULLIF-

 it accepts 2 column,
 if both are same then it returns null, else it will return 1st parameter.
 NULLIF expects datatype consistency

COALESCE-

 It accepts n number of column, it will return first not null value

 COALESCE expects datatype consistency

CHARACTER- FUNCTIONS
INSTR-
 In string will give me number as output
 It will find the position of the given search pattern or character
 In this we will have four arguments (One is input string, then search character
then default value as 1 which indicated starting position, and finally we have no of occurrence)
 SYNTAX: INSTR (INPUT_STRING, SEARCH_PATTERN, STARTING_POSITION (DEFAULT 1),
NO_OF_OCCURANCE)

SUBSTR-

 SUBSTRING functions allows you to extract a substring from a string

  SUBSTRING WILL RETURN CHARACTERS AS OUTPUT
 Syntax: SUBSTR (INPUT_STRING, STARTING_POSITION, NO_OF_CHARACTER)
 If start position is 0, then the SUBSTRING function treats start position as 1 (i.e.: the first position
in the string).
 If start position is a positive number, then the SUBSTRING function starts from the beginning of
the string.
 If start position is a negative number, then the SUBSTRING function starts from the end of the
string and counts backwards

CAST-
 CAST function in sql server is used to convert the value from one data type to another.
 SELECT CAST(25.65 AS varchar);
 SELECT CAST('2017-08-25' AS datetime);
 SELECT CAST(25.65 AS int);
 In ORACLE we have to_Date , to_number , to_char functions to convert the datatype
 TO_DATE – Will convert the varchar to system date format
 TO_CHAR – will convert number to varchar
 TO_NUMBER – will convert varchar to number

CLAUSES
 SELECT - Should be followed by column name
 FROM - Should be followed by table name or inline subquery
 WHERE - Table filter condition
 GROUPBY - To group data in a column
 HAVING - To filter group by output
 ORDER BY - To sort the output based on any columns in ascending or descending order
 If no specific order is given then order by will default take ascending order

DISTINCT-
 It will return us only unique values in the columns used

DUAL-
 Oracle inbuild table will have only one row and column by default
 And the default value is 'x'
 In runtime we can able to store value in dual but not permanently

ROWNUM-
 It always starts with 1 and will be in sequential manner
 Rownum generation is based on the result set of a query
 Rownum will be shuffled if will use order by
 It’s the pseudo column

ROWID-
 Permanent address of a row
 18-digit alpha numeric string
  And its unique from one another with case sensitiveness

diff between rowid and rownum?

Rowid Rownum
rowid is 18 digit alphanumeric value, it will rownum is sequence of value, it depends on
generate automatically when we insert new result set, it does not occupy table space.
records in table.
rowid will occupy table space rownum will not occupy table space

diff between translate and replace

Replace Translate

diff between instring and substring

In-string Sub-string
instring is used to find position of the char, it substring is used to subtract char by
accepts four arguments. mentioning position of the string, it accepts
three arguments..

Diff bw normal- subquery and co-related subquery

1. Normal subquery
 In normal subquery, Inner query will executed first and outer query will execute
next. Output of inner query will be the input of main query.
 To get dynamic data based on input so will use sub query.

2. Co-related subquery
 In Co-related Subqueries, inner query will not execute independent. Inner query
records matches outer query records and it matches for each and every record in a
table.

What is group- function/aggregate fun?

(Sum, average, count, min, max are the aggregate function)

 Group function is multiple row function

 Group function is used to reduce actual number of records

  Group function accepts only one argument

 Group function does not accepts null but by using count(*) we can count including null values

 we cannot able to mention multiple row function in where clause

 GROUP BY is used to group rows that have the same values.

 Where class used to filter records in base table

 Group by class is used to filter group by records in group by class

 Order we have to mention at last tof the query it will diaply based on colum order

Different between count- and count(*)?

 count function will accept null to count number of

records

 Count(*) function will not accept null to count number

of records.

Diff between where and having- class?

 ‘Where class’ is used to scan records in base table or physical table.
 ‘Having class’ is used to scan records in result of group by class. We cannot able to
mention group function in where class.

Difference between clustered and non-clustered index?

Clustered index Non-clustered index
It stores the data rows in the table based on A Non-clustered index stores the data at one
values. location and index at another location.
We can create only one clustered index per We can create more than one Non-clustered
table. index per table.

Group vs partition gvsp-

 Group by is the aggregation
 it will reduce the number of rows in the output
 group by as min, max, sum,avg, count of star and count of column as there functions

 Partition by is splitting the data into chunks

 it will not reduce the number of rows
 partition by we can use in rank , dense_rank , lead , lag , row_number
GROUP FUNCTIONS : MIN , MAX ,AVG , SUM , COUNT(COLUMN_NAME) , COUNT(*)

COUNT-(COLUMN_NAME) WILL GIVE THE COUNT OF NOT NULL VALUES IN THE COLUMN USED

COUNT(*) WILL RETURN TOTAL TABLE COUNT

diff between in- and exists operator?
1. IN operator will access values

2. EXISTS operator will get Boolean as output

1. In operator if nested sub-query and subquery result is less then IN will be faster

2. Exists sub-query result is large and it has more number of duplicate values then EXIST will be faster

Group by- vs having-

 Group by is used to group the data in row level based on any columns
 Having clause is used to filter the result of group by
 We cannot use having clause without group by

SUBQUERY
 A subquery is a query within a query.
 We can use it in multiple ways: in Select ,FROM clause, WHERE clause and even as
a column
 SCALAR SUB QUERY - SELECT
 INLINE SUB QUERY - FROM
 NESTED SUB QUERY - WHERE
 My inner query will be executed first after my outer query executed
1. Normal subquery

 In Subqueries Inner query will executed first and outer query will execute next.
Output of inner query will be the input of main query.

2. Co-related subquery

 In Co-related Subqueries, Outer Query will execute first, for each and every record
from outer query will be matched with inner query.
 Both main and subquery depends on each other

SET- OPERATORS

 SET OPERATIONS gets data from more than one selects query.
 To perform the set operations, number of column and data type of each column
must be same in the all SQL
 We have union , union all , intersect , minus
 UNION-

 It clubs both the result sets and returns result in ascending order based on first
column without any duplicate.
 Union will first perform the sorting operation and eliminates the records which are
duplicated across all columns.
 To club tow results and to ignore the duplicates between them we can use union

UNION ALL-

 Will just clubs both the result sets and it won’t returns result in ascending order and
it won’t ignore duplicates.
 Union all will be faster than union
 Union all will grow the table in vertical manner
 Full outer will grow the table in horizontal manner
 For using the full outer join we need the pk and fkrealtion ship
 But for unionall we don’t need any pk and fkrealtion

INTERSECT-

 It returns common records between result sets and returns result in ascending
order based on first column in select clause
 Inner join will work based on the column that is used in the join condition it will
return the matched data between tow tables
 So inner join need the tables relationship
 But intersect does not need the relationshihip

MINUS-

 It returns records available in result set1 and not available in result set2
 We used to compare source to target data mismatch
 If we use minus source and target it will give data in the source and that is not in
the target

TABLE PARTITIONS-
 We can split the table storage to access the table as much as faster by the select query.
 If the table having huge volume of records and if it is frequently accessed by the select query
then that table requires partition.
Table partition types
-----------------------
Range partition
list partition
hash partition
composite partition

partition pruning

 if we use the partition key column in the where clause of select qry then,
 select query will search the respective partition and
 it omits the other partition records while searching

SEQUENCE
 Sequence is a database object
 It Generates Unique Numbers Automatically
 Mainly Used To Generate The Sequence In Primary Key Columns
 It can be used in any column of any tables.

Pseudo Columns

 NEXTVAL -- Generate the next values

 CURRVAL -- Generate the current values

Syntax:

Create sequence seq_name

Start with
Increament by
Minvalue
Maxvalue
Cycle / no cycle
Cache ;

VIEW
 View is a database OBJECT
 View is the virtual TABLE
 This is stored to select query ONLY
 View does not occupy the memory spaces

Types of views
 Simple VIEW
 My select query contain single table called as simple view
 We can do it DML in directly
 Complex VIEW
 Query with having join, set operators, functions, subquery then it is complex
view
  we cannot able to do DML using complex view, if i want to do DML then we
can use instead of trigger PLSQL concept.

Materialized view
1. Materialized view is the snapshot on result set of queries, which means storing result
set permanently in database.

2. Materialized view occupy table space

3. We cannot get updated information in Materialized view, if i want to get updated

information then we should refresh the Materialized view.

4. We can create Index for Materialized view

5. If we drop table Materialized view can access until refresh.

How to refresh Materialized view?

exec dbms_mview.refresh('Mvie_name');

Different types of refreshes

 Fast

 Force

 Complete

Force:

 It is default refresh
 It will search log table for base table, if log table is available then it will navigate
to fast else complete refresh

Complete:

 It will remove all records in table and then all records from base table will be
populated into Materialized view table

fast:

 It will insert only amended records in log table so while refresh only new records will
populated to Materialized view table.
 create materialized view log on Materialized view name;
Difference between View and Materialized View?

View Materialized view

View used to save Query permanently in our Materialized view used to save result set
database permanently in the database

view will not occupy space Materialized view will occupy space
In view can see the updated data In Materialized view we cannot able to see
the updated data, if we want to see updated
data then we have to do refresh
In view cannot create index for view In Materialized view can create index
in view can perform DML In Materialized view cannot perform DML

IN VS EXIST- in-

IN Exist
In is faster than exists if sub query is having Exist is faster than in if sub query is having
less data more data
In In statement, we can directly define the We Cannot use the direct values, it should
multiple values instead of using sub query have a subquery with select
Exist is will return the data if sub query
returns true

INDEX
 Index are used to retrieve data faster from the table. Based on column value and rowid index
block will be created, so my optimizer will pick the data based on rowid.
 . By default, SQL query will take full table scan, if we use index for a column then index
scan will take. based on column type we have create index. In backend indexing will
happen, it will occupy space.

Index Types:

 btree INDEX ( Balaced Tree) ---high cordinality values,high duplicate values

 bitmap INDEX -- low duplicate values,low cordinality values
 unique INDEX --- no duplicae values
 composite INDEX -- for more then one COLUMNS
 function based index -- Appliying function in COLUMNS

1.unique index.

Unique index is created automatically when we applied in primary key constraint or unique key
constraint for a column.
Unique index will take index unique scan, when compare to another index unique index scan is fast.

2. Btree index

btree index will apply for if the column having low duplicates. btree index will take index range scan.

3.Bitmap

We can recommend Bitmap index if the column having high duplicates for example status column,
gender column. Bitmap index will take is fast full table scan.

4.Function based index

Index can skip during performing arithmetic operation, functions, and set operators in a sql query.

if we want to use index even if apply function in where class then we can use function based index.

5.Composite index

If I mention multiple condition in same table, in that if I want to create index from combined multiple
columns then we can use composite index.

How to see explain plan?

It is used to find plan used in a query, we can see id, operations, CPU, cost, time and
rows that accessed, hash value.

1. explain plan for

select * from table_name;

2. select * from table(dbms_xplan.display);

ANALYTICAL FUNCTION
 RANK()
 DENSE_RANK()
 ROW_NUMBER()
 LEAD
 LAG

1.RANK-
 Rank method will give rank based on the values in the column.
 The RANKS not be consecutive. Because if the current rank as duplicates, then the next rank will
be skipped.
 An ORDER BY clause is required when working with RANK functions, but a PARTITION BY clause
is optional
SYNTAX : RANK ()OVER(PARTITION BY COL_NAME ORDER BY COL_NAME )
 RANK ()OVER(ORDER BY COL_NAME )

2.DENSE_RANK-
 The DENSE_RANK is consecutive.
 Even if the current rank as duplicates the next rank will not be skipped.
 An ORDER BY clause is required when working with rank and DENSE_RANK functions, but a
PARTITION BY clause is optional
 Simply say the rank will consider the duplicates and dense rank will not.
 I have used the dense_rank to rank the counter party as per the payment completion the same
ranking I have performed in the branch wise data by using of the partition by clause
SYNTAX : DENSE_RANK () OVER(PARTITION BY COL_NAME ORDER BY COL_NAME )

3.LEAD-
 The LEAD function is used to access data from NEXT rows along with data from the current row
 An ORDER BY clause is required when working with LEAD and LAG functions, but a PARTITION BY
clause is optional
 I have used the lead function to get the next payment amount for the trade and I have
compared the payment difference between the pervious payment and current payment and I
have selected the difference in the output
SYNTAX : LEAD (COLUMN_NAME , NO_OF_POSITION , NULL_HANDLER)OVER(ORDER BY
COLUMN_NAME)

4.LAG-
 The LAG function is used to access data from PREVIOUS rows along with data from the current
row.
 An ORDER BY clause is required when working with LEAD and LAG functions, but a PARTITION BY
clause is optional.
 In my project, I have used the Lag function to get the previous payment amount for the trade
and I have compared the payment difference between the current payment and previous
payment and I have selected the difference in the output

SYNTAX : LAG (COLUMN_NAME , NO_OF_POSITION , NULL_HANDLER)OVER(ORDER BY

COLUMN_NAME)

5.ROW_NUMBER- its analytical function

 Row number function always generates unique ranking even with duplicate records in the data
 An ORDER BY clause is required when working with row number functions, but a PARTITION BY
clause is optional
 WE CAN USE THE ROW NUMNER TO NUMNER THE BRANCHES AND IN THE PARTIITON BY OF
THE SOME OTHER COLUMN
SYNTAX : ROW_NUMBER() OVER(partition by colimn_name ORDER BY COLUMN_NAME )

We have a partition by
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 de-normalize values from multiple rows
into a single value which can be a list of comma-separated values

 select job_id , LISTAGG( first_name ,',') WITHIN GROUP (ORDER BY first_name) from employees
group by job_id;

Diff between rank and dense rank?

Rank Dense Rank

Rank will skip next sequence of number in a Dense rank will not skip next sequence of
column if both value are same number in a column.

Col Rank Dense_rank Row_number

90 1 1 1
85 2 2 2
85 2 2 3
80 4 3 4

Pseudo columns
 sysdate / current_date
 systimestamp
 user
 uid
 rownum
 rowid
 level
 nextval
 currval

Synonyms
 Synonym is the database object
 Alternative name for the database objects
 We can create multiple time synonym for an table

Global Temporary Tables GTT-

  Global Temporary Tables are the Oracle tables, having data type as private.
 Its session dependent tables, it means we can access the GTT table data for that session alone.
 If session is disconnected or closed, GTT data will be truncated immediately.
 We jutst to create gtt for data check alone once we closed it will utomatically disavle in state
and

We have 2 types of the GTT.

o ON COMMIT DELETE ROWS – data will be truncated once committed the transaction
o ON COMMIT PRESERVE ROWS -- data will not be truncated once committed the transaction

Synonym
 A synonym is an alternative name for objects such as tables, views, sequences,
stored procedures, and other database objects.

We generally use synonyms when you are granting access to an object from another

schema and you don't want the users to have to worry about knowing which schema

owns the object.

SQL query performance Turing:

 First i will take AWR report with the help of

development team Then i will find which DB

object is getting slow.

 Then will use DBMS_profiler to get the line getting delay

 First i have to start DBMS_profier, then three tables are

populate automatically plsql_profiler_runs

plsql_profile

r_data

plsql_profile

r_unit

 using runs table we can fine last run id

  then i will join data and unit table to get the line which are getting delay, then

will tune the query also mean while I will check any DB blockage using

v$session table if I found any blockag

Step-1

 First i will check whether the query is written in subquery, if that query is in subquery,
then i will try to convert into join.
 Join will give good performance when compare to subquery, but all quires cannot able to
covert.

Step-2

 If the query used ‘in’ operator, then we have to replace with exists operator.

step-3

 We have to check whether the index is reading for 'where clause' mentioned
column by using explain plan.
 If index already applied and not reading then we have to check index is locked or
not by using v$locked_object table.
 if it is locked then we have to unlock the index by using

sql statement alter index index_name rebuild;

step-4

 If that query is complex then we cannot able to apply any other dependency object
then can use Materialized view.

step-5

 If we do predominant DML for table like insert, update and delete then there may the
chance to miss the updated row count.
 May be query will not get updated execution plan, if i want to get updated execution
plan then we have to take gather table stats.
 before performing gather table stats we have to check weather the table have to take
stats by using query
 Select * From all_tab_stats Where stale_stats = 'YES' and table_name =

table_name. Using dbms_stats will take the gather table stats.

  We have seprate house keeping jobs to take this gather table stats, will exec the autosys
job during non bunesiness hours
 Exec dbms_stats.gather_table_stats(user_name,table_name);

Dead lock or session blockage:

 Then I will l check the any DB blockage in DB

 If multiple users are trying to do DML for same value then will

get dead lock. to find session blockage:

 I will check the DB blockages using

 select * from v$session where blocked_session is not null;
 Then we have to kill the session by using
 alter system kill session(sid,serial#) immedaite;
 DBA team will kill the session. We have to crate ticket and have give the ticket

number to DBA team. DBA team will kill the session, we don’t have access to kill the

session.

2rd max salary

Select * from (select salary, dense rank () over (order by salary desc) drk from

table_name) where drk=3;

I am using dense rank function to assign rank for salary column, because dense rank will
not skip next sequence of value. I want to filter particular rank here, but dense rank cannot
mention in where class. So I used inline sub query in ‘from’ class, then it will act as a table,
then I used where class in main query to filter. Drk is alias name for dense rank

How to find duplicates- ?

Select count(column_name), column_name from table_name group by
column_name having count(col_name)>1;

 Using group by class will count duplicate values in a table.

 I used having class to filter group by values which is count more than 1.

How to delete- duplicates ?

delete from table_name where rowid not in (select max(rowid) from
table_name group by column_name);
 Using rowid I will find max of id, this rowid is system generated, it will generate based on
duplicate values.

TABLE PARTITIONS- tp- partition-

 Table partitioning in Oracle is a feature that allows you to subdivide a large table into smaller,
more manageable pieces.
 This can improve performance, manageability, and availability.
 We can split the table storage to access the table as much as faster by the select query.
 If the table having huge volume of records and if it is frequently accessed by the select query
then that table requires partition.
Partition types:

Range partition:
 Range partitioning involves dividing the data in a table based on a specified range of values
within a column.
 This column is typically a date or numeric type.
 Each partition represents a specific range of values.
Example: You can partition a sales table by date, where each partition represents a specific date
range (e.g., one partition for each month or year).

List partition
 List partitioning is used when data can be divided into discrete, non-overlapping sets based on
the values of a specific column.
 Each partition contains a predefined list of values.
Example: You can partition a customer table by region, where each partition includes customers
from a specific region.

Hash partition
 Hash partitioning distributes data evenly across partitions based on a hash function applied to
one or more columns.
 This method can be useful when you want to distribute data uniformly, but there's no specific
range or list to partition by.
Example: You can partition a table of user accounts by hashing the username to determine the
partition in which each user's data is stored.

Composite partition
 Composite partitioning involves combining multiple partitioning methods to create more
complex partitioning schemes.
 For example, you can range-partition data within each range-partitioned segment or list-
partition data within each range partition.
 Example: You can range-partition a sales table by date and then list-partition each date partition by
region for more granular control.

Duplicate- selection Du-

 I will select the columns in which we need to find the exact duplicates and group by with the
same columns
 Then by using of the having clause I will select the more than one entry of the data in each
group.
 We will be using group by and having clause to find the duplicate in a table
 If we have not used the having clause then it will give the distinct of the columns used in group
by
 Select id , count(*) from students group by id having count(*) > 1;
 We should not use the column in the select clause normally that is not in group by
 We can able to group multiple e column , and we can able to use group functions like min , max ,
sum , avg , count aggreagete
 If we want to filter the the output of grop by then we can use the having
 No its not mandatoty

Delete duplicates dd-

 To delete the duplicate records from a table and to retain only unique records we have to group
the data first for the column where we need to find the duplicate group by and then we have to
retain only one records from each group and delete the rest of the duplicate records from each
group by using max(rowid) or min(rowid) concept in the delete statement we have to use the
not in operator to delete the duplicate records.
 Delete from tablename where rowid not in (select max(rowid) from tablename group by
colname);
 This will retain one unique row from each group and deletes the rest of the duplicates in the
group
 It will retain the max rowed from each group and delete the rest of the data.

3Max-
 Here I will use dense_rank to rank the salary column values I descending after partition by
deptname and given the column alias name for it. In the inner sql I used dense_rank and given
the column alias name for it. And in the outer sql I have filtered only the 3 rd maximum value
using the same alias name in the where clause
 IF WE HAVE THE SAME SALARY GETTING BY MULTIPLE EMPLOYEES DENSE_RANK WILL GIVE ALL
THE EMPLOYEE
 To get the 3rd max value from a table,

( Select * from (select id,name,salary,dense_rank()over(order by salary desc) sal_rnk from

employees) where sal_rnk = 3; )
  If I want to filter the
 I will type and share it in chat
 Then I will explain the query

Max-
For getting the Nth maximum salary or minimum salary we have to use dense_rank analytical function. if
two employees are getting same salary. That is two employee are having the same third max salary then
dense_rank will give both the employees details but If we use row_number then it will give only one
employee detail so we have to prefer dense_rank over row_number in this scenario

REGEXP_LIKE-
REGEXP_LIKE(column_1, '![A-Z] [a-z]')

REGEXP_REPLACE(COLUMNNAME,[A-Za-z])

REGEXP_REPLACE(COLUMNNAME,[0-9])

Oracle- vs Sql server Ovs-

 Oracle database is the product Oracle Corporation.
 SQL server is the product of Microsoft Corporation.
 PL/SQL or is used by Oracle Corporation.
 MS-SQL uses transact SQL or T-SQL
 Number datatype we use in Oracle but in sql server we use integer datatype
 Varchar2 we use in Oracle and we use varchar in sql server
 INSERT, UPDATE, DELETE statements are executed serially in sql server
 INSERT, UPDATE, DELETE, and MERGE statement in parallel in oracle.
 When transactions take place in Oracle, they can be rolled back
 When transactions take place in SQL Server, they cannot be rolled back
 In oracle we have Minus set operator the same in sql server is named as except.

PLSQL-
 PL/SQL is a procedural language and executes blocks of code at once
 PLSQL is the sequential code, it perform a process
 PL/SQL is a procedural language and executes blocks of code at once
 it consists of many sql inside it
 In PLSQL we can able to do error handling
 But in SQL we cannot do error handling
 it have objects like procedure, function, package, cursor, trigger
Sql- vs PLSQL-
 SQL is the structured query language
 it will be stand alone and it will perform single operation
 SQL is a non-procedural language that executes a single query at a time
 PLSQL is the sequential code , it perform a process
 PL/SQL is a procedural language and executes blocks of code at once
 it consist of many sql inside it
 In PLSQL we can able to do error handling
 But in SQL we cannot do error handling
 it have objects like procedure , function, package , cursor, trigger
 these are the main difference

Procedure- Proc-
 Procedure is named plsql object that group of SQL statements and logic, it will store in
database, so that the code can be reused again and again.
 We can also pass parameters to a Procedure, so that the stored procedure can act based on
the parameter values that is passed. ( IN Parameter, OUT Parameter, IN OUT Parameter)
 We can able to handle multiple functionality inside the single procedure
 Structure of a procedure :Declare , begin , exception , end

Function- Func-
 Function is named plsql object that group of SQL statements and logic, it will store in
database, so that the code can be reused again and again.
 We can also pass parameters to a Procedure, so that the stored procedure can act based on
the parameter values that is passed. ( IN Parameter, OUT Parameter, IN OUT Parameter)
 Function should always return a value , Functions are used for calculations and computation.
 function can be called using Stored Procedure , Function can be called thru a select
statement.

Trigger- tri-
 Trigger is a type of Procedure. But we cannot able to call the trigger directly like we do in
procedure. Trigger will be called automatically whenever modification happening on the table to
which its created. The modification includes (Insert, Update, Delete,Merge)
 It will capture the DML and save the changes in a separate table. Which we usually call as audit
table.
 We have two types of triggers BEFORE and AFTER
 BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.
Cursor-
 Cursor holds the rows returned by a SQL statement. The set of rows the cursor holds is referred
to as the active set.
 Cursors are used to store Database Tables.
 I will use it inside the begin end block or inside the procedure
 There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors
 Explicit Cursors are select into type of cursors
 Implicit Cursors are used for DML operations.
 Implicit Cursors as set of attributes that enables an application program to test the state of the
cursor
 These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT.
 %FOUND - Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or
more rows or a SELECT INTO statement returned one or more rows. Otherwise, it
returns FALSE
 %NOTFOUND - The logical opposite of %FOUND
 %ISOPEN - Always returns FALSE for implicit cursors
 %ROWCOUNT - Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement.

Procedure vs Function Proc Vs-

Procedure Function
Procedure may or may not return a value. Function should always return a value.
Procedures are mostly used for DML Operations. Functions are used for calculations and
computation.
Procedures cannot be called from function. But a function can be called using Stored
Procedure.
Procedure cannot be called thru select Function can be called thru a select statement.
statement.

Performance tuning- tu-pt-

 I will check the explain plan of the query

 In that explain plan, I will check is there any full table scan is happening or not. If any full table
scan happens then, I will check where clause of the query is having index or not.
 If index is not available then, I will suggest create the index if the index is available, but not used
by the query then, I will check the index status at the user_indexes table and I will suggest
rebuild the index
 I will check the tables which are involved in that query is used partition pruning or not. Partition
pruning can be identify by explain plan p start and p stop columns.
  If partition pruning is not happening then, i will check is there any arithmetic operations used in
that where clause of partition key columns
 If the sql using the subquery or co related sub query then I can suggest to change it in join based
sql.

Anonymous block
Anonymous block is the unnamed block, it’s not stored in DATABASE.

uses:

It is used to write a simple programming thing, invoking of PLSQL objects like procedure,

function. It will reduce n/w traffic.

structure:

declare---------------optional

begin-----------------mandatory

exception------------optional

end-------------------mandatory

Cursor
 Cursor will create new SQL private work area used to fetch records in the table or view.
 If multiple users are accessing base table there may cause performance issue so that we
using cursor to create new work area and records are updated row by row in that.

It can be
parameterized.

Cursor has two types

1. implicit cursor

2. explicit cursor

1. Implicit cursor

no records fetch------->no data found error.

more record----------->exact fetch returns more than one values.

2. Explicit cursor
exception is optional.
for multiple record we can open the record in for loop or open fetch

close.

in open fetch close only first record will be fetched.

open fetch for loop

no record----------> null no record----------->null

multiple record---->first row multiple record---------->all records

Procedure
 Procedure is a Data Base object, mainly we can use procedure to performs an
action. We can combine multiple statements like select, DML, conditional
statement, loops in a block. We can declare variables in cursor in declaration part.
 This block will compile once and we can call multiple times. We cannot able to call
through select statement.
 Procedure can be parameterized, it accepts in, out, in out

parameters. use:

 to create new transactions, modify or delete existing transactions we can use procedure.

Functions
 Function is Data Base object.

 Function can be parameterized, it accepts in, out, in out parameters, it must return
value. We should not use multiple same set of programs in procedure, those
statements have to create as function and this function will call inside same
procedure or in another procedure using select statement. So will get good
performance.
 we can create functions for computation, calculation, validation like of

program. 6.Functions can be called in select, insert, update

 oracle allows dml inside the functions

 If the function has dml, then cannot be called through select, if we want to use in sql
statement then use pragma autonomous_transaction.
  If the function has out parameter, we can’t call through select.

Difference between procedure and Function?

Procedure Function
Procedure cannot call through select Functions can call through select statement
statement
procedure we need to use out parameters to Function should return values to get the
get the results results.
We can use DML in procedure We can use DML in function, but we cannot
call through select statement.

TRIGGER
 Trigger is the database object, It is used for automatic transaction and audit purpose.

 We cannot call the trigger; we do any changes in triggering object table then DML
will fire automatically to triggering action table.
 In my project up-stream and down-stream users will do some changes in UI. We have to
capture user name, what time, old record and new record in audit table. For this we used
row level trigger to get update in audit table. Because in row level only will mention :new
and :old values.

Oracle has two types of triggers

row level and statement level trigger

row level trigger:

 It will fire for each and every attached rows due to the DML statement.

Statement level trigger:

 It will fire only one time.

 It will fire once for each DML statement.

 We could not call trigger, if we try to commit inside the trigger comes run

time error.

 Trigger will automatically fire whenever we are executing DML

statements.

 DDL statement will not

fire trigger.
  we cannot commit inside

the trigger.

We can create 12 combination of

trigger. row level

before insert/update/delete.

after

insert/update/delete.

statement level

before

insert/update/delete.

after

insert/update/delete.

 We can enable or disable the trigger.syntax(alter trigger trg3

enable/disable). If we drop the table,trigger will also drop.

 If we fire more than one trigger in same timeing trigger fireing order is random,if we
want to fore the fireing order by follows clause.
 It is mis-use of trigger in after timing triggering event and triggering object is
pointing same timing mutating error will occur.

Pragma autonomous_transaction
 Pragma is the compiler Directive.

 autonomous transaction is independent transaction.

 It will execute in different session.

 It is used to commit particular DML operation from Pragma autonomous transaction.

 If we have DML inside the pragma block commit or rollback is mandatory.

 Pragma is used to create error log procedure used to avoid mutating errors also.

Pragma Exception_INIT
 Pragma is the compiler Directive.
  PRAGMA EXCEPTION_INIT statement is used to associate a user-defined exception with a specific
Oracle error code.
 This association allows you to handle Oracle errors using named exceptions in your PL/SQL code,
providing more meaningful error messages and better control over exception handling.

PRAGMA EXCEPTION_INIT(exception_name, oracle_error_code);

Pragma Serially_reusable
 The SERIALLY_REUSABLE pragma specifies that the package state is needed for only one call to the
server.
 After this call, the storage for the package variables can be reused, reducing the memory overhead
for long-running sessions.

 We have included the PRAGMA SERIALLY_REUSABLE term in package specification.

Refcursor
A REFCURSOR is Oracle Data type it allows to return query results (like collection of column & Rows )
from a stored procedure, function, or anonymous block. REFCURSOR is commonly used in Oracle PL/SQL
for returning dynamic result sets to Front End UI.

Types of Refcursor

1. Strong Refcursor -> A strong REF CURSOR has specifies a return type.

In Package specification we have to mention.

Type type_name is ref cursor return datatype;

2. Week Refcursor-> A weak Ref Cursor does not have any return type.

Sys_refcursor is oracle type weak refcursor.

Exception
To handle error in plsql block we can go for exception. It will handle error during execution of
program.we have 2 type of excetion.

Predefined Exceptions: Oracle provides a set of predefined exceptions, such as

NO_DATA_FOUND , TOO_MANY_ROWS, ZERO_DIVIDE, etc., which are raised
automatically when specific error conditions occurred.

User-Defined Exceptions: Developers can define their own custom exceptions using the
DECLARE section and based on business scenario we can use RAISE command to raise and handle in
exception block.