SQL_Practice_SingleTable.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Practice Exercises for Single Table SQL\n",
    "\n",
    "Given a dataset and a set of questions, form the queries to answer the questions. All questions in this particular exercise only require a query to a single table. \n",
    "\n",
    "⚠️ *Note:* Please display minimal number of attributes (columns) needed to make sense of your answer. Not too many, not too few.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "The SQLite database for these exercises has been setup for you. For the curious, you can look at this file for the DDL commands to setup the tables and insert data.  `db_illum_ddl_5_3.sql`\n",
    "\n",
    "Here is the schema for the database. The arrows show foreign keys, which is just fyi for now. \n",
    "\n",
    "![worker_project schema](worker_project_schema.png) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make sure to always run this cell when you open this notebook\n",
    "# Start the Jupyter SQL engine, connecting to a SQLite database \n",
    "%reload_ext sql \n",
    "%sql sqlite:///worker_project.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "0. In the command line, inspect the sqlite database (worker_project.db) to see what the tables and columns actually are. Any differences you observe? (Also, copy past your output in the cell below)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Get the names of all workers in the Accounting department."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Get the name of the employee in the Research department who has the lowest salary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Get the details of the project with the highest budget."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The following questions may require more than one query to answer. \n",
    "Show your work! Add as many SQL cells as needed for each question. Use comments to indicate how the output of each query is used to arrive at your answer. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. List out alphabetically the names of all workers working on Project 1001."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. Get an alphabetical list of names and corresponding ratings for all workers on any project managed by Michael Burns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6. For all projects starting after Feb 1, 2022, find the project number and names of all workers assigned to them. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7. Find the names of employees who are not assigned as managers any project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8. Find details of any project with the string \"urn\" in the project name. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "9. List out the projects from most to least efficient. (hint: the project with the lowest budget is not necessarily the one that uses time and human resources effectively.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10. Explain your reasoning for your solution to #9 (use a Markdown cell), including who should get the award for Most Efficient Project Manager."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Submission:\n",
    "\n",
    "Commit and push your changes to this document by the deadline, and share a link to the repository in the Moodle assignment.  "
   ]
  }
 ],
 "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.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "3ad933181bd8a04b432d3370b9dc3b0662ad032c4dfaa4e4f1596c548f763858"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}