MySQL-Inventory-Management-Database/queries.sql at main · Sandra1me/MySQL-Inventory-Management-Database · 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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
-- =================================================
-- Queries to analyze data
-- =================================================

-- See products with their category and supplier
SELECT
    products.name,
    categories.name AS category,
    suppliers.name AS supplier,
    products.price,
    products.stock
FROM
    products
        JOIN
    categories ON products.id_category = categories.id_category
        JOIN
    suppliers ON products.id_supplier = suppliers.id_supplier;

-- Search products by category
SELECT
    products.name
FROM
    products
        JOIN
    categories ON products.id_category = categories.id_category
WHERE
    categories.name = 'Gaming';

-- Search products by supplier
SELECT
    products.name
FROM
    products
        JOIN
    suppliers ON products.id_supplier = suppliers.id_supplier
WHERE
    suppliers.name = 'TechSupply';

-- Count number of products in inventory
SELECT
    COUNT(*) AS total_products
FROM
    products;

-- Count number of products in inventory by category
SELECT
    categories.name, COUNT(products.id_product)
FROM
    products
        JOIN
    categories ON products.id_category = categories.id_category
GROUP BY categories.name;

-- Find cheapest product
SELECT
    MIN(price)
FROM
    products;

-- Find most expensive product
SELECT
    MAX(price)
FROM
    products;

-- Find average price
SELECT
    AVG(price)
FROM
    products;

-- Find product with highest stock
SELECT
    name, stock
FROM
    products
ORDER BY stock DESC
LIMIT 1;

-- Find product with least stock
SELECT
    name, stock
FROM
    products
ORDER BY stock ASC
LIMIT 1;

-- Find average price by category
SELECT
    categories.name AS category,
    AVG(products.price) AS average_price
FROM
    products
        JOIN
    categories ON products.id_category = categories.id_category
GROUP BY categories.name;

-- Find supplier with most products
SELECT
    suppliers.name AS supplier,
    COUNT(products.id_product) AS total_products
FROM
    products
        JOIN
    suppliers ON products.id_supplier = suppliers.id_supplier
GROUP BY suppliers.name
ORDER BY total_products DESC
LIMIT 1;

-- Total stock value by category
SELECT
    categories.name AS category,
    SUM(products.price * products.stock) AS total_value
FROM
    products
        JOIN
    categories ON products.id_category = categories.id_category
GROUP BY categories.name
ORDER BY total_value DESC;

-- Find products with low stock
SELECT name, stock FROM products WHERE stock < 10;

-- Average price by supplier
SELECT
    suppliers.name AS supplier, AVG(products.price) AS avg_price
FROM
    products
        JOIN
    suppliers ON products.id_supplier = suppliers.id_supplier
GROUP BY suppliers.name;