iCreate interview questions
1.Dim_Account
Account_number Ac_open_date Ac_Status Ac_Branch
100 may 12 2001 opened B1
200 apr 15 2001 closed B1
300 jun 21 2002 opened B2
400 jan 25 2001 opened B2
Fact_Account
Account_number Balance Interest_rate
100 -20 4
200 -10 3.5
300 0 2.5
400 100 2
A)Display the account numbers whose balace is zero using SQL joins.
B)Display the account numbers whose balance is negative.
c)Display the branches whose balance is positive.
D)select the account number which has second maximum balance.
E)calculate average interest rate for each branch.
G)calculate the sum of balance for each brach
2)I/P O/P
0000000As12 As12
000000003DE 3DE
00000oytu oytu
3)create a stored procedure which will takes three parameters
Original table name
Duplicate with data(1,0)
Duplicate table name
Duplicate with data=1 create a duplicate table with records.
Duplicate with data=0 create a duplicate table without records.
4)declare
cursor A is select * from cust where 1=2;
begin
open A;
loop
<logic starts>
<logic ends>
end loop;
close A;
end;
will it successfully work? or will it give no data found error?
5)How can we define two dimentional array in plsaql block.
6)write three exceptions name.
7)select tab1.col1
from tab1,tab2
where rownum=1
and tab1.col1<>1234
and tab2.col1=tab1.col1
if it give no data found then
select tab1.col1
from tab1,tab2
where rownum=1
and tab1.col1<>1234
and tab2.col1=tab1.col1
if it give no data found then
return null
how do you compine these two sql queries?
8)In my schema I have only Test1 table
what will happen for the below queries?
insert into test1(col1) values (1);
drop table test2;
9)I have two tables one is account_details another one is account_history
which account no is not present in the account_details.
*Round (f2f) 1*
1)Tell about yourself?
2)Tell about your project?
3)Tell about scd’s ?
4) How do u handle errors using try and catch block?
5)what is your approach in Incremental extract?
6)Tell about cdc concepts?sql server
7) Difference between Datawarehouse and Data mart?
8) What is the difference between Star Schema and Snowflake Schema?
9) What is the difference between OLTP and OLAP?
10) Create dynamic view in sql server and plsql?
11) How do you populate Scd1 and Scd2 with example ?
Table 1
P_id
P_type
P_desc
P_price
Draw table for Scd1 and Scd2?
12) Difference between Additive Measure and Semi Additive Measure?
13) What are challenges you faced in your project ?
Round (f2f) 2
1) Tell about your project and company?
2) Can u explain 3’rd normal form ??
3) Do you have experience plsql?
4) Are you ready to work in plsql?
5) What is challenge you faced in your project?
6) Have you create index on view?
Round (f2f) 3
1) Tell about your project ?
2) Five naming standards in ssis?
3) What are the challenges you faced on your project?
4) How will you explain scenario to your junior?
5) Tell about scd’s ?
Written Test:
1. Write a query for 2nd highest salary?
2. What is repayment loan schedule?
3. Write a query to delete duplicates?
4. We have two tables Account_master and Account_history, each have a
column cod_acct_no. cod_acct_no has to be there is in account_history but
not in account_mastrer.
5. Scenario like
Account_Detail
1111 2
1111 1
2222 3
2222 2
2222 1
Output:
1111 2
2222 3
6. Create a view for all the tables in the schema and view
should start from v_’table_name’.
7. For the below PL/SQL block. What error may raise
Declare
Cursor C1 is select * from emp where 1=2;
V_emp emp%rowtype;
Begin
Open c1;
Fetch c1 into v_emp;
Loop
<loop statements>
<loop statements>
End loop;
Close c1
End;
i. Error
ii. No data found
iii. Successfully works
8. Write a query where Sal should be greater than 300
Name Sal
Jack 300
Jill 200
Mick 300
Gen 300
9. Types of Joins
10. There will be one billion of data. Each time it has to delete 1000 and
commit. Write a procedure for this.
11. Explain about your current project.
12. Write star schema of ur project.
13. Write a block to get top ten suppliers from your star schema .
14. Performance tuning .
Icreate interview question:-
1.Tell about your self.
2.Rate your self in sql/plsql
3.Scenrio 1
Src
tgt
Zone product_name qty saleprice count_qty totsalprice
tvqty tvtotsal
North tv 30 15000 62
45000 50 25000
South computer 12 20000
North tv 20 10000
4. Scenario 2
I have three table source
cust relational table,sale1 flat file,sal2 flat file
I want all the record from
Customer to target
5.Diff between lookup and joiner
6.How you handle errors
7.Do you know debug how it will use
8.What is reading thread? explain
9.What is dtm? explain
10. Scenario
I have 30000 records when I ran the mapping it will take more time to load
explain the steps to rectify.
11.Whether source qualifier active or passive ? explain.
12.Why should we hire you?
13.Do you have any question?
1.Dim_Account
Account_number Ac_open_date Ac_Status Ac_Branch
100 may 12 2001 opened B1
200 apr 15 2001 closed B1
300 jun 21 2002 opened B2
400 jan 25 2001 opened B2
Fact_Account
Account_number Balance Interest_rate
100 -20 4
200 -10 3.5
300 0 2.5
400 100 2
A)Display the account numbers whose balace is zero using SQL joins.
B)Display the account numbers whose balance is negative.
c)Display the branches whose balance is positive.
D)select the account number which has second maximum balance.
E)calculate average interest rate for each branch.
G)calculate the sum of balance for each brach
2)I/P O/P
0000000As12 As12
000000003DE 3DE
00000oytu oytu
3)create a stored procedure which will takes three parameters
Original table name
Duplicate with data(1,0)
Duplicate table name
Duplicate with data=1 create a duplicate table with records.
Duplicate with data=0 create a duplicate table without records.
4)declare
cursor A is select * from cust where 1=2;
begin
open A;
loop
<logic starts>
<logic ends>
end loop;
close A;
end;
will it successfully work? or will it give no data found error?
5)How can we define two dimentional array in plsaql block.
6)write three exceptions name.
7)select tab1.col1
from tab1,tab2
where rownum=1
and tab1.col1<>1234
and tab2.col1=tab1.col1
if it give no data found then
select tab1.col1
from tab1,tab2
where rownum=1
and tab1.col1<>1234
and tab2.col1=tab1.col1
if it give no data found then
return null
how do you compine these two sql queries?
8)In my schema I have only Test1 table
what will happen for the below queries?
insert into test1(col1) values (1);
drop table test2;
9)I have two tables one is account_details another one is account_history
which account no is not present in the account_details.
*Round (f2f) 1*
1)Tell about yourself?
2)Tell about your project?
3)Tell about scd’s ?
4) How do u handle errors using try and catch block?
5)what is your approach in Incremental extract?
6)Tell about cdc concepts?sql server
7) Difference between Datawarehouse and Data mart?
8) What is the difference between Star Schema and Snowflake Schema?
9) What is the difference between OLTP and OLAP?
10) Create dynamic view in sql server and plsql?
11) How do you populate Scd1 and Scd2 with example ?
Table 1
P_id
P_type
P_desc
P_price
Draw table for Scd1 and Scd2?
12) Difference between Additive Measure and Semi Additive Measure?
13) What are challenges you faced in your project ?
Round (f2f) 2
1) Tell about your project and company?
2) Can u explain 3’rd normal form ??
3) Do you have experience plsql?
4) Are you ready to work in plsql?
5) What is challenge you faced in your project?
6) Have you create index on view?
Round (f2f) 3
1) Tell about your project ?
2) Five naming standards in ssis?
3) What are the challenges you faced on your project?
4) How will you explain scenario to your junior?
5) Tell about scd’s ?
Written Test:
1. Write a query for 2nd highest salary?
2. What is repayment loan schedule?
3. Write a query to delete duplicates?
4. We have two tables Account_master and Account_history, each have a
column cod_acct_no. cod_acct_no has to be there is in account_history but
not in account_mastrer.
5. Scenario like
Account_Detail
1111 2
1111 1
2222 3
2222 2
2222 1
Output:
1111 2
2222 3
6. Create a view for all the tables in the schema and view
should start from v_’table_name’.
7. For the below PL/SQL block. What error may raise
Declare
Cursor C1 is select * from emp where 1=2;
V_emp emp%rowtype;
Begin
Open c1;
Fetch c1 into v_emp;
Loop
<loop statements>
<loop statements>
End loop;
Close c1
End;
i. Error
ii. No data found
iii. Successfully works
8. Write a query where Sal should be greater than 300
Name Sal
Jack 300
Jill 200
Mick 300
Gen 300
9. Types of Joins
10. There will be one billion of data. Each time it has to delete 1000 and
commit. Write a procedure for this.
11. Explain about your current project.
12. Write star schema of ur project.
13. Write a block to get top ten suppliers from your star schema .
14. Performance tuning .
Icreate interview question:-
1.Tell about your self.
2.Rate your self in sql/plsql
3.Scenrio 1
Src
tgt
Zone product_name qty saleprice count_qty totsalprice
tvqty tvtotsal
North tv 30 15000 62
45000 50 25000
South computer 12 20000
North tv 20 10000
4. Scenario 2
I have three table source
cust relational table,sale1 flat file,sal2 flat file
I want all the record from
Customer to target
5.Diff between lookup and joiner
6.How you handle errors
7.Do you know debug how it will use
8.What is reading thread? explain
9.What is dtm? explain
10. Scenario
I have 30000 records when I ran the mapping it will take more time to load
explain the steps to rectify.
11.Whether source qualifier active or passive ? explain.
12.Why should we hire you?
13.Do you have any question?
