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 5100SQL> 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 900SQL>
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 5100Views: Virtual statement, or mirror of the table
- Avoids re-writing querys
- 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 OOO8 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 OOO8 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 hr9 rows selected.
