guitar-shop-database/Solution Exercises.sql

/* How to retrieve data from a single table*/

/*1*/
SELECT
	product_code,
    product_name,
    list_price,
    discount_percent
FROM
	products
ORDER BY list_price DESC;


/*2*/
SELECT 
	concat(last_name,', ', first_name) AS full_name
FROM 
	customers
WHERE 
	last_name >= 'M%' 
ORDER BY 
	last_name;
    
/*3*/
SELECT 
	product_name,
    list_price,
    date_added
FROM 
	products
WHERE 
	list_price > 500 AND list_price < 2000;

/*4*/
SELECT 
	product_name,
    list_price,
    discount_percent,
    ROUND((list_price*discount_percent/100,2) AS discount_amount,
    ROUND(list_price - list_price*discount_percent/100,2)AS discount_price
FROM
	products
ORDER BY 
	ROUND(list_price -(list_price*(discount_percent/100)),2 DESC
LIMIT 5;

/*5*/
SELECT
	item_id,
    item_price,
    discount_amount,
    quantity,
    (item_price * quantity) AS price_total,
    (discount_amount * quantity) AS discount_total,
    ((item_price - discount_amount) * quantity) AS item_total
    
FROM
	order_items
WHERE
	((item_price - discount_amount) * quantity) > 500
ORDER BY 
	item_total DESC;

/*6*/
SELECT 
	order_id,
    order_date,
    ship_date
FROM
	orders
WHERE ship_date IS NULL;

/*7*/
SELECT 
	now() AS today_unformatted,
	date_format(now(),'%d-%b-%Y') AS today_formatted;

/*8*/
SELECT 
	 100 AS price,
     .07 AS tax_rate,
     (100 * 0.07) AS tax_amount,
     100 + (100 * 0.07) AS total;
     
     
/* HOW TO RETRIEVE DATA  FROM TWO OR MORE TABLES */

/*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;

/*2*/
SELECT 
	first_name,
    last_name,
    line1,
    city,
    state,
    zip_code
FROM 
	addresses a
JOIN
	customers c
ON 
	a.customer_id = c.customer_id
WHERE 
	email_address LIKE "allan.sherwood%";
    
/*3*/
SELECT
    first_name,
    last_name,
    line1,
    city,
    state,
    zip_code
FROM 
	addresses a
JOIN
	customers c
ON 
	a.customer_id = c.customer_id
WHERE
	address_id = shipping_address_id;
    
/*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 
    o.order_id = i.order_id
JOIN
	products p
ON
	i.product_id = p.product_id
ORDER BY 
	last_name, order_date, product_name;
    
/*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;

/*6*/
SELECT 
	category_name,
    product_id
FROM
	categories c
LEFT OUTER JOIN 
	products p
ON
	c.category_id = p.category_id
WHERE 
	product_id IS NULL;
    
/*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;
    
    
/* HOW TO CODE SUMMARY QUERRIES*/

/*1*/
SELECT
    count(*) AS count_number_of_orders,
    SUM(tax_amount) AS sum_tax_amounts
FROM
	orders;

/*2*/
SELECT
	category_name,
	COUNT(*) AS count_of_the_products,
    MAX(list_price)
FROM 
	products p
JOIN
	categories c
ON
	p.category_id = c.category_id
GROUP BY 
	category_name
ORDER BY COUNT(*) DESC;

/*3*/
SELECT 
	email_adress
	
    
    
    SELECT category_name FROM categories;