Session 12 - SQL_2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ced7b15d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "\n",
    "conn = sqlite3.connect(\"employee.db\")\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute(\"\"\"CREATE TABLE employee_demographics (\n",
    "  employee_id INT NOT NULL,\n",
    "  first_name VARCHAR(50),\n",
    "  last_name VARCHAR(50),\n",
    "  age INT,\n",
    "  gender VARCHAR(10),\n",
    "  birth_date DATE,\n",
    "  PRIMARY KEY (employee_id)\n",
    ")\"\"\")\n",
    "\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "147074ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"\"\"CREATE TABLE employee_salary (\n",
    "  employee_id INT NOT NULL,\n",
    "  first_name VARCHAR(50) NOT NULL,\n",
    "  last_name VARCHAR(50) NOT NULL,\n",
    "  occupation VARCHAR(50),\n",
    "  salary INT,\n",
    "  dept_id INT\n",
    ")\"\"\")\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5b94e897",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"\"\"INSERT INTO employee_demographics (employee_id, first_name, last_name, age, gender, birth_date)\n",
    "VALUES\n",
    "(1,'Leslie', 'Knope', 44, 'Female','1979-09-25'),\n",
    "(3,'Tom', 'Haverford', 36, 'Male', '1987-03-04'),\n",
    "(4, 'April', 'Ludgate', 29, 'Female', '1994-03-27'),\n",
    "(5, 'Jerry', 'Gergich', 61, 'Male', '1962-08-28'),\n",
    "(6, 'Donna', 'Meagle', 46, 'Female', '1977-07-30'),\n",
    "(7, 'Ann', 'Perkins', 35, 'Female', '1988-12-01'),\n",
    "(8, 'Chris', 'Traeger', 43, 'Male', '1980-11-11'),\n",
    "(9, 'Ben', 'Wyatt', 38, 'Male', '1985-07-26'),\n",
    "(10, 'Andy', 'Dwyer', 34, 'Male', '1989-03-25'),\n",
    "(11, 'Mark', 'Brendanawicz', 40, 'Male', '1983-06-14'),\n",
    "(12, 'Craig', 'Middlebrooks', 37, 'Male', '1986-07-27')\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "20b77c74",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"\"\"INSERT INTO employee_salary (employee_id, first_name, last_name, occupation, salary, dept_id)\n",
    "VALUES\n",
    "(1, 'Leslie', 'Knope', 'Deputy Director of Parks and Recreation', 75000,1),\n",
    "(2, 'Ron', 'Swanson', 'Director of Parks and Recreation', 70000,1),\n",
    "(3, 'Tom', 'Haverford', 'Entrepreneur', 50000,1),\n",
    "(4, 'April', 'Ludgate', 'Assistant to the Director of Parks and Recreation', 25000,1),\n",
    "(5, 'Jerry', 'Gergich', 'Office Manager', 50000,1),\n",
    "(6, 'Donna', 'Meagle', 'Office Manager', 60000,1),\n",
    "(7, 'Ann', 'Perkins', 'Nurse', 55000,4),\n",
    "(8, 'Chris', 'Traeger', 'City Manager', 90000,3),\n",
    "(9, 'Ben', 'Wyatt', 'State Auditor', 70000,6),\n",
    "(10, 'Andy', 'Dwyer', 'Shoe Shiner and Musician', 20000, NULL),\n",
    "(11, 'Mark', 'Brendanawicz', 'City Planner', 57000, 3),\n",
    "(12, 'Craig', 'Middlebrooks', 'Parks Director', 65000,1)\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "423445e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"\"\"CREATE TABLE parks_departments (\n",
    "  department_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "  department_name varchar(50) NOT NULL)\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b03fbed0",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"\"\"INSERT INTO parks_departments (department_name)\n",
    "VALUES\n",
    "('Parks and Recreation'),\n",
    "('Animal Control'),\n",
    "('Public Works'),\n",
    "('Healthcare'),\n",
    "('Library'),\n",
    "('Finance');\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2a1b0dd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Female', 71.0), ('Male', 100.0)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT gender, AVG(buy) FROM customers GROUP BY gender\")\n",
    "print(c.fetchall())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "60e9face",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Female', 23, 100, 3, 71.0), ('Male', 50, 150, 2, 100.0)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT gender, MIN(buy), MAX(buy), COUNT(buy),AVG(buy) FROM customers GROUP BY gender\")\n",
    "print(c.fetchall())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "746f59b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Minoo', 'Sayyadpour', 'Female', 'minoosayyadpour34@gmail.com', 90), ('Yalda', 'Goli', 'Female', 'yg@email.com', 23), ('customerName1', 'customerLastName1', 'Female', 'customer1@email.com', 100), ('customerName2', 'customerLastName2', 'Male', 'customer2@email.com', 150), ('customerName3', 'customerLastName3', 'Male', 'customer3@email.com', 50)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT * FROM customers ORDER BY first_name\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "942b6b27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('customerName3', 'customerLastName3', 'Male', 'customer3@email.com', 50), ('customerName2', 'customerLastName2', 'Male', 'customer2@email.com', 150), ('customerName1', 'customerLastName1', 'Female', 'customer1@email.com', 100), ('Yalda', 'Goli', 'Female', 'yg@email.com', 23), ('Minoo', 'Sayyadpour', 'Female', 'minoosayyadpour34@gmail.com', 90)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT * FROM customers ORDER BY first_name DESC\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5b51b9e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('customerName3', 'customerLastName3', 'Male', 'customer3@email.com', 50), ('customerName2', 'customerLastName2', 'Male', 'customer2@email.com', 150), ('Yalda', 'Goli', 'Female', 'yg@email.com', 23), ('Minoo', 'Sayyadpour', 'Female', 'minoosayyadpour34@gmail.com', 90), ('customerName1', 'customerLastName1', 'Female', 'customer1@email.com', 100)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT * FROM customers ORDER BY gender DESC, buy ASC\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d64fa039",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('customerName2', 'customerLastName2', 'Male', 'customer2@email.com', 150), ('customerName1', 'customerLastName1', 'Female', 'customer1@email.com', 100), ('Minoo', 'Sayyadpour', 'Female', 'minoosayyadpour34@gmail.com', 90), ('customerName3', 'customerLastName3', 'Male', 'customer3@email.com', 50), ('Yalda', 'Goli', 'Female', 'yg@email.com', 23)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT * FROM customers ORDER BY 5 DESC, 4 DESC\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5959d60",
   "metadata": {},
   "source": [
    "## Having vs Where\n",
    "\n",
    "- Both were created to filter rows of data, but they filter 2 separate things\n",
    "- Where is going to filters rows based off columns of data\n",
    "- Having is going to filter rows based off aggregated columns when grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5f86f978",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Male', 100.0)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT gender, AVG(buy) FROM customers GROUP BY gender HAVING AVG(buy) > 80\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d7f07e5",
   "metadata": {},
   "source": [
    "## ALIASING\n",
    "\n",
    "Aliasing is just a way to change the name of the column (for the most part), it can also be used in joins."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9aa9343b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Female', 71.0), ('Male', 100.0)]\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"SELECT gender, AVG(buy) AS BuyAverage FROM customers GROUP BY gender\")\n",
    "print(c.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "31a60dab",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0459a4e8",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}