import pymysql
pymysql.install_as_MySQLdb()
%load_ext sql
%config SqlMagic.autocommit=True
%sql mysql://root:simple@127.0.0.1

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Joins¶

%%sql
CREATE SCHEMA depend

 * mysql://root:***@127.0.0.1
1 rows affected.

[]

%%sql
use depend

 * mysql://root:***@127.0.0.1
0 rows affected.

[]

%%sql

CREATE TABLE person (
    person_id int,
    full_name varchar(60),
    PRIMARY KEY (person_id)
);

 * mysql://root:***@127.0.0.1
(pymysql.err.OperationalError) (1050, "Table 'person' already exists")
[SQL: CREATE TABLE person (
    person_id int,
    full_name varchar(60),
    PRIMARY KEY (person_id)
);]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

%%sql

CREATE TABLE dependent (
    person_id int,
    provider_id int,
    PRIMARY KEY (person_id),
    FOREIGN KEY (person_id) REFERENCES person(person_id), 
    FOREIGN KEY (provider_id) REFERENCES person(person_id))

 * mysql://root:***@127.0.0.1
0 rows affected.

[]

%%sql

INSERT INTO person (person_id, full_name) VALUES 
    (1, "Bob"),
    (2, "Anne"),
    (3, "Dave"),
    (4, "Carol")

 * mysql://root:***@127.0.0.1
4 rows affected.

[]

%%sql 
INSERT INTO dependent (person_id, provider_id) VALUES 
    (2, 1),
    (3, 1),
    (4, 2),
    (1, 4)

 * mysql://root:***@127.0.0.1
4 rows affected.

[]

%%sql
-- cross join
SELECT * FROM person as p1 JOIN person as p2

%%sql
-- inner product: cartesian product restricted by a condition on joining attributes
SELECT * FROM person join dependent ON person.person_id = dependent.person_id

%%sql
-- equijoin on a common attribute
SELECT * FROM person JOIN dependent USING (person_id)

%%sql
-- equijoin on a common attribute
SELECT * FROM person NATURAL JOIN dependent

%%sql
-- joining three tables
SELECT p1.*, dependent.*, provider.full_name as provider_name 
   FROM person as p1 JOIN dependent JOIN person as provider ON p1.person_id = dependent.person_id AND provider.person_id = dependent.provider_id

Queries

Joins

Queries

Homework 7: Aggregations