ASSIGNMENT ON INNER JOIN :
1. NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE EMPLOYEES
2.WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE WORKING IN ACCOUNTING.
3. WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340
4. WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING CAHARACTER 'A' IN THEIR DNAME
5.WAOTD ENAME AND DNAME FOR ALL THE EMPLOYEES WORKING AS SALESMAN
6.WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB AND DNAME STARTS WITH
CHARACTER 'S'
7. WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO 7839
8.WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER 83 INTO ACCOUNTING OR RESEARCH
DEPT
9.WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE GETTING COMM IN DEPT 10 OR 30
10.WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S EMPNO ARE (7839,7902) AND ARE
WORKING IN LOC NEW YORK.
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 23 17:41:25 2025
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: SCOTT
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> /* 1. NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE EMPLOYEES */
SQL> SELECT E1.ENAME,D1.LOC
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 ;
ENAME LOC
---------- -------------
CLARK NEW YORK
KING NEW YORK
MILLER NEW YORK
JONES DALLAS
FORD DALLAS
ADAMS DALLAS
SMITH DALLAS
SCOTT DALLAS
WARD CHICAGO
TURNER CHICAGO
ALLEN CHICAGO
ENAME LOC
---------- -------------
JAMES CHICAGO
BLAKE CHICAGO
MARTIN CHICAGO
14 rows selected.
SQL> SET PAGES 100 LINES 100;
SQL> /
ENAME LOC
---------- -------------
CLARK NEW YORK
KING NEW YORK
MILLER NEW YORK
JONES DALLAS
FORD DALLAS
ADAMS DALLAS
SMITH DALLAS
SCOTT DALLAS
WARD CHICAGO
TURNER CHICAGO
ALLEN CHICAGO
JAMES CHICAGO
BLAKE CHICAGO
MARTIN CHICAGO
14 rows selected.
SQL> /*
SQL> 2.WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE WORKING IN ACCOUNTING. */
SQL> SELECT D1.DNAME,E1.SAL FROM
2 EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE D1.DNAME='ACCOUNTING';
DNAME SAL
-------------- ----------
ACCOUNTING 2450
ACCOUNTING 5000
ACCOUNTING 1300
SQL> /* 3. WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN
2340 */
SQL> SELECT D1.DNAME,E1.SAL*12 AS ANUNNAL_SAL FROM
2 EMP E1 INNER DEPT D1
3 ON D1.DEPTNO=E1.DEPTNO
4 WHERE E1.SAL>2340;
EMP E1 INNER DEPT D1
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> SELECT D1.DNAME,E1.SAL*12 AS ANUNNAL_SAL FROM
2 EMP E1 INNER JOIN DEPT D1
3 ON D1.DEPTNO=E1.DEPTNO
4 WHERE E1.SAL>2340;
DNAME ANUNNAL_SAL
-------------- -----------
ACCOUNTING 29400
ACCOUNTING 60000
RESEARCH 36000
RESEARCH 36000
RESEARCH 35700
SALES 34200
6 rows selected.
SQL> /* 4. WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING CAHARACTER 'A' IN THEIR DNAME
*/
SQL> SELECT E1.ENAME,D1.DNAME
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE D1.DNAME LIKE '%A%';
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
JAMES SALES
BLAKE SALES
MARTIN SALES
14 rows selected.
SQL> /* 5.WAOTD ENAME AND DNAME FOR ALL THE EMPLOYEES WORKING AS SALESMAN */
SQL> SELECT E1.ENAME,D1.DNAME
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE E1.JOB='SALESMAN';
ENAME DNAME
---------- --------------
ALLEN SALES
TURNER SALES
MARTIN SALES
WARD SALES
SQL> /* 6.WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB AND DNAME STARTS WITH
CHARACTER 'S' */
SQL> SELECT D1.DNAME,E1.JOB
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON D1.DEPTNO=E1.DEPTNO
4 WHERE E1.JOB LIKE 'S%' AND D1.DNAME LIKE 'S%';
DNAME JOB
-------------- ---------
SALES SALESMAN
SALES SALESMAN
SALES SALESMAN
SALES SALESMAN
SQL> /* 7. WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO 7839 */
SQL> SELECT D1.DNAME,E1.MGR
2 FROM DEPT D1 INNER JOIN EMP E1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE E1.EMPNO='7839';
DNAME MGR
-------------- ----------
ACCOUNTING
SQL> /* 8.WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER 83 INTO ACCOUNTING OR
RESEARCH DEPT */
SQL> SELECT D1.DNAME,E1.HIREDATE
2 FROM DEPT D1 INNER JOIN EMP E1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE E1.HIREDATE>'31-DEC-1983' AND D1.DNAME IN('ACCOUNTING','RESEARCH');
DNAME HIREDATE
-------------- ---------
RESEARCH 19-APR-87
RESEARCH 23-MAY-87
SQL> /* 9.WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE GETTING COMM IN DEPT 10 OR
30 */
SQL> SELECT E1.ENAME,D1.DNAME
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE E1.COMM IS NOT NULL AND E1.DEPTNO IN (10,30);
ENAME DNAME
---------- --------------
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
SQL> /* 10.WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S EMPNO ARE (7839,7902)
AND ARE WORKING IN LOC NEW YORK.*/
SQL> SELECT D1.DNAME,E1.EMPNO
2 FROM EMP E1 INNER JOIN DEPT D1
3 ON E1.DEPTNO=D1.DEPTNO
4 WHERE E1.EMPNO IN (7839,7902) AND D1.LOC='NEW YORK';
DNAME EMPNO
-------------- ----------
ACCOUNTING 7839
