BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What has Lagos never sold?

Every SQL question, here or in an interview, can be cracked with the same 4 questions. Answer them before you type, and out loud in an interview: 1 What will my answer look like? 2 Where is the information? 3 Do I only want some of the rows? 4 Do I need to work something out first? Then check your answer. First question: is there anything the Lagos store has never sold? The word "never" means: first make the list of what Lagos did sell, then keep the products that aren't on it. NOT IN does the second part.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Never means a list minus another list

SELECT DISTINCT product
FROM orders
WHERE product NOT IN (
  SELECT product
  FROM orders
  WHERE store = 'Lagos'
)

Result:

product
Power bank
Smart watch

Step by step

  1. Picture the answer: one column, product, and no repeats.
    SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Lagos')
  2. Store and product are both in orders, so there's no join.
    SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Lagos')
  3. WHERE store = 'Lagos' inside the brackets.
    SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Lagos')
  4. A subquery can give back a list. NOT IN (that list) keeps everything that isn't on it.
    SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Lagos')

Practice

Try each one in your head first, then open the answer.

1. Is there anything the Lagos store has never sold? Show the product, or products.
SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Lagos')
2. Which stores have never sold a Speaker? Show the store, or stores.
SELECT DISTINCT store FROM orders WHERE store NOT IN (SELECT store FROM orders WHERE product = 'Speaker')
3. Which products has the Abuja store never sold?
SELECT DISTINCT product FROM orders WHERE product NOT IN (SELECT product FROM orders WHERE store = 'Abuja')
4. Which customers have never placed an order? Show their name.
SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders)
5. Which stores have never had an order with the BULK coupon?
SELECT DISTINCT store FROM orders WHERE store NOT IN (SELECT store FROM orders WHERE coupon = 'BULK')

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed