SQL Interview questions

Scenario's:

1. I have an employee table 4 of them are male candidates and 4 of them are female candidates. I need result in such a way that alternatively I have to show male and female employees

Employee Table:

Expected Result:

Query:

SELECT NAME,GENDER FROM (

SELECT

    NAME,GENDER,DECODE(GENDER,'MALE',1,0) ORD_VAL,ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY NAME) RNK

FROM

    employee) ORDER BY RNK,DECODE(GENDER,'MALE',1,0);

Output:

2. I have Table A (Driving Table) and Table B (Lookup table).I have to pick all the values from Table-A

but if I came across any duplicates when I join with Table-B then I have to pick only one value from Table-B.

For Example:

Table A(Employee):

Table B(Department):

Expected Result:

Query:

select name,department_name from (

select e.name,d.name department_name,row_number() over (partition by rn order by d.name) rn from

(select name,department_id,rownum rn from employee) e left outer join department d on e.department_id = d.department_id)

where rn=1

Output:

Debugger in ODI 12c

 Let me take a scenario:

I have implemented SCD 3 type in ODI 12c using Customized knowledge Module. Before using it I want to check it out whether is working as per requirement or not.

Source Data:

Target Data (After full load):

After update employee_id : 100 salary to 3500 Source Data:

Final Output:

Debugging Steps:

1. Go to Mapping
2. Open it
3. Click on Debugger on top (which appears as mentioned below)

     4. Select debugging properties as mentioned below

       

       Select the context and Agent as per your mapping data load. Suspend Before First Task which will suspend immediate after the first task.

        Click Ok.

5. Debugging Mode display as mentioned below

6. Right click on step 50 and select the add break point

7. Click on Current Cursor as mentioned below

8. Then click on Resume button as mentioned below

9. Then right click on Step 50 and say get Data

10. Click on the Run Task End and right click on step 50 and say get Data

You can observe the staging table data immediately after that step.

11. Right click on Step 140 and add debugger and check whether that record is loading to target or not.

by following step 7, 8 and Step 10. Now check the data from ODI. as mentioned above .

Run from DB it is still not committed so u can't see that updated value yet.

Now I felt it good to go so I will edit break point by right click at step 140 and enable Suspend after executing the task as below:

Now click on Resume it.

Hence completed!!.. Now you can see the data reflected from DB also.

Tip 4: Physical Mapping Design cannot be modified even if the Logical Design of the Mapping is changed

Q: You want to ensure that the Physical Mapping Design cannot be modified even if the Logical Design of the Mapping is changed. What sequence of steps must you follow to achieve this

Ans:

1. Go to Mapping Editor
2. Go to the Physical tab
3. select the Is Frozen check box of the Physical Mapping Design

Tip 3: Model that works with multiple underlying technologies

 Q: You need to create a Model that works with multiple underlying technologies. How must you proceed?

Ans: Create a new generic technology to support it.

How to specify Loading Order in ODI 12c?

 From ODI 12c onwards we can load multiple tables with single mapping. In that case we need to make sure that which table need to be load first and next. For that we need to specify order.

1. Open Mapping
2. Go to Property Inspector as mentioned below:

How to specify Join Order in ODI 12c Mappings?

 In ODI, we have option of specify join order. 

1. Select Join Component
2. Go to property Inspector and specify as mentioned below:

Join Order need to enable and specify your order next to User Defined.

JSON to Table using ODI 12c

JSON to Table using ODI 12c:

Scripts

Source: [                 {                                 "id": "0001",                                 "type": "donut",                                 "name": "Cake",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" },                                                                                                 { "id": "1002", "type": "Chocolate" },                                                                                                 { "id": "1003", "type": "Blueberry" },                                                                                                 { "id": "1004", "type": "Devil's Food" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5005", "type": "Sugar" },                                                                 { "id": "5007", "type": "Powdered Sugar" },                                                                 { "id": "5006", "type": "Chocolate with Sprinkles" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 },                 {                                 "id": "0002",                                 "type": "donut",                                 "name": "Raised",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5005", "type": "Sugar" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 },                 {                                 "id": "0003",                                 "type": "donut",                                 "name": "Old Fashioned",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" },                                                                                                 { "id": "1002", "type": "Chocolate" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 } ]

Target DDL:   CREATE TABLE "DEMO"."JSON_TABLE"    (           "FILE_NAME" VARCHAR2(255 BYTE),                 "LOAD_DATE" VARCHAR2(255 BYTE),                 "ID" NUMBER(10,0),                 "NAME" VARCHAR2(255 BYTE),                 "PPU" NUMBER(10,2),                 "TYPE" VARCHAR2(255 BYTE),                 "SEQ_NUM" NUMBER(10,0),                 "BATTER_ID" NUMBER(10,0),                 "BATTER_TYPE" VARCHAR2(255 BYTE),                 "TOPPING_ID" NUMBER(10,0),                 "SEQ_TOPPING" NUMBER(10,0),                 "TOPPING_TYPE" VARCHAR2(255 BYTE)    )

Topology Configuration:

Create new Data Server under "ComplexFile" Technology as mentioned above

Go to JDBC Tab, specify 

          Select JDBC Driver and

          JDBC URL as mentioned above

[Note: If you don't have XSD handy please click on Edit nXSD and proceed to generate XSD]

Provide the properties as mentioned above:

1. DTD : XSD file location we need to provide here
2. File : Actual file location we need to provide here
3. root_elt : root_element of your XSD
4. schema : Specify schema name or file name of the XSD

Create Data Server as mentioned below:

Create Physical Schema as mentioned below: 

Please specify your own schema name.

Create Logical Schema as mentioned below:

Go to Designer Navigator and 

Create Model as specified below:

Go to Selective Reverse-Engineering tab and select as mentioned below and click on Reverse Engineering

Reverse Engineer the target data store into corresponding model.

Go to Project Accordion under designer navigator

Create Project

Create Mapping as mentioned below:

Select Join and specify Execution on Hint as "Stage" for all the joins as mentioned below:

Run the mapping

Output: