DS108-Databases/SQL/DS108-L4-Creating-Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "50e6ddbb-52d3-4a02-9b69-100a4181e3b0",
   "metadata": {},
   "source": [
    "# DS108 Databases : Lesson Four - Creating Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1bd4bfc-9b15-4b80-87b4-b8b60eb56371",
   "metadata": {},
   "source": [
    "### Table of Contents <a class=\"anchor\" id=\"DS108L4_toc\"></a>\n",
    "\n",
    "* [Table of Contents](#DS108L4_toc)\n",
    "    * [Page 1 - Introduction](#DS108L4_page_1)\n",
    "    * [Page 2 - CREATE TABLE](#DS108L4_page_2)\n",
    "    * [Page 3 - Constraints](#DS108L4_page_3)\n",
    "    * [Page 4 - Primary Key](#DS108L4_page_4)\n",
    "    * [Page 5 - Foreign Key](#DS108L4_page_5)\n",
    "    * [Page 6 - Check, Default, Triggers](#DS108L4_page_6)\n",
    "    * [Page 7 - Insert](#DS108L4_page_7)\n",
    "    * [Page 8 - Drop Table](#DS108L4_page_8)\n",
    "    * [Page 9 - Views](#DS108L4_page_9)\n",
    "    * [Page 10 - Key Terms](#DS108L4_page_10)\n",
    "    * [Page 11 - Lesson 4 Hands-On](#DS108L4_page_11)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef5a8c65-ed48-4354-a257-e01b0fd733d4",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 1 - Introduction<a class=\"anchor\" id=\"DS108L4_page_1\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "17d758d0-6d83-49ad-b0a3-b44fe8820c62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"720\"\n",
       "            height=\"480\"\n",
       "            src=\"https://player.vimeo.com/video/243214697\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.VimeoVideo at 0x7fac10671eb0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import VimeoVideo\n",
    "# Tutorial Video Name: Creating Tables\n",
    "VimeoVideo('243214697', width=720, height=480)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b6ad526-95fa-43c4-bc43-1fe807708ed3",
   "metadata": {},
   "source": [
    "# Tables\n",
    "\n",
    "Now that you have worked somewhat with tables, you will explore more in-depth what tables are and what you can do with them.\n",
    "\n",
    "<div class=\"panel panel-success\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Additional Info!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>You may want to watch this <a href=\"https://vimeo.com/451685881\"> recorded live workshop </a> that goes over keys and constraints in more detail.</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n",
    "---\n",
    "\n",
    "## Columns\n",
    "\n",
    "Defining the structure of your table is essential to storing your data in an efficient and meaningful way. *Columns* are the most essential part of the table, this is where the actual data will live. You will define these columns (also called fields), with a datatype that you deem appropriate for the data you wish to store. A good choice for a name, for instance, would be a `VARCHAR(50)`, with the number in the parentheses indicating the maximum number of characters that any given name may have. A good choice for storing a date of birth might be `DATE`, while `INTEGER` may be the best choice for the model year of a vehicle. Be sure to choose appropriately here, as changing any of these types could be a cumbersome undertaking if a significant amount of data has been stored in the table.\n",
    "\n",
    "---\n",
    "\n",
    "## Nullability\n",
    "\n",
    "To review what you have learned so far, `Null` is a value that is undefined. Because of this, attempts to compare values to a field that contains a `NULL` value will fail. The only operation that works on this value is the binary evaluation of `IS NULL` or `IS NOT NULL`. As applied to tables, this is whether a column in your table can contain the value `NULL` or not. The keywords are either `NULL`, or `NOT NULL` appended to the datatype that follows the column name when you create the table. For instance: `FirstName varchar(50) NOT NULL` would indicate that the `FirstName` column should never be undefined. A practical application of using NULLs would be something like a voluntary survey – perhaps the applicant chose not to answer ethnicity.  So you could store `NULL` in that column when you save the data to indicate that there was no selection.\n",
    "\n",
    "The structure of a table is critical. You will be working mainly with the rows and columns of the tables in a database. Tables can be related to Excel spreadsheets. The columns define the data needed, and the rows list out the data for each object in the database. Below is a MS Excel spreadsheet with information about specific animals in a pet store. Tables in a SQL database will be structured very similarly to the below MS Excel example:\n",
    "\n",
    "![Excel table. Table with the headings name, species, breed, and age. Under name the entries are Henry, Rupert, Churchill, and Pepper. Under species the entries are dog, pig, cat, and dog. Under breed the entries are bulldog, mini, redpoint Siamese, mix, and under age the entries are 2, 1, 3 and 8.](Media/excelTable.png)\n",
    "_Figure 4-1: Excel Table_\n",
    "\n",
    "The columns define the data needed for each animal, and the rows provide that data for each animal.\n",
    "\n",
    "Great! Now that you understand the basic structure of a SQL table, you will learn how to create them!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7990ac7a-5a2f-4744-af14-1b7e385ea484",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 2 - CREATE TABLE<a class=\"anchor\" id=\"DS108L4_page_2\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "612429df-e4ef-4d85-8460-5bc8914079c3",
   "metadata": {},
   "source": [
    "Great! Now you understand the basic structure of a table, you will build one! You are going to be using the `CREATE TABLE` keywords to create a table in your existing database. Below is the syntax:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE table_name (\n",
    "    column1_name datatype,\n",
    "    column2_name datatype,\n",
    "    column3_name datatype,\n",
    "    column4_name datatype,\n",
    "    ...\n",
    ")\n",
    "```\n",
    "\n",
    "The column parameters will define the column names in the table. The datatype will specify the type of data each column can hold. Below is a list of common data types used:\n",
    "\n",
    "* CHAR(size)\n",
    "* VARCHAR(size)\n",
    "* NVARCHAR(size)\n",
    "* INTEGER\n",
    "* DATETIME\n",
    "* NUMERIC\n",
    "\n",
    "<div class=\"panel panel-info\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Tip!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>You explored SQL data types in Lesson 1 if you would like a refresher on their definitions.</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n",
    "Before you create your table in MySQL, take a look at a basic `CREATE TABLE` query:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE AppUsers (\n",
    "    AppUserID INTEGER,\n",
    "    FirstName VARCHAR(50),\n",
    "    LastName VARCHAR(50),\n",
    "    SignUpDate DATETIME\n",
    ")\n",
    "```\n",
    "\n",
    "The above query will create a table with four columns, but with no data,  as you can see below:\n",
    "\n",
    "![Basic table. File directory in Sakila. The folder Sakila has a folder called tables within it. Inside that folder are others titled actor, address, appusers,](Media/sql1.png)\n",
    "_Figure 4-2: Basic Table_\n",
    "\n",
    "The above table looks great, but a few things are missing that are used very frequently when creating a table. You will explore something called *Constraints* and how they can help you define your data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "832358fd-55fa-420a-8aa2-46fbf2097986",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 3 - Constraints<a class=\"anchor\" id=\"DS108L4_page_3\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f564e3e7-3a7f-4936-b3a3-2e0a9ab78512",
   "metadata": {},
   "source": [
    "# Constraints\n",
    "\n",
    "Constraints are used to specify rules for data in a table that will limit the type of data that can go within a table or column. This will make sure that the data is reliable and accurate and if any of the constraints are violated, the action or query will be aborted. You can apply a constraint to a column or a table. Read on to explore commonly used constraints.\n",
    "\n",
    "---\n",
    "\n",
    "## NOT NULL\n",
    "\n",
    "`NOT NULL` will force that column not to accept null values. By default, columns can hold `NULL` values. `NOT NULL` will prevent that column from having a null value. This is useful when you are gathering data from a user (like signing up for a website), and you need every input field to contain some data.\n",
    "\n",
    "Below is how it will look in your query:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE AppUsers1 (\n",
    "AppUserID INTEGER,\n",
    "FirstName VARCHAR(50) NOT NULL,\n",
    "LastName VARCHAR(50) NOT NULL,\n",
    "SignUpDate DATETIME NOT NULL)\n",
    "```\n",
    "\n",
    "Above, you are adding the `NOT NULL` constraint to the `FirstName`, `LastName` and `SignUpDate` columns. This will ensure that each of those rows in the `AppUsers` table will have data.\n",
    "\n",
    "---\n",
    "\n",
    "## UNIQUE\n",
    "\n",
    "*Unique* ensures that some column or combination of columns is unique to each row in the table. It's similar to a primary key, but a table may only have one Primary Key, whereas it might have several Unique Keys. An example here might be a column containing a social security number. The primary key might be id, but you might also want to monitor that the social security numbers that are being inputted are also unique to a particular customer. Since social security numbers SHOULD be unique, a failure to insert because of a violation of such a unique key could be a way to sound an alert.\n",
    "\n",
    "Consider below:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE AppUsers2 (\n",
    "AppUserID INTEGER UNIQUE,\n",
    "FirstName VARCHAR(50) NOT NULL,\n",
    "LastName VARCHAR(50) NOT NULL,\n",
    "SignUpDate DATETIME NOT NULL)\n",
    "```\n",
    "\n",
    "What you are doing above is making sure that the `AppUserID` is going to be unique for each user. You would not want to have two users with the same id. That could prove to be confusing and could potentially mess up your data.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb1aade2-59af-4cbc-b5ed-da31e39f266d",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 4 - Primary Key<a class=\"anchor\" id=\"DS108L4_page_4\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eba2ed6e-98dd-43f7-8761-6ecc9a13af08",
   "metadata": {},
   "source": [
    "# Primary Key\n",
    "\n",
    "`PRIMARY KEY` is a combination of `NOT NULL` and `UNIQUE`, so that every row in the table is unique and not null. This will enable you, when working in other tables, to identify that the data is related to the original table. For example, a customer table may have an id column that is a primary key. You could then use that customer id in a table that lists purchases, to relate a particular purchase to an individual customer account. **Almost all tables should have a primary key.** Without a primary key, there is no way to identify each row uniquely.\n",
    "\n",
    "All in all, `Primary Key` is a constraint that defines a column in a row so that it is not null and unique for every individual row. The SQL syntax for Primary Key is shown below:\n",
    "\n",
    "Consider below:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE AppUsers3 (\n",
    "AppUserID INTEGER PRIMARY KEY,\n",
    "FirstName VARCHAR(50) NOT NULL,\n",
    "LastName VARCHAR(50) NOT NULL,\n",
    "SignUpDate DATETIME NOT NULL)\n",
    "```\n",
    "\n",
    "What you have done above is replace `UNIQUE` with `PRIMARY KEY`. This will now ensure that the `AppUserID` is unique to every row and also is required to have a value.\n",
    "\n",
    "---\n",
    "\n",
    "## Autoincrement\n",
    "\n",
    "`AUTOINCREMENT` will automatically generate a unique number when a new record is inserted into a table. Often, the `PRIMARY KEY` field will also have AUTOINCREMENT since the primary key is the unique id for each row. It is handy to be able to generate the unique id for each row automatically.\n",
    "\n",
    "```sql\n",
    "CREATE TABLE AppUsers4 (\n",
    "AppUserID INTEGER PRIMARY KEY AUTO_INCREMENT,\n",
    "FirstName VARCHAR(50) NOT NULL,\n",
    "LastName VARCHAR(50) NOT NULL,\n",
    "SignUpDate DATETIME NOT NULL)\n",
    "```\n",
    "\n",
    "Above, your query will now automatically produce an `AppUserID` when a new row is generated.\n",
    "\n",
    "<div class=\"panel panel-info\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Tip!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>Autoincrement is written out differently depending on what type of SQL server you are using. In your case, you are using MySQL, but be aware it may change if you ever use a different flavor (version) of SQL.</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n",
    "---\n",
    "\n",
    "## Types of Primary Keys\n",
    "\n",
    "When using Primary Keys, there are two different types to keep in mind. Those types are *Natural* and *Surrogate*. Natural is when the Primary Key is assigned to a piece of data that already exists in the record, and will always be unique for each record. An example of this could be a Social Security Number. If you had employees in your database, every employee would have a unique SSN. A Surrogate is a Primary Key that doesn't have anything to do with the actual data. Up until this point, you have been using a Surrogate Primary Key by assigning an ID number to your customers or users. Surrogate Primary Keys are becoming more popular these days mainly because the cost of having a database with a significant amount of data is not nearly as expensive as it used to be. In the past, companies would save money by using a Natural key. Problems would arise when that Social Security Number of an employee changes, because that can and has happened. That could potentially break anything that is linked to that employee by his/her SSN. To wrap up, Surrogate keys are used more and more because that data has nothing to do with the actual record except to give it a unique piece of data.\n",
    "\n",
    "<div class=\"panel panel-success\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Additional Info!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>For a walkthrough example of creating a table and adding data, watch this workshop: <a href=\"https://vimeo.com/403016359\" target=\"_blank\">Create and Insert</a>.</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ead4ad3c-2fe6-457e-8924-1410e951bebd",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 5 - Foreign Key<a class=\"anchor\" id=\"DS108L4_page_5\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbbc06a2-e702-47a1-942f-cbcbe31221a7",
   "metadata": {},
   "source": [
    "\n",
    "# Foreign Key\n",
    "\n",
    "`FOREIGN KEY` is a relation between tables that will restrict the insertion of data in one table, depending on the contents of a second table. The `FOREIGN KEY` refers to the `PRIMARY KEY` in a separate table.\n",
    "\n",
    "Say you have a Customer Table that has a column for a state of residence. You might have a Foreign Key to a table with of all the states and their abbreviations in it, to ensure that any entry into the Customer Table has a valid state entry. Consider the following tables:\n",
    "\n",
    "Below, you have a snippet of a table called \"states\" that has the state name and their abbreviation:\n",
    "\n",
    "<table class=\"table table-striped\">\n",
    "    <tr>\n",
    "        <th>State</th>\n",
    "        <th>Abbreviation</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Alabama</td>\n",
    "        <td>AL</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Alaska</td>\n",
    "        <td>AK</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Arizona</td>\n",
    "        <td>AZ</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Arkansas</td>\n",
    "        <td>AR</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>California</td>\n",
    "        <td>CA</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Colorado</td>\n",
    "        <td>CO</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Connecticut</td>\n",
    "        <td>CT</td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "And below, you have a snippet of a table called \"customers\" that contains their info including the state in which they live.\n",
    "\n",
    "<table class=\"table table-striped\">\n",
    "    <tr>\n",
    "        <th>FirstName</th>\n",
    "        <th>LastName</th>\n",
    "        <th>State</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Alex</td>\n",
    "        <td>Smith</td>\n",
    "        <td>MA</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Ryan</td>\n",
    "        <td>Williams</td>\n",
    "        <td>AL</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>Bob</td>\n",
    "        <td>Jones</td>\n",
    "        <td>RI</td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "Below is how you would use `FOREIGN KEY` when you are creating the customers table:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE customerExample(\n",
    "  FirstName NVARCHAR(40),\n",
    "  LastName NVARCHAR(30),\n",
    "  State NVARCHAR(2),\n",
    "  FOREIGN KEY(State) REFERENCES states(Abbreviation)\n",
    ");\n",
    "```\n",
    "\n",
    "Above, you are using `FOREIGN KEY` to call out the column \"State\" in your customers table, so it references the `Abbreviation` column in the states table.\n",
    "\n",
    "<div class=\"panel panel-info\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Tip!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>To reference a foreign key, you will need to have the variable type exactly the same!</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd9e3b8a-9224-447f-8cf7-5035cae86421",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 6 - Check, Default, Triggers<a class=\"anchor\" id=\"DS108L4_page_6\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a81e2098-28ef-4ff5-a155-afe824de4323",
   "metadata": {},
   "source": [
    "# Check\n",
    "\n",
    "`CHECK` is an expression that you create that will check each entry into the table for validation.  If the entry fails the constraint, then the update or insert will fail. An example here would be a check constraint that would validate a phone or zip has the right number of digits, and that they were in fact digits instead of text. Here is an example:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE customerExample(\n",
    "  FirstName NVARCHAR(40),\n",
    "  LastName NVARCHAR(30),\n",
    "  State NVARCHAR(10),\n",
    "  PostalCode INTEGER,\n",
    "  FOREIGN KEY(State) REFERENCES states(Abbreviation),\n",
    "  CHECK (length(PostalCode) = 5)\n",
    ");\n",
    "```\n",
    "\n",
    "Above, you see that `CHECK` is used to ensure that `PostalCode` is equal to 5. To do this, you needed to use `length()`, a function that will return the length of whatever is inside the parenthesis. Say you are inserting a new customer into this table and forget a number in the postal code.  You will get the below error:\n",
    "\n",
    "```text\n",
    "Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## Default\n",
    "\n",
    "`DEFAULT` defines default values that you can assign to a column, in the case where an insert statement does not include a value for that column. A common use for this scenario is an Inserted column that you might have in your table, to indicate when the record was written. You might set the default constraint to the `datetime()` function, which would populate the date and time (local time, not UTC) of when the record was created in the table. An example would look like this:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE customerExample(\n",
    "  FirstName NVARCHAR(40),\n",
    "  LastName NVARCHAR(30),\n",
    "  State NVARCHAR(10),\n",
    "  PostalCode INTEGER,\n",
    "  SignUpDate datetime DEFAULT current_timestamp,\n",
    "  FOREIGN KEY(State) REFERENCES states(Abbreviation),\n",
    "  CHECK (length(PostalCode) = 5)\n",
    ");\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## Triggers\n",
    "\n",
    "`Triggers` are objects that you can append to your table that will run code based on the operation that is performed on a table. A common usage would be an 'after' trigger, which would be executed 'after' an insert, update or delete. You could use a trigger of this sort to capture the values changing in the table and send them to a log table for historical usage, to see how the values in the table have changed in the past. Triggers are usually found on smaller databases and not so much on larger ones, as the overhead and resource contention caused by triggers fired off continually can adversely impact performance to an extreme degree.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42cb7d8a-aced-42e5-ac24-32acf8c95473",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 7 - Insert<a class=\"anchor\" id=\"DS108L4_page_7\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf0debaf-5fae-4035-8e7d-ae62e62456cb",
   "metadata": {},
   "source": [
    "\n",
    "# Insert\n",
    "\n",
    "Now that you understand how to create a table, you can use `INSERT`, which you learned previously in this module.\n",
    "\n",
    "Run the below query:\n",
    "\n",
    "```sql\n",
    "create table actorExample(\n",
    "\tactor_id smallint(5) unsigned,\n",
    "    first_name varchar(45),\n",
    "    last_name varchar(45),\n",
    "    last_update timestamp)\n",
    "```\n",
    "\n",
    "Now you can insert data into this table:\n",
    "\n",
    "\n",
    "```sql\n",
    "insert into actorExample\n",
    "values (200, \"Jamie\", \"Thomas\", \"2020-01-23 12:16:34\")\n",
    "```\n",
    "\n",
    "And if you look at the data in the `actorExample` table, you should see the below output:\n",
    "\n",
    "```text\n",
    "200\tJamie\tThomas\t2020-01-23 12:16:34\n",
    "```\n",
    "\n",
    "Awesome! But what if you want to insert data into this table that already exists in your database? You can do that! \n",
    "\n",
    "---\n",
    "\n",
    "## Insert All Columns\n",
    "\n",
    "If you wanted to insert all columns from one table to another, you would do this in general terms:\n",
    "\n",
    "```sql\n",
    "INSERT INTO table2\n",
    "SELECT * FROM table1\n",
    "WHERE condition;\n",
    "```\n",
    "\n",
    "<div class=\"panel panel-info\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Tip!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>If you are inserting all columns from one table to the next, each table MUST have the same name, number, and order of the columns. Otherwise, you will get an error.</p>\n",
    "    </div>\n",
    "</div>\n",
    "\n",
    "---\n",
    "\n",
    "## Insert Some Columns\n",
    "\n",
    "If you wanted to insert only some columns, you would do this in general terms:\n",
    "\n",
    "```sql\n",
    "INSERT INTO table2 (column1, column2, column3, ...)\n",
    "SELECT column1, column2, column3, ...\n",
    "FROM table1;\n",
    "```\n",
    "\n",
    "You could also add a `where` statement to this:\n",
    "\n",
    "```sql\n",
    "INSERT INTO table2 (column1, column2, column3, ...)\n",
    "SELECT column1, column2, column3, ...\n",
    "FROM table1\n",
    "WHERE condition;\n",
    "```\n",
    "\n",
    "Notice the difference from the `INSERT` queries you have run in the past. Instead of having the keyword `VALUES`, you are now using `SELECT` to select which columns should be inserted into your table.\n",
    "\n",
    "---\n",
    "\n",
    "## Try it Yourself!\n",
    "\n",
    "Try inserting the data from the `actor` table into the `actorExample` table. Give it a try first, and if you get stuck, the answer is located below.\n",
    "\n",
    "If you get the following message with a green check mark next to it, then you did it correctly!\n",
    "\n",
    "```text\n",
    "3\t40\t12:23:25\tINSERT INTO actorExample (actor_id, first_name, last_name, last_update)\n",
    " SELECT actor_id, first_name, last_name, last_update\n",
    " FROM actor\t204 row(s) affected\n",
    " Records: 204  Duplicates: 0  Warnings: 0\t0.063 sec\n",
    "```\n",
    "\n",
    "If you do not see the same as above, the correct code is located below:\n",
    "\n",
    "```sql\n",
    "INSERT INTO actorExample (actor_id, first_name, last_name, last_update)\n",
    "SELECT actor_id, first_name, last_name, last_update\n",
    "FROM actor;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7b3f800-0a14-4d13-8c32-390965c537ca",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 8 - Drop Table<a class=\"anchor\" id=\"DS108L4_page_8\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4597e4b4-ce22-448d-843e-eab5ba74a9c4",
   "metadata": {},
   "source": [
    "# Dropping a Table\n",
    "\n",
    "Now that you know how to create a table and insert data into it, you will learn how to delete the table. The syntax is relatively straightforward:\n",
    "\n",
    "```sql\n",
    "DROP TABLE table_name;\n",
    "```\n",
    "\n",
    "Try out this syntax by deleting the table `actorExample` you created earlier:\n",
    "\n",
    "```sql\n",
    "drop table actorExample\n",
    "```\n",
    "\n",
    "If you did it correctly, you should see that wonderful green checkmark. \n",
    "\n",
    "Great work! Let's keep moving!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36943bcd-7e16-4af1-97d3-c09e81ae5e99",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 9 - Views<a class=\"anchor\" id=\"DS108L4_page_9\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdf2cdde-9447-49b5-8936-17d80f46b45e",
   "metadata": {},
   "source": [
    "# Views\n",
    "\n",
    "A `View` is a virtual table based on the result of a SQL statement. It contains rows and columns, just like a real table, but when a view is created, a table is NOT created; you are just showing the output of the selected data. The fields in the View are from one or more real tables that exist in the database. You can use SQL functions, `WHERE`, and `JOIN` statements in a view to be able to present the data as if it were coming from one table.\n",
    "\n",
    "---\n",
    "\n",
    "## CREATE VIEW Syntax\n",
    "\n",
    "Here is the generalized syntax for creating views:\n",
    "\n",
    "```sql\n",
    "CREATE VIEW view_name AS\n",
    "SELECT column1, column2, ...\n",
    "FROM table_name\n",
    "WHERE condition;\n",
    "```\n",
    "\n",
    "And now you can try actually creating a view:\n",
    "\n",
    "```sql\n",
    "create view CurrentCustomers as\n",
    "select customer_id, first_name, last_name\n",
    "from customer\n",
    "where active = 1\n",
    "```\n",
    "\n",
    "Above, you are creating a view to see all customers ID, first name and last name who are active. When the above query is executed, the green checkmark will appear, so you know the view was created correctly. Next, to see the view, run the following query:\n",
    "\n",
    "```sql\n",
    "SELECT * FROM CurrentCustomers;\n",
    "```\n",
    "\n",
    "You will now see the CustomerId, FirstName and LastName of customers that are active. Creating views are useful, because it means you don't need to run complicated queries many times. You can run a complicated query and set it to a view that you can then select easily.\n",
    "\n",
    "---\n",
    "\n",
    "## Create View with Join\n",
    "\n",
    "Creating a View using a Join will look very similar to what you have done in the past with Joins. Below is a Join query you ran in previous lessons:\n",
    "\n",
    "```sql\n",
    "SELECT first_name, last_name, film_id\n",
    "FROM sakila.actor\n",
    "INNER JOIN sakila.film_actor\n",
    "ON sakila.actor.actor_id = sakila.film_actor.actor_id\n",
    "```\n",
    "\n",
    "All you would need to do if you wanted to create a view with the above query is add the `Create View` statement on the first line:\n",
    "\n",
    "```sql\n",
    "create view ActorFilms as\n",
    "SELECT first_name, last_name, film_id\n",
    "FROM sakila.actor\n",
    "INNER JOIN sakila.film_actor\n",
    "ON sakila.actor.actor_id = sakila.film_actor.actor_id\n",
    "```\n",
    "\n",
    "And if you run:\n",
    "\n",
    "```sql\n",
    "SELECT * FROM ActorFilms;\n",
    "```\n",
    "\n",
    "You will easily be able to see the three columns selected in the CREATE VIEW query.\n",
    "\n",
    "---\n",
    "\n",
    "## Drop View\n",
    "\n",
    "Now, if you didn't need your view anymore, you would use the following generalized syntax:\n",
    "\n",
    "```sql\n",
    "DROP VIEW [view_name];\n",
    "```\n",
    "\n",
    "Practice by dropping the `CurrentCustomers` view you created:\n",
    "\n",
    "```sql\n",
    "drop view CurrentCustomers\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a0d02d5-04fa-4220-9637-d02c3b32dce5",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 10 - Key Terms<a class=\"anchor\" id=\"DS108L4_page_10\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57d3ae42-c060-4684-91b4-73f73a6bedda",
   "metadata": {},
   "source": [
    "# Key Terms\n",
    "\n",
    "Below are a list and short description of the relevant keywords you have learned in this lesson. Please read through and go back and review any concepts you don't fully understand. Great Work!\n",
    "\n",
    "<table class=\"table table-striped\">\n",
    "    <tr>\n",
    "        <th>Keyword</th>\n",
    "        <th>Description</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>CREATE TABLE</td>\n",
    "        <td>SQL keyword to create a new table in a database.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>DROP TABLE</td>\n",
    "        <td>Used to drop a table in a database.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>NOT NULL</td>\n",
    "        <td>Constraint used to define that a column in a table cannot be null.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>UNIQUE</td>\n",
    "        <td>Constraint used to define that a column in a table has to be unique.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>PRIMARY KEY</td>\n",
    "        <td>A combination of UNIQUE and NOT NULL and can only be used once in a table. Two types are Natural and Surrogate.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>AUTO_INCREMENT</td>\n",
    "        <td>Keyword to automatically generate a unique number for each record.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>FOREIGN KEY</td>\n",
    "        <td>The relation between tables that will restrict the insertion of data in one table, depending on the contents of a second table. The `FOREIGN KEY` refers to the `PRIMARY KEY` in a separate table.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>CHECK</td>\n",
    "        <td>An expression that will check each entry into the table for validation when the table is created.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>DEFAULT</td>\n",
    "        <td>Defines a default value for a column if a value is not supplied.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>Triggers</td>\n",
    "        <td>Objects that will run code based on the operation that is performed on a table.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>CREATE VIEW</td>\n",
    "        <td>Creates a virtual table called a View so queries only need to be run once and can then be assigned to the view. Prevents having to run a query many times to see information.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>UPDATE VIEW</td>\n",
    "        <td>Used to update the columns in a View.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style=\"font-weight: bold;\" nowrap>DROP VIEW</td>\n",
    "        <td>Used to drop a view.</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "342d63b4-a38e-4274-884a-c343985718e8",
   "metadata": {},
   "source": [
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">\n",
    "\n",
    "# Page 11 - Lesson 4 Hands-On<a class=\"anchor\" id=\"DS108L4_page_11\"></a>\n",
    "\n",
    "[Back to Top](#DS108L4_toc)\n",
    "\n",
    "<hr style=\"height:10px;border-width:0;color:gray;background-color:gray\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b791e307-c12f-43cb-99b1-27f5a71b3fbb",
   "metadata": {},
   "source": [
    "For your Lesson 4 Hands-On, you will be working with your new knowledge on SQL queries. This Hands-On **will** be graded, so be sure you complete all requirements. Please read through the below setup instructions before starting your project. \n",
    "\n",
    "---\n",
    "\n",
    "## Setup\n",
    "\n",
    "This Hands-On is structured into two parts, and each part may ask you to run multiple queries. After each query, please take a screenshot of the MySQL output and add it to a Word document (or an equivalent) and name this file `SQL-HandsOn4`. This way, you will be able to submit your answers to each part all at once.\n",
    "\n",
    "Now you are ready to get started! Good luck! \n",
    "\n",
    "---\n",
    "\n",
    "## Part 1\n",
    "\n",
    "Run the following queries:\n",
    "\n",
    "1.  Create a view named \"initialCustomers\" that shows the first name, last name, and email address of customers that have an id of less than 100. Once that is complete, select and view your newly created view.\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "## Part 2\n",
    "\n",
    "Complete the following:\n",
    "\n",
    "Create a table named \"ProductList\". Include the following columns:\n",
    "\n",
    "* ProductId\n",
    "* ProductName\n",
    "* Price\n",
    "* DateAdded\n",
    "* EmployeeSupportId\n",
    "\n",
    "Include the following requirements:\n",
    "\n",
    "1.  Every product should have an automatically generated id number that should be unique for each product.\n",
    "2.  Give each column a data type that would apply\n",
    "3.  Give the DateAdded column a default value for the current local time.\n",
    "4.  All columns CANNOT be null.\n",
    "\n",
    "Next, insert one product into the table following the given guidelines when the table was created. When inserting the data, don't include the `ProductId` or the `DateAdded`. Finally, run a query to see the single product in your table.\n",
    "\n",
    "<div class=\"panel panel-danger\">\n",
    "    <div class=\"panel-heading\">\n",
    "        <h3 class=\"panel-title\">Caution!</h3>\n",
    "    </div>\n",
    "    <div class=\"panel-body\">\n",
    "        <p>Be sure to zip and submit your <code>SQL-HandsOn4</code> Word document when finished! You will not be able to re-submit, so be sure the screenshots to each part are located within this document.</p>\n",
    "    </div>\n",
    "</div>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bf30f1f-ff17-4669-917f-90cf22c5d757",
   "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}