9 - Introduction to Relational Databases in SQL.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1 - Your first database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Query information_schema with SELECT\n",
    "\n",
    "# 1\n",
    "# -- Query the right table in information_schema\n",
    "SELECT table_name \n",
    "FROM information_schema.tables\n",
    "-- Specify the correct table_schema value\n",
    "WHERE table_schema = 'public';\n",
    "\n",
    "# 2\n",
    "# -- Query the right table in information_schema to get columns\n",
    "SELECT column_name, data_type \n",
    "FROM information_schema.columns \n",
    "WHERE table_name = 'university_professors' AND table_schema = 'public';\n",
    "\n",
    "# 3\n",
    "# -- Query the first five rows of our table\n",
    "SELECT * \n",
    "FROM university_professors \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## CREATE your first few TABLEs\n",
    "\n",
    "# 1\n",
    "# -- Create a table for the professors entity type\n",
    "CREATE TABLE professors (\n",
    " firstname text,\n",
    " lastname text\n",
    ");\n",
    "\n",
    "-- Print the contents of this table\n",
    "SELECT * \n",
    "FROM professors\n",
    "\n",
    "# 2\n",
    "# -- Create a table for the universities entity type\n",
    "CREATE TABLE universities (\n",
    "        university_shortname text,\n",
    "        university text,\n",
    "        university_city text\n",
    ");\n",
    "\n",
    "# -- Print the contents of this table\n",
    "SELECT * \n",
    "FROM universities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ADD a COLUMN with ALTER TABLE\n",
    "\n",
    "# -- Add the university_shortname column\n",
    "ALTER TABLE professors\n",
    "ADD COLUMN university_shortname text;\n",
    "\n",
    "# -- Print the contents of this table\n",
    "SELECT * \n",
    "FROM professors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## RENAME and DROP COLUMNS in affiliations\n",
    "\n",
    "## -- Rename the organisation column\n",
    "ALTER TABLE affiliations\n",
    "RENAME COLUMN organisation TO organization;\n",
    "\n",
    "## -- Delete the university_shortname column\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN university_shortname;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Migrate data with INSERT INTO SELECT DISTINCT\n",
    "\n",
    "# 1\n",
    "## -- Insert unique professors into the new table\n",
    "INSERT INTO professors \n",
    "SELECT DISTINCT firstname, lastname, university_shortname \n",
    "FROM university_professors;\n",
    "\n",
    "## -- Doublecheck the contents of professors\n",
    "SELECT * \n",
    "FROM professors;\n",
    "\n",
    "\n",
    "# 2\n",
    "## -- Insert unique affiliations into the new table\n",
    "INSERT INTO affiliations \n",
    "SELECT DISTINCT firstname, lastname, function, organization \n",
    "FROM university_professors;\n",
    "\n",
    "## -- Doublecheck the contents of affiliations\n",
    "SELECT * \n",
    "FROM affiliations;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Delete tables with DROP TABLE\n",
    "\n",
    "## -- Delete the university_professors table\n",
    "DROP TABLE university_professors;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "2 - Enforce data consistency with attribute constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Conforming with data types\n",
    "\n",
    "## -- Let's add a record to the table\n",
    "INSERT INTO transactions (transaction_date, amount, fee) \n",
    "VALUES ('2018-09-24', 5454, '30');\n",
    "\n",
    "## -- Doublecheck the contents\n",
    "SELECT *\n",
    "FROM transactions;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Type CASTs\n",
    "\n",
    "# -- Calculate the net amount as amount + fee\n",
    "SELECT transaction_date, amount + CAST(fee as INTEGER) AS net_amount \n",
    "FROM transactions;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Change types with ALTER COLUMN\n",
    "\n",
    "# -- Select the university_shortname column\n",
    "SELECT DISTINCT(university_shortname) \n",
    "FROM professors;\n",
    "\n",
    "# -- Specify the correct fixed-length character type\n",
    "ALTER TABLE professors\n",
    "ALTER COLUMN university_shortname\n",
    "TYPE char(3);\n",
    "\n",
    "# -- Change the type of firstname\n",
    "ALTER TABLE professors\n",
    "ALTER COLUMN firstname\n",
    "TYPE varchar(64);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Convert types USING a function\n",
    "\n",
    "# -- Convert the values in firstname to a max. of 16 characters\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN firstname \n",
    "TYPE varchar(16)\n",
    "USING SUBSTRING(firstname FROM 1 FOR 16);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Disallow NULL values with SET NOT NULL\n",
    "\n",
    "# -- Disallow NULL values in firstname\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN firstname SET NOT NULL;\n",
    "\n",
    "# -- Disallow NULL values in lastname\n",
    "ALTER TABLE professors\n",
    "ALTER COLUMN lastname SET NOT NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## What happens if you try to enter NULLs?\n",
    "\n",
    "INSERT INTO professors ( firstname, lastname, university_shortname)\n",
    "VALUES (NULL, \"Miller\", \"ETH\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Make your columns UNIQUE with ADD CONSTRAINT\n",
    "\n",
    "# -- Make universities.university_shortname unique\n",
    "ALTER TABLE universities\n",
    "ADD CONSTRAINT university_shortname_unq UNIQUE(university_shortname);\n",
    "\n",
    "\n",
    "-- Make organizations.organization unique\n",
    "ALTER TABLE organizations\n",
    "ADD CONSTRAINT organization_unq UNIQUE(organization)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "3 - Uniquely identify records with key constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Get to know SELECT COUNT DISTINCT\n",
    "\n",
    "# -- Count the number of rows in universities\n",
    "SELECT COUNT(*) \n",
    "FROM universities;\n",
    "\n",
    "\n",
    "# -- Count the number of distinct values in the university_city column\n",
    "SELECT COUNT(DISTINCT(university_city)) \n",
    "FROM universities;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Identify keys with SELECT COUNT DISTINCT\n",
    "\n",
    "# -- Try out different combinations\n",
    "SELECT COUNT(DISTINCT(firstname, lastname)) \n",
    "FROM professors;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ADD key CONSTRAINTs to the tables\n",
    "\n",
    "# -- Rename the organization column to id\n",
    "ALTER TABLE organizations\n",
    "RENAME COLUMN organization TO id;\n",
    "\n",
    "# -- Make id a primary key\n",
    "ALTER TABLE organizations\n",
    "ADD CONSTRAINT organization_pk PRIMARY KEY (id);\n",
    "__________________________________________________\n",
    "\n",
    "# -- Rename the university_shortname column to id\n",
    "ALTER TABLE universities\n",
    "RENAME COLUMN university_shortname TO id;\n",
    "\n",
    "# -- Make id a primary key\n",
    "ALTER TABLE universities\n",
    "ADD CONSTRAINT university_pk PRIMARY KEY (id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Add a SERIAL surrogate key\n",
    "\n",
    "# -- Add the new column to the table\n",
    "ALTER TABLE professors \n",
    "ADD COLUMN id serial;\n",
    "________________________________\n",
    "# -- Make id a primary key\n",
    "ALTER TABLE professors \n",
    "ADD CONSTRAINT professors_pkey PRIMARY KEY (id);\n",
    "____________________________________\n",
    "# -- Have a look at the first 10 rows of professors\n",
    "SELECT * FROM professors LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## CONCATenate columns to a surrogate key\n",
    "\n",
    "# -- Count the number of distinct rows with columns make, model\n",
    "SELECT COUNT(DISTINCT(make, model)) \n",
    "FROM cars;\n",
    "________________________\n",
    "# -- Add the id column\n",
    "ALTER TABLE cars\n",
    "ADD COLUMN id varchar(128);\n",
    "\n",
    "# -- Update id with make + model\n",
    "UPDATE cars\n",
    "SET id = CONCAT(make, model);\n",
    "\n",
    "# -- Make id a primary key\n",
    "ALTER TABLE cars\n",
    "ADD CONSTRAINT id_pk PRIMARY KEY(id);\n",
    "\n",
    "# -- Have a look at the table\n",
    "SELECT * FROM cars;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Test your knowledge before advancing\n",
    "\n",
    "# -- Create the table\n",
    "CREATE TABLE students (\n",
    "  last_name varchar(128) NOT NULL,\n",
    "  ssn integer PRIMARY KEY,\n",
    "  phone_no char(12)\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "4 - Introduction to Relational Databases in SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## REFERENCE a table with a FOREIGN KEY\n",
    "\n",
    "# -- Rename the university_shortname column\n",
    "ALTER TABLE professors\n",
    "RENAME COLUMN university_shortname TO university_id;\n",
    "\n",
    "# -- Add a foreign key on professors referencing universities\n",
    "ALTER TABLE professors \n",
    "ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES universities (id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Explore Foreign Key constraints\n",
    "\n",
    "# -- Try to insert a new professor\n",
    "INSERT INTO professors (firstname, lastname, university_id)\n",
    "VALUES ('Albert', 'Einstein', 'UZH');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## JOIN tables linked by a foreign key\n",
    "\n",
    "# -- Select all professors working for universities in the city of Zurich\n",
    "SELECT professors.lastname, universities.id, universities.university_city\n",
    "FROM professors\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id\n",
    "WHERE universities.university_city = 'Zurich';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Add foreign keys to the \"affiliations\" table\n",
    "\n",
    "# -- Add a professor_id column\n",
    "ALTER TABLE affiliations\n",
    "ADD COLUMN professor_id integer REFERENCES professors (id);\n",
    "\n",
    "# -- Rename the organization column to organization_id\n",
    "ALTER TABLE affiliations\n",
    "RENAME organization TO organization_id;\n",
    "\n",
    "# -- Add a foreign key on organization_id\n",
    "ALTER TABLE affiliations\n",
    "ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Populate the \"professor_id\" column\n",
    "\n",
    "# -- Have a look at the 10 first rows of affiliations\n",
    "SELECT * FROM affiliations LIMIT 10;\n",
    "\n",
    "# -- Set professor_id to professors.id where firstname, lastname correspond to rows in professors\n",
    "UPDATE affiliations\n",
    "SET professor_id = professors.id\n",
    "FROM professors\n",
    "WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;\n",
    "\n",
    "# -- Update professor_id to professors.id where firstname, lastname correspond to rows in professors\n",
    "UPDATE affiliations\n",
    "SET professor_id = professors.id\n",
    "FROM professors\n",
    "WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;\n",
    "\n",
    "# -- Have a look at the 10 first rows of affiliations again\n",
    "SELECT * FROM affiliations LIMIT 10;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Drop \"firstname\" and \"lastname\"\n",
    "\n",
    "# -- Drop the firstname column\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN firstname;\n",
    "\n",
    "# -- Drop the lastname column\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN lastname;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Referential integrity violations\n",
    "\n",
    "DELETE FROM univerties WHERE id = \"EPF\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Change the referential integrity behavior of a key\n",
    "\n",
    "# -- Identify the correct constraint name\n",
    "SELECT constraint_name, table_name, constraint_type\n",
    "FROM information_schema.table_constraints\n",
    "WHERE constraint_type = 'FOREIGN KEY';\n",
    "\n",
    "# -- Drop the right foreign key constraint\n",
    "ALTER TABLE affiliations\n",
    "DROP CONSTRAINT affiliations_organization_id_fkey;\n",
    "\n",
    "# -- Add a new foreign key constraint from affiliations to organizations which cascades deletion\n",
    "ALTER TABLE affiliations\n",
    "ADD CONSTRAINT affiliations_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;\n",
    "\n",
    "# -- Delete an organization \n",
    "DELETE FROM organizations \n",
    "WHERE id = 'CUREM';\n",
    "\n",
    "# -- Check that no more affiliations with this organization exist\n",
    "SELECT * FROM organizations\n",
    "WHERE id = 'CUREM';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Count affiliations per university\n",
    "\n",
    "# -- Count the total number of affiliations per university\n",
    "SELECT COUNT(*), professors.university_id \n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "GROUP BY professors.university_id \n",
    "ORDER BY count DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Join all the tables together\n",
    "\n",
    "# -- Filter the table and sort it\n",
    "SELECT COUNT(*), organizations.organization_sector, professors.id, universities.university_city\n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "JOIN organizations\n",
    "ON affiliations.organization_id = organizations.id\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id\n",
    "WHERE organizations.organization_sector = 'Media & communication'\n",
    "GROUP BY organizations.organization_sector, professors.id, universities.university_city\n",
    "ORDER BY count DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf-gpu-2.0",
   "language": "python",
   "name": "tf_gpu2"
  },
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}