-- Returns first 100 rows from tutorial.excel_sql_inventory_data
  SELECT 
        product_id,
        product_name,
        product_type,
        unit,
        price_unit,
        wholesale,
        current_inventory
 FROM tutorial.excel_sql_inventory_data LIMIT 100;

Mode SQL

The "excel_sql_inventory_data" dataset provides information about products and their inventory. Here is an overview of the data:

1. product_id: A numerical identifier for each product.

2. product_name: The name or description of the product.

3. product_type: The category or type of the product.

4. unit: The unit of measurement for the product (e.g., pieces, litres).

5. price_unit: The price per unit of the product.

6. wholesale: The wholesale price of the product.

7. current_inventory: The current quantity of the product available in inventory.

This dataset allows us to analyze various aspects of the inventory, such as the total inventory value, product pricing, inventory levels by type, and wholesale values.

By using SQL queries, one can perform different data analyses, including calculating the total inventory value, identifying products with high or low prices per unit, determining average inventory levels by product type, and evaluating the total wholesale value for each product.

Additionally, we can leverage SQL functions and operators to filter and manipulate the data further based on specific criteria. For example, we can check for null values in certain columns to identify any missing or incomplete data.

By exploring and analyzing this dataset, we can gain valuable insights into the product inventory, pricing, and overall performance, helping us make informed decisions for inventory management and business strategies.

Null Value Checking

SELECT *
FROM tutorial.excel_sql_inventory_data
WHERE product_name IS NULL;

1. Calculate total inventory value:

SELECT
  SUM(price_unit * current_inventory) AS total_inventory_value
FROM
  tutorial.excel_sql_inventory_data;

1. Find the product with the highest price per unit:

SELECT
  product_name,
  price_unit
FROM
  tutorial.excel_sql_inventory_data
ORDER BY
  price_unit DESC
LIMIT
    1;

1. Determine the average inventory level by product type:

SELECT
  product_type,
  AVG(current_inventory) AS average_inventory
FROM
  tutorial.excel_sql_inventory_data
GROUP BY
  product_type;

1. Identify products with low inventory levels (less than 10 units):

SELECT
  product_name,
  current_inventory
FROM
  tutorial.excel_sql_inventory_data
WHERE
  current_inventory < 10;

1. Calculate the total wholesale value for each product:

SELECT
  product_name,
  SUM(wholesale) AS total_wholesale_value
FROM
  tutorial.excel_sql_inventory_data
GROUP BY
  product_name;

1. Filtering by price range:

SELECT
  *
FROM
  tutorial.excel_sql_inventory_data
WHERE
  price_unit BETWEEN 10
  AND 50;

1. Products with Below-Average Inventory Levels

SELECT
  product_name,
  current_inventory
FROM
  tutorial.excel_sql_inventory_data
WHERE
  current_inventory < (
    SELECT
      AVG(current_inventory)
    FROM
      tutorial.excel_sql_inventory_data
  );

In this query, it retrieves the product name and current inventory for products that have an inventory level lower than the average inventory level across all products in the dataset.

8.Products with High Unit Price in Above-Average Populated Product Types

SELECT product_name, price_unit
FROM tutorial.excel_sql_inventory_data
WHERE product_type = (
    SELECT product_type
    FROM tutorial.excel_sql_inventory_data
    GROUP BY product_type
    HAVING COUNT(*) > (
        SELECT AVG(count_per_type)
        FROM (
            SELECT product_type, COUNT(*) AS count_per_type
            FROM tutorial.excel_sql_inventory_data
            GROUP BY product_type
        ) AS type_counts
    )
    LIMIT 1
);

In this nested query, it selects the product name and unit price for products that belong to a product type with a count greater than the average count per product type.

By adding LIMIT 1 at the end of the subquery, it esures that only one row is returned, resolving the error. Adjust the subquery or the limit clause as necessary based on specific requirements.

product_name	price_unit
chia_seeds	0.67
pine_nuts	0.91
dried_apricots	0.56
protein_powder	0.69
organic_mung_beans	0.89
organic_glazed_walnuts	0.86
pumpkin_spice_pumpkin_seeds	0.96
goji_berries	1.17
barberries	1.74
wholewheat_couscous	0.27
orzo	0.19
autumn_pilaf	0.24
wild_rice	0.27
garam_masala	1.44
darjeeling_black_tea	3.3
rooibos_tea	3.49
irish_breakfast_black_tea	2.99
mango_black_tea	3.08
buckwheat_flour	2.59