MySQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MySQL\n",
    "\n",
    "MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications.This notebook will give you a quick start to MySQL and make you comfortable with MySQL programming."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database\n",
    "A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A Relational DataBase Management System (RDBMS) is a software that −\n",
    "\n",
    "--> Enables you to implement a database with tables, columns and indexes.\n",
    "\n",
    "--> Guarantees the Referential Integrity between rows of various tables.\n",
    "\n",
    "--> Updates the indexes automatically.\n",
    "\n",
    "--> Interprets an SQL query and combines information from various tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RDBMS Terminology\n",
    "Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.\n",
    "\n",
    "**Database** − A database is a collection of tables, with related data.\n",
    "\n",
    "**Table** − A table is a matrix with data. A table in a database looks like a simple spreadsheet.\n",
    "\n",
    "**Column** − One column (data element) contains data of one and the same kind, for example the column postcode.\n",
    "\n",
    "**Row** − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.\n",
    "\n",
    "**Redundancy** − Storing data twice, redundantly to make the system faster.\n",
    "\n",
    "**Primary Key** − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.\n",
    "\n",
    "**Foreign Key** − A foreign key is the linking pin between two tables.\n",
    "\n",
    "**Compound Key** − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.\n",
    "\n",
    "**Index** − An index in a database resembles an index at the back of a book.\n",
    "\n",
    "**Referential Integrity** − Referential Integrity makes sure that a foreign key value always points to an existing row."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a database, type the following command. Replace dbname with the name of the database that you want to create:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE DATABASE dbname; # CREATE DATABASE <name_of_the_database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To work with the new database, type the following command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "USE dbname; #USE <name_of_the_database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also verify the database selected using the command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT DATABASE(); # To check the selected database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To list the databases created till now and to check the available databases, we can write the following command on the command line interface"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "show databases; # Show all the databases created till now"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To drop a database-"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP DATABASE dbname; # DROP DATABASE <name_of_the_database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Numeric Types- INT, SMALLINT, TINTINT, MEDIUMINT, BIGINT, DECIMAL, NUMERIC, FLOAT, DOUBLE, BIT \n",
    "#### String Types-CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, ENUM\n",
    "#### Date Times- DATE, DATETIME, TIMESTAMP, TIME, YEAR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "|Name  |Breed        |Age|\n",
    "|----  |-------------|---|\n",
    "|Blue  |Scottish Fold|1  |\n",
    "|Rocket|Persian      |3  |\n",
    "|Monty |Tabby        |10 |\n",
    "|Sam   |Munchkin     |5  |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can create a table in Mysql in a particular selected database using the following syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE table_name(column_name_1 data_type_1,\n",
    "                        column_name_2 data_type_2); #CREATE TABLE <name_of_the_table>(column_name_1 data_type_1,\n",
    "                                                    #column_name_2 data_type_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "here table_name is the name of the table you want to create. You can change this name according to your own requirement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### A Simple Example for creating a Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE cats\n",
    "  (\n",
    "name VARCHAR(100),\n",
    "age INT\n",
    "  );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to view the tables we created, we can type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SHOW TABLES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to get a jist of the table you created, you can type one of the following commands on the cli:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SHOW COLUMNS FROM tablename;\n",
    "#OR\n",
    "DESC tablename;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### INSERTING INTO TABLE:\n",
    "The INSERT INTO statement is used to insert new records in a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO tablename(column_name_1,column_name_2) VALUES(value_for_col_1,val_for_col_2);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This above line of code inserts a row into the mysql table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example for inserting a row into the cats table we created above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO cats(name, age) VALUES ('Jetson', 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To view the table we created and to check the row values we inserted, we can type-"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM tablename;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM cats;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If due to some reason a table is not needed anymore, you can delete it using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE tablename;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE cats;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CRUD Operations\n",
    "#### -->Create\n",
    "#### -->Read\n",
    "#### -->Update\n",
    "#### -->Delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create using INSERT INTO\n",
    "INSERT INTO cats(name, age) VALUES(Taco, 14);\n",
    "DROP TABLE cats;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE cats \n",
    "  ( \n",
    "     cat_id INT NOT NULL AUTO_INCREMENT, \n",
    "     name   VARCHAR(100), \n",
    "     breed  VARCHAR(100), \n",
    "     age    INT, \n",
    "     PRIMARY KEY (cat_id) \n",
    "  ); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DESC cats;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO cats(name, breed, age) \n",
    "VALUES ('Ringo', 'Tabby', 4),\n",
    "       ('Cindy', 'Maine Coon', 10),\n",
    "       ('Dumbledore', 'Maine Coon', 11),\n",
    "       ('Egg', 'Persian', 4),\n",
    "       ('Misty', 'Tabby', 13),\n",
    "       ('George Michael', 'Ragdoll', 9),\n",
    "       ('Jackson', 'Sphynx', 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SELECT Statements\n",
    "The SELECT statement is used to select data from a database.\n",
    "\n",
    "The data returned is stored in a result table, called the result-set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read\n",
    "SELECT * FROM cats;\n",
    "\n",
    "SELECT name FROM cats;\n",
    "\n",
    "SELECT age FROM cats;\n",
    "\n",
    "SELECT cat_id FROM cats;\n",
    "\n",
    "SELECT name, age FROM cats;\n",
    "\n",
    "SELECT cat_id, name, age FROM cats;\n",
    "\n",
    "SELECT age, breed, name, cat_id FROM cats;\n",
    "\n",
    "SELECT cat_id, name, age, breed FROM cats;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Intro to WHERE \n",
    "The WHERE clause is used to filter records.\n",
    "\n",
    "The WHERE clause is used to extract only those records that fulfill a specified condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select by age:\n",
    "\n",
    "SELECT * FROM cats WHERE age=4;\n",
    "\n",
    "#Select by name:\n",
    "\n",
    "SELECT * FROM cats WHERE name='Egg';\n",
    "\n",
    "#Notice how it deals with case:\n",
    "\n",
    "SELECT * FROM cats WHERE name='egG';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Introduction to Aliases\n",
    "SQL aliases are used to give a table, or a column in a table, a temporary name.\n",
    "\n",
    "Aliases are often used to make column names more readable.\n",
    "\n",
    "An alias only exists for the duration of the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table_name AS alias_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT cat_id AS id, name FROM cats;\n",
    "\n",
    "SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;\n",
    "\n",
    "DESC cats;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Updating Data\n",
    "The UPDATE statement is used to modify the existing records in a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "UPDATE table_name\n",
    "SET column1 = value1, column2 = value2, ...\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Change tabby cats to shorthair:\n",
    "\n",
    "UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';\n",
    "\n",
    "#Another update:\n",
    "\n",
    "UPDATE cats SET age=14 WHERE name='Misty';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Deleting Data\n",
    "The DELETE statement is used to delete existing records in a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DELETE FROM table_name WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DELETE FROM cats WHERE name='Egg';\n",
    "\n",
    "SELECT * FROM cats;\n",
    "\n",
    "SELECT * FROM cats WHERE name='egg';\n",
    "\n",
    "DELETE FROM cats WHERE name='egg';\n",
    "\n",
    "SELECT * FROM cats;\n",
    "\n",
    "DELETE FROM cats;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Running SQL Files\n",
    "We can run the MySQL queries from a .sql file using source filename.sql "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE cats\n",
    "    (\n",
    "        cat_id INT NOT NULL AUTO_INCREMENT,\n",
    "        name VARCHAR(100),\n",
    "        age INT,\n",
    "        PRIMARY KEY(cat_id)\n",
    "    );\n",
    "\n",
    "use cat_app;\n",
    "\n",
    "source first_file.sql\n",
    "\n",
    "DESC cats;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO cats(name, age)\n",
    "VALUES('Charlie', 17);\n",
    "\n",
    "INSERT INTO cats(name, age)\n",
    "VALUES('Connie', 10);\n",
    "\n",
    "SELECT * FROM cats;\n",
    "\n",
    "source testing/insert.sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Ultimate MySQL Bootcamp Go from SQL Beginner to Expert"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loading our Book Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE books \n",
    "    (\n",
    "        book_id INT NOT NULL AUTO_INCREMENT,\n",
    "        title VARCHAR(100),\n",
    "        author_fname VARCHAR(100),\n",
    "        author_lname VARCHAR(100),\n",
    "        released_year INT,\n",
    "        stock_quantity INT,\n",
    "        pages INT,\n",
    "        PRIMARY KEY(book_id)\n",
    "    );\n",
    "\n",
    "INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)\n",
    "VALUES\n",
    "('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),\n",
    "('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),\n",
    "('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),\n",
    "('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),\n",
    "('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),\n",
    "('The Circle', 'Dave', 'Eggers', 2013, 26, 504),\n",
    "('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),\n",
    "('Just Kids', 'Patti', 'Smith', 2010, 55, 304),\n",
    "('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),\n",
    "('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),\n",
    "('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),\n",
    "(\"Where I'm Calling From: Selected Stories\", 'Raymond', 'Carver', 1989, 12, 526),\n",
    "('White Noise', 'Don', 'DeLillo', 1985, 49, 320),\n",
    "('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),\n",
    "('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),\n",
    "('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Coding with CONCAT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT author_fname, author_lname FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combine data for cleaner output.\n",
    "CONCAT(column_name,another_column_name)\n",
    "\n",
    "CONCAT(author_fname, author_lname)\n",
    "\n",
    "CONCAT(column, 'text', anotherColumn, 'more text')\n",
    "\n",
    "CONCAT(author_fname, ' ', author_lname)\n",
    "\n",
    "SELECT CONCAT('Hello', 'World');\n",
    "\n",
    "SELECT CONCAT('Hello', '...', 'World');\n",
    "\n",
    "SELECT\n",
    "  CONCAT(author_fname, ' ', author_lname)\n",
    "FROM books;\n",
    "\n",
    "SELECT\n",
    "  CONCAT(author_fname, ' ', author_lname) AS 'full name'\n",
    "FROM books;\n",
    "\n",
    "SELECT author_fname AS first, author_lname AS last, \n",
    "  CONCAT(author_fname, ' ', author_lname) AS full\n",
    "FROM books;\n",
    "\n",
    "SELECT author_fname AS first, author_lname AS last, \n",
    "  CONCAT(author_fname, ', ', author_lname) AS full\n",
    "FROM books;\n",
    "\n",
    "SELECT CONCAT(title, '-', author_fname, '-', author_lname) FROM books;\n",
    "\n",
    "SELECT \n",
    "    CONCAT_WS(' - ', title, author_fname, author_lname) \n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### INTRODUCING SUBSTRING/SUBSTR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUBSTRING('Hello World', 1, 4);\n",
    "gives output- Hell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUBSTRING('Hello World', 7);\n",
    "gives output- World"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUBSTRING('Hello World', -3);\n",
    "gives output- rld"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT CONCAT\n",
    "(\n",
    "SUBSTRING(title, 1, 10),\n",
    "'...'\n",
    ")\n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT CONCAT\n",
    "(\n",
    "SUBSTRING(title, 1, 10),\n",
    "'...'\n",
    ") AS 'short title'\n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### INTRODUCING REPLACE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT REPLACE('Hello World', 'Hell', '%$#@');\n",
    "#removes hell and replace with %$#@"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "  REPLACE('cheese bread coffee milk', ' ', ' and ');\n",
    "#gives output- cheese and bread and coffee and milk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT REPLACE(title, 'e ', '3') FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using substring and replace together as:-\n",
    "SELECT SUBSTRING\n",
    "(\n",
    "    replace(title,'e','3'),1,10\n",
    ") AS 'weird string'\n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using REVERSE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT REVERSE('Hello World');\n",
    "gives output- dlroW olleH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT REVERSE(author_fname) FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#CONCAT and REVERSE\n",
    "SELECT CONCAT('woof', REVERSE('woof'));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with CHAR LENGTH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT CHAR_LENGTH('Hello World');\n",
    "gives output- 11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#CONCAT\n",
    "SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Changing Case with UPPER and LOWER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT UPPER('Hello World');\n",
    "gives output- HELLO WORLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT LOWER(title) FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Books Data\n",
    "INSERT INTO books\n",
    "    (title, author_fname, author_lname, released_year, stock_quantity, pages)\n",
    "    VALUES ('10% Happier', 'Dan', 'Harris', 2014, 29, 256), \n",
    "           ('fake_book', 'Freida', 'Harris', 2001, 287, 428),\n",
    "           ('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);\n",
    "\n",
    "\n",
    "SELECT title FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using DISTINCT\n",
    "The DISTINCT statement is used to return only distinct (different) values.\n",
    "\n",
    "Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT DISTINCT author_lname FROM books;\n",
    "#used to remove duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT DISTINCT author_fname, author_lname FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting data by ORDERBY\n",
    "The ORDER BY keyword is used to sort the result-set in ascending or descending order.\n",
    "\n",
    "The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT author_lname FROM books ORDER BY author_lname DESC;\n",
    "# for descending ordering of author_lname in the books table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using LIMIT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title FROM books LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year FROM books \n",
    "ORDER BY released_year DESC LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Better Searches with LIKE\n",
    "The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.\n",
    "\n",
    "There are two wildcards often used in conjunction with the LIKE operator:\n",
    "\n",
    "% - The percent sign represents zero, one, or multiple characters\n",
    "\n",
    "_ - The underscore represents a single character"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';\n",
    "SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### COUNT FUNCTION\n",
    "The COUNT() function returns the number of rows that matches a specified criteria."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### GROUPBY\n",
    "The GROUP BY statement group rows that have the same values into summary rows, like \"find the number of customers in each country\".\n",
    "\n",
    "The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table_name\n",
    "WHERE condition\n",
    "GROUP BY column_name(s)\n",
    "ORDER BY column_name(s);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, author_lname FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, author_lname FROM books\n",
    "GROUP BY author_lname\n",
    "SELECT author_lname, COUNT(*) \n",
    "FROM books GROUP BY author_lname;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AVERAGE/AVG()\n",
    "The AVG() function returns the average value of a numeric column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT AVG(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT AVG(released_year) \n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT AVG(stock_quantity) \n",
    "FROM books \n",
    "GROUP BY released_year;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SUM()\n",
    "The SUM() function returns the total sum of a numeric column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUM(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUM(pages) FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT author_fname,\n",
    "       author_lname,\n",
    "       Sum(pages)\n",
    "FROM books\n",
    "GROUP BY\n",
    "    author_lname,\n",
    "    author_fname;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MIN() and MAX()\n",
    "The MIN() function returns the smallest value of the selected column.\n",
    "\n",
    "The MAX() function returns the largest value of the selected column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#MIN Syntax\n",
    "SELECT MIN(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#MAX Syntax\n",
    "SELECT MAX(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT MIN(released_year) \n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT MAX(pages) \n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using Min and Max with Group By\n",
    "SELECT\n",
    "  author_fname,author_lname,Max(pages)\n",
    "FROM books\n",
    "GROUP BY author_lname,\n",
    "         author_fname;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Revisiting DataTypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#VARCHAR\n",
    "CREATE TABLE dogs (name CHAR(5), breed VARCHAR(10));\n",
    "\n",
    "INSERT INTO dogs (name, breed) VALUES ('bob', 'beagle');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#DECIMAL\n",
    "CREATE TABLE items(price DECIMAL(5,2));\n",
    "\n",
    "INSERT INTO items(price) VALUES(7);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#FLOAT and DOUBLE\n",
    "CREATE TABLE thingies (price FLOAT);\n",
    "\n",
    "INSERT INTO thingies(price) VALUES (88.45);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#DATE, TIME and DATETIME\n",
    "CREATE TABLE people (name VARCHAR(100), birthdate DATE, birthtime TIME, birthdt DATETIME);\n",
    "\n",
    "INSERT INTO people (name, birthdate, birthtime, birthdt)\n",
    "VALUES('Padma', '1983-11-11', '10:07:35', '1983-11-11 10:07:35');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#CURDATE, CURTIME and NOW\n",
    "INSERT INTO people (name, birthdate, birthtime, birthdt)\n",
    "VALUES('Toaster', CURDATE(), CURTIME(), NOW());"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CURDATE()- Gives current date\n",
    "\n",
    "CURTIME()- Gives current time\n",
    "\n",
    "NOW()- Gives current datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Formatting Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, DAY(birthdate) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, birthdate, DAYNAME(birthdate) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, birthdate, DAYOFWEEK(birthdate) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, birthdate, DAYOFYEAR(birthdate) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, birthdt, MONTH(birthdt) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT name, birthtime, HOUR(birthtime) FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Date Math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT DATEDIFF(NOW(), birthdate) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with TIMESTAMP()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE comments (\n",
    "    content VARCHAR(100),\n",
    "    created_at TIMESTAMP DEFAULT NOW()\n",
    ");\n",
    "INSERT INTO comments (content) VALUES('lol what a funny article');\n",
    "INSERT INTO comments (content) VALUES('I found this offensive');\n",
    "INSERT INTO comments (content) VALUES('Ifasfsadfsadfsad');\n",
    "SELECT * FROM comments ORDER BY created_at DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Logical Operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Not Equal(!=)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title FROM books WHERE released_year != 2017;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, author_lname FROM books WHERE author_lname != 'Harris';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NOT LIKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title FROM books WHERE title NOT LIKE 'W%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Greater Than(>)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year FROM books \n",
    "WHERE released_year > 2000 ORDER BY released_year;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, stock_quantity FROM books WHERE stock_quantity >= 100;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT 99>1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Less Than(<)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year FROM books\n",
    "WHERE released_year < 2000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT 3 < -10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Logical AND"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT  \n",
    "    title, \n",
    "    author_lname, \n",
    "    released_year FROM books\n",
    "WHERE author_lname='Eggers' \n",
    "    AND released_year > 2010;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT 1 < 5 && 7 = 9;\n",
    "#returns 0 or 1 based on the condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Logical OR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT \n",
    "    title, \n",
    "    author_lname, \n",
    "    released_year \n",
    "FROM books\n",
    "WHERE author_lname='Eggers' || released_year > 2010;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT 40 <= 100 || -2 > 0;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### BETWEEN\n",
    "The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.\n",
    "\n",
    "The BETWEEN operator is inclusive: begin and end values are included. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table_name\n",
    "WHERE column_name BETWEEN value1 AND value2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year FROM books WHERE released_year >= 2004 && released_year <= 2015;\n",
    "#or\n",
    "SELECT title, released_year FROM books \n",
    "WHERE released_year BETWEEN 2004 AND 2015;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### IN and NOT IN\n",
    "The IN operator allows you to specify multiple values in a WHERE clause.\n",
    "\n",
    "The IN operator is a shorthand for multiple OR conditions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT \n",
    "    title, \n",
    "    author_lname \n",
    "FROM books\n",
    "WHERE author_lname='Carver' OR\n",
    "      author_lname='Lahiri' OR\n",
    "      author_lname='Smith';\n",
    "\n",
    "SELECT title, author_lname FROM books\n",
    "WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year FROM books\n",
    "WHERE released_year IN (2017, 1985);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CASE Statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, released_year,\n",
    "       CASE \n",
    "         WHEN released_year >= 2000 THEN 'Modern Lit'\n",
    "         ELSE '20th Century Lit'\n",
    "       END AS GENRE\n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT title, stock_quantity,\n",
    "    CASE \n",
    "        WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'\n",
    "        WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'\n",
    "        ELSE '***'\n",
    "    END AS STOCK\n",
    "FROM books;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When there exist a relationship between two mysql tables like orders and customers, students and teachers, movies and reviews.\n",
    "And there are basically three types of relationships:-\n",
    "1. One to One Relationship\n",
    "2. One to Many Relationship\n",
    "3. Many to Many Relationship\n",
    "One to One Relationship are not that common"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One to Many (Customers and Orders)\n",
    "The Most Common Relationship.\n",
    "\n",
    "***Customer table*** - first_name,last_name,email\n",
    "\n",
    "***Orders table*** - order_id,order_date,amount,customer_id\n",
    "\n",
    "Implemented using PRIMARY KEY and FOREIGN KEY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with Foreign Key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create customers and orders table\n",
    "CREATE TABLE customers(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    first_name VARCHAR(100),\n",
    "    last_name VARCHAR(100),\n",
    "    email VARCHAR(100)\n",
    ");\n",
    "CREATE TABLE orders(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    order_date DATE,\n",
    "    amount DECIMAL(8,2),\n",
    "    customer_id INT,\n",
    "    FOREIGN KEY(customer_id) REFERENCES customers(id)\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Inserting into customers and orders\n",
    "INSERT INTO customers (first_name, last_name, email) \n",
    "VALUES ('Boy', 'George', 'george@gmail.com'),\n",
    "       ('George', 'Michael', 'gm@gmail.com'),\n",
    "       ('David', 'Bowie', 'david@gmail.com'),\n",
    "       ('Blue', 'Steele', 'blue@gmail.com'),\n",
    "       ('Bette', 'Davis', 'bette@aol.com');\n",
    "       \n",
    "INSERT INTO orders (order_date, amount, customer_id)\n",
    "VALUES ('2016/02/10', 99.99, 1),\n",
    "       ('2017/11/11', 35.50, 1),\n",
    "       ('2014/12/12', 800.67, 2),\n",
    "       ('2015/01/03', 12.50, 2),\n",
    "       ('1999/04/11', 450.25, 5);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Gives Error because of our foreign key constraint.No user with id: 98\n",
    "INSERT INTO orders (order_date, amount, customer_id)\n",
    "VALUES ('2016/06/06', 33.67, 98);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### A JOIN clause is used to combine rows from two or more tables, based on a related column between them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CROSS JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Finding Orders Placed By George: 2 Step Process\n",
    "SELECT id FROM customers WHERE last_name='George';\n",
    "SELECT * FROM orders WHERE customer_id = 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Find orderss placed by George: Using a subquery \n",
    "SELECT * FROM orders WHERE customer_id =\n",
    "    (\n",
    "        SELECT id FROM customers\n",
    "        WHERE last_name='George'\n",
    "    );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### INNER JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#IMPLICIT INNER JOIN\n",
    "SELECT * FROM customers, orders \n",
    "WHERE customers.id = orders.customer_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#EXPLICIT INNER JOIN\n",
    "SELECT * FROM customers\n",
    "JOIN orders\n",
    "    ON customers.id = orders.customer_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ARBITRARY JOIN - meaningless, but still possible\n",
    "SELECT * FROM customers\n",
    "JOIN orders ON customers.id = orders.id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#LEFT JOIN\n",
    "SELECT * FROM customers\n",
    "LEFT JOIN orders\n",
    "    ON customers.id = orders.customer_id;\n",
    "    \n",
    "    \n",
    "    \n",
    "SELECT \n",
    "    first_name, \n",
    "    last_name,\n",
    "    IFNULL(SUM(amount), 0) AS total_spent\n",
    "FROM customers\n",
    "LEFT JOIN orders\n",
    "    ON customers.id = orders.customer_id\n",
    "GROUP BY customers.id\n",
    "ORDER BY total_spent;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#RIGHT JOIN\n",
    "SELECT * FROM customers\n",
    "RIGHT JOIN orders\n",
    "ON customers.id = orders.customer_id;\n",
    "\n",
    "\n",
    "\n",
    "SELECT \n",
    "    IFNULL(first_name,'MISSING') AS first, \n",
    "    IFNULL(last_name,'USER') as last, \n",
    "    order_date, \n",
    "    amount, \n",
    "    SUM(amount)\n",
    "FROM customers\n",
    "RIGHT JOIN orders\n",
    "    ON customers.id = orders.customer_id\n",
    "GROUP BY first_name, last_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Working with ON DELETE CASCADE \n",
    "CREATE TABLE customers(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    first_name VARCHAR(100),\n",
    "    last_name VARCHAR(100),\n",
    "    email VARCHAR(100)\n",
    ");\n",
    "\n",
    "CREATE TABLE orders(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    order_date DATE,\n",
    "    amount DECIMAL(8,2),\n",
    "    customer_id INT,\n",
    "    FOREIGN KEY(customer_id) \n",
    "        REFERENCES customers(id)\n",
    "        ON DELETE CASCADE\n",
    ");\n",
    "\n",
    "\n",
    "INSERT INTO customers (first_name, last_name, email) \n",
    "VALUES ('Boy', 'George', 'george@gmail.com'),\n",
    "       ('George', 'Michael', 'gm@gmail.com'),\n",
    "       ('David', 'Bowie', 'david@gmail.com'),\n",
    "       ('Blue', 'Steele', 'blue@gmail.com'),\n",
    "       ('Bette', 'Davis', 'bette@aol.com');\n",
    "       \n",
    "INSERT INTO orders (order_date, amount, customer_id)\n",
    "VALUES ('2016/02/10', 99.99, 1),\n",
    "       ('2017/11/11', 35.50, 1),\n",
    "       ('2014/12/12', 800.67, 2),\n",
    "       ('2015/01/03', 12.50, 2),\n",
    "       ('1999/04/11', 450.25, 5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Many to One (Reviewers, Series and Reviews)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Reviewers table\n",
    "CREATE TABLE reviewers (\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    first_name VARCHAR(100),\n",
    "    last_name VARCHAR(100)\n",
    ");\n",
    "\n",
    "#Series table\n",
    "CREATE TABLE series(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    title VARCHAR(100),\n",
    "    released_year YEAR(4),\n",
    "    genre VARCHAR(100)\n",
    ");\n",
    "\n",
    "#Reviews table\n",
    "CREATE TABLE reviews (\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    rating DECIMAL(2,1),\n",
    "    series_id INT,\n",
    "    reviewer_id INT,\n",
    "    FOREIGN KEY(series_id) REFERENCES series(id),\n",
    "    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)\n",
    ");\n",
    "\n",
    "\n",
    "\n",
    "#Inserting Data\n",
    "INSERT INTO series (title, released_year, genre) VALUES\n",
    "    ('Archer', 2009, 'Animation'),\n",
    "    ('Arrested Development', 2003, 'Comedy'),\n",
    "    (\"Bob's Burgers\", 2011, 'Animation'),\n",
    "    ('Bojack Horseman', 2014, 'Animation'),\n",
    "    (\"Breaking Bad\", 2008, 'Drama'),\n",
    "    ('Curb Your Enthusiasm', 2000, 'Comedy'),\n",
    "    (\"Fargo\", 2014, 'Drama'),\n",
    "    ('Freaks and Geeks', 1999, 'Comedy'),\n",
    "    ('General Hospital', 1963, 'Drama'),\n",
    "    ('Halt and Catch Fire', 2014, 'Drama'),\n",
    "    ('Malcolm In The Middle', 2000, 'Comedy'),\n",
    "    ('Pushing Daisies', 2007, 'Comedy'),\n",
    "    ('Seinfeld', 1989, 'Comedy'),\n",
    "    ('Stranger Things', 2016, 'Drama');\n",
    "\n",
    "\n",
    "INSERT INTO reviewers (first_name, last_name) VALUES\n",
    "    ('Thomas', 'Stoneman'),\n",
    "    ('Wyatt', 'Skaggs'),\n",
    "    ('Kimbra', 'Masters'),\n",
    "    ('Domingo', 'Cortes'),\n",
    "    ('Colt', 'Steele'),\n",
    "    ('Pinkie', 'Petit'),\n",
    "    ('Marlon', 'Crafford');\n",
    "    \n",
    "\n",
    "INSERT INTO reviews(series_id, reviewer_id, rating) VALUES\n",
    "    (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),\n",
    "    (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),\n",
    "    (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),\n",
    "    (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),\n",
    "    (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),\n",
    "    (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),\n",
    "    (7,2,9.1),(7,5,9.7),\n",
    "    (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),\n",
    "    (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),\n",
    "    (10,5,9.9),\n",
    "    (13,3,8.0),(13,4,7.2),\n",
    "    (14,2,8.5),(14,3,8.9),(14,4,8.9);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get Rating for every series table title(series and reviews table)\n",
    "SELECT \n",
    "    title, \n",
    "    rating \n",
    "FROM series\n",
    "JOIN reviews\n",
    "    ON series.id = reviews.series_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Average Rating(series and reviews)\n",
    "SELECT\n",
    "    title,\n",
    "    AVG(rating) as avg_rating\n",
    "FROM series\n",
    "JOIN reviews\n",
    "    ON series.id = reviews.series_id\n",
    "GROUP BY series.id\n",
    "ORDER BY avg_rating;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rating by every user(reviewers and reviews)\n",
    "SELECT\n",
    "    first_name,\n",
    "    last_name,\n",
    "    rating\n",
    "FROM reviewers\n",
    "INNER JOIN reviews\n",
    "    ON reviewers.id = reviews.reviewer_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unreviewed series\n",
    "SELECT title AS unreviewed_series\n",
    "FROM series\n",
    "LEFT JOIN reviews\n",
    "    ON series.id = reviews.series_id\n",
    "WHERE rating IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Genre Average Rating\n",
    "SELECT genre, \n",
    "       Round(Avg(rating), 2) AS avg_rating \n",
    "FROM   series \n",
    "       INNER JOIN reviews \n",
    "               ON series.id = reviews.series_id \n",
    "GROUP  BY genre; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reviewers Stats\n",
    "SELECT first_name, \n",
    "       last_name, \n",
    "       Count(rating)                               AS COUNT, \n",
    "       Ifnull(Min(rating), 0)                      AS MIN, \n",
    "       Ifnull(Max(rating), 0)                      AS MAX, \n",
    "       Round(Ifnull(Avg(rating), 0), 2)            AS AVG, \n",
    "       IF(Count(rating) > 0, 'ACTIVE', 'INACTIVE') AS STATUS \n",
    "FROM   reviewers \n",
    "       LEFT JOIN reviews \n",
    "              ON reviewers.id = reviews.reviewer_id \n",
    "GROUP  BY reviewers.id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Reviewers Stats with PowerUsers\n",
    "SELECT first_name, \n",
    "       last_name, \n",
    "       Count(rating)                    AS COUNT, \n",
    "       Ifnull(Min(rating), 0)           AS MIN, \n",
    "       Ifnull(Max(rating), 0)           AS MAX, \n",
    "       Round(Ifnull(Avg(rating), 0), 2) AS AVG, \n",
    "       CASE \n",
    "         WHEN Count(rating) >= 10 THEN 'POWER USER' \n",
    "         WHEN Count(rating) > 0 THEN 'ACTIVE' \n",
    "         ELSE 'INACTIVE' \n",
    "       end                              AS STATUS \n",
    "FROM   reviewers \n",
    "       LEFT JOIN reviews \n",
    "              ON reviewers.id = reviews.reviewer_id \n",
    "GROUP  BY reviewers.id; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join 3 tables\n",
    "SELECT \n",
    "    title,\n",
    "    rating,\n",
    "    CONCAT(first_name,' ', last_name) AS reviewer\n",
    "FROM reviewers\n",
    "INNER JOIN reviews \n",
    "    ON reviewers.id = reviews.reviewer_id\n",
    "INNER JOIN series\n",
    "    ON series.id = reviews.series_id\n",
    "ORDER BY title;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}