SQL LEARNING CLASS5

SUBQUERIES AND EXISTS:

SUBQUERIES

Nesting of queries, one within the other is termed as a subquery.

A statement containing a subquery is called a parent query.

Subqueries are used to retrieve data from tables that depend on the values in the table itself.

TYPES

• Single row subqueries
• Multi row subqueries
• Multiple subqueries
• Correlated subqueries

SINGLE ROW SUBQUERIES

In single row subquery, it will return one value.

Ex:

SQL> select * from emp where sal > (select sal from emp where empno = 7566);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

MULTI ROW SUBQUERIES

In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.

Ex:

SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and

4000);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and

4000);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 KING PRESIDENT 17-NOV-81 5000 10

MULTIPLE SUBQUERIES

There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.

Ex:

SQL> select * from emp where sal = (select max(sal) from emp where sal < (select

max(sal) from emp));

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

CORRELATED SUBQUERIES

A subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.

Ex:

SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where

e.deptno = deptno);

DEPTNO

----------

20

30

EXISTS

Exists function is a test for existence. This is a logical test for the return of rows from a query.

Ex:

Suppose we want to display the department numbers which has more than 4 employees.

SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

DEPTNO COUNT(*)

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

20 5

30 6

From the above query can you want to display the names of employees?

SQL> select deptno,ename, count(*) from emp group by deptno,ename having count(*) > 4;

no rows selected

The above query returns nothing because combination of deptno and ename never return

more than one count.

The solution is to use exists which follows.

SQL> select deptno,ename from emp e1 where exists (select * from emp e2

where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

deptno,ename;

DEPTNO ENAME

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

20 ADAMS

20 FORD

20 JONES

20 SCOTT

20 SMITH

30 ALLEN

30 BLAKE

30 JAMES

30 MARTIN

30 TURNER

30 WARD

NOT EXISTS

SQL> select deptno,ename from emp e1 where not exists (select * from emp e2

where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

deptno,ename;

DEPTNO ENAME

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

10 CLARK

10 KING

10 MILLER

 

SET COMMANDS:

These commands does not require statement terminator and applicable to the sessions , those will be automatically cleared when session was closed.

LINESIZE

This will be used to set the linesize. Default linesize is 80.

Syntax:

Set linesize <value>

Ex:

SQL> set linesize 100

PAGESIZE

This will be used to set the pagesize. Default pagesize is 14.

Syntax:

Set pagesize <value>

Ex:

SQL> set pagesize 30

DESCRIBE

This will be used to see the object’s structure.

Syntax:

Describe or desc <object_name>

Ex:

SQL> desc dept

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

PAUSE

When the displayed data contains hundreds or thousands of lines, when you select it then it will automatically scrolls and displays the last page data. To prevent this you can use this pause option. By using this it will display the data correspoinding to the pagesize with a break which will continue by hitting the return key. By default this will be off.

Syntax:

Set pause on | off

Ex:

SQL> set pause on

FEEDBACK

This will give the information regarding howmany rows you selected the object. By default the feedback message will be displayed, only when the object contains more than 5 rows.

Syntax:

Set feedback <value>

Ex:

SQL> set feedback 4

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

4 rows selected.

HEADING

If you want to display data without headings, then you can achieve with this. By default heading is on.

Syntax:

Set heading on | off

Ex:

SQL> set heading off

SQL> select * from dept;

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SERVEROUTPUT

This will be used to display the output of the PL/SQL programs. By default this will be off.

Syntax:

Set serveroutput on | off

Ex:

SQL> set serveroutput on

TIME

This will be used to display the time. By default this will be off.

Syntax:

Set time on | off

Ex:

SQL> set time on

19:56:33 SQL>

TIMING

This will give the time taken to execute the current SQL statement. By default this will be off.

Syntax:

Set timing on | off

Ex:

SQL> set timing on

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Elapsed: 00:00:00.06

SQLPROMPT

This will be used to change the SQL prompt.

Syntax:

Set sqlprompt <prompt>

Ex:

SQL> set sqlprompt 'ORACLE>'

ORACLE>

SQLCASE

This will be used to change the case of the SQL statements. By default the case is mixed.

Syntax:

Set sqlcase upper | mixed | lower

Ex:

SQL> set sqlcase upper

SQLTERMINATOR

This will be used to change the terminator of the SQL statements. By default the terminator is ;.

Syntax:

Set sqlterminator <termination_character>

Ex:

SQL> set sqlterminator :

SQL> select * from dept:

DEFINE

By default if the & character finds then it will treat as bind variable and ask for the input. Suppose your want to treat it as a normal character while inserting data, then you can prevent this by using the define option. By default this will be on

Syntax:

Set define on | off

Ex:

SQL>insert into dept values(50,'R&D','HYD');

Enter value for d:

old 1: insert into dept values(50,'R&D','HYD')

new 1: INSERT INTO DEPT VALUES(50,'R','HYD')

SQL> set define off

SQL>insert into dept values(50,'R&D','HYD'); -- here it won’t ask for value

NEWPAGE

This will shows how many blank lines will be left before the report. By default it will leave one blank line.

Syntax:

Set newpage <value>

Ex:

SQL> set newpage 10

The zero value for newpage does not produce zero blank lines instead it switches to a special property which produces a top-of-form character (hex 13) just before the date on each page. Most modern printers respond to this by moving immediately to the top of the next page, where the priting of the report will begin.

HEADSEP

This allow you to indicate where you want to break a page title or a column heading that runs longer than one line. The default heading separator is vertical bar (|).

Syntax:

Set headsep <separation_char>

Ex:

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> set headsetp !

SQL> col dname heading 'DEPARTMENT ! NAME'

SQL> /

DEPARTMENT

DEPTNO NAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

ECHO

When using a bind variable, the SQL statement is maintained by echo. By default this is off.

Syntax:

Set echo on | off

VERIFY

When using a bind variable, the old and new statements will be maintained by verify. By default this is on.

Syntax:

Set verify on | off

Ex:

SQL> select * from dept where deptno = &dno;

Enter value for dno: 10

old 1: select * from dept where deptno = &dno

new 1: select * from dept where deptno = 10

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

SQL> set verify off

SQL> select * from dept where deptno = &dno;

Enter value for dno: 20

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

PNO

This will give displays the page numbers. By default the value would be zero.

Ex:

SQL> col hiredate new_value xtoday noprint format a1 trunc

SQL> ttitle left xtoday right 'page' sql.pno

SQL> select * from emp where deptno = 10;

09-JUN-81 page 1

EMPNO ENAME JOB MGR SAL COMM DEPTNO

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

7782 CLARK MANAGER 7839 2450 10

7839 KING PRESIDENT 5000 10

7934 MILLER CLERK 7782 1300 10

In the above noprint tells SQLPLUS not to display this column when it prints the results of the SQL statement. Dates that have been reformatted by TO_CHAR get a default width of about 100 characters. By changing the format to a1 trunc, you minimize this effect. NEW_VALUE inserts contents of the column retrieved by the SQL statement into a variable called xtoday.