Insert into select statement

1.How can U call Pl Sql Procedure From Sql
We can type  execute procedure (short term EXEC)
like EXEC procedure_name;


2.Write a query to find Nth highest salary from a table.

select distinct(e.salary)
from employees e
where &eno=(select count(distinct(e1.salary))
from employees e1
where e.salary<=e1.salary)

Output:- Suppose You want to find second highest salary

Enter value for eno: 2
old   3: where &eno=(select count(distinct(e1.salary))
new   3: where 2=(select count(distinct(e1.salary))

    SALARY
----------
     17000

3.Explain Insert into select statement .

In order to insert a data from one table to another table we use below query.

First We need to create a table

create table emp1 (empno number primary key,deptno number,job_id varchar2(50))
/
 insert into emp1(empno,deptno,job_id)
 select employee_id,department_id,job_id
 from employees
 where department_id=20;

2 rows created.
sql> select * from emp1;

     EMPNO     DEPTNO JOB_ID
---------- ---------- --------------------------------------------------
       201         20 MK_MAN
       202         20 MK_REP




4.Inline view:-It is a subquery that comes with from clause of the sql statement.
The inline view where you can put your query in sql from clause.

Suppose we want to select top highest salary 4 records use below query.

select employee_id,last_name,salary
from(select employee_id,last_name,salary
from employees
order by salary desc )
where rownum <=4

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000
        101 Kochhar                        17000
        102 De Haan                        17000
        145 Russell                        14000