SQL JOIN Interview Questions

SQL JOIN Interview Questions

These are three examples of SQL Join Interview Questions:

Example 1:
Below are three tables: Client, Bank and Bill. The question is based on these three tables.

Creating tables:

CREATE TABLE Client
(
ClientID int,
ClientName varchar(255),
Primary Key (ClientID)
);

CREATE TABLE Bank
(
BranchID int,
BranchName varchar(255),
ClientID int,
Primary Key (BranchID),
FOREIGN KEY (ClientID) REFERENCES Client (ClientID)
);

CREATE TABLE Bill
(
InvoiceID int,
Year DateTime,
BranchID int,
Amount int,
Primary Key (InvoiceID),
FOREIGN KEY (BranchID) REFERENCES Bank(BranchID)
);

Inserting data into tables:

insert into Client values (1, 'O_A')
insert into Client values (2, 'O_B')
insert into Client values (3, 'O_C')
insert into Client values (4, 'O_D')
insert into Client values (5, 'O_E')

insert into Bank values (1, 'B_1', 1)
insert into Bank values (2, 'B_2', 2)
insert into Bank values (3, 'B_3', 3)
insert into Bank values (4, 'B_4', 4)
insert into Bank values (5, 'B_5', 5)

insert into Bill values (1, '2012-01-01 00:00:00.000',    1,    100)
insert into Bill values (2, '2013-01-01 00:00:00.000',    2,    200)
insert into Bill values (3, '2012-01-01 00:00:00.000',    3,    300)
insert into Bill values (4, '2017-01-01 00:00:00.000',    4,    400)
insert into Bill values (5, '2012-01-01 00:00:00.000',    5,    500)
insert into Bill values (6, '2012-01-01 00:00:00.000',    1,    900)
insert into Bill values (7, '2013-01-01 00:00:00.000',    1,    900)

Verifying data in the tables:

Select * from Client

ClientID	ClientName
1	O_A
2	O_B
3	O_C
4	O_D
5	O_E

Select * from Bank

BranchID	BranchName	ClientID
1	B_1	1
2	B_2	2
3	B_3	3
4	B_4	4
5	B_5	5

Select * from Bill

InvoiceID	Year	BranchID	Amount
1	2012-01-01 00:00:00.000	1	100
2	2012-01-01 00:00:00.000	2	200
3	2012-01-01 00:00:00.000	3	300
4	2012-01-01 00:00:00.000	4	400
5	2012-01-01 00:00:00.000	5	500
6	2012-01-01 00:00:00.000	1	900
7	2012-01-01 00:00:00.000	1	900

Question:
Retrieve all invoices from table bill for year 2012 and 2013 which belong to client ‘O_A’:

Select i.InvoiceID,i.YEAR, i.BranchID, i.Amount from Bill as i
join Bank as b on i.BranchID = b.BranchID
join Client as o on o.ClientID = b.ClientID
where i.Year >= '2012-01-01' and i.Year <= '2013-01-01'
and o.ClientName = 'O_A'

Result:


Example 2:

Below are three tables: salesman, Client and Items. The questions below are based on these three tables.

Creating tables:

CREATE TABLE salesman
(
salesman_id int,
Name varchar(255),
Age int,
Salary int,
Primary key (salesman_id)
);

CREATE TABLE Client
(
Cust_ID int,
Name varchar(255),
City varchar(255),
IndustryType char,
Primary key (Cust_ID)
);

CREATE TABLE Items
(
Number int,
Order_date date,
Cust_ID int,
salesman_id int,
Amount int,
Foreign Key (Cust_ID) references Client (Cust_ID),
Foreign Key (salesman_id) references salesman (salesman_id)
);

Inserting data into tables:

Insert into salesman values (1, 'Amir', 61, 140000)
Insert into salesman values (2, 'Balbir', 34, 44000)
Insert into salesman values (5, 'Chander', 34, 40000)
Insert into salesman values (7, 'Damdar', 41, 52000)
Insert into salesman values (8, 'Kumar', 57, 115000)
Insert into salesman values (11, 'Jaggu', 38, 38000)

Insert into Client values (4, 'Samsung','Delhi', 'J')
Insert into Client values (6, 'Panasonic','Orange', 'J')
Insert into Client values (7, 'Nokia','Jamshedpur', 'B')
Insert into Client values (9, 'Apple','Jamshedpur', 'B')

Insert into Items values (10, '8/2/97', 4, 2, 540)
Insert into Items values (20, '1/30/96', 4, 8, 1800)
Insert into Items values (30, '7/14/94', 9, 1, 460)
Insert into Items values (40, '1/29/95', 7, 2, 2400)
Insert into Items values (50, '2/3/95', 6, 7, 600)
Insert into Items values (60, '3/2/95', 6, 7, 720)
Insert into Items values (70, '5/6/95', 9, 7, 150)

Verifying data in the tables:

Select * from Items

Number	Order_date	Cust_ID	salesman_id	Amount
10	1997-08-02	4	2	540
20	1996-01-30	4	8	1800
30	1994-07-14	9	1	460
40	1995-01-29	7	2	2400
50	1995-02-03	6	7	600
60	1995-03-02	6	7	720
70	1995-05-06	9	7	150

Select * from Client

Cust_ID	Name	City	IndustryType
4	Samsung	Delhi	J
6	Panasonic	Orange	J
7	Nokia	Jamshedpur	B
9	Apple	Jamshedpur	B

Select * from Salesman

salesman_id	Name	Age	Salary
1	Amir	61	140000
2	Balbir	34	44000
5	Chander	34	40000
7	Damdar	41	52000
8	Kumar	57	115000
11	Jaggu	38	38000

Question 1:
Get the names of all salespersons that have an order with Samsung.

Select distinct (s.name) from salesman as s 
join Items as o on s.Salesman_id = o.Salesman_id 
join Client as c on c.Cust_ID = o.Cust_ID
where c.Name = 'Samsung'

Result:
name
Balbir
Kumar

Question 2:
Get the names of all salespersons that do not have any order with Samsung.

Select name from salesman where name not in  (
Select distinct (s.name) from salesman as s 
join Items as o on s.salesman_id = o.salesman_id 
join Client as c on c.Cust_ID = o.Cust_ID
where c.Name = 'Samsung')

Result:
name
Amir
Chander
Damdar
Jaggu

Question 3:
Get the names of salespersons that have 2 or more orders.

Select s.name from salesman as s join Items as o on s.salesman_id = o.salesman_id
group by s.name
having count (*) >= 2

Result:
name
Balbir
Damdar

Question 4:
Find the third highest salary:

select min (salary)
from (select distinct top 3 salary from salesman
order by Salary desc) as a

Result:
(No column name)
52000

Question 5:
Find the third lowest salary:

select max (salary)
from (select distinct top 3 salary from salesman
order by Salary asc) as a

Result:
(No column name)
44000

Example 3:

Below example has been taken from Stackoverflow:

CREATE TABLE A
(
A int,
);

CREATE TABLE B
(
B int,
);

Insert into A values (1)
Insert into A values (2)
Insert into A values (3)
Insert into A values (4)

Insert into B values (3)
Insert into B values (4)
Insert into B values (5)
Insert into B values (6)

Select * from A

A
1
2
3
4

Select * from B

B
3
4
5
6

Question 1:
What will be the query and result of inner join between tables A and B?

Select * from a 
INNER JOIN b 
on a.a = b.b;

A	B
3	3
4	4

Question 2:
What will be the query and result of full outer join between tables A and B?

Select * from a 
FULL OUTER JOIN b 
on a.a = b.b;

A	B
1	NULL
2	NULL
3	3
4	4
NULL	5
NULL	6

Question 3:
What will be the query and result of left outer join between tables A and B?

Select * from a 
LEFT OUTER JOIN b 
on a.a = b.b;

A	B
1	NULL
2	NULL
3	3
4	4