DataBase

Comparison and Logical Condition in SQL

Comparison Condition:-

Comparison conditions are used in conditions that compare one expression to another value or expression. These operator are used in WHERE condition.

Comparison Operator:-

 

1. Equal to (=):-

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 100;

 

2. GREATER THAN (>):-

SELECT *

FROM EMPLOYEES

WHERE SALARY > 4000;

 

3. LESS THAN (<):-

SELECT *

FROM EMPLOYEES

WHERE SALARY < 5000;

 

4. GREATER THAT OR EQUAL TO (>=)

SELECT *

FROM EMPLOYEES

WHERE SALARY >= 4000;

 

5. LESS THAN OR EQUAL TO (<=):-

SELECT *

FROM EMPLOYEES

WHERE SALARY <= 5000;

 

6. NOT EQUAL TO (<>):-

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID <> 50;

 

 

Others Comparison Operator:-

1. BETWEEN .. AND ..:- Between two value(include)

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID BETWEEN (200 AND 300);

 

2. IN:- Match any of the list of values

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(101, 110, 200, 220, 109);

3. LIKE:- Match a character pattern.

SELECT *

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’;

 

4. IS NULL:- Is a null value.

SELECT *

FROM EMPLOYEES

WHERE COMMISSION_PCT IS NULL;

 

 

Logical Condition:-

1. AND:- If both compare condition are TRUE then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

AND DEPARTMENT_ID = 20;

2. OR:- If any one compare condition is TRUE then it return TRUE.

 

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

OR DEPARTMENT_ID = 20;

 

3. NOT:- If condition is false then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN(10,50);

 

 

 

Advertisements

Sql, PL/SQL Special Query

Ques:-

TEAM RESULT

A        WIN

B        WIN

A        LOSS

A        WIN

B        DRAW

C        WIN

C        LOSS

D        LOSS

E        DRAW

EXPECTED OUTPUT

TEAM  WIN  LOSS DRAW

A       2    1    0

B       1    0    1

C       1    1    0

D       0    1    0

E       0    0    1

ANS:-

select TEAM,SUM(CASE RESULT when ‘WIN’ then 1

else 0 end) WIN

,SUM(CASE RESULT when ‘LOSS’ then 1

else 0 end) LOSS

,SUM(CASE RESULT when ‘DRAW’ then 1

else 0 end) DRAW

,SUM((CASE RESULT when ‘WIN’ then 1 else 0 end)+(CASE RESULT when ‘LOSS’ then 1 else 0 end)+(CASE RESULT when ‘DRAW’ then 1 else 0 end)) TOTAL

from xxc05_test1

GROUP BY TEAM

order by 1

 

 

———————————————————————-

 

Ques:- Display the salary of each and every employee in 1991,1992 and 1993.

(hint: use ‘DECODE’ function)

Zigzag

NAME YEAR ATM_NO

————- ———– —————–

JOHN 1991 1000

JOHN 1992 2000

JOHN 1993 3000

JACK 1991 1500

JACK 1992 1200

JACK 1993 1340

MARY 1991 1250

MARY 1992 2323

MARY 1993 8700

EXPECTED OUTPUT

YEAR JOHN JACK MARY

———— ———- ———- ———-

1991 1000 1500 1250

1992 2000 1200 2323

1993 3000 1340 8700

Ans:-

SELECT YEAR,SUM(DECODE(NAME,’JOHN’,ATM_NO,0)) JOHN

,SUM(DECODE(NAME,’JACK’,ATM_NO,0)) JACK

,SUM(DECODE(NAME,’MERRY’,ATM_NO,0)) MERRY

FROM XXC05_TEST2

GROUP BY YEAR

ORDER BY 1

———————————————————————-

Ques:-

Write a Query to display each letter of the world “Happy” in a separate row.

(Hint: use ‘SUBSTRING and CONNECT BY LEVEL’)

Answer table should be

Output

result

H

A

P

P

Y

Ans:-

select substr(‘HAPPY’,LEVEL,1) FROM DUAL

CONNECT BY LEVEL<=LENGTH(‘HAPPY’);

———————————————————————-

Ques:-

My table have some records

NAME    ACT_NO

SAJAL    1234567890

AMAL    2345678901

NAVAL    9876542211

but i want to print Like thet

******7890

******8901

******2211

Ans:-

SELECT LPAD(SUBSTR(ACT_NO,LENGTH(ACT_NO)-3),LENGTH(ACT_NO),’*’) FROM DUAL

———————————————————————-

Ques:- Convert number to cahr

Ans:-

select to_char(to_date(999999,’j’),’jsp’) from dual;

range is 1 to 5373484

———————————————————————-

Ques:-

Find Nth Highest Salary

Ans:-

select * from

EMPloyees x

where &no=(

select count(*)

from EMPloyees y

where y.employee_id>=x.employee_id

)

 

 

———————————————————————-

Ques:- Count the no of Male and Female Candidate in a table.

Ans:-

select SUM(case sex when ‘MALE’ then 1

end )”MALE”

,SUM(case sex when ‘FEMALE’ then 1

end)”FEMALE”

from XXC05_TEST3;

———————————————————————-

Ques:- how to insert a column in a table which is already existing in other table

First you add column name in second table then

Run that query.

DECLARE

BEGIN

FOR I IN (SELECT ID,DEPARTMENT_NAME FROM XXC05_T1)

LOOP

UPDATE XXC05_T2 SET NAME = I.DEPARTMENT_NAME

WHERE ID = I.ID;

END LOOP;

COMMIT;

END;

———————————————————————-

QUES:- HOW TO PRINT MAX,MIN,AVG,SUM OF SALARY AND PRINT ALL COLUMN NAME.

ANS:-

SELECT E.*,MAX(SALARY) OVER() MAX_SALARY ,MIN(SALARY) OVER() MIN_SALARY,ROUND(AVG(SALARY) OVER()) AVG_SALARY,SUM(SALARY) OVER() SUM_SALARY FROM EMPLOYEES E

or

SELECT

e.*,

(SELECT MAX(salary)

FROM Employees) MAXSAL,

(SELECT SUM(salary)

FROM Employees) SUMSAL

FROM Employees e;

———————————————————————-

Ques:-

HI FRND HOW TO REPLACE

col

a*b@c#d

o/p

a,b,c,d

ANS:-

select ‘a*b@c#d’,regexp_replace(‘a*b@c#d’, ‘[^0-9a-zA-Z]’, ‘,’ ) from dual;

or

SELECT translate(‘a*b@c#d’,’*@#’,’,,,’) FROM dual

or

select replace (replace (replace (‘a*b@c#d’,’*’,’,’),’@’,’,’),’#’,’,’) N from dual

———————————————————————-

Ques:- Without use rowid delete duplicate record.

ANS:-

select *

from (select e.empno, rank() over(order by e.deptno desc) rk

from scott.emp e)

———————————————————————-

Ques:- How to find greated, smallest number

Table is

c1 c2  c3

1    4    10

2    5    15

10    20    28

Ouput is 28,1

ANS:-

SELECT MAX(GREATEST(C1,C2,C3)) FROM T1;

Or

SELECT GREATEST(MAX(C1),MAX(C2), MAX(C3)) FROM T1

SELECT LEAST(MIN(C1),MIN(C2),MIN(C3)) FROM  T1;

Or

SELECT MIN(LEAST(C1,C2,C3)) FROM T1;

———————————————————————-

QUES:- hi…frd….suppose a number is 2642.how we get output 14 (2+6+4+2) by the use of sql.

ANS:- select sum(s) total from(

select substr(num,level,1) s from(select &g num from dual)

connect by level<=length(num))

 

 

SELECT SUBSTR(7654,1,1)+SUBSTR(7654,2,1)+SUBSTR(7654,3,1)+SUBSTR(7654,4,1) FROM DUAL

 

———————————————————————-

 

QUES:- HOW TO CHACK STRING IS PALINDROM OR NOT

DECLARE

LEN NUMBER;

PALSTR VARCHAR2(20) := ‘&PALSTR’;

CHKSTR VARCHAR2(20);

BEGIN

LEN := LENGTH(PALSTR);

FOR I IN REVERSE 1..LEN LOOP

CHKSTR := CHKSTR||SUBSTR(PALSTR,I,1);

END LOOP;

IF CHKSTR = PALSTR THEN

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS A PALINDROME’);

ELSE

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS NOT A PALINDROME’);

END IF;

END;

 

 

———————————————————————-

 

DIFFERENCE BETWEEN DATASET AND DATATABLE

DataSet:- DataSet is the Collection of DataTables

DataSet can Fetch multiple TablesRows at a time

In DataSet DataTable objects can be related to each other like(primary key, forign key, unique key etc)

 

DataTable:- DataTable is single datbase table

DataTable Fetch data from only one table.

As DataTable is a single database table, so there is no Data relation object in it.

 

———————————————————————-

QUES:-  How to find Those record in which and special symbol.

SELECT * FROM T11

WHERE REGEXP_LIKE(NAME,’%’);

OR

SELECT * FROM T11

WHERE NAME LIKE ‘%\%%’ ESCAPE ‘\’;

———————————————————————-

QUES:- Given String is   11223344556677 or give it at run time

output is:- 11**********77

Ans:-

SELECT RPAD(SUBSTR(&A,1,2),LENGTH(&&A)-2,’*’)||SUBSTR(&&A,-2,2) FROM DUAL

———————————————————————-

Ques:- In table data is

1  SAJAL     MALE

2  AMAL        MALE

3  MEENA    FEMALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

7  NANCY    FEMALE

8  NAVAL    MALE

My requirement is print record alternative of Gender

1  SAJAL     MALE

3  MEENA    FEMALE

2  AMAL        MALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

8  NAVAL    MALE

7  NANCY    FEMALE

ANS:-

select ROW_NUM,id,name,gender  from (

select (rownum*2) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘M’

union

select (rownum*2-1) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘F’

)

order by ROW_NUM

———————————————————————-

QUES:- Print 1 to 100 number in sql

ANS:-

Select Rownum

From dual

Connect By Rownum <= 100

OR

SELECT LEVEL

FROM DUAL

CONNECT BY LEVEL <= 100;

Trigger Example

Trigger:-    Trigger is a pl/sql block or procedure that implicitly execute when some event occur.

Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or shutdown) occur on a schema or database

Trigger Timing
For table:- Before, After
For View:- Instead of

Trigger Event:-
Insert, Update or Delete

Trigger Name:-
On Table, View

Trigger Type:-
Row Level
Statement Level

Trigger Body:-
What Action perform

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

 

 

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENCT OF clause in an UPDATE or DELETE statement states that most recent row fetched from the table should be update or deleted. We must declare the cursor with the CURRENCT OF clause to use this feature.

Oracle provide the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of transactions.

The syntax of using the WHERE CURRENT OF clause is UPDATE OR DELETE statement.

Syntax:-

WHERE[CURRENT OF cursor_name | search_condition]

Example:-

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL
FOR UPDATE OF COMMISSION_PCT;
BEGIN
FOR I IN C1
LOOP
UPDATE EMPLOYEES
SET COMMISSION_PCT= 0.19
WHERE CURRENT OF C1;
END LOOP;
END;

Ref Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but  it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week    ref cursor
Example 1-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_ID,L_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_ID||’ and ‘||’LAST_NAME IS  ‘||L_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_ID,DEPT_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example 2-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example of Strong ref cursor

DECLARE
TYPE XXC_EMP_REF_CUR IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
XXC_EMP_REF_CUR_C1 XXC_EMP_REF_CUR;
TYPE XXC_DEPT_REF_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
XXC_DEPT_REF_CUR_C1 XXC_DEPT_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_EMP_REF_CUR_C1;
OPEN XXC_DEPT_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_DEPT_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_DEPT_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_DEPT_REF_CUR_C1;
END;

SYS Ref Cursor:-

DECLARE
XXC_EMP_REF_CUR SYS_REFCURSOR;
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);
END LOOP;
END;

 

————————- normal corsor ———————
declare
cursor c1 is select * from employees;
v1 employees%rowtype;
begin
open c1;
for i in 1..3
loop
fetch c1 into v1;
dbms_output.put_line(v1.employee_id);
end loop;
close c1;
end;

—————– week ref cursor ——————–
declare
type week_ref_cursor is ref cursor;
wrf week_ref_cursor;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open wrf for select * from employees;
loop
fetch wrf into v1;
exit when wrf%notfound;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
close wrf;
open wrf for select * from departments;
loop
fetch wrf into v2;
exit when wrf%notfound;
dbms_output.put_line(v2.department_id||chr(9)||v2.department_name);
end loop;
close wrf;
end;

——————- strong ref cursor ————–

declare
type strong_ref_cursor is ref cursor return employees%rowtype;
src strong_ref_cursor;
v1 employees%rowtype;
begin
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id);
end loop;
close src;
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id||’    ‘||v1.last_name);
end loop;
close src;
end;

 

***************************************REF CURSOR *******************************

declare
emp_id number;
emp_name varchar2(20);
type test_cur is ref cursor;
v_ref test_cur;
begin
if(no=1)then
open v_ref for select employee_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
elsif(no=2)then
open v_ref for select department_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
else
open v_ref for select last_name from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_name;
dbms_output.put_line((emp_name));
end loop;
close v_ref;
end;
/

***************************************************** PARAMETERIZED CURSOR *************************************

declare
cursor c1(no number)is select employee_id from employees where employee_id<no;
id number;
begin
open c1(198);
for i in 1..10
loop
fetch c1 into id;
dbms_output.put_line(id);
end loop;
close c1;
end;

declare
type p1 is ref cursor;
t1 p1;
v1 varchar2(10);
begin
open t1 for select employee_id from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
open t1 for select last_name from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
end;
/

create or replace procedure p1 is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name);
end loop;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.salary||chr(9)||v1.manager_id);
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.manager_id);
end loop;
end if;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
type v1_table is table of employees%rowtype;
v1 v1_table;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).employee_id||chr(9)||v1(i).last_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id||chr(9)||v1(i).first_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
end if;
end;
/

 

Thanks

Sajal Agarwal

Set operators with examples

Set Operator:-
Set Operators combine the result of two or more component queries into a single result. Queries containing set operators are called compound queries.

1. UNION
2. MINUS
3. UNION ALL
4. INTERSECT

Table EMP

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000

Table EMP_DETAIL

ID    NAME    SALARY
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000

UNION:-
All distinct rows selected by either query. In UNION operator data is sorted in ascending order according to first column of first query.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
2    NAVAL    10000
3    SEEMA    50000
4    SUSHIL    90000
4    ANKIT    60000
5   AYUSH    30000

MINUS:-
All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
minus
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
2    SAJAL    20000
4    SUSHIL    90000

UNION ALL:-
All rows selected by either queries, including all duplicates.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union all
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000

INTERSECT:-
All distinct rows selected by both queries.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
intersect
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
3    SEEMA    50000