OPERATORS:
**********
-->Arithemetic Operators
-->Logical Operators
-->Relational Operators
-->Special Operators
-->Set Operators
1)Arithemetic Operators(+,-,*,/):
*********************************
-->It is used to do the mathematical functions(Arithmetical calculations) like addition,subtraction,multiplication and division.
What is DUAL:
*************
--> Pre-Define table in oracle.
--> It is having single row and single column.
--> It is called as dummy table in oracle.
--> Testing Functions(PRE-DEFINE & USER DEFINE) Functionalities.
TO VIEW STRCTURE.OF DUAL TABLE:
*******************************
DESC DUAL;
TO VIEW DATA OF DUAL TABLE:
***************************
SELECT * FROM DUAL;
EX:
***
SELECT 2+8 FROM DUAL;
SELECT 40+50 FROM DUAL;
SELECT 8-6 FROM DUAL;
SELECT 2*9 FROM DUAL;
SELECT 100/10 FROM DUAL;
select 2+2 addi,5-3 sub,2*8 mul, 50/5 div from dual;
SELECT SAL,SAL*12 MULTIPLICATION,SAL+10 ADDITION,SAL-100 SUB
FROM EMP;
NVL (expr1, expr2):
*******************
-->It assgns the value to the column if it is null.
-->The NVL() function accepts two arguments. If e1 evaluates to null, then NVL() function returns e2.
-->If e1 evaluates to non-null, the NVL() function returns e1.
SELECT SAL,COMM,NVL(COMM,0),SAL+COMM,SAL+NVL(COMM,0)
FROM EMP;
SAL COMM SAL+COMM SAL+NVL(COMM,0)
5000 NULL 5000+NULL=NULL 5000+0=5000
1000 NULL 1000+NULL=NULL 1000+0=1000
2000 NULL 2000+NULL=NULL 2000+0=2000
3000 NULL 3000+NULL=NULL 3000+0=3000
1600 300 1600+300=1900 1900
600 200 600+200=800 800
800 0 800+0=800 800
3)Relational Operators:(=,(!=or <>),<,<=,>,>=)
****************************************
EX:
***
SELECT * FROM EMP WHERE DEPTNO<=20;
SELECT * FROM EMP WHERE SAL>2000;
SELECT * FROM EMP WHERE SAL=2000;
SELECT * FROM EMP WHERE DEPTNO!=10;
/
=
!= OR <>
<
<=
>
>=
EX:
***
SELECT *
FROM EMP
WHERE DEPTNO=30;
SELECT *
FROM EMP
WHERE DEPTNO!=10;
/
SELECT *
FROM EMP
WHERE DEPTNO<>10;
/
SELECT *
FROM EMP
WHERE SAL<3000;
/
SELECT *
FROM EMP
WHERE SAL<=3000;
/
SELECT *
FROM EMP
WHERE SAL>1600;
/
SELECT *
FROM EMP
WHERE SAL>=1600;
SELECT UNIT_PRICE
FROM PO_LINES_ALL
WHERE UNIT_PRICE>2000;
/
SELECT *
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE='RFQ';
/
SELECT INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE='EUR';
/
SELECT *
FROM EMP
WHERE EMPNO=7782;
/
SELECT *
FROM EMP
WHERE EMPNO!=7782;
/
SELECT *
FROM EMP
WHERE EMPNO<>7782;
/
SELECT *
FROM EMP
WHERE JOB='SALESMAN';
/
SELECT *
FROM EMP
WHERE JOB!='SALESMAN';
/
SELECT *
FROM EMP
WHERE SAL<3000;
/
SELECT *
FROM EMP
WHERE SAL<=3000;
/
SELECT *
FROM EMP
WHERE SAL>1500;
/
SELECT *
FROM EMP
WHERE SAL>=1500;
/
SELECT PAYMENT_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE PAYMENT_CURRENCY_CODE='USD';
/
SELECT PAYMENT_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE PAYMENT_CURRENCY_CODE<>'USD';
/
SELECT INVOICE_AMOUNT
FROM AP_INVOICES_ALL
WHERE INVOICE_AMOUNT<=5000;
/
2)Logical Operartors:
*********************
AND OPERATOR:
*************
-->If both the conditions are true then it returns the result as TRUE.
-->If both the conditions or if any one condition is false then it returns the result as FALSE.
EX:
***
A B RESULT
T T T
T F F
F T F
F F F
-->Display the details of employees whose salary is greater than 2000 and also whose salary is lessthan 5000.
SELECT * FROM EMP WHERE SAL>2000 AND SAL<5000;
-->Display the details of employees whose deptno is 10 and also whose job is CLERK
SELECT * FROM EMP WHERE DEPTNO=10 AND JOB='CLERK';
-->Display the details of employees whose job is SALESMAN and also whose SAL is greater than 1200.
select * from emp where job = 'SALESMAN' and sal > 1200;
/
SELECT *
FROM EMP
WHERE DEPTNO=10
AND SAL=1300;
/
SELECT *
FROM EMP
WHERE JOB='SALESMAN'
AND SAL=1250;
/
SELECT *
FROM EMP
WHERE JOB='SALESMAN'
AND SAL=1250
AND COMM=500;
/
OR OPERATOR:
************
-->Atleast one condition is true then it returns the result as TRUE.
-->If both the conditions are TRUE then it returns the result as TRUE
-->If both the conditions are FALSE then it returns the result as FALSE.
A B RESULT
T T T
T F T
F T T
F F F
EX:
***
SELECT * FROM EMP WHERE JOB='MANAGER' OR DEPTNO=20;
SELECT * FROM EMP WHERE DEPTNO=10 OR DEPTNO=20;
select * from emp where (job='CLERK' or job='SALESMAN' or job='ANALYST');
SELECT ENAME FROM EMP WHERE JOB='CLERK' OR JOB='SALESMAN';
/
SELECT *
FROM EMP
WHERE DEPTNO=30 OR SAL<3000
/
SELECT *
FROM EMP
WHERE JOB='SALESMAN' OR DEPTNO=10;
/
SELECT *
FROM EMP
WHERE ENAME='CLARK' OR SAL>2000;
/
SELECT TYPE_LOOKUP_CODE,SEGMENT1 PO_NUM
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE='STANDARD'
OR SEGMENT1=500;
/
SELECT *
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID=39441 OR ORDER_NUMBER=4721;
/
SELECT *
FROM EMP
WHERE DEPTNO=10
OR SAL=1300;
/
SELECT *
FROM EMP
WHERE DEPTNO=10
OR SAL>1300;
/
SELECT *
FROM EMP
WHERE JOB='SALESMAN'
OR DEPTNO=20;
/
SELECT *
FROM EMP
WHERE DEPTNO=40 OR JOB='ABC';
NOT OPERATOR:
*************
>Specified condition must be false.
EX:
***
SELECT * FROM EMP WHERE NOT DEPTNO=20;
SELECT * FROM EMP WHERE NOT ENAME='KING';
SELECT * FROM EMP WHERE NOT EMPNO=7782;
SELECT * FROM EMP WHERE DEPTNO!=10;
SELECT * FROM EMP WHERE DEPTNO!=10 AND JOB!='MANAGER';
/
SELECT *
FROM EMP
WHERE NOT DEPTNO=20;
/
SELECT *
FROM EMP
WHERE NOT JOB='MANAGER';
/
SELECT *
FROM EMP
WHERE JOB!='MANAGER';
/
SELECT *
FROM EMP
WHERE JOB<>'MANAGER';
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE NOT TYPE_LOOKUP_CODE='STANDARD';
/
SELECT *
FROM EMP
WHERE NOT DEPTNO=10;
/
SELECT *
FROM EMP
WHERE NOT JOB='SALESMAN';
/
SELECT *
FROM PO_HEADERS_ALL
WHERE NOT TYPE_LOOKUP_CODE='STANDARD';
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE !='STANDARD';
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE <>'STANDARD';
/
4)Special Operators(In,Like,Between,Is NULL):
****************************************
-->Special operators are used to frame the conditions while retrieving or manipulating data.
-->To improve the performance.
IN/NOT IN:
**********
-->It is used to pick the values with in the given list.
-->It returns true if value is available in given list of values.
-->It supports with all types of data.
EX:
****
SELECT * FROM EMP WHERE DEPTNO IN (10,20,30);
SELECT * FROM EMP WHERE JOB IN ('CLERK','SALESMAN');
SELECT * FROM EMP WHERE SAL IN (2000,3000,850);
SELECT * FROM EMP WHERE DEPTNO IN (10,20,30)
SELECT * FROM EMP WHERE ENAME IN ('KING','BLAKE','CLARK');
SELECT * FROM EMP WHERE HIREDATE IN ('17-NOV-1981','01-MAY-1981','09-JUN-1981') AND DEPTNO IN (10,30) AND JOB='MANAGER';
/
SELECT *
FROM EMP
WHERE EMPNO IN (7839,7698,100);
/
SELECT *
FROM EMP
WHERE SAL IN (3000,1600);
/
SELECT *
FROM EMP
WHERE HIREDATE IN ('17-NOV-1981','01-MAY-1981');
/
SELECT INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE IN ('USD','EUR');
/
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID IN (1001,1503);
/
SELECT *
FROM EMP
WHERE JOB NOT IN ('SALESMAN','CLERK');
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE NOT IN ('STANDARD','RFQ');
/
SELECT *
FROM EMP
WHERE ENAME IN ('MILLER');
/
SELECT *
FROM EMP
WHERE ENAME IN ('123','7839','7782');
/
SELECT *
FROM EMP
WHERE DEPTNO IN (10,20,40,50);
/
SELECT *
FROM EMP
WHERE JOB IN ('SALESMAN','MANAGER','CLERK');
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE IN ('RFQ','QUOTATION');
/
SELECT INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE IN ('USD','EUR');
/
SELECT *
FROM EMP
WHERE ENAME NOT IN ('123','7839','7782');
/
SELECT *
FROM EMP
WHERE DEPTNO IN (10,20,40,50);
/
SELECT *
FROM EMP
WHERE JOB NOT IN ('SALESMAN','MANAGER','CLERK');
/
SELECT TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL
WHERE TYPE_LOOKUP_CODE NOT IN ('RFQ','QUOTATION');
/
SELECT INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE NOT IN ('USD','EUR');
/
SELECT *
FROM EMP1;
/
CREATE TABLE EMP1
AS
SELECT *
FROM EMP;
/
UPDATE EMP1
SET SAL=SAL+10000
WHERE EMPNO IN (7698,7782);
/
SELECT *
FROM EMP1;
/
DELETE FROM EMP1 WHERE EMPNO IN (7782,7698);
/
SEELCT * FROM EMP1;
/
UPDATE EMP1
SET SAL=SAL+10000
WHERE EMPNO NOT IN (7566,7788);
/
SELECT *
FROM EMP1;
/
DELETE FROM EMP1 WHERE EMPNO NOT IN (7566,7698);
/
SEELCT * FROM EMP1;
/
DML operations:
***************
CREATE TABLE EMP_BKP AS SELECT * FROM EMP;
/
SELECT *
FROM EMP_BKP
WHERE EMPNO IN (7839,7698);
/
UPDATE EMP_BKP
SET SAL=10000
WHERE EMPNO IN (7839,7698);
/
SELECT * FROM EMP_BKP;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
UPDATE EMP1 SET SAL=SAL+100 WHERE ENAME IN ('KING','SCOTT');
DELETE FROM EMP1 WHERE DEPTNO IN (10,20);
/
DELETE FROM EMP_BKP
WHERE EMPNO IN (7839,7698);
/
SELECT *
FROM EMP_BKP;
/
NOT IN:
********
-->NOT IN operator is quite opposite to IN operator.
SELECT * FROM EMP1 WHERE ENAME NOT IN ('KING','SCOTT');
SELECT * FROM EMP1 WHERE EMPNO NOT IN(712,7369,7782);
UPDATE EMP1 SET SAL=SAL+200 WHERE EMPNO NOT IN (7788,7902);
DELETE FROM EMP1 WHERE ENAME NOT IN ('KING','BLAKE');
BETWEEN/NOT BETWEEN
********************
-->It is used to pick the values with in the specified range.
-->Returns true if value specified is with in the specified range.
-->It supports with numbers and date values.
EX:
***
-->All employee records are fetched whose salary is between 1000 and 2000.
1)SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
Whenever lower bound value is larger than upper bound then it shows "no rows selected".
2)SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 1000;
3)SELECT * FROM EMP WHERE JOB BETWEEN 'MANAGER' AND 'SALESMAN'; --Improper data
4)SELECT ENAME,JOB,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN '17-DEC-81' AND '20-JUN-83';
/
SELECT *
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000;
/
01-JAN-1980 TO 31-DEC-1982
/
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN '01-JAN-1980' AND '31-DEC-1982';
/
SELECT INVOICE_AMOUNT
FROM AP_INVOICES_ALL
WHERE INVOICE_AMOUNT BETWEEN 1000 AND 100000;
/
SELECT INVOICE_DATE
FROM AP_INVOICES_ALL
WHERE INVOICE_DATE BETWEEN '01-JAN-2000' AND '31-DEC-2006';
/
5)UPDATE EMP_BKP SET SAL=SAL+1000 WHERE HIREDATE BETWEEN '30-jun-1981' AND '31-dec-1981';
/
SELECT *
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000;
/
SELECT *
FROM EMP
WHERE SAL>=1000 AND SAL<=3000;
/
SELECT *
FROM EMP
WHERE SAL>=1000 AND SAL<3000;
/
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN '01-JAN-1980' AND '31-DEC-1982';
/
SELECT *
FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 3000;
/
SELECT *
FROM EMP
WHERE SAL<1000 OR SAL>3000;
/
SELECT *
FROM EMP
WHERE HIREDATE NOT BETWEEN '01-JAN-1980' AND '31-DEC-1982';
NOT BETWEEN:
************
-->Returns TRUE if value specified is not within the specified range.
-->It supports numbers and date values
1)SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000;
2)UPDATE EMP1 SET SAL=SAL+1000 WHERE HIREDATE NOT BETWEEN '30-jun-1981' AND '31-dec-1981';
LIKE/NOT LIKE:
**************
-->It is used to search for a pattern in given input supports with character data only.
-->It uses two specal characters.
a) % represents zero or more characters.
b)_(Underscore) -- It represents one character only.
SYNTAX:
*******
SELECT * FROM <TABLENAME> WHERE <COLUMNNAME> LIKE '<VALUE>'
EX:
***
-->List the employees whose name start with 'A'
1)SELECT * FROM EMP WHERE ENAME LIKE 'A%';
-->List the employees whose name END with 'S'
2)SELECT * FROM EMP WHERE ENAME LIKE '%S';
-->List the employees whose name got 5 letters.
3)SELECT * FROM EMP WHERE ENAME LIKE '_____';
select ename from emp where length(ename)=5;
-->List the employees whose name got 'LL'
4)SELECT * FROM EMP WHERE ENAME LIKE '%LL%';
-->Display the employees whise names are having second letter as 'L' in EMP table
5)SELECT * FROM EMP WHERE ENAME LIKE '_L%';
6)display all those employees whose names are starting with J and Ending with S?
SELECT * FROM emp WHERE ename LIKE 'J%S';
7)display all those employees whose names contain A.
SELECT * FROM emp WHERE ename LIKE '%A%';
8)display ename,sal of all those employees whose salary ends with 00?
SELECT ename,sal FROM emp WHERE sal LIKE '%00';
9)display ename,job,sal,hiredate of all those employees who have been hired in those months which starts with A?
SELECT ename,job,sal,hiredate FROM emp WHERE hiredate LIKE '___A%';
10)display ename,job,sal of all those employees whose names contain A and salary ends with 00?
SELECT ename,job,sal FROM emp WHERE ename LIKE '%A%' AND sal LIKE '%00';
11)SELECT * FROM EMP WHERE HIREDATE LIKE '%JAN%';
12)SELECT * FROM EMP WHERE JOB LIKE '_____';
/
--List the OBJECTS whose name start with 'C'
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'C%'
/
--List the OBJECTS whose name end with 'S'
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%S';
/
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%A%';
/
NOT LIKE OPERATOR:
******************
SYNTAX:
*********
SELECT * FROM <TABLENAME> WHERE <COLUMNNAME> NOT LIKE '<VALUE>';
EX:
***
SELECT * FROM EMP WHERE ENAME NOT LIKE '%S';
SELECT * FROM EMP WHERE ENAME NOT LIKE '_L%';
SELECT * FROM EMP WHERE ENAME NOT LIKE 'S%';
SELECT * FROM EMP WHERE HIREDATE NOT LIKE '%JAN%';
SELECT * FROM EMP WHERE ENAME LIKE '%R_';
SELECT * FROM EMP WHERE ENAME NOT LIKE '%LL%';
/
SELECT *
FROM EMP
WHERE ENAME LIKE 'A%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'A%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'S%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '%A';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '%S';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '%A%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'A%S';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'AD%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'S%S';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '___A%';
/
SELECT *
FROM EMP
WHERE ENAME LIKE '____';
/
SELECT *
FROM EMP
WHERE ENAME LIKE '_____';
/
SELECT *
FROM EMP
WHERE ENAME LIKE 'A_%';
/
SELECT *
FROM EMP
WHERE ENAME NOT LIKE 'A_%';
/
SELECT *
FROM EMP
WHERE ENAME NOT LIKE 'A%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME NOT LIKE 'A%';
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME NOT LIKE 'S%';
/
IS NULL/IS NOT NULL:
*********************
-->It is used to compare null values.
-->It is used to search for NULL values in the given input.
-->Supports with all types of data.
SYNTAX:
*******
SELECT * FROM <TABLENAME> WHERE <mCOLUMNNAME> IS NULL;
EX:
****
-->List the employees who are not having comission.
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE SAL IS NULL; --NOW ROWS SELECTED
IS NOT NULL:
************
-->Used to search for NOT NULL values in the in the given input.
-->It supports with all types of data.
SYNTAX:
*******
SELECT * FROM <TABLENAME> WHERE <COLUMNAME> IS NOT NULL;
EX:
*****
-->List the employees who are having comission.
SELECT * FROM EMP WHERE COMM IS NOT NULL;
SELECT * FROM EMP WHERE SAL IS NOT NULL;
SELECT * FROM EMP WHERE MGR IS NOT NULL;
/
SELECT *
FROM EMP
WHERE COMM IS NULL;
/
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;
/
SELECT REVISED_DATE
FROM PO_HEADERS_ALL
WHERE REVISED_DATE IS NOT NULL;
/
SELECT REVISED_DATE
FROM PO_HEADERS_ALL
WHERE REVISED_DATE IS NULL;
/
SELECT DESCRIPTION
FROM AP_INVOICES_ALL
WHERE DESCRIPTION IS NOT NULL;
/
SELECT *
FROM EMP
WHERE MGR IS NOT NULL;
SET OPERATORS:
**************
-->It is used to join the outputs of select statements based on the operator specified.
-->By using set operators we join more than one query such queries are called compound queries.
-->Set operators are used to combine the result of two or more queries into a single result.
-->Select statements must have equal number of columns and similar data type columns but must not be same size.
-->Maximum 32 queries can be joined with set operators.
RULES:
*******
-->NO.OF.COLUMNS should same with in both select statements.
-->Order of the columns should be same
-->Data types of the columns must be match.
1)UNION ALL
2)UNION
3)INTERSECT
4)MINUS
S1={A,B,C,D}
S2={X,Y,Z,B,C}
1)O/P={A,B,C,D,X,Y,Z,B,C} --UNION ALL
2)O/P={A,B,C,D,X,Y,Z} --UNION
3)O/P={B,C} --INTERSECT
4)O/P={A,D} --MINUS
CREATE TABLE XX_EMP_SET_OPR
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER
);
/
INSERT INTO XX_EMP_SET_OPR VALUES (100,'A',1000);
/
INSERT INTO XX_EMP_SET_OPR VALUES (101,'B',2000);
/
INSERT INTO XX_EMP_SET_OPR VALUES (102,'C',3000);
/
INSERT INTO XX_EMP_SET_OPR VALUES (103,'D',4000);
/
INSERT INTO XX_EMP_SET_OPR VALUES (109,'Z',8000);
/
COMMIT;
/
SELECT * FROM XX_EMP_SET_OPR;
/
CREATE TABLE XX_EMP_SET_OPR_1
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER
);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (100,'A',1000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (101,'B',2000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (102,'C',3000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (103,'D',4000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (104,'E',5000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (105,'F',6000);
/
INSERT INTO XX_EMP_SET_OPR_1 VALUES (106,'G',7000);
/
COMMIT;
/
SELECT * FROM XX_EMP_SET_OPR_1;
/
CREATE TABLE XX_EMP_SET_OPR_2
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER
);
/
INSERT INTO XX_EMP_SET_OPR_2 VALUES (110,'AA',1000);
/
INSERT INTO XX_EMP_SET_OPR_2 VALUES (111,'BB',2000);
/
INSERT INTO XX_EMP_SET_OPR_2 VALUES (112,'CC',3000);
/
INSERT INTO XX_EMP_SET_OPR_2 VALUES (113,'DD',4000);
/
INSERT INTO XX_EMP_SET_OPR_2 VALUES (114,'EE',5000);
/
COMMIT;
/
CREATE TABLE XX_SD1
(
SNO NUMBER,
SNAME VARCHAR2(30)
);
/
CREATE TABLE XX_SD2
(
SNO NUMBER,
SNAME VARCHAR2(30)
);
/
INSERT INTO XX_SD1 VALUES(100,'MOHAN');
/
INSERT INTO XX_SD1 VALUES(101,'RAVI');
/
INSERT INTO XX_SD1 VALUES(102,'AJAY');
/
INSERT INTO XX_SD1 VALUES(103,'RANI');
/
COMMIT;
/
SELECT * FROM XX_SD1;
/
INSERT INTO XX_SD2 VALUES(100,'MOHAN');
/
INSERT INTO XX_SD2 VALUES(101,'RAVI');
/
INSERT INTO XX_SD2 VALUES(105,'JHON');
/
INSERT INTO XX_SD2 VALUES(103,'RANI');
/
INSERT INTO XX_SD2 VALUES(104,'VINOD');
/
COMMIT;
/
SELECT * FROM XX_SD2;
/
UNION ALL:
**********
-->It displays all the values along with duplicate values also.
--->Two queries must have the equal number of columns.
/
EX
****
/
SELECT *
FROM XX_EMP_SET_OPR
UNION ALL
SELECT *
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_SD1
UNION ALL
SELECT *
FROM XX_SD2;
/
SELECT *
FROM EMP
UNION ALL
SELECT *
FROM EMP;
/
SELECT JOB FROM EMP WHERE DEPTNO=10
UNION ALL
SELECT JOB FROM EMP WHERE DEPTNO=20;
/
UNION:
*****
-->It is used to combine the results of two or more SELECT statements.
-->However it will eliminate duplicate rows from its resultset.
-->In case of union, number of columns and datatype must be same in both the tables
EX:
****
/
SELECT *
FROM XX_EMP_SET_OPR
UNION
SELECT *
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_EMP_SET_OPR_1
UNION
SELECT *
FROM XX_EMP_SET_OPR;
/
SELECT *
FROM XX_SD1
UNION
SELECT *
FROM XX_SD2;
/
SELECT *
FROM EMP
UNION
SELECT *
FROM EMP;
/
SELECT JOB FROM EMP WHERE DEPTNO=10
UNION
SELECT JOB FROM EMP WHERE DEPTNO=20;
INTERSECT:
**********
-->Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements.
-->In case of Intersect the number of columns and datatype must be same.
EX:
***
/
SELECT *
FROM XX_EMP_SET_OPR
INTERSECT
SELECT *
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_EMP_SET_OPR_1
INTERSECT
SELECT *
FROM XX_EMP_SET_OPR;
/
SELECT *
FROM XX_SD1
INTERSECT
SELECT *
FROM XX_SD2;
/
SELECT JOB FROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO=20;
MINUS:
******
-->The Minus operation combines results of two SELECT statements and return only those in the final result,
which belongs to the first set of the result.
EX:
***
/
SELECT *
FROM XX_EMP_SET_OPR
MINUS
SELECT *
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_EMP_SET_OPR_1
MINUS
SELECT *
FROM XX_EMP_SET_OPR;
/
SELECT *
FROM XX_SD1
MINUS
SELECT *
FROM XX_SD2;
/
SELECT JOB FROM EMP WHERE DEPTNO=10
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=20;
/
ALTER TABLE XX_SD1 ADD JOB VARCHAR2(30);
/
UPDATE XX_SD1
SET JOB='ABC';
/
COMMIT;
/
SELECT SNO,SNAME,JOB
FROM XX_SD1
UNION ALL
SELECT SNO,SNAME
FROM XX_SD2;
--IT WILL THROW THE ERROR
SELECT SNO,SNAME,JOB
FROM XX_SD1
UNION ALL
SELECT SNO,SNAME,NULL JOB
FROM XX_SD2;
/
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,NULL DNAME,NULL LOC
FROM EMP
UNION
SELECT NULL EMPNO,NULL ENAME,NULL JOB,NULL MGR,NULL HIREDATE,NULL SAL,NULL COMM,DEPTNO,DNAME,LOC
FROM DEPT;
/
SELECT * FROM EMP;
/
SELECT * FROM DEPT;
/
SET OPERATORS RULES:
********************
DESC XX_EMP_SET_OPR;
/
ALTER TABLE XX_EMP_SET_OPR ADD MOBILE NUMBER;
/
---NO.OF.COLUMNS should same with in both select statements.
SELECT *
FROM XX_EMP_SET_OPR
UNION
SELECT *
FROM XX_EMP_SET_OPR_1;
--IT CAN THROW THE ERROR
/
--Order of the columns should be same
SELECT EMPNO,ENAME,SAL,MOBILE
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL
FROM XX_EMP_SET_OPR_1;
--IT CAN THROW THE ERROR
/
SELECT EMPNO,ENAME,SAL
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL
FROM XX_EMP_SET_OPR_1;
--GENERATES THE OUTPUT
/
--Data types of the columns must be match.
SELECT EMPNO,ENAME,SAL
FROM XX_EMP_SET_OPR
UNION
SELECT ENAME,EMPNO,SAL
FROM XX_EMP_SET_OPR_1;
--IT CAN THROW THE ERROR
/
ALTER TABLE XX_EMP_SET_OPR_1 ADD EMAIL VARCHAR2(50);
/
SELECT EMPNO,ENAME,SAL,MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,EMAIL
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_EMP_SET_OPR;
/
SELECT *
FROM XX_EMP_SET_OPR_1;
/
SELECT *
FROM XX_EMP_SET_OPR_2;
/
SELECT EMPNO,ENAME,SAL,MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,EMAIL
FROM XX_EMP_SET_OPR_1
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR_2
/
UPDATE XX_EMP_SET_OPR
SET MOBILE='1234567890';
/
COMMIT;
/
UPDATE XX_EMP_SET_OPR_1
SET EMAIL='ABC@GMAIL.COM';
/
COMMIT;
/
SELECT EMPNO,ENAME,SAL,MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,EMAIL
FROM XX_EMP_SET_OPR_1;
/
XX_EMP_SET_OPR --5 ROWS -- 4 COLUMNS
XX_EMP_SET_OPR_1 --7 ROWS --4 COLUMNS
XX_EMP_SET_OPR_2 --5 ROWS --3 COLUMNS
/
SELECT EMPNO,ENAME,SAL,MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,EMAIL
FROM XX_EMP_SET_OPR_1
UNION
SELECT EMPNO,ENAME,SAL,NULL MOBILE,NULL EMAIL
FROM XX_EMP_SET_OPR_2
/
No comments:
Post a Comment