LAB 3

Today’s lab will focus on using Python as the host-laguage interface.
Software tools needed: DBeaver and Visual Studio Code.

Create the tables in DBeaver

As we learned in Lab 2, lets create the following tables in DBeaver:

Member Table

Type Table

Join queries using Python

Lets open VSCode, and create a python file called lab3.py and save this file on your desktop.

If you don’t have VS Code installed, follow this short youtube video for installation instructions.

We have to connect to the database that we created in DBeaver using Python:

     import sqlite3
        
     conn = sqlite3.connect("dbeaverdatabase.db")
     cur = conn.cursor()

Lets execute a CROSS JOIN and display the results using the following code:

     cur.execute("""
     SELECT *
     FROM Member m CROSS JOIN Type t;
     """)
        
     rows = cur.fetchall()
     for row in rows:
         print(row)

Close the connection
```
     conn.close()
```
Run the program with
```
     python lab3.py
```

Your complete lab3.py file should look like this:

      import sqlite3
      
      conn = sqlite3.connect("dbeaverdatabase.db")
      cur = conn.cursor()
      
      cur.execute("""
      SELECT *
      FROM Member m CROSS JOIN Type t;
      """)
      
      rows = cur.fetchall()
      for row in rows:
          print(row)
      
      conn.close()

It will display

CROSS JOIN

INNER JOIN

Now it is your turn to try an INNER JOIN. Your result should look like the following:

INNER JOIN

OUTER JOINS

For outer joins, the idea is similar to the inner join.

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Extending Join Queries

To reproduce the examples in the book, let’s create the three tables shown on page 39.

    DROP TABLE IF EXISTS Entry;
    DROP TABLE IF EXISTS Tournament;
    DROP TABLE IF EXISTS Member;

    CREATE TABLE Member (
    MemberID INTEGER PRIMARY KEY,
    LastName  TEXT,
    FirstName TEXT
    );

    INSERT INTO Member (MemberID, LastName, FirstName) VALUES
    (118, 'McKenzie', 'Melissa'),
    (138, 'Stone', 'Michael'),
    (153, 'Nolan', 'Brenda'),
    (176, 'Branch', 'Helen'),
    (178, 'Beck', 'Sarah'),
    (228, 'Burton', 'Sandra'),
    (235, 'Cooper', 'William'),
    (239, 'Spence', 'Thomas'),
    (258, 'Olson', 'Barbara'),
    (286, 'Pollard', 'Robert'),
    (290, 'Sexton', 'Thomas'),
    (323, 'Wilcox', 'Daniel'),
    (331, 'Schmidt', 'Thomas'),
    (332, 'Bridges', 'Deborah'),
    (339, 'Young', 'Betty'),
    (414, 'Gilmore', 'Jane'),
    (415, 'Taylor', 'William'),
    (461, 'Reed', 'Robert'),
    (469, 'Willis', 'Carolyn'),
    (487, 'Kent', 'Susan');

    CREATE TABLE Tournament (
    TourID   INTEGER PRIMARY KEY,
    TourName TEXT
    );

    INSERT INTO Tournament (TourID, TourName) VALUES
    (24, 'Leeston'),
    (25, 'Kaiapoi'),
    (36, 'WestCoast'),
    (38, 'Canterbury'),
    (40, 'Otago');

    CREATE TABLE Entry (
    MemberID INTEGER,
    TourID   INTEGER,
    Year     INTEGER
    );

    INSERT INTO Entry (MemberID, TourID, Year) VALUES
    (118, 24, 2014),
    (228, 24, 2015),
    (228, 25, 2015),
    (228, 36, 2015),
    (235, 38, 2013),
    (235, 38, 2015),
    (235, 40, 2014),
    (235, 40, 2015),
    (239, 25, 2015),
    (239, 40, 2013),
    (258, 24, 2014),
    (258, 38, 2014),
    (286, 24, 2013),
    (286, 24, 2014),
    (286, 24, 2015),
    (415, 24, 2015),
    (415, 25, 2013),
    (415, 36, 2014),
    (415, 36, 2015),
    (415, 38, 2013),
    (415, 38, 2015),
    (415, 40, 2013),
    (415, 40, 2014),
    (415, 40, 2015);

We also want to return the names of everyone who entered the Leeston tournament in 2014. To do this, we will follow the process approach, which leads us to the outcome approach.

    import sqlite3

    conn = sqlite3.connect("dbeaverdatabase.db")
    cur = conn.cursor()

    cur.execute("""
    SELECT LastName, FirstName
    FROM (Member m INNER JOIN Entry e ON m.MemberID = e.MemberID)
    INNER JOIN Tournament t ON e.TourID = t.TourID
    WHERE TourName = 'Leeston'
    AND Year = 2014;
    """)

    rows = cur.fetchall()
    for row in rows:
        print(row)

    conn.close()

Process Approach

We first join the Member table to the Entry table and the result to the Tournament table.

process approach

Outcome Approach

This is the final outcome.
Our goal was simply to retrieve the names that satisfy the criteria specified in the SQL query.

outcome approach