Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE my_guitar_shop;
- -- Chapter 4 Exercise 1
- SELECT category_name, product_name, list_price
- FROM categories c INNER JOIN products p
- ON c.category_id = p.category_id
- ORDER BY category_name, product_name ASC;
- -- Chapter 4 Exercise 2
- SELECT first_name, last_name, line1, city, state, zip_code
- FROM customers c INNER JOIN addresses a
- ON c.customer_id = a.customer_id
- WHERE email_address = 'allan.sherwood@yahoo.com';
- -- Chapter 4 Exercise 3
- SELECT first_name, last_name, line1, city, state, zip_code
- FROM customers c INNER JOIN addresses a
- ON c.customer_id = a.customer_id
- WHERE shipping_address_id = address_id;
- -- Chapter 4 Exercise 4
- SELECT c.last_name, c.first_name, o.order_date, p.product_name, oi.item_price, oi.discount_amount, oi.quantity
- FROM customers c
- INNER JOIN orders o ON c.customer_id = o.customer_id
- INNER JOIN order_items oi on o.order_id = oi.order_id
- INNER JOIN products p on oi.product_id = p.product_id
- ORDER BY c.last_name, o.order_date, p.product_name;
- -- Chapter 4 Exercise 5
- SELECT p1.product_name, p1.list_price, p2.list_price
- FROM products p1
- JOIN products p2 ON p1.list_price = p2.list_price
- WHERE p1.product_id != p2.product_id ;
- -- Chapter 4 Exercise 6
- SELECT category_name, p.product_name
- FROM categories
- left join products p on categories.category_id = p.category_id
- WHERE p.product_name IS NULL;
- -- Chapter 4 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;
- --===================================
- USE my_guitar_shop;
- -- Chapter 5: Exercise 1
- INSERT INTO categories (category_name)
- VALUES ('Brass');
- -- Chapter 5: Exercise 2
- UPDATE categories
- SET category_name = 'Woodwinds'
- WHERE category_id = 5;
- -- Chapter 5: Exercise 3
- DELETE FROM categories
- WHERE category_id = 5;
- -- Chapter 5: Exercise 4
- INSERT INTO products (category_id, product_code, product_name, description, list_price, discount_percent, date_added)
- VALUES ('4','dgx_640','Yamaha DGX 640 88-Key Digital Piano','Long description to come.',799.99,0,NOW());
- -- Chapter 5: Exercise 5
- UPDATE products
- SET discount_percent = 35
- WHERE product_id = 11;
- -- Chapter 5: Exercise 6
- DELETE FROM products WHERE category_id = 4;
- DELETE FROM categories WHERE category_name = 'Keyboards';
- -- Chapter 5: Exercise 7
- INSERT INTO customers (email_address, password, first_name, last_name)
- VALUES ('rick@raven.com', '', 'Rick', 'Raven');
- -- Chapter 5: Exercise 8
- UPDATE customers
- SET password = 'secret'
- WHERE customer_id = 9;
- -- Chapter 5: Exercise 9
- UPDATE customers
- SET password = 'reset'
- WHERE customer_id IS NOT NULL;
Add Comment
Please, Sign In to add comment