Question of the Week – 11/1/2014

By | January 11, 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 thoughts on “Question of the Week – 11/1/2014

  1. Vimal Kumar Prajapati

    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)

    Reply
  2. Vimal Kumar Prajapati

    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)

    Reply
    1. kumar gourav

      [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.

      Reply
    2. admin Post author

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

      Reply
  3. Saptarshi Ghosh

    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

    Reply
    1. admin Post author

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

      Reply

Leave a Reply