KEMBAR78
Subquery With Different Table | PDF | Information Science | Information Technology
Scribd
Upload
4 views7 pages

Subquery With Different Table

The document contains a series of SQL commands executed in Oracle SQL*Plus, showcasing various queries on employee and department tables. It includes examples of successful queries, errors encountered, and results returned from the database. The queries demonstrate data retrieval, filtering, and aggregation operations within an Oracle Database 11g environment.

Uploaded by

hitechbrain7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views7 pages

Subquery With Different Table

The document contains a series of SQL commands executed in Oracle SQL*Plus, showcasing various queries on employee and department tables. It includes examples of successful queries, errors encountered, and results returned from the database. The queries demonstrate data retrieval, filtering, and aggregation operations within an Oracle Database 11g environment.

Uploaded by

hitechbrain7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 7

SQL*Plus: Release 11.2.0.1.

0 Production on Tue Oct 8 14:31:15 2024

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: scott


Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * tab


2 ;
select * tab
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select * tab;


select * tab
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select * from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> select * from dept;

DEPTNO DNAME LOC


---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300


30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500


30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400


30

7698 BLAKE MANAGER 7839 01-MAY-81 2850


30

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000


20

7839 KING PRESIDENT 17-NOV-81 5000


10

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100


20

7900 JAMES CLERK 7698 03-DEC-81 950


30

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300


10

14 rows selected.

SQL> set line 100;


SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select ename


2 from emp
3 where deptno = (select * from deptno where deptno=10);
where deptno = (select * from deptno where deptno=10)
*
ERROR at line 3:
ORA-00942: table or view does not exist

SQL> select ename


2 from emo
3 w;
from emo
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> select ename
2 from emp
3 where deptno = (select deptno
4 from dept
5 where loc='NEW YORK');

ENAME
----------
CLARK
KING
MILLER

SQL> select ename


2 from emp
3 where ename ='MILLER';

ENAME
----------
MILLER

SQL> select dname


2 from dept
3 where deptno = (select deptno
4 from emp
5 where ename = 'MILLER');

DNAME
--------------
ACCOUNTING

SQL> select loc


2 from dept
3 where deptno = (select deptno
4 from emp
5 where ename = 'ADAMS');

LOC
-------------
DALLAS

SQL> select loc,count(*)


2 from dept
3 where deptno = (select deptno
4 from emp
5 where enam;
where deptno = (select deptno
*
ERROR at line 3:
ORA-00921: unexpected end of SQL command

SQL> select ename


2 from emp
3 where deptno = (select deptno
4 from dept
5 where loc ='NEW YORK';
where deptno = (select deptno
*
ERROR at line 3:
ORA-00921: unexpected end of SQL command

SQL> select ename,count(*)


2 from emp
3 where deptno = (select deptno
4 from dept
5 where loc = 'NEW YORK');
select ename,count(*)
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> select count(*)


2 from emp
3 where deptno = (select deptno
4 from dept
5 where loc ='NEW YORK');

COUNT(*)
----------
3

SQL> select count(*)


2 from emp
3 where deptno = (select deptno
4 from dept
5 where dname = 'RESEARCH');

COUNT(*)
----------
5

SQL> SELECT ENAME,SAL


2 FROM EMP
3 WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME ='SCOTT') AND DEPTNO = 20;

no rows selected

SQL> SELECT * FROM EMP


2 WHERE DEPTNO = (SELECT ;
WHERE DEPTNO = (SELECT
*
ERROR at line 2:
ORA-00921: unexpected end of SQL command

SQL> SELECT * FROM


2 DEPT
3 WHERE DEPTNO = (SELECT * FROM EMP WHERE JOB = 'MANAGER');
WHERE DEPTNO = (SELECT * FROM EMP WHERE JOB = 'MANAGER')
*
ERROR at line 3:
ORA-00913: too many values

SQL> SELECT *
2 FROM DEPT
3 WHERE DNAME ='ACCOUNTING' AND DEPTNO = (SELECT * FROM EMP
4 WHERE JOB = 'MANAGER');
WHERE DNAME ='ACCOUNTING' AND DEPTNO = (SELECT * FROM EMP
*
ERROR at line 3:
ORA-00913: too many values

SQL> SELECT *
2 FROM EMP
3 WHERE JOB = 'MANAGER' AND DEPTNO = (SELECT * FROM DEPT WHERE DNAME =
'ACCOUNTING');
WHERE JOB = 'MANAGER' AND DEPTNO = (SELECT * FROM DEPT WHERE DNAME = 'ACCOUNTING')
*
ERROR at line 3:
ORA-00913: too many values

SQL> SELECT *
2 FROM EMP
3 WHERE JOB = 'MANAGER' AND DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME =
'ACCOUNTING');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

SQL> SELECT *
2 FROM EMP
3 WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = MILLER) AND DEPTNO = (SELECT
DEPTNO FROM DEPT WHERE LOC='NEW YORK')
4 ;
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = MILLER) AND DEPTNO = (SELECT DEPTNO
FROM DEPT WHERE LOC='NEW YORK')
*
ERROR at line 3:
ORA-00904: "MILLER": invalid identifier

SQL> SELECT *
2 FROM EMP
3 WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'MILLER') AND DEPTNO = (SELECT
DEPTNO FROM DEPT WHERE LOC='NEW YORK');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> SELECT COUNT(*)


2 FROM EMP
3 WHERE JOB = 'CLERK' AND DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME =
'SMITH') AND SAL > (SELECT SAL FROM EMP WHERE ENAME = 'KING') AND HIREDATE >
(SELECT HIREDATE FROM EMP WHERE ENAME ='MARTIN') AND DEPTNO = (SELECT DEPTNO FROM
DEPT WHERE LOC='BOSTON');

COUNT(*)
----------
0

SQL> SELECT *
2 ;

*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

SQL> SELECT MAX(SAL)


2 FROM EMP
3 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');

MAX(SAL)
----------
3000

SQL>

select

You might also like