Installation of Oracle 11g and 12c

*Kindly see the You Tube video to get installed the database.

insert emp

CREATE OR REPLACE PROCEDURE insert_employee (EMPLOYEE_ID IN number, NAME IN VARCHAR2, ADDRESS IN VARCHAR2 ) IS
BEGIN   
    INSERT INTO EMP_T (EMP_ID, NAME, ADDRESS)
    VALUES (EMPLOYEE_ID, NAME, ADDRESS);
END;

procedure p_get_ename_sal_dept_of_emp

create or replace procedure p_get_ename_sal_dept_of_emp
(pv_emp_no in emp_100.empno%type)
as
lv_ename emp_100.ename%type;
lv_sal emp_100.salary%type;
lv_dept emp_100.deptno%type;
begin
  select ename,salary,deptno into lv_ename,lv_sal,lv_dept from emp_100 where empno=pv_emp_no;
  dbms_output.put_line('the emp no'||' '||pv_emp_no||' '||'are..');
    dbms_output.put_line('the name of the  emp'||' '||lv_ename);
    dbms_output.put_line('the emp dept'||' '||lv_dept);
end p_get_ename_sal_dept_of_emp;
  -- select * from emp_100;

Proc_p_emp_info (I_deptno)- to display emp info

create or replace procedure p_emp_info (I_deptno in emp_100.deptno%type)
as
    cursor cur_empinfo is
    select empno,ename,salary from emp_100 where deptno=I_deptno;
            lv_emprecord    cur_empinfo%rowtype;
            lv_empname      emp_100.ename%type;
            lv_salary       emp_100.salary%type;
            lv_avg_salary   emp_100.salary%type;
            lv_max_salary   emp_100.salary%type;
            /*lv_exp number(4,2);=1;*/
begin
  open cur_empinfo;
  loop
    fetch cur_empinfo into lv_emprecord;
    exit when cur_empinfo%notfound;
    dbms_output.put_line ('EMPOLYEE NAME     : '||lv_emprecord.ename);
    dbms_output.put_line ('EMPOLYEE SALARY   : '||lv_emprecord.salary);
    /*lv_exp:=empExp(cur_empinfo.empno);*/
   /* dbms_output.put_line ('EMPOLYEE"S exp    : '||lv_emprecord.lv_exp);*/
    dbms_output.put_line ('****************************************');
   /* lv_salary:=lv_salary+cur_empinfo.SALARY;*/
   END LOOP;
   CLOSE  cur_empinfo;
END;

hash, nested and merge join

---nested loop  join ---
Nested Loops” operator basically does is: For each record from the outer input – find matching rows from the inner input.
Technically, this means that the clustered index scan you see as the outer input is executed once to get all the relevant records,
and the clustered index seek you see below it is executed for each record from the outer input.

SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id;
--- merge join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OH.department_id  between 10 and 50;

--- Hash join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OC.first_name like '%S%';

Find the Number in var char column .

Find the Number in var char column .

select * from (select * from table@pwrdev where rtrim(column, '0123456789') is null);
select e1.eentry from table e1 where not regexp_like (e1.column, '^[A-Z]');

Column to Row Chnages in Oracle :

# How to Change the  Column to Row.
# Using PIVOT Operator :

 Pivot operator is display the data in column which all takes from row. this operator use for cross tabulation and give you the result in row to column format.

SELECT * FROM (SELECT first_name, last_name FROM employees) PIVOT (SUM(salary) AS sum_salary FOR (first_name) IN (1000,2400,17000));

SELECT REPLACE(Testbulk, ' ') FROM (SELECT SYS_CONNECT_BY_PATH(Testbulk,' ') Testbulk, level  FROM T1

START WITH Testbulk= (select min(Testbulk) from t1) CONNECT BY PRIOR Testbulk < Testbulk ORDER BY level DESC) WHERE rownum = 1;