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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged . Bookmark the permalink.

31 Responses to Question of the Week – 11/1/2014

  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)

    • kumar gourav says:

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

    • admin says:

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

    • admin says:

      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

  4. admin says:

    Thanks to all of you for Giving the correct Answers…

  5. Pingback: JDOt-Ova-Flowin

  6. Pingback: fun88

  7. Pingback: ww88 casino

  8. Pingback: MyWeb

  9. Pingback: Shoarmazaak hengelo

  10. Pingback: Crystal Meth kaufen

  11. Pingback: Spray Drying Equipment Market Projections Analysis 2017 – 2027

  12. Pingback: Drogen kaufen im Darknet

  13. Pingback: https://novushub.com/hindi-gana-video/

  14. Pingback: ซีเกมส์ 2019

  15. Pingback: indoqqpoker

  16. Pingback: cdrqq

  17. Pingback: middle tennessee vs western kentucky live

  18. Pingback: www.nuguru.info

  19. Pingback: kompasqq

  20. Pingback: www.ratucapsa1.club

  21. Pingback: senangpoker

  22. Pingback: elang qq

  23. Pingback: ร้านเช่าชุดราตรี

  24. Pingback: 12bet link

  25. Pingback: iPhone XR Screen Replacement for LCD Digitizer Amazon Best Seller

  26. Pingback: dang nhap w88

Leave a Reply