Answers to Interview Questions
VRR, April 11, 2017 - 6:57 pm UTC
Hi Tom,
I have attended an interview recently and I am able to answer below questions, could you please to answer these two questions. Oracle Database version is 11g.
Q1) I have table cricket having one column team, the data in the table as below
team
-----
IND
PAK
BAN
SRI
output:
-------
team_a team_b
------ ------
IND PAK
IND BAN
IND SRI
PAK BAN
PAK SRI
BAN SRI
Interviewer gave me clue as use cross join, but I didn't produce above output?
script:
create table cricket(team varchar2(50));
insert into cricket values('IND');
insert into cricket values('PAK');
insert into cricket values('BAN');
insert into cricket values('SRI');
commit;
select * from cricket;
--this is output I provided, but its not give above
select a.team as team_a,b.team team_b from cricket a, cricket b where a.team<>b.team;
Q2) Write function to generate sequence numbers without using the sequence?
When we call each time it should produce output similar to sequence value.
Ex:- select fucn1() from dual; --1
select fucn1() from dual; --2 ..etc
Thanks
VRR
April 12, 2017 - 5:11 am UTC
For Q1
SQL> create table cricket(team varchar2(50));
Table created.
SQL> insert into cricket values('IND');
1 row created.
SQL> insert into cricket values('PAK');
1 row created.
SQL> insert into cricket values('BAN');
1 row created.
SQL> insert into cricket values('SRI');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from cricket home, cricket away
3 where home.team < away.team;
TEAM TEAM
-------------------------------------------------- -----------
BAN IND
BAN PAK
BAN SRI
IND PAK
IND SRI
PAK SRI
6 rows selected.
So basically we're joining every row to every other, and then eliminating ones that we dont need.
FOr Q2 - its funny, I see this question a lot, and its just a terrible terrible question. It's like saying "Query some data without using the SELECT clause"...It's nonsense. Anyway... you could do this:
SQL> create table t ( seq int );
Table created.
SQL> insert into t values (0);
1 row created.
SQL>
SQL> create or replace
2 function f return int is
3 pragma autonomous_transaction;
4 x int;
5 begin
6 update t
7 set seq = seq + 1
8 returning seq into x;
9
10 commit;
11 return x;
12 end;
13 /
Function created.
SQL>
SQL> select f from dual;
F
----------
1
SQL> select f from dual;
F
----------
2
SQL> select f from dual;
F
----------
3
Thanks a lot !
VRR, April 12, 2017 - 3:13 pm UTC
Great answers as always.
How to insert the data into oracleDB using Oracle SOA
kumar, September 11, 2017 - 8:55 pm UTC
Hi Tom,
How can we insert XML type which has more than 4000 characters form the Oracle soa to Oracle DB
September 12, 2017 - 2:55 pm UTC
How is this relevant to any of the content above?
que?
Chuck Jolley, September 13, 2017 - 3:22 pm UTC
3. You mean recompile it? Yes. Assuming you have the correct privileges. Doing so is very bad practice though!
I don't understand this one. How else could you fix a bug or change the functioning of a procedure inside a package?
September 13, 2017 - 5:06 pm UTC
I read that as:
"Can you call a package to recompile another procedure?"
As in, call:
create or replace procedure ...
Which is a terrible idea.
But if they just meant:
"Is it possible to change a procedure in a package to have new code?"
Then yes, absolutely you're right. The joys of answering with incomplete information.
Ahh.
Chuck Jolley, September 13, 2017 - 8:15 pm UTC
I didn't think of reading it that way because the idea never occurred to me.
But actually, you may be right.
September 15, 2017 - 2:36 pm UTC
Perhaps. I've seen too many people ask how to do weird things here sometimes I miss the "obvious" reason ;)
Nina, February 13, 2018 - 7:06 pm UTC
Tom, For question 1, i understand that cross join gives all the possible combinations but i don't get the where clause, i mean how is it the < getting the desired output. I created the table and tired it, it is giving the desired output but i couldn't understand how. Can you please explain?
February 14, 2018 - 5:28 pm UTC
First up the cross join gives you every row from the table combined with itself. Which looks like:
TEAM TEAM
BAN BAN
BAN IND
BAN PAK
BAN SRI
IND BAN
IND IND
IND PAK
IND SRI
PAK BAN
PAK IND
PAK PAK
PAK SRI
SRI BAN
SRI IND
SRI PAK
SRI SRI
Now you only want one row for each team.
where home.team < away.team
filters out all those combinations where the away team is less than or equal to the home team alphabetically. B is before I, so BAN is less than IND and so on.
Interview question on Constraints
Rami Reddy, July 14, 2018 - 8:18 pm UTC
I have two tables as below.
Table1 --> col1 (pk) and col2(fk-Table2(col1))
Table2 --> col1 (pk) and col2(fk-Table1(col1))
Lets assume both tables are empty, when we try to insert data on table1 or table2 individually its fail's with constraint error with no parent key found, How to achieve to insert the data into those two tables without violating constraint error, without disabling the Constraints?
Script:
create table table1(col1 number primary key, col2 number);
create table table2(col1 number primary key, col2 number references table1 (col1));
alter table table1 add constraint fk_table1 foreign key (col2) references table2(col1);
Thanks and Regards,
VRR
July 16, 2018 - 9:05 am UTC
You need to make at least one of them deferrable:
create table table1(col1 number primary key, col2 number);
create table table2(col1 number primary key, col2 number references table1 (col1));
alter table table1 add constraint fk_table1
foreign key (col2) references table2(col1)
deferrable initially deferred;
insert into table1 values ( 1, 2 );
insert into table2 values ( 2, 1 );
commit;
select * from table1;
COL1 COL2
1 2
select * from table2;
COL1 COL2
2 1
Thanks Tom, Great answer as always.
Rami Reddy, July 25, 2018 - 6:58 pm UTC
Can we achieve the same by using trigger?
Thanks and Regards,
Rami Reddy
July 26, 2018 - 2:33 am UTC
The sequence of the O/P is not matched
Nilesh Kumar, July 09, 2019 - 11:34 am UTC
Hey Tom,
SQL> select *
2 from cricket home, cricket away
3 where home.team < away.team;
TEAM TEAM
-------------------------------------------------- -----------
BAN IND
BAN PAK
BAN SRI
IND PAK
IND SRI
PAK SRI
6 rows selected.
Here the O/P is sorted alphabetically as you have already explained, but lets say the interviewer asked to maintain the O/P sequence as given in the question (which is basically sorted in the sequence of data stored into the table), and cannot be sorted either in forward or in reverse. So how can we do it.
July 09, 2019 - 11:43 am UTC
The easiest way is to add a sort column to the table that stores the order you want to display the rows. Set it appropriately in your inserts. And order by that.
A reader, July 10, 2019 - 11:53 am UTC
unused column
saad, July 10, 2019 - 11:54 am UTC
hello
i want to help about :
i cannot execute
alter table e_emp3 set unused fname online;
when i delete online keyword it is executed, when i add online keyword give me error
ora00933 error
July 10, 2019 - 4:05 pm UTC
I'm not sure what this has to do with the original question?
Anway, the online option was only added in 12.1.
Oracle inventory safety stock maintain
Rahimul Bakas, February 15, 2020 - 1:07 pm UTC
How we can map in oracle and which process for below two cases.
1. Everyweek 50 nos each (Finished Goods) received from supplier should be shipped to above 2 customers on with ….Business does not wants to carry the inventory at thiswarehouse for these finished goods sourced from supplier for more than a week.
2.Quantityavailable on the release/PO should not be visible to receivers while receivingthe items in receiving Org.
I request you please give map idea/process in oracle.
rahimulsk@gmail.com
mob-+91976531308
February 17, 2020 - 1:49 am UTC
Sorry - I don't know what you're referring to, and how it applies to this question.