from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_demo PWORD orcl_demo CONTYPE local; # !!! need to run tests/fillTestTables.py before running this output=SELECT TIME TIME_1, p.PRICE, p.VETID AS VETID_P, p.PETID FROM VISITS AS p WHERE PRICE = 80; #works neatPrintTable(output); output=SELECT * FROM VISITS; #works neatPrintTable(output); output=SELECT * FROM VISITS, PETS, VETS; #works neatPrintTable(output); output=SELECT TIME, PRICE, VETID, PETID FROM VISITS WHERE PRICE = 80; #works neatPrintTable(output); output=SELECT VISITS.TIME, VISITS.PRICE, VISITS.VETID, VISITS.PETID FROM VISITS WHERE VISITS.PRICE = 80; #works neatPrintTable(output); output=SELECT TIME, PRICE, PETS.NAME, NAME FROM VISITS JOIN PETS ON VISITS.PETID = PETS.PETID JOIN VETS ON VISITS.VETID = VETS.VETID; #works neatPrintTable(output); output=SELECT VISITS.TIME, VISITS.PRICE, PETS.NAME, VETS.NAME FROM VISITS JOIN PETS ON VISITS.PETID = PETS.PETID JOIN VETS ON VISITS.VETID = VETS.VETID; #works neatPrintTable(output); output=SELECT PETS.NAME, PETS.BIRTH_YEAR, ANIMALTYPES.SPECIES FROM PETS JOIN ANIMALTYPES ON PETS.TYPEID = ANIMALTYPES.TYPEID; neatPrintTable(output); output=SELECT VISITS.TIME, VISITS.PRICE, VETS.NAME FROM VISITS JOIN VETS ON VISITS.VETID = VETS.VETID WHERE VISITS.PRICE > 75 AND VISITS.TIME < 24; neatPrintTable(output);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # working - multi-table subquery projecting multiple columns print "\n\n\n\n********Test2 - SELECT VETID, NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); " test2=SELECT VETID, NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); neatPrintTable(test2);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # working - multi-table subquery with two criteria in the subquery print "\n\n\n\n********Test4 - SELECT * FROM VISITS WHERE PETID = (SELECT PETID FROM PETS WHERE PETS.TYPEID > 2001 AND NAME = 'NEMO';" test4=SELECT * FROM VISITS WHERE PETID = (SELECT PETID FROM PETS WHERE PETS.TYPEID > 2001 AND NAME = 'NEMO'); neatPrintTable(test4);
EMPs = SQL """ select * from EMP"""
DEPTs = SQL """ select * from DEPT"""
print DEPTs
model = 'RELEMPJ_CS347_PROF'
m = SQL """ SELECT TABLE_NAME FROM MDSYS.SEM_MODEL$ WHERE MODEL_NAME = """ model
if m != (('TABLE_NAME',),) : SQL """ truncate table """ "\"{0}\"".format(m[1][0])
SQL """ ALTER SYSTEM SET open_cursors = 2000 SCOPE=BOTH; """
connectTo 'jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl' 'CS347_prof' 'orcl_prof' 'rdf_mode' 'ReLEMPJ';
for i in range(1, len(EMPs)) :
SQL """ INSERT INTO EMP """ str(EMPs[0]).replace("'", "") """ VALUES """ EMPs[i]
t = SQL "select EMP.EMPNO, EMP.ENAME from EMP"
neatPrintTable(t)
for i in range(1, len(DEPTs)) :
SQL """ INSERT INTO DEPT """ str(DEPTs[0]).replace("'", "") """ VALUES """ DEPTs[i]
t = SQL "select DEPT.DEPTNO, DEPT.DNAME from DEPT"
print t
neatPrintTable(t)
# Ask Cannata on ReL database syntax
#batch db:
# for i in range(1, len(EMPs)) :
# db.employees.insert(
# SQL """ INSERT INTO EMP """ str(EMPs[0]).replace("'", "") """ VALUES """ EMPs[i]
# t = SQL "select EMP.EMPNO, EMP.ENAME from EMP"
# neatPrintTable(t)
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # basic select with criteria print "\n********Test0a - SELECT * FROM VISITS WHERE PRICE < 80; " test0a=SELECT * FROM VISITS WHERE PRICE < 80; neatPrintTable(test0a); # basic select with multiple criteria print "\n********Test0b - SELECT * FROM PETS WHERE BIRTH_YEAR >= 2010 AND TYPEID = 2002; " test0b=SELECT * FROM PETS WHERE BIRTH_YEAR >= 2010 AND TYPEID = 2002; neatPrintTable(test0b); # multi-table subquery projecting one column print "\n\n\n\n********Test1 - SELECT NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); " test1=SELECT NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); neatPrintTable(test1); # working - multi-table subquery projecting multiple columns print "\n\n\n\n********Test2 - SELECT VETID, NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); " test2=SELECT VETID, NAME FROM VETS WHERE VETID = (SELECT VETID FROM VISITS WHERE PETID = 1001); neatPrintTable(test2); # working - multi-table subquery projecting all columns print "\n\n\n\n********Test3 - SELECT * FROM PETS WHERE PETID = (SELECT PETID FROM VISITS WHERE TIME = 2.9); " test3=SELECT * FROM PETS WHERE PETID = (SELECT PETID FROM VISITS WHERE TIME = 2.9); neatPrintTable(test3);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # working - triple table subquery projecting one column print "\n\n\n\n********Test6 - SELECT NAME FROM VETS WHERE VETS.VETID = (SELECT VISITS.VETID FROM VISITS WHERE VISITS.VETID = (SELECT VETS.VETID FROM VETS WHERE VETS.VETID = 4001));" test6=SELECT NAME FROM VETS WHERE VETS.VETID = (SELECT VISITS.VETID FROM VISITS WHERE VISITS.VETID = (SELECT VETS.VETID FROM VETS WHERE VETS.VETID = 4001)); neatPrintTable(test6);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # working - From Project Requirements Examples - triple table subquery with multiple rows returned using "IN (...)" print "\n\n\n\n********Test9 - SELECT PETS.NAME, PETS.TYPEID FROM PETS WHERE PETS.TYPEID = (SELECT PETS.TYPEID FROM PETS WHERE PETS.BIRTH_YEAR = (SELECT PETS.BIRTH_YEAR FROM PETS WHERE PETS.PETID = 1001));" test9=SELECT PETS.NAME, PETS.TYPEID FROM PETS WHERE PETS.TYPEID = (SELECT PETS.TYPEID FROM PETS WHERE PETS.BIRTH_YEAR = (SELECT PETS.BIRTH_YEAR FROM PETS WHERE PETS.PETID = 1001)); neatPrintTable(test9);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; # working - triple table subquery with multiple rows returned using "IN (...)" print "\n\n\n\n********Test8 - SELECT * FROM ANIMALTYPES WHERE TYPEID = (SELECT TYPEID FROM PETS WHERE PETID = (SELECT PETID FROM VISITS WHERE TIME = 2));" test8=SELECT * FROM ANIMALTYPES WHERE TYPEID = (SELECT TYPEID FROM PETS WHERE PETID = (SELECT PETID FROM VISITS WHERE TIME = 2)); neatPrintTable(test8);
from NeatPrint import neatPrintTable MAKECONNECT URL jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl UNAME CS345_ecp456 PWORD orcl_ecp456 CONTYPE local; #working - multi-table subquery with two criteria in WHERE and two criteria in subquery print "\n\n\n\n********Test5 - SELECT * FROM VISITS WHERE PRICE > 80 AND PETID = (SELECT PETID FROM PETS WHERE PETS.TYPEID > 2001 AND NAME = 'NEMO');" test5=SELECT * FROM VISITS WHERE PRICE > 80 AND PETID = (SELECT PETID FROM PETS WHERE PETS.TYPEID > 2001 AND NAME = 'NEMO'); neatPrintTable(test5);
