my-guitar-shop-database-exersices/How-to-retrieve-data-from-a-single-table.sql

USE my_guitar_shop;

-- Exercise 1 --
SELECT 
	product_code, 
	product_name, 
	list_price,
	discount_percent
FROM products
ORDER BY list_price DESC;

-- Exercise 2 --

SELECT concat(last_name, ', ', first_name) AS full_name
FROM customers
WHERE  last_name >= 'M%' 
ORDER BY last_name;


-- Exercise 3 --

SELECT
	product_name,
	list_price,
	date_added
FROM products
WHERE list_price BETWEEN '500' AND  '2000'
ORDER BY date_added DESC;

-- Exercise 4 --

SELECT
	product_name,
	list_price,
	discount_percent,
	round((discount_percent/100) * list_price, 2) AS Discount_Amount,
	round(list_price - ((discount_percent/100) * list_price),2) AS Discount_Price
FROM products
ORDER BY discount_price DESC
LIMIT 5;

-- Exercise 5 --

SELECT
	item_id AS 'The Item Id',
	item_price AS 'The Item price',
	discount_amount AS 'The discount amount',
	quantity AS 'The quantity',
	(item_price * quantity) AS 'The price_total',
	(discount_amount * quantity) AS 'The discount_total',
	((item_price - discount_amount) * quantity) AS 'The item_total'
FROM order_items
WHERE ((item_price - discount_amount) * quantity) > 500
ORDER BY ((item_price - discount_amount) * quantity) DESC;

-- Exercise 6 -- 
SELECT
	order_id AS 'The order_id',
    order_date AS 'The order_date',
    ship_date AS 'The ship_date'
FROM orders
WHERE ship_date IS NULL;

-- Exercie 7 --

SELECT
NOW() AS 'today_unformatted',
date_format(NOW(), '%d-%b-%Y') AS 'today_formatted';

-- Exercise 8--

SELECT
'100 dollars' AS Price,
'.07 (7percent)' AS Tax_Rate,
100 * 0.07 AS Tax_Amount,
100 + (100 * 0.07) AS total;