EDAF75 - notebook for lab 2

EDAF75 - lab 2: Experimenting with your own database

To begin, we need to enable SQL in our notebook: 

%load_ext sql

If we use jupysql (instead of ipython-sql) we get a default limit of 10 rows for any query – to turn this limit off we can write: 

%config SqlMagic.displaylimit = None

We can now load the lab database: 

%sql sqlite:///movies.sqlite

We want to make sure that SQLite3 really checks our foreign key constraints – to do that, we run: 

%%sql
PRAGMA foreign_keys=ON;

Now write SQL code for the following tasks:

  • Show the names of all movies. 

    %%sql

  • Show the performance dates for one of the movies. 

    %%sql

  • Show all data concerning performances at a given theatere on a given date. 

    %%sql

  • List all customers 

    %%sql

  • List all tickets 

    %%sql

  • Create a new ticket to some performance (i.e., insert a new row in your table of tickets). 

    %%sql

    In newer versions of SQLite (since version 3.35, released in March 2021), and in [PostgreSQL](https://www.postgresql.org/docs/current/sql-insert.html), we can get any value generated during an insert using the INSERT...-RETURNING statement: 

    INSERT
INTO       students
VALUES     ('Amy', 3.9, 1200)
RETURNING  s_id

    which would return the generated s_id for the new student.

    If your SQLite version is older than 3.35, and you can't upgrade, you can instead use the following idea: each row in a SQLite3 table has a rowid attribute, it is a unique integer which essentially tells in which order the rows were inserted, and it's not displayed in queries unless we ask for it. SQLite3 also have a function, last_insert_rowid(), which returns the rowid of the last inserted row of a table, so we can see the s_id of the most recently inserted student with the following query: 

    SELECT s_id
FROM   students
WHERE  rowid = last_insert_rowid();

Now, check what ticket number we got for the ticket we created above (it should be the same as the ticket id, which should be a randomblob): 

%%sql

  • Try to insert two movie theaters with the same name (this should fail). 

    %%sql

  • Try to insert a performance where the theater doesn’t exist in the database (this should fail). 

    %%sql

  • Create a ticket where either the user or the performance doesn’t exist (this should fail). 

    %%sql