JOINS PRACTICE SAMPLES

TABLE 1 : NAME -- EMP66: 


CREATE TABLE EMP66
(
EMP_ID NUMBER(2) CONSTRAINT EMP_COLL_PK PRIMARY KEY,
EMP_NAME VARCHAR(20),
EMP_SALARY NUMBER(5)
);

--------------------------------------------------------------------------------------------------------------------------


TABLE 2 : NAME -- DEPT66: 

CREATE TABLE DEPT66
(
DEPT_ID NUMBER(2) CONSTRAINT DEPT_COL1_PK PRIMARY KEY,
DEPT_NAME VARCHAR(20),
EMP_ID CONSTRAINT DEPT_COL2_FK REFERENCES EMP66(EMP_ID)
);

_________________________________________________________________________________

INSERT VALUES  INTO EMP66:


INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(1,'STEVE',20000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(2,'NANCY',30000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(3,'GURU',40000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(4,'ELLEN',55000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(5,'JULIA',60000);



_________________________________________________________________________________

INSERT VALUES  INTO DEPT66:

INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(1,'SALES',1);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(2,'ACCOUNTS',2);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(3,'FINANCE',3);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME) VALUES(4,'IT');
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME) VALUES(5,'MARKETING');

_________________________________________________________________________________

SELECT * FROM EMP66;
_________________________________________________________________________________

SELECT * FROM DEPT66;
_________________________________________________________________________________

RIGHT OUTER JOIN :

QUERY 1: RIGHT OUTER JOIN WITH ‘ON’ CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

------------------------------------------------------------------------------------------------------------

QUERY 2: RIGHT OUTER JOIN WITH WHERE CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 ON(EMP66.EMP_ID = DEPT66.EMP_ID) WHERE EMP_SALARY< 50000;

------------------------------------------------------------------------------------------------------------

QUERY 3: RIGHT OUTER JOIN WITH USING CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 USING(EMP_ID);

________________________________________________________________________

LEFT OUTER JOIN :

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 LEFT OUTER JOIN EMP66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

________________________________________________________________________

FULL OUTER JOIN:

QUERY 1 : FULL OUTER JOIN WITH ON CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

..................................................................................................................................

QUERY 2 : FULL OUTER JOIN WITH USING CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 USING(EMP_ID);

..................................................................................................................................

QUERY 3 : FULL OUTER JOIN WITH WHERE CLAUSE

SELECT EMP_NAME,DEPT_NAME, EMP_SALARY FROM EMP66 FULL OUTER JOIN DEPT66 USING (EMP_ID) WHERE EMP66.EMP_SALARY < 50000;

..................................................................................................................................

QUERY 4 : FULL OUTER JOIN WITH ORDER BY CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 USING(EMP_ID) WHERE EMP66.EMP_SALARY<50000 ORDER BY EMP_NAME DESC;