12345678910111213141516171819202122232425262728293031323334353637383940414243444546 |
- -- Exercise 1
- SELECT category_name, product_name, list_price
- FROM products p JOIN categories c
- ON c.category_id = p.category_id
- ORDER BY category_name, product_name;
-
- -- Exercise 2
- SELECT first_name, last_name, line1, city, state, zip_code
- FROM customers c JOIN addresses a
- ON c.customer_id = a.customer_id
- WHERE email_address LIKE 'allan.sherwood@yahoo.com';
-
- -- Exercise 3
- SELECT first_name, last_name, line1, city, state, zip_code
- FROM customers c JOIN addresses a
- ON c.customer_id = a.customer_id
- AND c.shipping_address_id = address_id;
-
- -- Exercise 4
- SELECT last_name, first_name, order_date, product_name, item_price, discount_amount, quantity
- FROM customers c JOIN orders o ON c.customer_id = o.customer_id
- JOIN order_items i ON i.order_id = o.order_id
- JOIN products p ON p.product_id = i.product_id
- ORDER BY last_name, order_date, product_name;
-
- -- Exercise 5
- SELECT p1.product_name, p2.list_price
- FROM products p1 JOIN products p2
- ON p1.product_id <> p2.product_id
- AND p1.list_price = p2.list_price
- ORDER BY product_name;
-
- -- Exercise 6
- SELECT * FROM categories c JOIN products p
- ON p.category_id = c.category_id
- WHERE p.product_id IS NULL;
-
- -- Exercise 7
- SELECT 'SHIPPED' AS ship_status, order_id, order_date
- FROM orders
- WHERE ship_date IS NOT NULL
- UNION
- SELECT 'NOT SHIPPED' AS ship_status, order_id, order_date
- FROM orders
- WHERE ship_date IS NULL
- ORDER BY order_date;
|