Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Interview Questions

Question of the Week – 11/1/2014

Byvivekjohari

Jan 11, 2014 #Question of the Week
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.

Trending
Advantages of SQL Stored Procedure

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

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By 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

Related Post

Interview Questions SQL Advanced SQL Indexes

Interview question – Is Clustered index on column with duplicate values possible?

Aug 13, 2015 vivekjohari
Interview Questions

Question of the Month (July)

Aug 3, 2015 vivekjohari
Interview Questions SQL Basic SQL Server

Script to find complete months between two given dates

Jul 10, 2015 vivekjohari
31 thought on “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)

    Reply

  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)

    Reply

    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.

      Reply

  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

    Reply

Leave a Reply

You missed

Career Coaching

How to Update Your Resume After a Career Break: A Strategic Approach

February 28, 2025 Megha No Comments
Career Coaching

The Importance of Soft Skills in the Modern Workplace

February 28, 2025 Megha No Comments
Career Coaching

Unlocking the Power of Resume Keywords: Your Guide to Standing Out

February 28, 2025 Megha No Comments
Career Coaching

Top 10 High-Demand Careers in India for 2025: Secure Your Future Today

February 28, 2025 Megha No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading