Lookup components - Advantages

Scenario 1:
If the lookup value is not available (no match row) then I need to populate default value without any expression.

For example:
Stage fact table is having "department_id" where as in Dimension table is not have that specific "department_id". In that case we need to populate 0 for "deparment_wid".

Scenario 2:
For one lookup code we are having multiple lookup values but we have to pick only one value without using distinct , aggregate and analytical functions

For example:
If lookup table is versioned table (i.e., one lookup code will have multiple values one is current and rest are old values)

Scenario 1:

Employee table is having department_id : 10
Whereas department table is not having deparment_id : 10

Scenario 2:

In department table for department_id : 90 we are having two records but only one is active record

(we can understand based on effective date)

Output - Fact Table

Whose department_id is not exists in department table for those department_wid is populated as 0

for rest those are populated from dimension table (row_wid column) as below

If you observe we are having multiple values for department_id : 90 but we picked only row_wid in the fact_table i.e., 11 (single value picked)

Approach:

In the lookup component, we can see match row rules

Multiple Match rows: (scenario 2) Select first single row (i.e., eff_dt desc - row_wid: 11)
Return a row with the following default values: (scenario 1) row_wid:0 (i.e., whenever there is no match then it will populate with 0)

Please comment out if you have any questions!!!!!!!!

Usuage of In and Exists - Taken from Oracle Document (Interview Question)

Example 1: Using IN - Selective Filters in the Subquery
This example demonstrates how rewriting a query to use IN can improve performance. This query identifies all employees who have placed orders on behalf of customer 144.

The following SQL statement uses EXISTS:
SELECT /* EXISTS example */
         e.employee_id, e.first_name, e.last_name, e.salary
  FROM employees e
 WHERE EXISTS (SELECT 1 FROM orders o                  /* Note 1 */
                  WHERE e.employee_id = o.sales_rep_id   /* Note 2 */
                    AND o.customer_id = 144);            /* Note 3 */


Note 1: This shows the line containing EXISTS.
Note 2: This shows the line that makes the subquery a correlated subquery.
Note 3: This shows the line where the correlated subqueries include the highly selective predicate customer_id = number.

Execution Plan:

Rewriting the statement using IN results in significantly fewer resources used.

SELECT /* IN example */
         e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
   WHERE e.employee_id IN (SELECT o.sales_rep_id         /* Note 4 */
                             FROM orders o
                            WHERE o.customer_id = 144);  /* Note 3 */


Note 3: This shows the line where the correlated subqueries include the highly selective predicate customer_id = number
Note 4: This indicates that an IN is being used. The subquery is no longer correlated, because the IN clause replaces the join in the subquery.

Execution Plan:

Example 2: Using EXISTS - Selective Predicate in the Parent

This example demonstrates how rewriting a query to use EXISTS can improve performance. This query identifies all employees from department 80 who are sales reps who have placed orders.
The following SQL statement uses IN:

SELECT /* IN example */
         e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
    FROM employees e
   WHERE e.department_id = 80                                    /* Note 5 */
     AND e.job_id        = 'SA_REP'                              /* Note 6 */
     AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */


Note 4: This indicates that an IN is being used. The subquery is no longer correlated, because the IN clause replaces the join in the subquery.
Note 5 and 6: These are the selective predicates in the parent SQL.

Execution Plan:

Rewriting the statement using Exists results in significantly fewer resources used.

SELECT /* EXISTS example */
         e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
   WHERE e.department_id = 80                           /* Note 5 */
     AND e.job_id        = 'SA_REP'                     /* Note 6 */
     AND EXISTS (SELECT 1                               /* Note 1 */
                   FROM orders o
                  WHERE e.employee_id = o.sales_rep_id);  /* Note 2 */

Note 1: This shows the line containing EXISTS.
Note 2: This shows the line that makes the subquery a correlated subquery.
Note 5 & 6:These are the selective predicates in the parent SQL.

Execution Plan:

Data Engineer - Written Exam Questions

Complex SQL Written Questions for Data Engineer

1.    We need to populate male and female alternatively

For Example:

Above records need to Populate as below:

Ans:

2.    Find all the students whose marks greater than the average marks in each subject within a class

For Example:

Expected Output:

Output:

3.    Find the data given below, and display the data with cumulative values

CREATE TABLE MONTHLYVOLUME   (YEAR NUMBER(4),   MONTH CHAR(3),   SALES NUMBER(3)); / INSERT INTO MONTHLYVOLUME VALUES (2007,'JAN',11); INSERT INTO MONTHLYVOLUME VALUES (2007,'FEB',9); INSERT INTO MONTHLYVOLUME VALUES (2007,'MAR',7); INSERT INTO MONTHLYVOLUME VALUES (2007,'APR',5); INSERT INTO MONTHLYVOLUME VALUES (2007,'MAY',14); INSERT INTO MONTHLYVOLUME VALUES (2007,'JUN',6); INSERT INTO MONTHLYVOLUME VALUES (2007,'JUL',8); INSERT INTO MONTHLYVOLUME VALUES (2007,'AUG',17); INSERT INTO MONTHLYVOLUME VALUES (2007,'SEP',18); INSERT INTO MONTHLYVOLUME VALUES (2007,'OCT',19); INSERT INTO MONTHLYVOLUME VALUES (2007,'NOV',12); INSERT INTO MONTHLYVOLUME VALUES (2007,'DEC',1); INSERT INTO MONTHLYVOLUME VALUES (2008,'JAN',4); INSERT INTO MONTHLYVOLUME VALUES (2008,'FEB',7); INSERT INTO MONTHLYVOLUME VALUES (2008,'MAR',8); INSERT INTO MONTHLYVOLUME VALUES (2008,'APR',21); INSERT INTO MONTHLYVOLUME VALUES (2008,'MAY',23); INSERT INTO MONTHLYVOLUME VALUES (2008,'JUN',24); INSERT INTO MONTHLYVOLUME VALUES (2008,'JUL',17); INSERT INTO MONTHLYVOLUME VALUES (2008,'AUG',18); INSERT INTO MONTHLYVOLUME VALUES (2008,'SEP',15); INSERT INTO MONTHLYVOLUME VALUES (2008,'OCT',13); INSERT INTO MONTHLYVOLUME VALUES (2008,'NOV',15); INSERT INTO MONTHLYVOLUME VALUES (2008,'DEC',16); INSERT INTO MONTHLYVOLUME VALUES (2009,'JAN',4); INSERT INTO MONTHLYVOLUME VALUES (2009,'FEB',5); INSERT INTO MONTHLYVOLUME VALUES (2009,'MAR',8); INSERT INTO MONTHLYVOLUME VALUES (2009,'APR',1); INSERT INTO MONTHLYVOLUME VALUES (2009,'MAY',3); INSERT INTO MONTHLYVOLUME VALUES (2009,'JUN',2); INSERT INTO MONTHLYVOLUME VALUES (2009,'JUL',7); INSERT INTO MONTHLYVOLUME VALUES (2009,'AUG',8); INSERT INTO MONTHLYVOLUME VALUES (2009,'SEP',5); INSERT INTO MONTHLYVOLUME VALUES (2009,'OCT',3); INSERT INTO MONTHLYVOLUME VALUES (2009,'NOV',1); INSERT INTO MONTHLYVOLUME VALUES (2009,'DEC',6); INSERT INTO MONTHLYVOLUME VALUES (2010,'JAN',4); INSERT INTO MONTHLYVOLUME VALUES (2010,'FEB',17); INSERT INTO MONTHLYVOLUME VALUES (2010,'MAR',8); INSERT INTO MONTHLYVOLUME VALUES (2010,'APR',2); INSERT INTO MONTHLYVOLUME VALUES (2010,'MAY',2); INSERT INTO MONTHLYVOLUME VALUES (2010,'JUN',4); INSERT INTO MONTHLYVOLUME VALUES (2010,'JUL',7); INSERT INTO MONTHLYVOLUME VALUES (2010,'AUG',8); INSERT INTO MONTHLYVOLUME VALUES (2010,'SEP',25); INSERT INTO MONTHLYVOLUME VALUES (2010,'OCT',13); INSERT INTO MONTHLYVOLUME VALUES (2010,'NOV',19); INSERT INTO MONTHLYVOLUME VALUES (2010,'DEC',18);

Expected Output:

Actual Output:

1.    Find all the students who enrol for all the courses in the particular year

For Example:

Expected Output:

Actual Output:

2.    Find out the top 3 highest sold products

For Example:

Expected Output:

Actual Output:

3.    Based on the call logs find out the customer whose first call and last call is same person within a day

For Example:

Expected Output:

Actual Output:

4.    Find out all the sales representative who involves in sales of subsequent day as well

For Example:

Expected Output:

Actual Output:

Purpose of default Physical schema for a dataserver - Interview Question

Purpose of default Physical schema for a dataserver:

A CDC common infrastructure for the data server is installed in the Work Schema for the Oracle Data Integrator physical schema that is flagged as Default for this data server.

This common infrastructure contains information about subscribers, consistent sets, etc. for all the journalized schemas of this data server. This common infrastructure consists of tables whose names are prefixed with SNP_CDC_.

How to implement continue option in Oracle PL/SQL block?

PL/SQL:

DECLARE 
TNAME VARCHAR2(100);
BEGIN
    FOR I IN (SELECT TABLE_NAME FROM DM_TABLES)
    LOOP
       begin 
        SELECT TABLE_NAME INTO TNAME FROM ALL_TAB_COLS WHERE OWNER ='SYSTEM' AND TABLE_NAME = I.TABLE_NAME AND COLUMN_NAME='VERSION';
        
        EXCEPTION
            WHEN NO_DATA_FOUND THEN GOTO end_loop;
            
        end;
        INSERT INTO DM_TABLE_VERSION VALUES(TNAME);
        <<end_loop>>
        null;  
    END LOOP;
    COMMIT;
END;


Note:
<<end_loop>> - Lable name


/*Whenever there is no table with version column then I need to skip that particular record, so I need to go end of loop and followed by null*/
EXCEPTION
            WHEN NO_DATA_FOUND THEN GOTO end_loop;

How to populate/retrieve records oracle in customize order?

Scenario:

I have records in the following order:

I need to retrieve the records in the following order:

(i.e., Priority of retrieve records should be based on currency value - 1. USA , 2 . IND 3. SL 4. PAK and 5. BAG)

Query and Output:

How to get deleted the records even after commit?

In Oracle, there is concept called flash back query. Using this we are able to get the data back into table.

Scripts:

/*Step 1: */

DROP TABLE STUDENT_TEST;

/*Step 2:*/
CREATE TABLE STUDENT_TEST
(SID NUMBER,
SNAME VARCHAR2(100));

/*Step 3:*/

INSERT INTO STUDENT_TEST VALUES(1,'TRINESH');
INSERT INTO STUDENT_TEST VALUES(2,'SHANTHI');
INSERT INTO STUDENT_TEST VALUES(3,'TEJESH');
INSERT INTO STUDENT_TEST VALUES(4,'VASAVI');
INSERT INTO STUDENT_TEST VALUES(5,'LAKSHMI');
COMMIT;

/*Step 4:*/
DELETE FROM STUDENT_TEST;
COMMIT;

/*Step 5:*/
SELECT * FROM STUDENT_TEST;

/*Step 6:*/
INSERT INTO STUDENT_TEST
SELECT * FROM STUDENT_TEST AS OF TIMESTAMP TO_TIMESTAMP('15-MAR-2018 03:41:01.750');
COMMIT;

/*Step 7:*/
SELECT * FROM STUDENT_TEST;

At Step 4 we are deleting all the records and commit. Many of us think that if we delete the records and commit it is impossible to get the data back. But there is flexibility that we can get the data back.

At Step 5: we are cross checking whether really deleted or not.

At Step 6: We are re-inserting records back to table using flash back query.
[Note: We have to specify the timestamp at what time you need to go back and get the data]

At Step 7: We are able to see our records back.

Output:

FinalOutput: