SQL> SELECT * FROM EMP;
ID NAME DOJ EMAIL
---------- --------------- --------- ------------------------------
1 KAMLESH 07-MAY-15 kamlesh.mishra@aol.com
2 RANJIT 11-DEC-11 kamlesh.mishra@aol.com
3 RAJESH 11-FEB-01 kamlesh.mishra@aol.com
4 SHIVAM 01-FEB-21 kamlesh.mishra@aol.com
SELECT NTH RECORD FROM TABLE.
SQL> SELECT * FROM EMP WHERE ROWNUM<=3
2 MINUS
3 SELECT * FROM EMP WHERE ROWNUM<3;
ID NAME DOJ EMAIL
---------- --------------- --------- ------------------------------
3 RAJESH 11-FEB-01 kamlesh.mishra@aol.com
JOINS : TO FETCH DATA FROM FROM MULTIPLE TABLE BASED ON THE GIVEN CONDITION
EQUI JOIN :
SQL> SELECT * FROM EMP;
ID NAME DOJ DNO
---------- --------------- --------- ----------
1 KAMLESH 07-MAY-15 10
2 RANJIT 11-DEC-11 30
3 RAJESH 11-FEB-01 20
4 SHIVAM 01-FEB-21 10
SQL> SELECT * FROM DEPARTMENT;
DNO DNAME
---------- ------------
10 HR
20 BPO
SQL> SELECT ID,NAME,DOJ,DEPARTMENT.DNO,DEPARTMENT.DNAME FROM EMP,DEPARTMENT WHERE EMP.DNO=DEPARTMENT.DNO;
ID NAME DOJ DNO DNAME
---------- --------------- --------- ---------- ------------
1 KAMLESH 07-MAY-15 10 HR
3 RAJESH 11-FEB-01 20 BPO
4 SHIVAM 01-FEB-21 10 HR
NON EQUI JOIN
SQL> SELECT * FROM EMP;
ID NAME DOJ DNO SALARY
---------- --------------- --------- ---------- ----------
1 KAMLESH 07-MAY-15 10 1500
2 RANJIT 11-DEC-11 30 1200
3 RAJESH 11-FEB-01 20 1100
4 SHIVAM 01-FEB-21 10 2100
SQL> SELECT * FROM SALARY;
SALARY GRADE
---------- -----
500 D
1000 C
1500 B
2000 A
SQL> SELECT ID,NAME,DOJ,DNO,SALARY.SALARY,SALARY.GRADE FROM EMP,SALARY WHERE EMP.SALARY>SALARY.SALARY;
ID NAME DOJ DNO SALARY GRADE
---------- --------------- --------- ---------- ---------- -----
1 KAMLESH 07-MAY-15 10 500 D
1 KAMLESH 07-MAY-15 10 1000 C
2 RANJIT 11-DEC-11 30 500 D
2 RANJIT 11-DEC-11 30 1000 C
3 RAJESH 11-FEB-01 20 500 D
3 RAJESH 11-FEB-01 20 1000 C
4 SHIVAM 01-FEB-21 10 500 D
4 SHIVAM 01-FEB-21 10 1000 C
4 SHIVAM 01-FEB-21 10 1500 B
4 SHIVAM 01-FEB-21 10 2000 A
10 rows selected.
OUTER JOIN :
SQL> SELECT * FROM EMP;
ID NAME DOJ DNO SALARY
---------- --------------- --------- ---------- ----------
1 KAMLESH 07-MAY-15 10 1500
2 RANJIT 11-DEC-11 30 1200
3 RAJESH 11-FEB-01 20 1100
4 SHIVAM 01-FEB-21 10 2100
SQL> SELECT * FROM DEPARTMENT;
DNO DNAME
---------- ------------
10 HR
20 BPO
LEFT OUTER JOIN : FETCHS NON MATCH DATA FROM LEFT TABLE .
SQL> SELECT ID,NAME,DOJ,SALARY,DEPARTMENT.DNO,DEPARTMENT.DNAME FROM EMP,DEPARTMENT WHERE EMP.DNO=DEPARTMENT.DNO(+);
ID NAME DOJ SALARY DNO DNAME
---------- --------------- --------- ---------- ---------- ------------
4 SHIVAM 01-FEB-21 2100 10 HR
1 KAMLESH 07-MAY-15 1500 10 HR
3 RAJESH 11-FEB-01 1100 20 BPO
2 RANJIT 11-DEC-11 1200
RIGHT OUTER JOIN :
SQL> SELECT ID,NAME,DOJ,SALARY,DEPARTMENT.DNO,DEPARTMENT.DNAME FROM EMP,DEPARTMENT WHERE EMP.DNO(+)=DEPARTMENT.DNO;
ID NAME DOJ SALARY DNO DNAME
---------- --------------- --------- ---------- ---------- ------------
1 KAMLESH 07-MAY-15 1500 10 HR
3 RAJESH 11-FEB-01 1100 20 BPO
4 SHIVAM 01-FEB-21 2100 10 HR
FULL OUTER JOIN
SQL> SELECT ID,NAME,DOJ,SALARY,DEPARTMENT.DNO,DEPARTMENT.DNAME FROM EMP,DEPARTMENT WHERE EMP.DNO=DEPARTMENT.DNO(+)
2 UNION
3 SELECT ID,NAME,DOJ,SALARY,DEPARTMENT.DNO,DEPARTMENT.DNAME FROM EMP,DEPARTMENT WHERE EMP.DNO(+)=DEPARTMENT.DNO;
ID NAME DOJ SALARY DNO DNAME
---------- --------------- --------- ---------- ---------- ------------
1 KAMLESH 07-MAY-15 1500 10 HR
2 RANJIT 11-DEC-11 1200
3 RAJESH 11-FEB-01 1100 20 BPO
4 SHIVAM 01-FEB-21 2100 10 HR
SELF JOIN :
SQL> SELECT * FROM EMP;
NAME NO MGR_ID
---------- ---------- ----------
KAMLESH 1 4
RAJESH 2 3
SUNIL 3 1
ANJAY 4 2
SQL> SELECT E.NAME,E.NO,E.MGR_ID,E1.NAME AS MANAGER_NAME FROM EMP E,EMP E1 WHERE E.NO=E1.MGR_ID;
NAME NO MGR_ID MANAGER_NA
---------- ---------- ---------- ----------
ANJAY 4 2 KAMLESH
SUNIL 3 1 RAJESH
KAMLESH 1 4 SUNIL
RAJESH 2 3 ANJAY
CROSS JOIN :
SQL> SELECT * FROM EMP;
NAME NO MGR_ID
---------- ---------- ----------
KAMLESH 1 4
RAJESH 2 3
SUNIL 3 1
ANJAY 4 2
SQL> SELECT * FROM DEPARTMENT;
DNO DNAME
---------- ------------
10 HR
20 BPO
SQL> SELECT NAME,NO,MGR_ID ,DNO,DNAME FROM EMP,DEPARTMENT;
NAME NO MGR_ID DNO DNAME
---------- ---------- ---------- ---------- ------------
KAMLESH 1 4 10 HR
RAJESH 2 3 10 HR
SUNIL 3 1 10 HR
ANJAY 4 2 10 HR
KAMLESH 1 4 20 BPO
RAJESH 2 3 20 BPO
SUNIL 3 1 20 BPO
ANJAY 4 2 20 BPO
8 rows selected.
0 comments:
Post a Comment