Lecture 06¶

In [1]:
# Run this cell to set up imports
import numpy as np
import pandas as pd
In [2]:
%reload_ext sql

There's a new jupysql version available (0.10.1), you're running 0.10.0. To upgrade: pip install jupysql --upgrade
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 [ ]:
%%sql
INSERT INTO Manager
    (age, manager_id)
VALUES
    (23, 'sd45')
;

/***********/
SELECT * FROM Manager;




Add a tuple (failure)

In [ ]:
%%sql
INSERT INTO Manager
    (manager_name, age)
VALUES
    ('John Smith', 123);
    
/***********/
SELECT * FROM Manager;




Change schema: add attributes

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

/***********/
SELECT * FROM Manager;




Change schema: drop attributes

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

/***********/
SELECT * FROM Manager;




Delete the relation entirely

In [ ]:
%%sql
DROP TABLE Manager;
In [ ]:
%sql
/***********/
SELECT * FROM Manager;
In [ ]:
%%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)
);
In [ ]:
%%sql
INSERT INTO Stops
    (personID, stopTime)
VALUES
    (NULL, NULL)
;
SELECT * FROM Stops;

Actor/Cast_info¶

In [ ]:
%reload_ext sql
In [ ]:
%sql postgresql://127.0.0.1:5432/postgres
In [ ]:
%%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)
;
In [ ]:
%%sql
SELECT * FROM Cast_info;

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

In [ ]:
%%sql
DELETE FROM Actor
WHERE id = 1;
In [ ]:
%%sql
SELECT * FROM Cast_info;

(note: reset table layout for each choice)

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

In [ ]:
%%sql
UPDATE Actor
SET id = 2
WHERE id = 1;
In [ ]:
%%sql
SELECT * FROM Actor;
In [ ]:
%%sql
SELECT * FROM Cast_info;

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

In [ ]:
%%sql
DELETE FROM Movie
WHERE id = 23;
In [ ]:
%%sql
SELECT * FROM Cast_info;

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

In [ ]:
%%sql
UPDATE Movie
SET id = 24
WHERE id = 23;

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

In [ ]:
%%sql
INSERT INTO Cast_info VALUES
    (467, 23)
;
In [ ]: