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
Question of the Week -25/12/2013- Question1
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
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)
Good Answer Vimal
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)
[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.
Hi Vimal
Again very good answer with good use of Except command. Keep the good work going..
Good Answer Gaurav…. keep it up
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
Hi Saptarshi,
really very good answer with good use of Joins. Keep the good work going
Thanks to all of you for Giving the correct Answers…