Question of the Week – 11/1/2014
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.
Answer – Objective Question (Which table is called Heap table in SQL Server)
How to find duplicate values in a table
Answer -Question of the Week – 25/12/2013- Question 1
About The Author
vivekjohari
I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com
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…