SQL Solutions

Solutions

Answer 
SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

  1. There are results without values for the order_id. This is because the left join will not only considered the intersection of the tables, but also the customers elements that are not part of the intersection.

Answer 
SELECT *
FROM orders
INNER JOIN customers
WHERE orders.customer_id = customers.customer_id
Answer 
SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
Answer 
SELECT
  c.customer_id,
  c.first_name,
  o.order_id,
  sh.name as shipper
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
Answer 
SELECT
  c.first_name AS customer,
  p.name AS product
FROM customers c, products p
ORDER BY c.first_name
Answer 
SELECT 
    customer_id,
    first_name, 
    points, 
CASE 
    WHEN points < 2000 THEN "platinum"
    WHEN points BETWEEN 2000 and 3000 THEN "gold"
    ELSE "silver"
END as category 
FROM customers
ORDER BY first_name
Answer 
SELECT 
    customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
)
Query update 
UPDATE orders
SET status = 3
WHERE customer_id IN (
 SELECT customer_id
 FROM customers
 WHERE customers.first_name = 'Ilene'
 AND customers.last_name = 'Dowson'
)
Check the results 
SELECT *
FROM orders, customers
WHERE orders.customer_id = customers.customer_id
AND customers.first_name = 'Ilene'
AND customers.last_name = 'Dowson'
Answer 
SELECT products.product_id, products.name, COUNT(*)
FROM products, order_items
WHERE products.product_id = order_items.product_id
GROUP BY product_id
ORDER BY COUNT(*) DESC;