def fetch_sname_product_cost_for_cost_gte_47():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.join(table_name, "pid", "Products", "pid")
table_name_2 = relAlg.select(table_name_1, "cost", ">=", "47")
relAlg.project(table_name_2, ["sname", "pname", "cost"])
return
# select_supplier_name_s23_withBTree()
# select_name_s23()
# cost_of_p20_supplied_by_kiddie()
# find_addresses_of_suppliers_who_supplied_p15()
# fetch_sname_product_cost_for_cost_gte_47()
def query_e():
tmp_result = select(SUPPLY_FILE, "cost", ">=", 47.00)
tmp_result = join(tmp_result, "pid", PRODUCTS_FILE, "pid")
tmp_result = join(tmp_result, "sid", SUPPLIERS_FILE, "sid")
project(tmp_result, ["sname", "pname", "cost"])
def query_d():
tmp_result = select(SUPPLIERS_FILE, "sname", "=", "Kiddie")
tmp_result = join(tmp_result, "sid", SUPPLY_FILE, "sid")
tmp_result = select(tmp_result, "pid", "=", "p20")
project(tmp_result, ["cost"])
def query_c():
tmp_result = select(SUPPLY_FILE, "pid", "=", "p15")
tmp_result = join(SUPPLIERS_FILE, "sid", tmp_result, "sid")
project(tmp_result, ["address"])
aSel = relAlg.select("Suppliers", "sid", "=", "s23")
aProj = relAlg.project(aSel, ["sname"])
display.displayTable(aProj, "queryResult.txt")
queryHelpers.writeToQueryFile("\n")
# question b
remove.removeTree("Suppliers", "sid")
bStr = "Query b) Find the name for the supplier 's23' without a B+ tree:\n"
queryHelpers.writeToQueryFile(bStr)
aSel2 = relAlg.select("Suppliers", "sid", "=", "s23")
aProj2 = relAlg.project(aSel, ["sname"])
display.displayTable(aProj2, "queryResult.txt")
queryHelpers.writeToQueryFile("\n")
# there are a few common queries we will perform here
suppliersJoinSupply = relAlg.join("Suppliers", "sid", "Supply", "sid")
joinAll = relAlg.join(suppliersJoinSupply, "pid", "Products", "pid")
# question c: Find the address of the suppliers who supplied ‘p15’
cStr = "Query c) Find the address of the suppliers who supplied 'p15':\n"
queryHelpers.writeToQueryFile(cStr)
cProj = relAlg.project(suppliersJoinSupply, ["address"])
display.displayTable(cProj, "queryResult.txt")
queryHelpers.writeToQueryFile("\n")
# question d What is the cost of ‘p20’ supplied by ‘Kiddie’?
dStr = "Query d) What is the cost of 'p20' supplied by 'Kiddie'?:\n"
queryHelpers.writeToQueryFile(dStr)
dSelPart = relAlg.select(suppliersJoinSupply, "pid", "=", "p20")
dSelName = relAlg.select(dSelPart, "sname", "=", "Kiddie")
dCost = relAlg.project(dSelName, ["cost"])
def cost_of_p20_supplied_by_kiddie():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.select(table_name, "sname", "=", "Kiddie")
table_name_2 = relAlg.select(table_name_1, "pid", "=", "p20")
relAlg.project(table_name_2, ["cost"])
return
def find_addresses_of_suppliers_who_supplied_p15():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.select(table_name, "pid", "=", "p15")
relAlg.project(table_name_1, ["address"])
return