SQL-Northwind-Lesson-Solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1a1ab6cf-c65f-4c47-9984-c0609baca104",
   "metadata": {},
   "source": [
    "# Northwind SQLite Solutions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bea76c41-ae98-48c5-9964-4ce98f029361",
   "metadata": {},
   "source": [
    "## Prerequisite Setup\n",
    "\n",
    "To enable the `%%sql` Python Magic in the code cells below, run these two setup steps to:\n",
    "1. Load the SQL Jupyter Notebook extension\n",
    "2. Open the SQLite database to allow this notebook to access it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27d5a0c7-25ed-4672-ad91-44ccaabbb0de",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa540a95-fd06-4c35-b57d-8ac94ecdf33a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///data/northwind.db"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65e61f5d-0659-4a72-981a-6a434f613783",
   "metadata": {},
   "source": [
    "<a id='ex1'></a>\n",
    "### Exercise 1: Basic SELECT Queries\n",
    "\n",
    "**Objective:** Practice retrieving data using `SELECT` statements."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3053840-5093-4bee-9ad6-f6cee166560d",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 1.1\n",
    "What is the `CompanyName` of the customer with `CustomerID` 'ALFKI'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a440dfba-ccb5-4c0d-b90c-7f6bb50c79cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT CompanyName\n",
    "FROM Customers\n",
    "WHERE CustomerID = 'ALFKI';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe50c160-ea00-47e1-a7d2-8f5e6b5ba3ec",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 1\n",
    ".2\n",
    "How many products are listed in the `Products` table?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e50aef1-0fe1-4f4b-aa5b-df430d668dde",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) AS ProductCount\n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abfdb371-d352-48bc-b9cb-6fe72ee7c81f",
   "metadata": {},
   "source": [
    "<a id='ex2'></a>\n",
    "### Exercise 2: Filtering Data with WHERE Clause\n",
    "\n",
    "**Objective:** Use the `WHERE` clause to filter query results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91027113-88c4-4652-b164-2caaa8c5be32",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 2.1\n",
    "How many suppliers are from the USA?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96a8341d-cd4b-4d69-a412-93ca412efd6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) AS USASupplierCount\n",
    "FROM Suppliers\n",
    "WHERE Country = 'USA';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "341411fc-6425-4d60-8b4f-6c22f7553364",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 2.2\n",
    "Which employees have the title \"Sales Representative\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bb9173c-b9f8-4707-b598-d295a23db451",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LastName, FirstName, Title\n",
    "FROM Employees\n",
    "WHERE Title = 'Sales Representative';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3ddbdd9-dc46-4cf4-bc34-876fd55ba1d7",
   "metadata": {},
   "source": [
    "<a id='ex3'></a>\n",
    "### Exercise 3: Sorting Data with ORDER BY\n",
    "\n",
    "**Objective:** Learn how to sort query results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "654841c4-5575-4e81-b241-f9be4d5cad73",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 3.1\n",
    "What is the cheapest product available?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7df05ad5-5a19-47c7-b0c7-6018691e0e96",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT ProductName, UnitPrice\n",
    "FROM Products\n",
    "ORDER BY UnitPrice ASC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91a7b8a-2c03-47a9-834f-b27f78df6942",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 3.2\n",
    "Which customer comes first when sorted by `CompanyName`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76a26e7a-d893-4686-bf51-643ccd0ab4f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT CompanyName\n",
    "FROM Customers\n",
    "ORDER BY CompanyName ASC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0999b96c-72b1-4f3e-bf8d-73adf98d95c8",
   "metadata": {},
   "source": [
    "<a id='ex4'></a>\n",
    "### Exercise 4: Joining Tables\n",
    "\n",
    "**Objective:** Combine data from multiple tables using joins."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9287e70b-6a53-4682-8a43-e755b482f415",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 4.1\n",
    "How many orders has each customer placed?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f5b3cd2-d1af-4fe3-9c05-8bd002f5cf09",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Customers.CompanyName, COUNT(Orders.OrderID) AS OrderCount\n",
    "FROM Customers\n",
    "LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID\n",
    "GROUP BY Customers.CompanyName;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e96424c7-9e48-40f6-8f03-bef8f00191e3",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 4.2\n",
    "Which products are supplied by \"Exotic Liquids\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2dbfd4f-2107-4a6b-a2ab-cddb255b0087",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Products.ProductName\n",
    "FROM Products\n",
    "JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID\n",
    "WHERE Suppliers.CompanyName = 'Exotic Liquids';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5309bc4-629b-4b8a-a9b6-7a0bc44e3e6d",
   "metadata": {},
   "source": [
    "<a id='ex5'></a>\n",
    "### Exercise 5: Aggregate Functions and GROUP BY\n",
    "\n",
    "**Objective:** Use aggregate functions to summarize data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a066d20e-1670-47c2-8c3b-e1363e5601b2",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 5.1\n",
    "Which country has the most customers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2ea9837-26fc-4a9a-80d4-d4303efe1c1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Country, COUNT(*) AS CustomerCount\n",
    "FROM Customers\n",
    "GROUP BY Country\n",
    "ORDER BY CustomerCount DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8d678c7-69ed-40f7-8bec-35cd0d3c70ea",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 5.2\n",
    "What is the total revenue generated from all orders? \n",
    "\n",
    "💡 **Tip:** You'll need to join `OrderDetails` and `Orders` tables and use `SUM`.\n",
    "\n",
    "⚠️ **Warning:** There is no `OrderDetails` table, but there is an `Order Details` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5235dc35-415b-424d-8ed8-f46c0439db39",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT SUM(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount)) AS TotalRevenue\n",
    "FROM `Order Details` AS OrderDetails\n",
    "JOIN Orders ON OrderDetails.OrderID = Orders.OrderID;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aab1594f-5e1e-482b-89c7-1824505b0bb0",
   "metadata": {},
   "source": [
    "<a id='ex6'></a>\n",
    "### Exercise 6: Advanced Joins and Subqueries\n",
    "\n",
    "**Objective:** Solve complex queries using advanced SQL techniques.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e98ffe43-d603-4dbc-8f58-c82e1324adaf",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 6.1\n",
    "Which employee has processed the most orders?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36019480-caba-4195-92b0-cd56edec3409",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Employees.LastName, Employees.FirstName, COUNT(Orders.OrderID) AS OrderCount\n",
    "FROM Employees\n",
    "JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID\n",
    "GROUP BY Employees.LastName, Employees.FirstName\n",
    "ORDER BY OrderCount DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c2def34-21d1-4dae-be50-d90eeded4aa7",
   "metadata": {},
   "source": [
    "#### 🥊 Challenge 6.2\n",
    "Are there any products supplied by multiple suppliers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4b00d6d-3190-43c2-a144-43ec2324fae9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Products.ProductName, COUNT(DISTINCT Products.SupplierID) AS SupplierCount\n",
    "FROM Products\n",
    "GROUP BY Products.ProductName\n",
    "HAVING SupplierCount > 1;"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}