Computer Hardware Database
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    Computer Hardware Database

    👋 Welcome to your workspace! Here, you can run SQL queries, write Python code, and add text in Markdown. This workspace is automatically connected to an Oracle database containing order and inventory details for a computer hardware firm (source).

    You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.

    There is a short query and a visualization of the inventory stored in warehouses in each country rendered in Plotly to get you started.

    Unknown integration
    DataFrameavailable as
    hardware_inventory
    variable
    SELECT 
	country_name AS "country_name",
    SUM(quantity) AS "total_inventory",
    SUM(CASE WHEN category_name = 'Video Card' THEN quantity
        ELSE 0 END) AS "video_card",
    SUM(CASE WHEN category_name = 'Mother Board' THEN quantity
        ELSE 0 END) AS "motherboard",
    SUM(CASE WHEN category_name = 'CPU' THEN quantity
		ELSE 0 END) AS "cpu",
    SUM(CASE WHEN category_name = 'Storage' THEN quantity
        ELSE 0 END) AS "storage"
FROM inventories
INNER JOIN warehouses using(warehouse_id)
INNER JOIN locations using(location_id)
INNER JOIN countries using(country_id)
INNER JOIN products using(product_id)
INNER JOIN product_categories using (category_id)
GROUP BY country_name
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    # Import libraries
import pandas as pd
import plotly.express as px

# Create choropleth map
fig = px.choropleth(
    hardware_inventory,
    locations="country_name",
    color="total_inventory",
    locationmode="country names",
    custom_data=[
        "country_name",
        "total_inventory",
        "video_card",
        "motherboard",
        "cpu",
        "storage",
    ],
    color_continuous_scale=px.colors.sequential.Viridis,
)

# Customize data to show
fig.update_traces(
    hovertemplate="<br>".join(
        [
            "<b>%{customdata[0]}</b>",
            "<br>",
            "<b>Total Inventory:</b> %{customdata[1]}",
            "<b>Video card:</b> %{customdata[2]}",
            "<b>Motherboard:</b> %{customdata[3]}",
            "<b>CPU:</b> %{customdata[4]}",
            "<b>Storage:</b> %{customdata[5]}",
        ]
    ),
)

# Create labels and show plot
fig.update_layout(
    title="Worldwide Computer Hardware Inventory<br><sup>Total Quantity and by Category</sup>",
    title_x=0.5,
    template="plotly_dark",
    coloraxis_colorbar_title_text="Total Inventory",
)

fig.show()

    This is an interactive plot! Hover over different countries to view the total quantity of each category in storage.

    💪 Now it's your turn to construct your own queries and analyze the data! Remember, you can review the tables in the database at any point using the "Browse tables" button.