{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
vendor_idvendor_name
0MKMountain King
1PGPacifica Gear
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_namecustomer_zip
0Tina60137
1Tony60611
2Pam35401
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_nameproduct_price
01X1Zzz Bag100.00
15X5Tiny Tent150.00
26X6Biggy Tent250.00
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_nameproduct_pricevendor_name
01X1Zzz Bag100.00Pacifica Gear
12X2Easy Boot70.00Mountain King
23X3Cosy Sock15.00Mountain King
34X4Dura Boot90.00Pacifica Gear
45X5Tiny Tent150.00Mountain King
56X6Biggy Tent250.00Mountain King
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_nameproduct_pricevendor_namecategory_name
01X1Zzz Bag100.00Pacifica GearCamping
12X2Easy Boot70.00Mountain KingFootwear
23X3Cosy Sock15.00Mountain KingFootwear
34X4Dura Boot90.00Pacifica GearFootwear
45X5Tiny Tent150.00Mountain KingCamping
56X6Biggy Tent250.00Mountain KingCamping
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_nameproduct_price
01X1Zzz Bag100.00
15X5Tiny Tent150.00
26X6Biggy Tent250.00
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
tidcustomer_nametdate
0T444Pam2023-02-02
1T555Tony2023-02-02
\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 }