{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"id": "2db70a62-9226-46b5-ab2c-9d7de9d19d10",
"metadata": {},
"outputs": [],
"source": [
"from DATA225utils import make_connection, dataframe_query"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "f14b6fb9-f062-4ed2-9aa0-c02050eede7c",
"metadata": {},
"outputs": [],
"source": [
"conn = make_connection('zagi-sales.ini')\n",
"cursor = conn.cursor()"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "bd4a9b1e-57a2-439c-8a9e-8d3307f4fe8b",
"metadata": {},
"outputs": [],
"source": [
"def display_df(conn, sql):\n",
" _, df = dataframe_query(conn, sql)\n",
" display(df)"
]
},
{
"cell_type": "markdown",
"id": "0e70bc4f-1c98-4b28-8e88-80c80d39ca28",
"metadata": {},
"source": [
"#### **PROBLEM 1.** Display the vendor ID and vendor name for all vendors."
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "0675e3d8-15fd-4ed5-b76f-ddcadf6da26b",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" vendor_id | \n",
" vendor_name | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" MK | \n",
" Mountain King | \n",
"
\n",
" \n",
" 1 | \n",
" PG | \n",
" Pacifica Gear | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" vendor_id vendor_name\n",
"0 MK Mountain King\n",
"1 PG Pacifica Gear"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT *\n",
" FROM vendor\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "2a5192bf-a164-4815-807d-57f89af9e532",
"metadata": {},
"source": [
"#### **PROBLEM 2.** Display the customer name and customer ZIP for all customers."
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "4324e22a-c6df-4073-95b0-ddd80912a9ff",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" customer_name | \n",
" customer_zip | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Tina | \n",
" 60137 | \n",
"
\n",
" \n",
" 1 | \n",
" Tony | \n",
" 60611 | \n",
"
\n",
" \n",
" 2 | \n",
" Pam | \n",
" 35401 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" customer_name customer_zip\n",
"0 Tina 60137\n",
"1 Tony 60611\n",
"2 Pam 35401"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT customer_name, customer_zip\n",
" FROM customer\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "a77b415d-d875-48ef-926d-02605ea9b7f5",
"metadata": {},
"source": [
"#### **PROBLEM 3.** Display the product ID, product name, and product price for products with a product price of $100 or higher."
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "8318fc26-d89b-4984-8a48-512a9b6fb5df",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" product_id | \n",
" product_name | \n",
" product_price | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1X1 | \n",
" Zzz Bag | \n",
" 100.00 | \n",
"
\n",
" \n",
" 1 | \n",
" 5X5 | \n",
" Tiny Tent | \n",
" 150.00 | \n",
"
\n",
" \n",
" 2 | \n",
" 6X6 | \n",
" Biggy Tent | \n",
" 250.00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" product_id product_name product_price\n",
"0 1X1 Zzz Bag 100.00\n",
"1 5X5 Tiny Tent 150.00\n",
"2 6X6 Biggy Tent 250.00"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT product_id, product_name, product_price\n",
" FROM product\n",
" WHERE product_price >= 100\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "66d035a9-e809-42e9-b6b8-9b4312f34195",
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"id": "33053bc1-313f-4921-9dbf-6e37a9182c39",
"metadata": {},
"source": [
"#### **PROBLEM 4.** Display the product ID, product name, product price, and vendor name for all products. Sort the results by product ID."
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "356b4beb-1036-478f-993d-be0dbf0169c9",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" product_id | \n",
" product_name | \n",
" product_price | \n",
" vendor_name | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1X1 | \n",
" Zzz Bag | \n",
" 100.00 | \n",
" Pacifica Gear | \n",
"
\n",
" \n",
" 1 | \n",
" 2X2 | \n",
" Easy Boot | \n",
" 70.00 | \n",
" Mountain King | \n",
"
\n",
" \n",
" 2 | \n",
" 3X3 | \n",
" Cosy Sock | \n",
" 15.00 | \n",
" Mountain King | \n",
"
\n",
" \n",
" 3 | \n",
" 4X4 | \n",
" Dura Boot | \n",
" 90.00 | \n",
" Pacifica Gear | \n",
"
\n",
" \n",
" 4 | \n",
" 5X5 | \n",
" Tiny Tent | \n",
" 150.00 | \n",
" Mountain King | \n",
"
\n",
" \n",
" 5 | \n",
" 6X6 | \n",
" Biggy Tent | \n",
" 250.00 | \n",
" Mountain King | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" product_id product_name product_price vendor_name\n",
"0 1X1 Zzz Bag 100.00 Pacifica Gear\n",
"1 2X2 Easy Boot 70.00 Mountain King\n",
"2 3X3 Cosy Sock 15.00 Mountain King\n",
"3 4X4 Dura Boot 90.00 Pacifica Gear\n",
"4 5X5 Tiny Tent 150.00 Mountain King\n",
"5 6X6 Biggy Tent 250.00 Mountain King"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT p.product_id, p.product_name, p.product_price, v.vendor_name\n",
" FROM product p, vendor v\n",
" WHERE p.vendor_id = v.vendor_id\n",
" ORDER BY p.product_id\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "0b1ee4f3-04fa-493e-8bb5-1ff5d18bec68",
"metadata": {},
"source": [
"#### **PROBLEM 5.** Display the product ID, product name, product price, vendor name, and category name for all products. Sort the results by product ID."
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "6786e5bf-1629-4f58-8a6d-3d279fa2eb6e",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" product_id | \n",
" product_name | \n",
" product_price | \n",
" vendor_name | \n",
" category_name | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1X1 | \n",
" Zzz Bag | \n",
" 100.00 | \n",
" Pacifica Gear | \n",
" Camping | \n",
"
\n",
" \n",
" 1 | \n",
" 2X2 | \n",
" Easy Boot | \n",
" 70.00 | \n",
" Mountain King | \n",
" Footwear | \n",
"
\n",
" \n",
" 2 | \n",
" 3X3 | \n",
" Cosy Sock | \n",
" 15.00 | \n",
" Mountain King | \n",
" Footwear | \n",
"
\n",
" \n",
" 3 | \n",
" 4X4 | \n",
" Dura Boot | \n",
" 90.00 | \n",
" Pacifica Gear | \n",
" Footwear | \n",
"
\n",
" \n",
" 4 | \n",
" 5X5 | \n",
" Tiny Tent | \n",
" 150.00 | \n",
" Mountain King | \n",
" Camping | \n",
"
\n",
" \n",
" 5 | \n",
" 6X6 | \n",
" Biggy Tent | \n",
" 250.00 | \n",
" Mountain King | \n",
" Camping | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" product_id product_name product_price vendor_name category_name\n",
"0 1X1 Zzz Bag 100.00 Pacifica Gear Camping\n",
"1 2X2 Easy Boot 70.00 Mountain King Footwear\n",
"2 3X3 Cosy Sock 15.00 Mountain King Footwear\n",
"3 4X4 Dura Boot 90.00 Pacifica Gear Footwear\n",
"4 5X5 Tiny Tent 150.00 Mountain King Camping\n",
"5 6X6 Biggy Tent 250.00 Mountain King Camping"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT p.product_id, p.product_name, p.product_price, \n",
" v.vendor_name, c.category_name\n",
" FROM product p, vendor v, category c\n",
" WHERE p.category_id = c.category_id \n",
" AND p.vendor_id = v.vendor_id\n",
" ORDER BY p.product_id\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "85078c84-92aa-4ef3-9515-ebc21746394d",
"metadata": {},
"source": [
"#### **PROBLEM 6.** Display the product ID, product name, and product price for products in the category whose category name value is Camping. Sort the results by product ID."
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "a6c1561c-1e58-42e2-9095-553055c5203c",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" product_id | \n",
" product_name | \n",
" product_price | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1X1 | \n",
" Zzz Bag | \n",
" 100.00 | \n",
"
\n",
" \n",
" 1 | \n",
" 5X5 | \n",
" Tiny Tent | \n",
" 150.00 | \n",
"
\n",
" \n",
" 2 | \n",
" 6X6 | \n",
" Biggy Tent | \n",
" 250.00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" product_id product_name product_price\n",
"0 1X1 Zzz Bag 100.00\n",
"1 5X5 Tiny Tent 150.00\n",
"2 6X6 Biggy Tent 250.00"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT p.product_id, p.product_name, p.product_price\n",
" FROM product p, category c\n",
" WHERE p.category_id = c.category_id \n",
" AND c.category_name = 'Camping'\n",
" ORDER BY p.product_id\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "5294786c-4d07-4904-8b23-c44d038724dc",
"metadata": {},
"source": [
"#### **PROBLEM 7.** Display the transaction ID, customer name, and transaction date for sales transactions involving a customer buying a product whose product name is Dura Boot."
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "1fa5fcdc-f058-4394-851c-17f0d911c64f",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" tid | \n",
" customer_name | \n",
" tdate | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" T444 | \n",
" Pam | \n",
" 2023-02-02 | \n",
"
\n",
" \n",
" 1 | \n",
" T555 | \n",
" Tony | \n",
" 2023-02-02 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" tid customer_name tdate\n",
"0 T444 Pam 2023-02-02\n",
"1 T555 Tony 2023-02-02"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"display_df(conn,\n",
" \"\"\"\n",
" SELECT t.tid, c.customer_name, t.tdate\n",
" FROM sales_transaction t, customer c, product p, sold_via s\n",
" WHERE t.tid = s.tid\n",
" AND p.product_id = s.product_id\n",
" AND c.customer_id = t.customer_id\n",
" AND p.product_name = 'Dura Boot'\n",
" \"\"\"\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a3145a4e-a5c0-4ee2-825a-91d77ad04a4f",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.13"
}
},
"nbformat": 4,
"nbformat_minor": 5
}