JDBC/SQL/Financial Interview Question

[Agnes Hyndman]

Hey,
I have been asked the following questions during a recent interview:
You have two tables:
A
--------------
| key | data |
--------------
| 1 | |
--------------
| 2 | |
--------------
| 4 | |
--------------
| 6 | |
--------------
| 8 | |
--------------
| 10 | |
--------------
| 11 | |
--------------
| 12 | |
--------------
B
--------------
| key | data |
--------------
| 1 | |
--------------
| 4 | |
--------------
| 8 | |
--------------
| 9 | |
--------------
| 10 | |
--------------
| 12 | |
--------------
| 15 | |
--------------
| 17 | |
--------------
1. Question_1: How would you select "key" from table A that is both in A and B ?
My answer:
SELECT A.key
FROM A
where A.key = B.key;
2. Question_2: How would you select "key" from table A that is only in A (should return 2, 6, 11)
My answer:
SELECT A.key
FROM A
WHERE A.key NOT IN (SELECT key from B);
Is this correct ?
3. Question_3: Solve question_2 without using subqueries. Is it even possible ?
SELECT A.key
FROM A
WHERE A.key <> B.key;
???
4. Trader comes up to you and says that he needs you to develop an order-entry screen. Describe how you would go about doing it.
Any input will be greately appreciated.
Thanks !
[ May 21, 2002: Message edited by: Agnes Hyndman ]

[Reid M. Pinchback]

Originally posted by Agnes Hyndman:
Hey,
I have been asked the following questions during a recent interview:
...
4. Trader comes up to you and says that he needs you to develop an order-entry screen. Describe how you would go about doing it.

This one I'd put in the stupid-question category. Having worked in a brokerage house, the answer is 'the trader should know better'. Changes to systems that trade on public exchanges don't get made just because a trader walked up to a developer and asked for it. If you want to give a nice answer:
Don't commit to creating the functionality, ask him or her for some initial requirements information and the business issue behind the change, then give the data to your boss. Your boss will have to walk it through the correct process.

[Michael Matola]

Originally posted by Agnes Hyndman:
1. Question_1: How would you select "key" from table A that is both in A and B ?
My answer:
SELECT A.key
FROM A
where A.key = B.key;

select a.key
  from a , b
 where a.key = b.key ;

You need to have b in the from part.
2. Question_2: How would you select "key" from table A that is only in A (should return 2, 6, 11)
My answer:
SELECT A.key
FROM A
WHERE A.key NOT IN (SELECT key from B);
Is this correct ?
Yep. You could also do it this way:

select a.key
  from a
 where not exists ( select 'x' from b where b.key = a.key ) ;

3. Question_3: Solve question_2 without using subqueries. Is it even possible ?
SELECT A.key
FROM A
WHERE A.key <> B.key;
Sure.

( select a.key
    from a , b
   where a.key = b.key (+) )
minus
( select a.key
    from a , b
   where a.key = b.key ) ;

Small chance the syntax isn't perfect, but you get the idea: outer join b to a, then inner join the two tables, then subtract the results. Two selects, but neither is a sub-.

[Shura Balaganov]

Originally posted by Michael Matola:
Originally posted by Agnes Hyndman:
3. Question_3: Solve question_2 without using subqueries. Is it even possible ?
SELECT A.key
FROM A
WHERE A.key <> B.key;
Sure.

( select a.key
    from a , b
   where a.key = b.key (+) )
minus
( select a.key
    from a , b
   where a.key = b.key ) ;

Small chance the syntax isn't perfect, but you get the idea: outer join b to a, then inner join the two tables, then subtract the results. Two selects, but neither is a sub-.

This particular scenario has an easier solution using MINUS (I think it'll work both in Oracle and SQL server):
select a.key as key
from a
minus
select b.key as key
from b
Michael is right in the general idea though, OuterJoin MINUS InnerJoin will give you all rows in table A that do not exist in table B.
I love question #4!!! What you do is this, since they trust you this much:
1. Open an account with your firm, under false name.
2. Open an offshore account with a false name
3. Write order entry screen that has a trojan horse in it.
4. Leave the company.
5. At certain date, make trojan horse wire all the money through your local account to your offshore bank account. At the same time, walk into the bank and perform a cash withdraw (it is preferrable to be in Switzerland, because they won't release your account info so easily, even to Interpol).
6. With this cash, move on with your life.
Beware though, that FBI might have a profile on you, and you might never be able to come to US again. Laugh at this from your own castle.
Shura

[Michael Matola]

Originally posted by Shura Balaganov:
This particular scenario has an easier solution using MINUS (I think it'll work both in Oracle and SQL server):
select a.key as key
from a
minus
select b.key as key
from b

Molodets, Shurik! Works in Oracle. (Don't have access to SQL Server and MS Access doesn't support minus.)

Michael is right in the general idea though, OuterJoin MINUS InnerJoin will give you all rows in table A that do not exist in table B.

Right, but your version is cleaner. I was just stuck on the idea that you had to have an outer join in there somewhere to tease out the unmatched rows.

[Agnes Hyndman]

Yeah, I really enjoyed reading replies about designing an order entry form.....they were rather entertaining....but I think all he wanted was to "get a feel" for my design skills. I guess it fits into the category of "How would you go about designing an application ?"
Anyways, even though I said some nonsense things during the last interview, they called me up for a second interview - got to meet more IT people, some brokers, they gave me a tour of the whole place (again), said thing like "this is probably where you would be sitting".....but still no concrete offer !
What happens now ? How long of a wait am I in for ? Oh yeah, this is a financial company in NYC......it's absolutely killing me !!! I need this job !!!