QUERYS ON INNER JOINS 

SYNTAX:

 ANSI: 

 SELECT * 

 FROM TABLE_NAME1 INNER JOIN TABLE_NAME2 

 ON <JOIN_CONDITION>; 

ORACLE: 

 SELECT * 

 FROM TABLE_NAME1 , TABLE_NAME2 

 WHERE <JOIN_CONDITION>; 

 JOIN_CONDITION: TABLE_NAME1.COL_NAME=TABLE_NAME2.COL_NAME

1.NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE EMPLOYEES. 

ANSI:

SELECT ENAME,LOC

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO;

ORACLE:

SELECT ENAME,LOC

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO;

2.WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE WORKING IN ACCOUNTING. 

ANSI:

SELECT DNAME,SAL

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE DNAME IN 'ACCOUNNTING';

ORACLE:

SELECT DNAME,SAL

FROM EMP,DEPT

WHERE EMP.DEPTNO.DEPT.DEPTNO AND DNAME IN 'ACCOUNTING'; 

3.WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340 

ANSI:

SELECT DNAME,SAL*12 ANNUAL_SAL

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO.DEPT.DEPTNO

WHERE SAL>2340;

ORACLE:

SELECT DNAME,SAL*12 ANNUAL_SAL

FROM EMP,DEPT

WHERE EMP.DEPTNO.DEPT.DEPTNO AND SAL>2340;

4.WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING CHARACTER 'A' IN THEIR DNAME 

ANSI:

SELECT ENAME,DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO 

WHERE DNAME LIKE '%A%';

ORACLE:

SELECT ENAME,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME LIKE '%A%';

5.WAQTD ENAME AND DNAME FOR ALL THE EMPLOYEES WORKING AS SALESMAN 

ANSI:

SELECT ENAME,DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE JOB IN 'SALESMAN';

ORACLE:

SELECT ENAME,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB IN 'SALESMAN';

6.WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB AND DNAME STARTS WITH CHARACTER 'S' 

ANSI:

SELECT DNAME,JOB

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE JOB LIKE 'S%' AND DNAME LIKE 'S%';

ORACLE:

SELECT DNAME,JOB

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB LIKE 'S%' AND DNAME LIKE 'S%';

7.WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO 7839 

ANSI:

SELECT DNAME,MGR

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE MGR =7839;

ORACLE:

SELECT DNAME,MGR

FROM EMP,JOIN DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR =7839;

8.WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER 83 INTO ACCOUNTING OR RESEARCH DEPT 

ANSI:

SELECT DNAME,HIREDATE

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE HIREDATE>'31-DEC-83' AND DNAME IN ('ACCOUNTING',RESEARCH');

ORACLE:

SELECT DNAME,HIREDATE

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND HIREDATE>'31-DEC-83' AND DNAME IN ('ACCOUNTING',RESEARCH');

9.WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE GETTING COMM IN DEPT 10 OR 30 

ANSI:

SELECT ENAME,DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE EMP.DEPT IN(10,30) AND COMM IS NOT NULL;

ORACLE:

SELECT ENAME,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPT IN(10,30) AND COMM IS NOT NULL;

10.WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S EMPNO ARE (7839,7902) AND ARE WORKING IN LOC NEW YORK. 

ANSI: 

SELECT ENAME,DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE EMP.DEPT IN(10,30) AND COMM IS NOT NULL;

ORACLE:

SELECT ENAME,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND  EMP.DEPT IN(10,30) AND COMM IS NOT NULL;

11.WAQTD LOC AND AVERAGE SALARY GIVEN FOR EACH LOCATION BY EXCLUDING ALL THE EMPLOYEES WHOS SECOND CHAR IS A IN THEIR NAME . 

ANSI:

SELECT LOC,AVG(SAL)

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE ENAME NOT LIKE '_A%'

GROUP BY LOC;

ORACLE:

SELECT LOC,AVG(SAL)

FROM EMP,,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME NOT LIKE '_A%'

GROUP BY LOC;

12.WAQTD NAME OF THE EMP AND HIS LOC IF EMPLOYEE IS WORKING AS MANAGER AND WORKING UNDER THE EMPLOYEE WHOS EMPNO IS 7839 

ANSI:

SELECT ENAME,LOC

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE JOB='MAMAGER' AND MGR=7839;

ORACLE:

SELECT ENAME,LOC

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND  JOB='MAMAGER' AND MGR=7839;

13.WAQTD DNAME AND EMPLOYEE ID'S OF ALL THE EMPLOYEES WHO ARE CLERKS AND HAVING REPORTING MANAGERS. 

ANSI:

SELECT DNAME,EMPNO

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE JOB='CLERK' AND MGR IS NOT NULL;

ORACLE:

SELECT DNAME,EMPNO

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='CLERK' AND MGR IS NOT NULL;

14.WAQTD DNAME AND TOTAL SALARY GIVEN TO THAT DEPT IF THERE ARE ATLEAST 4 EMPLOYEES WORKING FOR EACH DEPT. 

ANSI:

SELECT DNAME,SUM(SAL)

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

GROUP BY DNAME

HAVING COUNT(*)>=4;

ORACLE:

SELECT DNAME,SUM(SAL)

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

GROUP BY DNAME

HAVING COUNT(*)>=4;

15.WAQTD DNAME AND NUMBER OF EMPLOYEES WORKING IN EACH DEPT ONLY IF THERE ARE MANAGER OR CLERKS. 

ANSI:

SELECT DNAME,COUNT(EMPNO)  TOTAL_NO_OF_EMP

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE JOB IN ('CLERK','MANAGER')

GROUP BY DNAME;

ORACLE:

SELECT DNAME,COUNT(EMPNO)  TOTAL_NO_OF_EMP

FROM EM,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB IN ('CLERK','MANAGER')

GROUP BY DNAME;