Hint for C # as well as SQL puzzle

Question

I looked through SO careers and came across a job in which there was a pdf file with a couple of puzzles that wanted applicants to come.

Although I am not interested in working, I still read the questions and played in Visual Studio / SMSS. The first question was quite easy to solve, although I could not think how to optimize it (I decided it in C #). The second puzzle brings up only one obvious solution, and I cannot think of others.

I’m not sure that it’s bad to discuss these issues here, but if someone can give me some hints or perhaps suggest somewhere where I can ask about it without creating any grief, this will be appreciated.

Questions here: http://www.debtx.com/doc/DebtX_Programming_Problems.pdf

I could allow the first slide, but the second leads me to other ways of solving it than the obvious. Shame there is no PM function on SO ...

Solution for the boiler for the first part of C #:

public static bool Compare(int[] num, int[] div)
{
    for (int i = 0; i < num.Length; i++)
    {
        for (int j = 0; j < div.Length; j++)
        {
            if (num[i] % div[j] == 0)
                return true;
        }
    }

    return false;
}

My SQL solutions

select Table1.Key1, Table1.Key2 from Table1 inner join Table2 on Table1.Key1 = Table2.key2 where IsDeleted=0

select * from Table1 where key1 in(select Key2 from Table2 where IsDeleted=0)

It all seems the same though

Answer 1

some examples using pseudo SQL to not give too much.

Not in

   SELECT * FROM TBL1 
   WHERE NOT IN (
            SELECT FROM TBL2  
            WHERE Deleted=0 AND Tbl2.Key1= Tbl1.Key1 AND Tbl2.Key2=Tbl1.Key2
            )

Does not exist

    SELECT * FROM TBL1 
    WHERE NOT EXISTS (
        SELECT FROM TBL2 
        WHERE Deleted =0 AND Tbl2.Key1= Tbl1.Key1 AND Tbl2.Key2=Tbl1.Key2
        ) 

Outter join is null

    SELECT * FROM TBL1 LEFT JOIN TBL2 
    WHERE TBL2.Key1 IS NULL OR Deleted=0

Answer 2

One optimization for the C # question is to sort the DIV array. You are more likely to find a match, starting with smaller numbers.

EDIT: # , , , .

SQL, () , , JOIN, IN EXISTS.

Answer 3

Ok, what solution have you already used? Immediately I think that this can be done using a subquery with IN, using LEFT OUTER JOINand filtering on NULL, or using EXISTS.

Answer 4

Spoiler alert !!!!!

SELECT
     T1.key1,
     T1.key2
FROM
    Table1 T1
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            Table2 T2
        WHERE
            T2.key1 = T1.key1 AND
            T2.key2 = T1.key2 AND
            COALESCE(T2.IsDeleted, 0) <> 1
    )

---

SELECT
     T1.key1,
     T1.key2
FROM
    Table1 T1
LEFT OUTER JOIN Table2 T2 ON
    T2.key1 = T1.key1 AND
    T2.key2 = T1.key2 AND
    COALESCE(T2.IsDeleted, 0) <> 1
WHERE
    T2.key1 IS NULL

---

SELECT
     T1.key1,
     T1.key2
FROM
    Table1 T1
WHERE
    (
        SELECT COUNT(*)
        FROM
            Table2 T2
        WHERE
            T2.key1 = T1.key1 AND
            T2.key2 = T1.key2 AND
            COALESCE(T2.IsDeleted, 0) <> 1
    ) = 0