SQL - STRING QUERY

--Enter any name in the select field return string query.

SELECT  substr('Surya DTJ Chennai', 1, Instr('Surya DTJ Chennai',' ',1,1)-1)name,
     Substr('Surya DTJ Chennai', Instr('Surya DTJ Chennai',' ',1,1)+1,Instr('Surya DTJ Chennai',' ',1,2)-Instr('Surya DTJ Chennai',' ',1,1))company,
     Substr('Surya DTJ Chennai', Instr('Surya DTJ Chennai',' ',1,2)+1) city
FROM DUAL;
   
Select Substr('suriya$$dtj$$chennai',1,Instr('suriya$$dtj$$chennai','$',1,1)-1)Name,
Substr('suriya$$dtj$$chennai',Instr('suriya$$dtj$$chennai','$',1,2)+1,Instr('suriya$$dtj$$chennai','$',1,3)-1-Instr('suriya$$dtj$$chennai','$',1,2))Company,
Substr('suriya$$dtj$$chennai',Instr('suriya$$dtj$$chennai','$',1,4)+1)city
FROM DUAL;


SELECTSubstr('kumaravelu/dtj/madurai/600082',1,Instr('kumaravele/dtj/madurai/600082','/',1,1)-1)Name,
Substr('kumaravelu/dtj/madurai/600082',Instr('kumaravele/dtj/madurai/600082','/',1,1)+1,Instr('kumaravelu/dtj/madurai/600082','/',1,2)-1
-Instr('kumaravelu/dtj/madurai/600082','/',1,1))company,
 Substr('kumaravelu/dtj/madurai/600082',Instr('kumaravele/dtj/madurai/600082','/',1,2)+1,Instr('kumaravelu/dtj/madurai/600082','/',1,3)-1
-Instr('kumaravelu/dtj/madurai/600082','/',1,2))City,
Substr('kumaravelu/dtj/madurai/600082',Instr('kumaravelu/dtj/madurai/600082','/',1,3)+1)Pincode From Dual;

SELECT  Substr(:Get_Char,1,Instr(:Get_Char,'/',1,1)-1)Name1,
Substr(:Get_Char,Instr(:Get_Char,'/',1,1)+1,Instr(:Get_Char,'/',1,2)-1-Instr(:Get_Char,'/',1,1))Company,
Substr(:Get_Char,Instr(:Get_Char,'/',1,2)+1,Instr(:Get_Char,'/',1,3)-1
-Instr(:Get_Char,'/',1,2))City,
Substr(:Get_Char,Instr(:Get_Char,'/',1,3)+1)Pincode From Dual;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - FOR LOOP

BEGIN
  FOR someone IN (
    SELECT * FROM employees
    WHERE employee_id < 120
    ORDER BY employee_id
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
                         ', Last name = ' || someone.last_name);
  End Loop;
End;

OUTPUT:
 First name = Steven, Last name = King
First name = Neena, Last name = Kochhar
First name = Lex, Last name = De Haan
First name = Alexander, Last name = Hunold
First name = Bruce, Last name = Ernst

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - ANONYMOUS BLOCK, UPDATE COLUMNS

Declare
   V_dept_id xxdtj_Employees.department_Id%Type:=10;
Begin
   Update Xxdtj_Employees Set Salary=Salary + 1000
   Where Department_Id=V_dept_id;
   Dbms_Output.Put_Line(V_DEPT_ID);
End;

OUTPUT :
   10

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - TRIGGER with Example Programs

First, create two tables for the same structure.
   create table empl(name varchar2(50),company       varchar2(50),join_date date);
   create table employee as select * from empl where 1 = 1;

create or replace trigger insert_empl
after insert or update or delete
on empl
for each row
declare
      v_name varchar2(30);
begin
     if inserting then
         insert into employee values(:new.name,:new.company,sysdate);
     end if;
end;


Output :
       insert into empl values('suriya','abc',sysdate);
      select * from employee;
           suriya abc 09-MAR-18.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - PROCEDURE IN /OUT PARAMETER

create or replace procedure xx1
(a in out number)
is
begin
     a:=a+4;
end;

OUTPUT :
  declare
      a number:=&a;
begin
     xx1(a);
     dbms_output.put_line('the value is' || a);
end;

  Enter number 6
    the value is10

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.