Saturday, January 11, 2014

Question of the Week - 11/1/2014

Question:- Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.


First Query:-

In this case, you have to write a SQL Query using SQL JOIN which return the data which is not common to these two tables A& B as shown in the shaded part in 1st figure.


Second Query:-

 In this case, you have to write a SQL Query using SQL JOIN which will return the data from table A which is not in the table B as shown as shaded part in 2nd figure.



Third Query:-

 In this case, you have to write a SQL Query using SQL JOIN which will return the data from table B which is not in the table A as shown as shaded part in 3rd figure.








Previous Questions and their Answers


 Question of the Week - 2/1/2014 - Question 1
  
 Answer

 Question of the Week - 28/12/2013 - Question 2

 Answer

 Question of the Week -25/12/2013- Question1


 Answer

DMCA.com

9 comments:

  1. I think this will work:

    CREATE TABLE A (ID INT )
    CREATE TABLE B (ID INT)
    INSERT INTO A VALUES(1),(2),(3),(4)
    INSERT INTO B VALUES(4),(5),(6),(7)


    First Query:-
    SELECT * FROM A WHERE A.ID NOT IN(SELECT * FROM A INTERSECT SELECT * FROM B)
    UNION
    SELECT * FROM B WHERE B.ID NOT IN (SELECT * FROM A INTERSECT SELECT * FROM B)

    SECOND Query:-
    SELECT * FROM A WHERE A.ID NOT IN(SELECT * FROM A INTERSECT SELECT * FROM B)

    Third Query:-
    SELECT * FROM B WHERE B.ID NOT IN (SELECT * FROM A INTERSECT SELECT * FROM B)

    ReplyDelete
  2. First Query:
    (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)
    SECOND Query:-
    (SELECT * FROM A EXCEPT SELECT * FROM B) EXCEPT (SELECT * FROM B EXCEPT SELECT * FROM A)
    Third Query:-
    (SELECT * FROM B EXCEPT SELECT * FROM A) EXCEPT (SELECT * FROM A EXCEPT SELECT * FROM B)

    ReplyDelete
    Replies
    1. [db2inst1@Reporter_Dev1 Gaurav]$ db2 -tvf createTable.sql
      CREATE TABLE A ( ID INTEGER , VALUE VARCHAR(10) ) organize by column
      DB20000I The SQL command completed successfully.

      CREATE TABLE B ( ID INTEGER , VALUE VARCHAR(10) ) organize by column
      DB20000I The SQL command completed successfully.

      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (1, 'One')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (2, 'Two')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into a values (3, 'Three')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (3, 'Three')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (4, 'Four')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "insert into b values (5, 'Five')"
      DB20000I The SQL command completed successfully.
      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from A where A.ID not in (select A.ID from A inner join B ON A.ID = B.ID) UNION Select distinct * from B where B.ID not in (select A.ID from A inner join B ON A.ID = B.ID)"

      ID VALUE
      ----------- ----------
      1 One
      2 Two
      4 Four
      5 Five

      4 record(s) selected.

      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from A where A.ID not in (select A.ID from A inner join B ON A.ID = B.ID) "

      ID VALUE
      ----------- ----------
      1 One
      2 Two

      2 record(s) selected.

      [db2inst1@Reporter_Dev1 Gaurav]$ db2 "Select distinct * from B where B.ID not in (select A.ID from A inner join B ON A.ID = B.ID)"

      ID VALUE
      ----------- ----------
      4 Four
      5 Five

      2 record(s) selected.

      Delete
    2. Hi Vimal
      Again very good answer with good use of Except command. Keep the good work going..

      Delete
    3. Good Answer Gaurav.... keep it up

      Delete
  3. CREATE TABLE A (ID INT )
    CREATE TABLE B (ID INT)
    INSERT INTO A VALUES(1),(2),(3),(4)
    INSERT INTO B VALUES(4),(5),(6),(7)

    QUERY 1 :
    select A.ID from A left join B on A.ID = B.ID where B.ID is null
    union
    select B.ID from B left join A on B.ID = A.ID where A.ID is null

    Query 2 : select A.ID from A left join B on A.ID = B.ID where B.ID is null
    Query 3: select B.ID from B left join A on B.ID = A.ID where A.ID is null

    ReplyDelete
    Replies
    1. Hi Saptarshi,
      really very good answer with good use of Joins. Keep the good work going

      Delete
  4. Thanks to all of you for Giving the correct Answers...

    ReplyDelete