bamazon/Bamazon.sql at master · markpython86/bamazon · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
DROP DATABASE IF EXISTS bamazon;

CREATE DATABASE bamazon;

USE bamazon;

CREATE TABLE products (
	item_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(50) NULL,
    department_name VARCHAR(50) NULL,
    price FLOAT(10,2) NULL,
    stock_quantity INT NULL,
    products_sold INT NULL,
    products_revenue FLOAT(10,2) NULL,
    PRIMARY KEY (item_id)
);


INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
VALUES ("PlayStation4", "Electronics", 400.34, 10, 5, 0);

INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Samsung TV LED", "Electronics", 1200.25, 20, 20, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Dell Laptop XPS 15", "Electronics", 2500.99, 24, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Sofa", "Home and Garden", 1300, 40, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Office Desk", "Home and Garden", 320, 120, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Twin Bed", "Home and Garden", 450, 24, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("T 6.5 S Treadmill", "Sports and Outdoors", 750, 80, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Folding Exercise Bike", "Sports and Outdoors", 230, 49, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Harry Potter Books 1-7", "Books and Audible", 120, 50, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("The Lord of the Rings", "Books and Audible", 25, 110, 0, 0);

 INSERT INTO products (product_name, department_name, price, stock_quantity, products_sold, products_revenue)
 VALUES ("Patio Furniture Set", "Home and Garden", 1250, 40, 0, 0);


 CREATE TABLE departments(
	department_id INT NOT NULL AUTO_INCREMENT,
    department_name VARCHAR(50) NULL,
    over_head_costs FLOAT(10,2) NULL,
    product_sales FLOAT(10,2) NULL,
    -- total_profit FLOAT(10,2) NULL,
    PRIMARY KEY (department_id)
);

INSERT INTO departments (department_name, over_head_costs)
Values ("Electronics", 67600);

 INSERT INTO departments (department_name, over_head_costs)

VALUES ("Books and Audible", 6650);

INSERT INTO departments (department_name, over_head_costs)

values ("Sports and Outdoors", 65800);


-- SELECT departments.department_name department_name,
-- over_head_costs, sum(products.products_revenue) AS product_sales,
-- sum(products.products_revenue) - over_head_costs AS total_profit
-- FROM departments
-- LEFT JOIN products
-- ON departments.department_name=products.department_name AND departments.product_sales = products.products_revenue
-- -- ON departments.product_sales = products.products.products_revenue
-- GROUP BY departments.department_name;


SELECT d.department_id,
    d.department_name,
    d.over_head_costs,
    SUM(p.products_revenue) as product_sales,
    SUM(p.products_revenue) - over_head_costs as total_profit

FROM departments d
LEFT JOIN products p
ON d.department_name = p.department_name
GROUP BY department_id;


-- SELECT d.department_id, d.department_name, d.over_head_costs, SUM(p.products_revenue) product_sales, SUM(p.products_revenue) - over_head_costs total_profit FROM departments d LEFT JOIN products p ON d.department_name = p.department_name GROUP BY department_id;