7. SQL SET Operators

Question 1

When joining rows from two tables using SET operators, which SQL clause is allowed to be used only at the end?

Answer 1

order by

column names in result set are decided by column names present in the first query.

Question 2

1. SET operator used to combine rows without duplicate entries.
2. SET operator used to combine rows with duplicate entries

Answer 2

1. union
2. union all

Avoid retrieving IDs in select clause, since they lose their meaning in the result set after UNION.

Question 3

Find employees who are not customers at the same time.

Hint: use minus (except) clause

Answer 3

select firstname, lastname
from employees
except
select firstname, lastname 
from customers;

Question 4

Find employees who are also customers.

Answer 4

select firstname, lastname
from employees
intersect
select firstname, lastname
from customers

Question 5

You copied data from table X in DB A to table Y in DB B.
How to check if all the data was moved completely?

Answer 5

select * from A.X
except
select * from B.Y

The count of above result set must be zero (empty).

Question 6

Your source table gets data inserted from application, and on each insert you need to check if this row (entry) is new or not.
For example, unique visits on a website.

Answer 6

Use EXCEPT operator to fetch unique user IDs.

MINUS

Question 7

Find the c.id and c.first_name of customers who bought iphone and/or samsung but never xiomi.

– customers: Id(int), Name(varchar)
– orders: Id(int), Customer_id(int), Product_id(int)
– products: Id(int), Name(varchar)

Answer 7

-- Customers who bought iPhone or Samsung
SELECT DISTINCT c.id, c.first_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE p.name IN ('iphone','samsung')

EXCEPT

-- Customers who bought Xiaomi
SELECT DISTINCT c.id, c.first_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE p.name = 'xiomi';