RSS Feed

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
ClientIDClientName
1O_A
2O_B
3O_C
4O_D
5O_E

Select * from Bank
BranchIDBranchNameClientID
1B_1 1
2B_2 2
3B_3 3
4B_4 4
5B_5 5

Select * from Bill
InvoiceIDYearBranchIDAmount
12012-01-01 00:00:00.0001100
22012-01-01 00:00:00.0002200
32012-01-01 00:00:00.0003300
42012-01-01 00:00:00.0004400
52012-01-01 00:00:00.0005500
62012-01-01 00:00:00.0001900
72012-01-01 00:00:00.0001900


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_dateCust_IDsalesman_idAmount
101997-08-0242540
201996-01-30481800
301994-07-1491460
401995-01-29722400
501995-02-0367600
601995-03-0267720
701995-05-0697150

Select * from Client
Cust_IDNameCityIndustryType
4SamsungDelhiJ
6PanasonicOrangeJ
7NokiaJamshedpurB
9AppleJamshedpurB

Select * from Salesman
salesman_idNameAgeSalary
1Amir61140000
2Balbir3444000
5Chander3440000
7Damdar4152000
8Kumar57115000
11Jaggu3838000

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