Tutorial: Databases

Inner Joins

Recall that with an inner join, it creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. We saw this with our earlier example of customerID's where it created a new table of all the related values from two separate tables.

In this tutorial we will be finding a inner joins between two tables. We are also creating and inserting data from scratch. In this tutorial, there will be a total of 5 separate programs that all lead into creating our two types of joins: creating our tables, adding data into table 1, adding data into table 2, and creating inner join. For the table creation, we will be referencing methods on a tutorial on how to create database tables. For the data insertion, we will be referring to methods on a tutorial on how to insert data. Regardless, for each tutorial, we will want to connect our Database into Quorum Studio before we proceed into any work. In the first part of this tutorial we will be creating our tables.

We will be wanting to create two tables: one table called "amazon_wishlist" and the other table called "customer_wishlist" and we will follow a very similar pattern to our "Orders" and "Customers" example. We will first be creating two CreateTable objects: newTable and newTable1. We will first start to build our first table, "amazon_wishlist" using the actions SetTable(text table name), AddIntegerColum(text columnName), and AddTextColumn(text columnName, int charLimit). The columns and the data types will be as follows:

Once we have created all our columns, we will now want to pass in our newTable object into an action called CreateTable(CreateTable objectName) using our database object.

CreateTable newTable
    newTable:SetTable("amazon_wishlist")
    newTable:AddIntegerColumn("orderID")
    newTable:AddIntegerColumn("customerID")
    newTable:AddTextColumn("orderItem", 500)
    newTable:AddIntegerColumn("orderCost")
    newTable:AddIntegerColumn("orderQuantity")
    database:CreateTable(newTable)

We will want to follow this same process for the other table, "customer_list" where the columns and data types will be as follows:

The full code will be below:

use Libraries.Data.Database.Database
    use Libraries.Data.Database.CreateTable

    Database database
    database:Connect("localhost", "myfirstdatabase", "root", "password")

    // create first table
    CreateTable newTable
    newTable:SetTable("amazon_wishlist")
    newTable:AddIntegerColumn("orderID")
    newTable:AddIntegerColumn("customerID")
    newTable:AddTextColumn("orderItem", 500)
    newTable:AddIntegerColumn("orderCost")
    newTable:AddIntegerColumn("orderQuantity")
    database:CreateTable(newTable)

    CreateTable newTable1
    newTable1:SetTable("customer_list")
   newTable1:AddIntegerColumn("customerNum")
    newTable1:AddIntegerColumn("customerID")
    newTable1:AddTextColumn("customerName", 500)
    newTable1:AddTextColumn("Country", 500)
    newTable1:AddTextColumn("City", 500)
    newTable1:AddIntegerColumn("Zipcode')
    database:CreateTable(newTable1)

Next, we will want to enter data inside our tables. We will have two separate programs to insert data into our "customer_list" table and then our "amazon_wishlist" table. Let's go ahead and define our Insert object named "insert." We will be using this object to access the actions SetTable(), ToText(), Empty() and Add().

To insert data, we will need to make sure we select our table using SetTable(text tableName); let's do this with "amazon_wishlist" first and insert this string as our parameter. Then we will want to use the action Add(text columnName, dataType dataValue) with our insert object to enter in appropriate data on the columns.

Recall that our columns are: orderID, customerID, orderItem, orderCost, and orderQuantity. For example, say that we want to add an entry for "orderName," we will need to call the action Add() with our Insert object and insert "orderName" in the first parameter and an item we see off of Amazon on inside the second parameter. Below is a code snippet of adding one complete row onto our table:

// add first entry
  insert:Add("orderID", 1)
  insert:Add("customerID", 101)
  insert:Add("orderItem", "Laundry Rack")
  insert:Add("orderCost", 22)
  insert:Add("orderQuantity", 2)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()

It is important that once we add one record, we must immediately call the action Empty() which will clear out our object so we can add multiple records inside of one program. Optionally, to confirm our insert, we can also call the action ToText() that outputs the SQL notation of insertion. Now that we know how to insert a row, we can populate the table for "amazon_wishlist" as well as "customer_list." Below are the two sets of code for inserting both tables, amazon_wishlist and customer_list.

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

  Insert insert
  insert:SetTable("amazon_wishlist")
 // keep a running sum on how many rows we add 
 integer rowsAffected = 0
   
  // add first entry
  insert:Add("orderID", 1)
  insert:Add("customerID", 101)
  insert:Add("orderItem", "Laundry Rack")
  insert:Add("orderCost", 22)
  insert:Add("orderQuantity", 2)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()

/* clears out info from previous insert
   we need to call this because it only inserts one
   at a time, we can insert multiple queries at 
   the same time with this method
*/
  insert:Empty()
   insert:Add("orderID", 2)
  insert:Add("customerID", 102)
  insert:Add("orderItem", "Blackout Curtain")
  insert:Add("orderCost", 11)
  insert:Add("orderQuantity", 1)
  database:Insert(insert)
  output insert:ToText()

   rowsAffected = rowsAffected + 1

  insert:Empty()
   insert:Add("orderID", 3)
  insert:Add("customerID", 103)
  insert:Add("orderItem", "Shadow Box Frame")
  insert:Add("orderCost", 18)
  insert:Add("orderQuantity", 5)
  database:Insert(insert)
output insert:ToText()

  rowsAffected = rowsAffected + 1

  insert:Empty()
 insert:Add("orderID", 4)
  insert:Add("customerID", 104)
  insert:Add("orderItem", "Decorative Throw Pillows")
  insert:Add("orderCost", 32)
  insert:Add("orderQuantity", 2)

  database:Insert(insert)
  rowsAffected = rowsAffected + 1
output insert:ToText()

insert:Empty()
  insert:Add("orderID", 5)
  insert:Add("customerID", 105)
  insert:Add("orderItem", "Small Table Lamp")
  insert:Add("orderCost", 12)
  insert:Add("orderQuantity", 3)

  database:Insert(insert)
  rowsAffected = rowsAffected + 1
output insert:ToText()


insert:Empty()
  insert:Add("orderID", 6)
  insert:Add("customerID", 106)
  insert:Add("orderItem", "Dove Beauty Bar")
  insert:Add("orderCost", 23)
  insert:Add("orderQuantity", 10)

  database:Insert(insert)
  rowsAffected = rowsAffected + 1
output insert:ToText()


output "Inserted " + rowsAffected + " row(s)"

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert  

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

  Insert insert
  insert:SetTable("customer_list")
 // keep a running sum on how many rows we add 
 integer rowsAffected = 0
   
  // add first entry
  insert:Add("customerNum", 1)
  insert:Add("customerID", 99)
  insert:Add("customerName", "Bob Ross")
  insert:Add("Country", "US")
  insert:Add("City", "Orlando")
  insert:Add("Zipcode", 32801)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

insert:Add("customerNum", 2)
  insert:Add("customerID", 100)
  insert:Add("customerName", "Elmo")
  insert:Add("Country", "US")
  insert:Add("City", "Sesame Street")
  insert:Add("Zipcode", 10128)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

insert:Add("customerNum", 3)
  insert:Add("customerID", 101)
  insert:Add("customerName", "Harry Styles")
  insert:Add("Country", "UK")
  insert:Add("City", "London")
  insert:Add("Zipcode", 5208)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

insert:Add("customerNum", 4)
  insert:Add("customerID", 102)
  insert:Add("customerName", "Anya Forger")
  insert:Add("Country", "Germany")
  insert:Add("City", "Berlin")
  insert:Add("Zipcode", 10115)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

insert:Add("customerNum", 5)
  insert:Add("customerID", 103)
  insert:Add("customerName", "Steven Universe")
  insert:Add("Country", "US")
  insert:Add("City", "Beach City")
  insert:Add("Zipcode", 44608)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

insert:Add("customerNum", 6)
  insert:Add("customerID", 104)
  insert:Add("customerName", "Chihiro Ogino")
  insert:Add("Country", "Japan")
  insert:Add("City", "Tokyo")
  insert:Add("Zipcode", 1000004)
 // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
    output insert:ToText()
insert:Empty()

  output "Inserted " + rowsAffected + " row(s)"

Lastly, we are going to create an inner join of our two tables. To do this, we will be calling the action, FindInnerJoin(text tableName, text joinedTable, text jointCondition) and then saving our result into a QueryResult object. For the first two parameters, we will want to insert our two table names, "amazon_wishlist" and "customer_list" but for our third parameter, we will need to find a corresponding column that will essentially map the two tables together.

As we made our tables in the beginning, notice that both tables contain the column "customerID"; we will be using this column in our jointCondition. Our condition is formatted to be table1:column = table2:column. Therefore, our actual joint condition will be: "amazon_wishlist:customerID = customer_list:customerID" inside the 3rd parameter. Let's also go ahead and print the results of our QueryResult item inside a repeat while loop.

QueryResult result = database:FindInnerJoin("amazon_wishlist", "customer_list", "amazon_wishlist:customerID = customer_list:customerID")

Congratulations! We have created an inner join! We can view the entire code below.

use Libraries.Data.Database.Database
use Libraries.Data.Database.CreateTable
use Libraries.Data.Database.QueryResult
 
   Database database
    database:Connect("localhost", "myfirstdatabase", "root", "password")

    QueryResult result = database:FindInnerJoin("amazon_wishlist", "customer_list", "amazon_wishlist:customerID = customer_list:customerID")
    repeat while result:HasNext()
        result:Next()
        output "CustomerName: " + result:GetText("customerName")
        output "orderItem: " + result:GetText("orderItem")

        output " "
    end

Next Tutorial

In the next tutorial, we will discuss Views, which describes how to create a view from a database table.