{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "d18c0fa9-7b5e-4a07-b851-e8bb3396a25d", "metadata": {}, "outputs": [], "source": [ "from data201 import db_connection, df_query" ] }, { "cell_type": "code", "execution_count": 2, "id": "f0ea4227-43f3-44ec-8365-5618eea7eca3", "metadata": {}, "outputs": [], "source": [ "conn = db_connection('ZAGI-Sales.ini')" ] }, { "cell_type": "markdown", "id": "216952f5-0893-4f93-87b8-34b98c8f5e3e", "metadata": {}, "source": [ "## Which products sell below the average price?\n", "### What is the average price?" ] }, { "cell_type": "code", "execution_count": 3, "id": "4b2b844a-b9ec-41b5-b382-d45771e6e70f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AVG(product_price)
0112.5
\n", "
" ], "text/plain": [ " AVG(product_price)\n", "0 112.5" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT AVG(product_price)\n", " FROM product\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "37b7b1dc-6010-4bbd-b0dd-81d4785b54b9", "metadata": {}, "source": [ "### Products selling below the average price." ] }, { "cell_type": "code", "execution_count": 4, "id": "3f92e21f-52e7-40fa-aa9d-35c3d907a84f", "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", "
product_idproduct_nameproduct_price
01X1Zzz Bag100.0
12X2Easy Boot70.0
23X3Cosy Sock15.0
34X4Dura Boot90.0
\n", "
" ], "text/plain": [ " product_id product_name product_price\n", "0 1X1 Zzz Bag 100.0\n", "1 2X2 Easy Boot 70.0\n", "2 3X3 Cosy Sock 15.0\n", "3 4X4 Dura Boot 90.0" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, product_name, product_price\n", " FROM product\n", " WHERE product_price < (\n", " SELECT AVG(product_price)\n", " FROM product\n", " )\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "698cc138-930c-4563-9cb9-99ab8fa275c1", "metadata": {}, "source": [ "## What is the name, id, and price of products that have more than 3 items sold in all sales transactions?\n", "### Number of product items sold." ] }, { "cell_type": "code", "execution_count": 5, "id": "29f98ba5-af59-4195-90c0-4afac690f411", "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", "
product_idno_of_items
01X11
11X11
21X12
32X21
42X22
53X35
64X41
74X44
85X52
96X61
\n", "
" ], "text/plain": [ " product_id no_of_items\n", "0 1X1 1\n", "1 1X1 1\n", "2 1X1 2\n", "3 2X2 1\n", "4 2X2 2\n", "5 3X3 5\n", "6 4X4 1\n", "7 4X4 4\n", "8 5X5 2\n", "9 6X6 1" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, no_of_items\n", " FROM sold_via\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "b0cdecd4-7e42-4f7b-8970-e2ebe13d49ec", "metadata": {}, "source": [ "### Products that sold more than 3 items." ] }, { "cell_type": "code", "execution_count": 6, "id": "ebe953a1-4e34-413f-a622-4c43f52088da", "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", "
product_idSUM(no_of_items)
01X14.0
13X35.0
24X45.0
\n", "
" ], "text/plain": [ " product_id SUM(no_of_items)\n", "0 1X1 4.0\n", "1 3X3 5.0\n", "2 4X4 5.0" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, SUM(no_of_items)\n", " FROM sold_via\n", " GROUP BY product_id\n", " HAVING SUM(no_of_items) > 3\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "f59ffd76-e067-4f6c-b08f-a7a6db407bdd", "metadata": {}, "source": [ "### Product id, name, and price." ] }, { "cell_type": "code", "execution_count": 7, "id": "20900a8d-7115-4ef2-84c6-825fa0aead2e", "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.0
13X3Cosy Sock15.0
24X4Dura Boot90.0
\n", "
" ], "text/plain": [ " product_id product_name product_price\n", "0 1X1 Zzz Bag 100.0\n", "1 3X3 Cosy Sock 15.0\n", "2 4X4 Dura Boot 90.0" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, product_name, product_price\n", " FROM product\n", " WHERE product_id IN (\n", " SELECT product_id\n", " FROM sold_via\n", " GROUP BY product_id\n", " HAVING SUM(no_of_items) > 3\n", " )\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "e088f742-21e3-44cb-9bd3-7cac7080cb12", "metadata": {}, "source": [ "# What is the name, id, and price of products that were each sold in more than one sales transaction?\n", "### Sales transactions." ] }, { "cell_type": "code", "execution_count": 8, "id": "d5b60ce5-1b3b-4ad6-a680-8908227366e6", "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", "
product_idtid
01X1T111
12X2T222
21X1T333
33X3T333
42X2T444
54X4T444
64X4T555
75X5T555
86X6T555
91X1T666
\n", "
" ], "text/plain": [ " product_id tid\n", "0 1X1 T111\n", "1 2X2 T222\n", "2 1X1 T333\n", "3 3X3 T333\n", "4 2X2 T444\n", "5 4X4 T444\n", "6 4X4 T555\n", "7 5X5 T555\n", "8 6X6 T555\n", "9 1X1 T666" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, tid\n", " FROM sold_via\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "229e84bb-d983-4162-9024-7cc2bb694727", "metadata": {}, "source": [ "### More than one transaction." ] }, { "cell_type": "code", "execution_count": 9, "id": "788c8ab6-71b8-44bf-9bdb-4639c1baa7fc", "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", "
product_id
01X1
12X2
24X4
\n", "
" ], "text/plain": [ " product_id\n", "0 1X1\n", "1 2X2\n", "2 4X4" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id\n", " FROM sold_via\n", " GROUP BY product_id\n", " HAVING COUNT(*) > 1\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "markdown", "id": "5c480e77-b837-49bf-b9c9-de9d2f4662bf", "metadata": {}, "source": [ "### Product id, name, and price." ] }, { "cell_type": "code", "execution_count": 10, "id": "4c0b0c15-4da4-49ba-a532-7da5c5f345b4", "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.0
12X2Easy Boot70.0
24X4Dura Boot90.0
\n", "
" ], "text/plain": [ " product_id product_name product_price\n", "0 1X1 Zzz Bag 100.0\n", "1 2X2 Easy Boot 70.0\n", "2 4X4 Dura Boot 90.0" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "df = df_query(conn, \n", " \"\"\"\n", " SELECT product_id, product_name, product_price\n", " FROM product\n", " WHERE product_id IN (\n", " SELECT product_id\n", " FROM sold_via\n", " GROUP BY product_id\n", " HAVING COUNT(*) > 1\n", " )\n", " \"\"\"\n", ")\n", "\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 11, "id": "9e0b3032-a8ba-451a-b91d-af659376ef35", "metadata": {}, "outputs": [], "source": [ "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "6bbcc3f3-3a20-4f92-8502-b9a2a6d81d64", "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.12.4" } }, "nbformat": 4, "nbformat_minor": 5 }