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.
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
# 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.