6. SQL Joins

Question 1

_ combines rows from multiple tables, while _ combines columns from multiple tables. (join/set)

Answer 1

1. SET
2. JOIN

join combines based on key matching, while set combines based on value matching.

Question 2

Three purposes of Joins in SQL?

Answer 2

1. Combine data from other table.
2. Filtering based on data from other table.
3. Reshape own data (using self join).

Note: It is better to use Window Functions instead of self joins

Question 3

Find name and total sales of customers who have made at least one order.
customers: id, name, country, score
MyOrders: OrderId, CustomerId, OrderDate, Sales

Answer 3

select c.id, c.first_name, o.totalSales
from customers c 
inner join (select CustomerID, sum(Sales) as totalSales from MyOrders group by CustomerID) o
on o.CustomerID = c.id

Question 4

Write equivalent SQL query using left join:

select * from A
right join B
on A.key = B.key;

Answer 4

select * from B
left join A
on B.key = A.key;

Question 5

If A is left table and B is right table, then A left join B ensures all keys of table A are present even if, no matching key found in table B.
So in the result set, some columns from table _ might be NULL.

Answer 5

table B

Question 6

select customer id and name who have not placed any order as of yet.
customers: id, name, country, score
orders: id, customer_id, order_date, sales

Answer 6

select c.id, c.name
from customers c
left join orders o
on c.id = o.customer_id
where o.customer_id is null;

Question 7

Write equivalent SQL query using right anti join (right outer join)

select c.id, c.name
from customers c
left join orders o
on c.id = o.customer_id
where o.customer_id is null;

Answer 7

select c.id, c.name
from orders o
right join customers c
on o.customer_id = c.id
where o.customer_id is null;

Question 8

Retrieve customers without orders and orders without customers:
customers: id, name, country, score
orders: id, customer_id, order_date, sales

Answer 8

select * from
customers full join orders
on customers.id = orders.customer_id
where customers.id is null or orders.customer_id is null;

Question 9

1. WHERE EXISTS (subquery) returns True if subquery fetches _ rows.
2. WHERE EXISTS (subquery) returns False if subquery fetches _ rows.
3. WHERE NOT EXISTS (subquery) returns True if subquery fetches _ rows.
4. WHERE NOT EXISTS (subquery) returns False if subquery fetches _ rows.

(zero/non-zero)

Answer 9

1. non-zero
2. zero
3. zero
4. non-zero

Question 10

Find name of customers who bought an iphone and samsung but not bought xiomi.

Customer: Id(int), Name(varchar)
Order: Id(int), Customer_id(int), Product_id(int)
Product: Id(int), Name(varchar)

Answer 10

SELECT DISTINCT c.Id, c.Name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    JOIN products p ON o.Product_id = p.Id
    WHERE o.Customer_id = c.Id
      AND p.Name IN ('iphone', 'samsung')
)
AND NOT EXISTS (
    SELECT 1
    FROM orders o
    JOIN products p ON o.Product_id = p.Id
    WHERE o.Customer_id = c.Id
      AND p.Name = 'xiomi'
);