-- 1) Write a query to create a database named “store”.
create database store 

-- 2) Write a query to create a table in  ‘store’ Database with name “orderDemo” which will have 5 columns and data type as below:

use store

create table orderDemo(
	Product_id varchar(30),	--Must be varchar as the product IDs in the data are letters and numbers.
	Product_name varchar(30), 
	Order_date date,
	Cost_price money,
	Sales_price money,
	Quantity int
	);

-- 3) Insert the following records into the above “orderDemo” table.
-- Had to use CONVERT to allow the dd/mm/yyyy dates to be allowed into the DATE format which is yyyy-mm-dd. The 103 in CONVERT chooses the dd/mm/yyyy format. 
INSERT INTO orderDemo VALUES
('p011', 'pen', CONVERT(DATE, '01/01/2023', 103), 1.1, 9.05, 3),
('b022', 'Book', CONVERT(DATE, '01/01/2023', 103), 125, 520, 2),
('i033', 'Ink', CONVERT(DATE, '12/01/2023', 103), 15, 50.55, 1),
('m044', 'Map', CONVERT(DATE, '19/01/2023', 103), 35, 25, 5),
('bt055', 'Bottle', CONVERT(DATE, '30/01/2023', 103), 150, 600, 1),
('m066', 'Marker', CONVERT(DATE, '05/02/2023', 103), 34, 80, 4),
('tr0077', 'Tango', CONVERT(DATE, '01/02/2022', 103), 34, 23, 5),
('we0088', 'Wisky', CONVERT(DATE, '04/04/2021', 103), 19, 45, 2),
('ao0099', 'Audio Tape', CONVERT(DATE, '04/09/2021', 103), 23, 78, 4);

-- 4.)	a. Write a query to display all the records of orderDemo Table.
select * from orderDemo;

-- 4) b. Write a query to display all data of product name, order date, and price of orderDemo.
select
	[Product_name],
	[Order_date],
	[Cost_price],
	[Sales_price]
from orderDemo

-- 4) c. Write a query to display all products starting with ‘m’
select 
	[Product_name]
from orderDemo
where [Product_name] like 'm%';

-- 4) d. Write a query to display product Id, product name and order date where quantity is 3 or more.  

select 
	[Product_id],
	[Product_name],
	[Order_date]
from orderDemo 
where [Quantity] >= 3;

-- 4) e. Write a query to display product Id, product name and order date of products ordered after January 2023.
-- Must use convert again to accept the differet date format

select 
	[Product_id],
	[Product_name],
	[Order_date]
from orderDemo 
where [Order_date] >= CONVERT(DATE, '31/01/2023', 103);


-- 4) f. Write a query to display Total Profit. Note: Total Profit = Sales Price x Quantity ordered.
select  
[Product_id],
[Product_name],
[Order_date],
[Cost_price],
[Sales_price],
[Quantity],
([Sales_Price] * [Quantity]) AS total_profit
from orderDemo;

-- Shows the total profit from all products 
select SUM([Sales_Price] * [Quantity]) AS total_profit
from orderDemo;

-- 4) g. Write a query to display Total Cost. Note: Total Cost =  Cost Price x Quantity ordered.
select  
[Product_id],
[Product_name],
[Order_date],
[Cost_price],
[Sales_price],
[Quantity],
([Cost_price] * [Quantity]) AS total_cost
from orderDemo;

-- Shows the total cost from all products 
select SUM([Cost_price] * [Quantity]) AS total_cost
from orderDemo;

-- 4) i. Write a query to display Net Profit.    Note: Net Profit  = Total Profit - Total Cost
select  
[Product_id],
[Product_name],
[Order_date],
[Cost_price],
[Sales_price],
[Quantity],
([Sales_Price] * [Quantity]) AS total_profit,
([Cost_price] * [Quantity]) AS total_cost,
([Sales_Price] * [Quantity]) - ([Cost_price] * [Quantity]) AS net_profit
from orderDemo;

-- Shows the total net profit over all products
select SUM(([Sales_Price] * [Quantity]) - ([Cost_price] * [Quantity])) AS net_profit
from orderDemo;

-- 4) j. Write a query to display all products with negative Total Profit.
-- Cannot use net_profit as a variable as it is not created, only exists as a label for a calculation. 
-- Must use the orginal calculation to find any negative total profits
select  
[Product_name],
([Sales_Price] * [Quantity]) AS total_profit,
([Cost_price] * [Quantity]) AS total_cost,
([Sales_Price] * [Quantity]) - ([Cost_price] * [Quantity]) AS net_profit
from orderDemo
where ([Sales_Price] * [Quantity]) - ([Cost_price] * [Quantity]) < 0;