How to DELETE multiple rows in Oracle?

We can DELETE multiple rows in Oracle by using the rowid.

Steps1:Lets Create a Table named DELETE_MULTIPLE 

CREATE TABLE DELETE_MULTIPLE
(
  EMP_ID NUMBER NOT NULL
, EMP_NAME VARCHAR2(20 BYTE)
, SALARY NUMBER

);

Table DELETE_MULTIPLE created.

Step2:Insert data into table DELETE_MULTIPLE (insert duplicate values too)

insert into DELETE_MULTIPLE values(1311,'John',25411);
insert into DELETE_MULTIPLE values(1313,'Perk',46456);
insert into DELETE_MULTIPLE values(1315,'Adam',46654);
insert into DELETE_MULTIPLE values(1317,'Gill',45454);
insert into DELETE_MULTIPLE values(1311,'John',25411);
insert into DELETE_MULTIPLE values(1313,'Perk',46456);
insert into DELETE_MULTIPLE values(1315,'Adam',46654);

insert into DELETE_MULTIPLE values(1317,'Gill',45454);

Here is the DELETE_MULTIPLE Table data.

Step3:Now query to delete the multiple rows.

delete from delete_multiple where rowid not in (select max(rowid) from delete_multiple group by emp_id);

4 rows deleted(there was 4 duplicate rows.)

Now give a try,And if you like this post please do share and like.

How to find Nth Highest Salary?

How to solve IO ERROR:THE NETWORK ADAPTER COULD NOT ESTABLISH THE CONNECTION?

Go to Start in your computer, type services

Go to services[select standard among Extended and standard] and start[right click on service and click start]
1.Oracle ORCL VSS writer Service
2.OracleDBConsole
3.OracleMTSRecovery Service
4.OracleOraDb11g_Home1ClrAgent
5.OracleOraDb11g_Home1TNSListener
6.OracleServiceORCL

Now connect the connection in Oracle SQL Developer by using username and password.
Eg:Username:hr
Password:hr

Functions in Oracle

Example of Functions

--concat
select concat(concat('Adams','martin'),' Rodrick') from dual;
Ans->Adamsmartin Rodrick
--initcap
select INITCAP('adams martin rodrick') from dual;
Ans->Adams Martin Rodrick
--LOWER
select LOWER('LOWER CASE EXAMPLE') from dual;
Ans->lower case example
--upper
select UPPER('upper case example') from dual;
Ans->UPPER CASE EXAMPLE
--UCASE
select UCASE('upper case example') from dual;
Ans->UPPER CASE EXAMPLE
--LPAD
select first_name,LPAD(SALARY,length(salary)+1,'$')from EMPLOYEES
Ans->Steven $24000
--LTRIM
select LTRIM('xyxYyXz WORD','xy') from dual;
Ans->YyXz WORD
--replace
select replace('lets learn','learn','earn') from dual;
Ans->lets earn
--RPAD
select RPAD('PASSION',12,'x') from dual
Ans->PASSIONxxxxx
--LPAD
select LPAD('Rodrick',15,'Mr ') from dual;
Ans->Mr Mr MrRodrick
--trim
select trim(' Rodrick ') from dual;
Ans->Rodrick

How to Find Nth Highest Salary?

This is very important for Oracle SQL interview.so Lets see how to Find Nth Highest Salary

Step 1:Create a simple N_TH_HIGHESTSALTable.

CREATE TABLE HIGHESTSAL
   ( EMP_ID NUMBER(2,0),
SALARY NUMBER(3,0)
   ) ;
step 2:Insert Values into the table N_TH_HIGHESTSAL
insert into n_th_highestSal values(1,200);
insert into n_th_highestSal values(2,700);
insert into n_th_highestSal values(3,300);
insert into n_th_highestSal values(4,600);
insert into n_th_highestSal values(5,800);
insert into n_th_highestSal values(6,500);
insert into n_th_highestSal values(7,900);
insert into n_th_highestSal values(8,400);

Step 3:Now use the RANK() function and retrieve the nth highest salary as per your requirement.
Eg:We will find 3 rd highest salary

select newta.* from (select emp_id,salary,rank() over (order by salary desc) sal_rank from N_TH_HIGHESTSAL)newta WHERE newta.sal_rank=3;

N:B:Try by yourself.

Q.2 How to Find 2nd Highest Salary?

Ans:
select max(SALARY) from n_th_highestSal where SALARY not in (select max(SALARY) from n_th_highestSal)
select max(SALARY) from n_th_highestSal where SALARY <(select max(SALARY) from n_th_highestSal)