Lecture 06¶

In [1]:
# Run this cell to set up imports
import numpy as np
import pandas as pd
In [2]:
%reload_ext sql
In [3]:
%sql postgresql://127.0.0.1:5432/postgres

Manager demo¶

Creating the manager relation

In [4]:
%sql DROP TABLE IF EXISTS Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
Out[4]:
In [5]:
%%sql
CREATE TABLE Manager (
    manager_name VARCHAR(20),
    age INTEGER DEFAULT 50,
    manager_id CHAR(4),
    PRIMARY KEY (manager_id),
    UNIQUE (manager_name)
);


/***********/
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
Out[5]:
manager_name age manager_id




Add a tuple (success)

In [6]:
%%sql
INSERT INTO Manager
    (age, manager_id)
VALUES
    (23, 'sd45')
;

/***********/
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
1 rows affected.
Out[6]:
manager_name age manager_id
None 23 sd45




Add a tuple (failure)

In [7]:
%%sql
INSERT INTO Manager
    (manager_name, age)
VALUES
    ('John Smith', 123);
    
/***********/
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres' 
RuntimeError: (psycopg2.errors.NotNullViolation) null value in column "manager_id" of relation "manager" violates not-null constraint
DETAIL:  Failing row contains (John Smith, 123, null).

[SQL: INSERT INTO Manager
    (manager_name, age)
VALUES
    ('John Smith', 123);]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
If you need help solving this issue, send us a message: https://ploomber.io/community




Change schema: add attributes

In [8]:
%%sql
ALTER TABLE Manager 
ADD address VARCHAR (20),
ADD income REAL DEFAULT 10000.0;

/***********/
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
Out[8]:
manager_name age manager_id address income
None 23 sd45 None 10000.0




Change schema: drop attributes

In [9]:
%%sql
ALTER TABLE Manager
DROP address,
DROP income;

/***********/
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
Out[9]:
manager_name age manager_id
None 23 sd45




Delete the relation entirely

In [10]:
%%sql
DROP TABLE Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres'
Out[10]:
In [11]:
%%sql
SELECT * FROM Manager;
Running query in 'postgresql://127.0.0.1:5432/postgres' 
RuntimeError: (psycopg2.errors.UndefinedTable) relation "manager" does not exist
LINE 1: SELECT * FROM Manager;
                      ^

[SQL: SELECT * FROM Manager;]
(Background on this error at: https://sqlalche.me/e/20/f405)
If you need help solving this issue, send us a message: https://ploomber.io/community
In [12]:
%%sql
DROP TABLE IF EXISTS Stops;
CREATE TABLE Stops(
  stopID BIGSERIAL,
  personID INTEGER,
  stopTime TIMESTAMP, 
  race VARCHAR(10),
  location VARCHAR(20), 
  age INTEGER, 
  PRIMARY KEY (stopID),
  UNIQUE (personID, stopTime)
);
Running query in 'postgresql://127.0.0.1:5432/postgres'
Out[12]:
In [13]:
%%sql
INSERT INTO Stops
    (personID, stopTime)
VALUES
    (NULL, NULL)
;
SELECT * FROM Stops;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
1 rows affected.
Out[13]:
stopid personid stoptime race location age
1 None None None None None

Actor/Cast_info¶

In [14]:
%reload_ext sql
In [15]:
%sql postgresql://127.0.0.1:5432/postgres
In [27]:
%%sql
DROP TABLE IF EXISTS Actor CASCADE;
DROP TABLE IF EXISTS Movie CASCADE;
DROP TABLE IF EXISTS Cast_info;

CREATE TABLE Actor (
  id INTEGER,
  name TEXT,
  PRIMARY KEY(id)
); 

CREATE TABLE Movie (
  id INTEGER,
  title TEXT,
  PRIMARY KEY(id)
); 

CREATE TABLE Cast_info (
  person_id INTEGER,
  movie_id INTEGER, 
  FOREIGN KEY (person_id)
    REFERENCES Actor (id)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (movie_id)
    REFERENCES Movie (id)
    ON DELETE SET NULL);

INSERT INTO Actor VALUES
    (1, 'Tom Hanks'),
    (3, 'Michelle Yeoh')
;

INSERT INTO Movie VALUES
    (23, 'Forrest Gump'),
    (45, 'Tomorrow Never Dies')
;

INSERT INTO Cast_info VALUES
    (1, 23),
    (3, 45)
;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
2 rows affected.
2 rows affected.
Out[27]:
In [17]:
%%sql
SELECT * FROM Cast_info;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
Out[17]:
person_id movie_id
1 23
3 45

A. What if we… Delete a tuple from Actor corresponding to id = 1?

In [18]:
%%sql
DELETE FROM Actor
WHERE id = 1;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
Out[18]:
In [19]:
%%sql
SELECT * FROM Cast_info;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
Out[19]:
person_id movie_id
3 45
None 23

(note: reset table layout for each choice)

B. Change a tuple in Actor from id = 1 to id = 2?

In [21]:
%%sql
UPDATE Actor
SET id = 2
WHERE id = 1;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
Out[21]:
In [22]:
%%sql
SELECT * FROM Actor;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
Out[22]:
id name
3 Michelle Yeoh
2 Tom Hanks
In [23]:
%%sql
SELECT * FROM Cast_info;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
Out[23]:
person_id movie_id
3 45
2 23

C. Delete a tuple from Movie corresponding to id = 23?

In [25]:
%%sql
DELETE FROM Movie
WHERE id = 23;
Running query in 'postgresql://127.0.0.1:5432/postgres'
1 rows affected.
Out[25]:
In [26]:
%%sql
SELECT * FROM Cast_info;
Running query in 'postgresql://127.0.0.1:5432/postgres'
2 rows affected.
Out[26]:
person_id movie_id
3 45
1 None

D. Change a tuple in Movie from id = 23 to id = 24?

In [28]:
%%sql
UPDATE Movie
SET id = 24
WHERE id = 23;
Running query in 'postgresql://127.0.0.1:5432/postgres' 
RuntimeError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "movie" violates foreign key constraint "cast_info_movie_id_fkey" on table "cast_info"
DETAIL:  Key (id)=(23) is still referenced from table "cast_info".

[SQL: UPDATE Movie
SET id = 24
WHERE id = 23;]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
If you need help solving this issue, send us a message: https://ploomber.io/community

E. Insert a tuple into Cast_info that adds a new person_id not found in Actor?

In [29]:
%%sql
INSERT INTO Cast_info VALUES
    (467, 23)
;
Running query in 'postgresql://127.0.0.1:5432/postgres' 
RuntimeError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "cast_info" violates foreign key constraint "cast_info_person_id_fkey"
DETAIL:  Key (person_id)=(467) is not present in table "actor".

[SQL: INSERT INTO Cast_info VALUES
    (467, 23)
;]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
If you need help solving this issue, send us a message: https://ploomber.io/community
In [ ]: