Assignment 4 ch 8-9-10-11

-- Assignment 4

-- Chapter 8
-- ===============================================================================================
use my_guitar_shop;

-- Question 1

SELECT FORMAT(list_price,1),
       CONVERT(list_price, SIGNED ),
       CAST(list_price AS SIGNED)
FROM products;

-- Question 2
SELECT date_added,
       CAST(date_added AS DATE),
       CAST(date_added AS CHAR(7)),
       CAST(date_added AS TIME)
FROM products;





use my_guitar_shop;

-- Chapter 9
-- ===============================================================================================

-- Question 1

SELECT list_price,
       discount_percent,
       ROUND((list_price*(discount_percent/100)), 2) AS discount_amount
FROM products;


-- Question 2

SELECT order_date,
       DATE_FORMAT(order_date, '%Y') AS order_year,
       DATE_FORMAT(order_date, '%b-%d-%Y') AS order_date_formatted,
       DATE_FORMAT(order_date, '%l:%i %p') AS order_time,
       DATE_FORMAT(order_date, '%m/%d/%y %h:%i') AS order_datetime
FROM orders;

-- Question 3

SELECT card_number,
       LENGTH(card_number) AS card_number_length,
       RIGHT(card_number, 4) AS last_4_digits,
       IF(LENGTH(card_number) = 16, CONCAT('XXXX-XXXX-XXXX', RIGHT(card_number, 4)), CONCAT('XXXX-XXXXXX-X', RIGHT(card_number, 4)))
FROM orders;

-- Question 4

SELECT order_id,
       order_date,
       DATE_ADD(order_date, INTERVAL 2 DAY) AS approx_ship_date,
       ship_date,
       DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL
AND DATE_FORMAT(order_date, '%Y-%m') = '2018-03';



-- Chapter 11

-- Question 1

use my_guitar_shop;

CREATE INDEX index_zipcode ON addresses(zip_code);

-- Question 2

DROP DATABASE IF EXISTS my_web_db;
CREATE DATABASE IF NOT EXISTS my_web_db;
USE my_web_db;


CREATE TABLE IF NOT EXISTS users
(
user_id int Primary key NOT NULL AUTO_INCREMENT,
email_address varchar(100),
first_name varchar(45),
last_name varchar(45)
);

CREATE TABLE IF NOT EXISTS products
(
product_id int Primary key NOT NULL AUTO_INCREMENT,
product_name varchar(20)
);

CREATE TABLE IF NOT EXISTS downloads
(
download_id int Primary key NOT NULL AUTO_INCREMENT,
user_id int REFERENCES users(user_id),
download_date date,
filename varchar(50),
product_id int REFERENCES products(product_id)
);

-- Question 3

INSERT INTO users (email_address, first_name, last_name) values ('johnsmith@gmail.com', 'John', 'Smith');
INSERT INTO users (email_address, first_name, last_name) values ('janedoe@yahoo.com', 'Jane', 'Doe');
INSERT INTO products (product_name) VALUES ('Local Music Vol 1');
INSERT INTO products (product_name) VALUES ('Local Music Vol 2');

INSERT INTO downloads (user_id, download_date, filename, product_id) VALUES (1, '2019-03-04', 'pedals_falling.mp3', 2);
INSERT INTO downloads (user_id, download_date, filename, product_id) VALUES (2, '2019-03-05', 'turn_signal.mp3', 1);
INSERT INTO downloads (user_id, download_date, filename, product_id) VALUES (2, '2019-03-06', 'one_horse_town.mp3', 2);

SELECT email_address,
       first_name,
       last_name,
       download_date,
       filename,
       product_name
FROM users
JOIN downloads d on users.user_id = d.user_id
JOIN products p on d.product_id = p.product_id;


-- Question 4

ALTER TABLE products
ADD product_price DECIMAL(3,2) DEFAULT 9.99,
ADD date_added DATETIME;

-- Question 5

ALTER TABLE users
MODIFY first_name VARCHAR(20) NOT NULL;

UPDATE users
SET first_name = NULL
WHERE  user_id = 1;

UPDATE users
SET first_name = 'thisIsNotGoingToWorkBecauseItIsTooLong'
WHERE  user_id = 1;







USE my_guitar_shop;

-- ===============================================================================
-- Chapter 12

-- Question 1

CREATE OR REPLACE VIEW customer_addresses AS
SELECT c.customer_id, email_address, last_name, first_name,
       bill.line1 AS bill_line1,
       bill.line2 AS bill_line2,
       bill.city AS bill_city,
       bill.state AS bill_state,
       bill.zip_code AS bill_zip,
       ship.line1 AS ship_line1,
       ship.line2 AS ship_line2,
       ship.city AS ship_city,
       ship.state AS ship_state,
       ship.zip_code AS ship_zip
FROM customers c
    JOIN addresses bill ON c.customer_id = bill.customer_id AND c.billing_address_id = bill.address_id
    JOIN addresses ship ON c.customer_id = ship.customer_id AND c.shipping_address_id = ship.address_id;


-- Question 2

SELECT customer_id,
       last_name,
       first_name,
       bill_line1
FROM customer_addresses;

-- Question 3

UPDATE customer_addresses
SET ship_line1 = '1990 Westwood Blvd.'
WHERE customer_id = 8;

-- Question 4

CREATE OR REPLACE VIEW order_item_products AS
SELECT o.order_id,
       o.order_date,
       o.tax_amount,
       o.ship_date,
       p.product_name,
       oi.item_price,
       oi.discount_amount,
       (oi.item_price - oi.discount_amount) AS final_price,
       oi.quantity,
       (oi.item_price - oi.discount_amount)*quantity AS item_total
FROM orders o
JOIN order_items oi on o.order_id = oi.order_id
JOIN products p on p.product_id = oi.product_id;


-- CREATE OR REPLACE VIEW full_monty AS
SELECT c.customer_id,
       c.email_address,
       c.last_name,
       ba.line1 as bill_line1,
       ba.line2 as bill_line2,
       sa.line1 as shipp_line1,
       sa.line2 as shipp_line2
FROM customers c
    JOIN addresses ba ON c.billing_address_id  = ba.address_id
    JOIN addresses sa ON c.shipping_address_id = sa.address_id;