My Fusion, SOA, OSB, ADF Learnings: 2020

Saturday, 5 September 2020

Typical knowledge test: sql interviews

1. Employee Names who are indiviudal cotributors

2. Total number of Managers

3. Manager Name, Employee count

4. Manager Name, total salary of thier employees

a. including Manager

b. excluding Manager

5. Manager names with their employees avg salary

6. Manager names with any of their employee salary < 10L

7. Managers having less salary with any of their employee

8. Employee names having more salary than their Managers

9. select all manager names

10. second heighest salary of employee

11. Heighest and lowest salary of employee, manager

Employee, Department

1. Avg salary of employees in a Department
2. Higest, lowest salary of employees in a department

Solns:

---------

Data: for practice purpose, you may use http://sqlfiddle.com/

CREATE TABLE departments (
department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;

CREATE TABLE employees (
employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job           VARCHAR2(9),
manager_id    NUMBER(4),
hiredate      DATE,
salary        NUMBER(7,2),
commission    NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Qtn numbers are not in sync with Answers:

1. Employee Names who are individual contributors
select employee_id, employee_name
from employees emp
where emp.employee_id not in
(select distinct manager_id from employees mgr where mgr.manager_id is not null)

2. Total number of Managers
select count(distinct mgr.employee_id)
--select distinct mgr.employee_id, mgr.employee_name
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id;

3. Manager Name, Employee count
select mgr.employee_id mgrId, mgr.employee_name MgrName, count(emp.employee_id) tot_emp
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
group by mgr.employee_id, mgr.employee_name;

4. Manager Name, total salary of thier employees
    a. including Manager
select mgr.employee_id mgrId, mgr.employee_name MgrName,
count(emp.employee_id) tot_emp, sum(emp.salary) tot_emp_sal, sum(emp.salary + mgr.salary) tot_mgr_emp_sal
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
group by mgr.employee_id, mgr.employee_name;

    b. excluding Manager
select mgr.employee_id mgrId, mgr.employee_name MgrName,
count(emp.employee_id) tot_emp, sum(emp.salary) tot_emp_sal
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
group by mgr.employee_id, mgr.employee_name;

5. Manager names with their employees avg salary
select mgr.employee_id mgrId, mgr.employee_name MgrName,
count(emp.employee_id) tot_emp, avg(emp.salary) avg_emp_sal
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
group by mgr.employee_id, mgr.employee_name;

6. Manager names with any of their employee salary < 10L
select mgr.employee_id mgrId, mgr.employee_name MgrName
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
and emp.salary <= 1000
group by mgr.employee_id, mgr.employee_name;

7. Managers having less salary with any of their employee
select mgr.employee_id mgrId, mgr.employee_name MgrName
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
and mgr.salary < emp.salary
group by mgr.employee_id, mgr.employee_name;

8. Employee names having more salary than their Managers
select emp.employee_id EmpId, emp.employee_name EmpName
from employees mgr,
      employees emp
where mgr.employee_id = emp.manager_id
and emp.salary > mgr.salary
group by emp.employee_id, emp.employee_name;

9. select all manager names
select employee_id mgr_id, employee_name mgr_name
from employees emp
where emp.employee_id in
(select distinct manager_id from employees mgr where mgr.manager_id is not null)

10. second highest salary of any employee in an organization

select employee_id, employee_name from employees emp
where 2 = (select count(distinct salary) from employees emp1
        where emp.salary<=emp1.salary);

11. Highest and lowest salary of employee, manager
emp:
select * from (
select employee_id, employee_name, salary from employees emp1
where not exists (select 1 from employees emp2 where emp1.employee_id = emp2.manager_id)
order by salary desc)
where rownum=1;

option-2:
select * from
(select employee_id, employee_name, salary,
row_number() over (order by salary desc) sal_rank
from employees emp1
where not exists (select 1 from employees emp2 where emp1.employee_id = emp2.manager_id)
) emp
where emp.sal_rank <=1;

option-3:
select * from
(select employee_id, employee_name, salary,
row_number() over (order by salary desc) sal_rank
from employees emp1
where emp1.employee_id not in (select distinct manager_id from employees emp2 where manager_id is not null)
) emp
where emp.sal_rank <=3;

Manager:
select * from (
select employee_id mgrId, employee_name mgrName, salary from employees mgr
where exists (select 1 from employees emp where emp.manager_id=mgr.employee_id) order by salary desc
) mgr
where ROWNUM=1;

option-2:
select * from
(select employee_id, employee_name, salary,
row_number() over (order by salary desc) sal_rank
from employees emp1
where emp1.employee_id in (select distinct manager_id from employees emp2 where manager_id is not null)
) emp
where emp.sal_rank <=3;

both:
select * from (
select employee_id, employee_name, salary, 'Emp' emp_type from employees emp1
where not exists (select 1 from employees emp2 where emp1.employee_id = emp2.manager_id)
order by salary desc)
where rownum=1
union
select * from (
select employee_id, employee_name, salary, 'Mgr' emp_type from employees mgr
where exists (select 1 from employees emp where emp.manager_id=mgr.employee_id) order by salary desc
) mgr
where ROWNUM=1;

Union -> removes duplicates
Union All -> returns duplicate records

Employee, Department
1. Avg salary of employees in a Department
select dept.department_id, dept.department_name, avg(emp.salary)
from employees emp,
      departments dept
where dept.department_id = emp.department_id
group by dept.department_id, dept.department_name;

2. Higest, lowest salary of employees in a department
select dept.department_id, dept.department_name, max(emp.salary), min(emp.salary)
from employees emp,
      departments dept
where dept.department_id = emp.department_id
group by dept.department_id, dept.department_name;

Thursday, 6 August 2020

sql to retrive maximum value between two columns of record in a table

We had a requirement to retrieve maximum values between two columns of a record using an SQL.

The requirement was simple and even the SQL.

Wanted to share if it helps for one us gets into this need.

table:

create table student
(
    rollno number primary key,
    sname varchar2(30),
    marks1 number,
    marks2 number
);

insert into student (rollno, sname, marks1, marks2) values (01, 'name1', 90, 89);
insert into student (rollno, sname, marks1, marks2) values (02, 'name2', 88, 89);
insert into student (rollno, sname, marks1, marks2) values (03, 'name3', 78, 89);
insert into student (rollno, sname, marks1, marks2) values (04, 'name4', 87, 70);
insert into student (rollno, sname, marks1, marks2) values (05, 'name5', 99, 89);

SQL to return maximum between the two columns:

select rollno, sname,
case when marks1 >= marks2 then marks1
else marks2
end as better_marks
from student;

Saturday, 25 July 2020

XSLT TransformationTIP

Purpose:

While reviewing one of implementation issue, I have observed a different behavior wrt XSLT transformation.

Thought of sharing.

Issue:

The previous data of the XSLT target variable will be reset/cleared. The target variable will be updated with the result of the Transformation.

Fix/workaround:

1. Use XSLT target variable also as one of its input variable.

2. Copy previous data from its own variable inside XSLT to target.

3. Assign the new data from other XSLT input variables as needed.

Implementation:

The below bpel has two transformations conditionally.

Else Block

DemoXsltTransformComposite/SOA/Transformations/UOM_Amount_status_date.xsl

If Block

DemoXsltTransformComposite/SOA/Transformations/UOM_Amount_Status_Date_2.xsl

Input:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        <soap:Body>
    <ns1:PurchaseOrder xmlns:ns1="http://xmlns.oracle.com/apps/scm/orderManagement/purchaseOrder/service/types">
        <ns1:orderId>10</ns1:orderId>
        <ns1:orderedQty>10</ns1:orderedQty>
        <ns1:orderedUOM>EA</ns1:orderedUOM>
        <ns1:amount>100</ns1:amount>
        <ns1:status>XSLT</ns1:status>
        <ns1:orderedDate>2019-08-25</ns1:orderedDate>
        </ns1:PurchaseOrder>
    </soap:Body>
</soap:Envelope>

Else Block result:

OrderId, OrderedQty were missing as data cleared by default xslt transformation.

If Block result:

OrderId, OrderedQty were retained.

Please find the project here.

Sunday, 19 July 2020

Using Global Temporary Tables - GTT

Purpose:

Global temporary tables are used when we have to prepare bulk data in a transaction/session.

The life cycle of the data will be cleared from temporary tables as soon the transaction committed or session closed.

We don't need a separate process to clear the data.

Generally we create purge scripts on permanent tables and schedule them for all practical purposes.

1. Keeping life cycle per transaction commit:


CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         0

SQL>

1. Keeping life cycle per session:

CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN soademo/soademo

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         0

SQL>


Features:
- Data in temporary tables automatically deleted at the end of the database session, even if it ends abnormally.
- Indexes can be created on the temporary tables. The life cycle of the index is same as database session.
- Views can be created on temporary tables and with combination of temporary and permanent tables.
- Truncate statement issued on temporary table will effect only that session.

Thanks to the blog for references.

Wednesday, 1 July 2020

Consuming plsql database TYPE Objects from Oracle SOA

- The idea of this article is to consume single/multiple purchase orders from SOA to database table using Record/type objects.

if the Purchase order gets updated in database table if already having same purchase order id, otherwise a new record will be created.

Please find the below steps.

1. create database object types

1.1:

create or replace type PURCHASEORDER_TYPE as OBJECT (ORDER_ID NUMBER,
ORDERED_QTY          NUMBER,
ORDERED_UOM          VARCHAR2(6),
AMOUNT               NUMBER,
STATUS               VARCHAR2(10),
ORDER_DATE             DATE);

1.2:

create or replace TYPE PURCHASEORDER_TBL IS TABLE of PURCHASEORDER_TYPE;

2. Create a package header with procedures for creating single and multiple Purchase Orders

create or replace package soademo_pk

as
procedure createPO(p_purchaseOrder IN PurchaseOrder_type);
procedure createPOs(p_pos IN purchaseorder_tbl);

END soademo_pk;

procedure:

- createPO -> for creating single purchase order

- createPOs -> for creating multiple purchase orders.

3. Create a package body and implement the procedures in package header.

create or replace PACKAGE BODY SOADEMO_PK AS

procedure createPO(p_purchaseOrder IN PurchaseOrder_type)
AS
l_count integer;
BEGIN
    DBMS_OUTPUT.PUT_LINE('inside createPO !!');
    DBMS_OUTPUT.PUT_LINE('order_id: ' || p_purchaseOrder.order_id);
    DBMS_OUTPUT.PUT_LINE('ORDERED_QTY: ' || p_purchaseOrder.ORDERED_QTY);
    DBMS_OUTPUT.PUT_LINE('ORDERED_UOM: ' || p_purchaseOrder.ORDERED_UOM);
    DBMS_OUTPUT.PUT_LINE('AMOUNT: ' || p_purchaseOrder.AMOUNT);
    DBMS_OUTPUT.PUT_LINE('STATUS: ' || p_purchaseOrder.STATUS);
    DBMS_OUTPUT.PUT_LINE('ORDER_DATE: ' || p_purchaseOrder.ORDER_DATE);
    /*
    -- insert the purchase order directly.
    insert into purchase_order
    select p_purchaseOrder.order_id,
            p_purchaseOrder.ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM,
            p_purchaseOrder.AMOUNT,
            p_purchaseOrder.STATUS,
            p_purchaseOrder.ORDER_DATE from dual;
    */
-- merge the purchase order.
    MERGE INTO PURCHASE_ORDER po
    USING (select p_purchaseOrder.order_id as order_id,
            p_purchaseOrder.ORDERED_QTY as ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM as ORDERED_UOM,
            p_purchaseOrder.AMOUNT as AMOUNT,
            p_purchaseOrder.STATUS as status,
            p_purchaseOrder.ORDER_DATE as order_date from dual) pt
    ON (po.order_id=pt.order_id)
    when matched then
        update set
                ordered_qty = pt.ordered_qty,
                ordered_uom = pt.ordered_uom,
                amount      = pt.amount,
                status      = pt.status,
                order_date = pt.order_date

    when not matched then
        insert (order_id,ORDERED_QTY,ORDERED_UOM,AMOUNT,STATUS,ORDER_DATE)
        values( p_purchaseOrder.order_id,
            p_purchaseOrder.ORDERED_QTY,
            p_purchaseOrder.ORDERED_UOM,
            p_purchaseOrder.AMOUNT,
            p_purchaseOrder.STATUS,
            p_purchaseOrder.ORDER_DATE);
    l_count := sql%rowcount;
    DBMS_OUTPUT.PUT_LINE('l_count: ' || l_count);

    EXCEPTION
    when OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
        DBMS_OUTPUT.PUT_LINE('SQLCODE' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('sqlerrm' || SQLERRM);
END createPO;

procedure createPOs(p_pos IN purchaseorder_tbl)
AS
BEGIN
    -- TODO: Implementation required for procedure SOADEMO_PK.createPOs
    MERGE INTO PURCHASE_ORDER po
    USING(select * from table(p_pos)) pt
    ON (po.order_id=pt.order_id)
    when matched then
        update set
                ordered_qty = pt.ordered_qty,
                ordered_uom = pt.ordered_uom,
                amount      = pt.amount,
                status      = pt.status,
                order_date = pt.order_date

    when not matched then
        insert (order_id,ORDERED_QTY,ORDERED_UOM,AMOUNT,STATUS,ORDER_DATE)
        values( pt.order_id,
                pt.ORDERED_QTY,
                pt.ORDERED_UOM,
                pt.AMOUNT,
                pt.STATUS,
                pt.ORDER_DATE);
    NULL;
END createPOs;

END SOADEMO_PK;

4. create a datasource in WebLogic console.

5. create DB Adapter connection.

JNDI:

DB Adapter, Datasource association

6. Consume database procedures from SOA using DB adapter.

please find the project here.