## 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

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)

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)

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.

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

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

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

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

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