25.Subquery, Views

SQL

Sub Querys : Query inside a query is called sub query

Syntax: SQL> select t1.col1,
(select t2.col1,
from table2 t2
where condition) “Alieas”,
t1.col2
from table1 t1
where ;

Rules: 1.Sub query should have only one column to select
2.Sub query should not retun more than one value
3.Sub query MAY have the alias when used select section

Sample Data:

Table EMP:
ENo Ename Sal Deptno Deptno Depname
—————————— —————-
115 CHANDU 25000 222 222 HR
111 RAM 12000 222 333 Finance
112 RAMESH 25000 444
555 Purchase

Output

ENo Ename Sal Depname

115 CHANDU 25000 HR
111 RAM 12000 Finance
112 RAMESH 25000

SQL> select e.eno,
e.ename,
e.sal,
(select d.dept_name
from dept d
where d.dept_id = e.dept_id) “Dept name”
from emp e
/

ENO ENAME SAL Dept name


115 CHANDU 25000 hr
105 kumar 12200 manager
111 RAM 12000 hr
112 RAMESH 25000 finnaces
113 KRISHNA 30000 manager
114 SHEKAR 56000 manager

Example:
SQL> select e.eno,
e.ename,
e.sal,
(select grade from sal_grade where e.sal between low_sal and high_sal) “Grade”
from emp e;

Example:

SQL>
select e.empno,
e.ename,
e.sal,
(select dname from dept where deptno = e.deptno) “DeptName”,
(select loc from dept where deptno = e.deptno) “Location”,
e.job
from emp e;

Other examples:

SQL> select * from emp where empno in (7839,7369);

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER

  7369 SMITH      CLERK           7902 17-DEC-80       1800                    20        900
  7839 KING       PRESIDENT            17-NOV-81       6000                    10       5100

SQL> select * from emp where sal in ((select max(sal) from emp), (select min(sal) from emp));

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER

  7839 KING       PRESIDENT            17-NOV-81       6000                    10       5100
  7369 SMITH      CLERK           7902 17-DEC-80       1800                    20        900

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

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER

  7839 KING       PRESIDENT            17-NOV-81       6000                    10       5100

Views: Virtual statement, or mirror of the table

  1. Avoids re-writing querys
  2. Collects information from more than one table and show as a single result
    which can be used in applications/forms

Syntax: sql> create or replace view as select statement;
Ex: sql> create or replace view empl_v as select * from emp;
sql> select * from emp;

Ex: SQL> create or replace view dept_empl_v
as select e.eno,
e.ename,
e.sal,
d.dept_id,
d.dept_name
from emp e,
dept d
where e.dept_id = d.dept_id
/

SQL> select * from dept_empl_v;

SQL> desc emp_dept_b;
ERROR:
ORA-04043: object emp_dept_b does not exist

SQL> desc emp_dept_v;
Name


ENO
ENAME
SAL
JOB
dept name

SQL> insert into emp_dept_v values(991,’xyz’,34000,’manager’,’hr’);
insert into emp_dept_v values(991,’xyz’,34000,’manager’,’hr’)
*
ERROR at line 1:

ORA-01733: virtual column not allowed here

SQL> SELECT E.ENO,
E.ENAME,
E.SAL,
E.JOB,
(SELECT DEPT_NAME FROM DEPT D WHERE E.DEPT_ID = D.DEPT_ID) “dept name”
2 from emp_new e;

   ENO ENAME             SAL JOB        dept name

   115 CHANDU          25500 manager    hr
   105 kumar           12200            manager
   111 RAM             12000 manager    hr
   112 RAMESH          25000 B          finnaces
   113 KRISHNA         30000 C          manager
   114 SHEKAR          56500 manager    manager
   116 XYZ             12000 ABC
   117 KKK              9000 OOO

8 rows selected.

SQL> CREATE OR REPLACE VIEW EMP_DEPT_V AS SELECT E.ENO,E.ENAME,E.SAL, E.JOB, (SELECT DEPT_NAME FROM DEPT D WHERE E.DEPT_ID = D.DEPT_ID) “dept name”
2 from emp_new e;

View created.

SQL> SELECT * FROM EMP_DEPT_V;

   ENO ENAME             SAL JOB        dept name

   115 CHANDU          25500 manager    hr
   105 kumar           12200            manager
   111 RAM             12000 manager    hr
   112 RAMESH          25000 B          finnaces
   113 KRISHNA         30000 C          manager
   114 SHEKAR          56500 manager    manager
   116 XYZ             12000 ABC
   117 KKK              9000 OOO

8 rows selected.

SQL> insert into emp_new (eno,ename,dept_id) values(909,’abc’,222);

1 row created.

SQL> SELECT * FROM EMP_DEPT_V;

   ENO ENAME             SAL JOB        dept name

   115 CHANDU          25500 manager    hr
   105 kumar           12200            manager
   111 RAM             12000 manager    hr
   112 RAMESH          25000 B          finnaces
   113 KRISHNA         30000 C          manager
   114 SHEKAR          56500 manager    manager
   116 XYZ             12000 ABC
   117 KKK              9000 OOO
   909 abc                              hr

9 rows selected.

Leave a Reply

Your email address will not be published. Required fields are marked *