public void dml059()
{
var sql = "INSERT INTO VTABLE VALUES(10,11,12,13,15);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO VTABLE VALUES(100,111,1112,113,115);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = @"
SELECT COL1, MAX(COL2 + COL3), MIN(COL3 - COL2)
FROM VTABLE
GROUP BY COL1
ORDER BY COL1;";
TU.ExecuteSQL(sql, "0,3,1;10,50,1;100,1223,100;1000,1000,5000");
Assert.AreEqual("", TU.error_);
sql = "DROP TABLE VTABLE;";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
CreateVTABLE();
sql = "INSERT INTO VTABLE VALUES (10,11,12,13,15);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO VTABLE VALUES (100,111,1112,113,115);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = @"
SELECT COL1,SUM(2 * COL2 * COL3)
FROM VTABLE
GROUP BY COL1
HAVING SUM(COL2 * COL3) > 2000
OR SUM(COL2 * COL3) < -2000
ORDER BY COL1;";
TU.ExecuteSQL(sql, "100,366864;1000,-12000000");
sql = "DROP TABLE VTABLE";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
CreateVTABLE();
sql = "INSERT INTO VTABLE VALUES(10,11,12,13,15);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO VTABLE VALUES(100,111,1112,113,115);";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = @"
SELECT COL1, MAX(COL2)
FROM VTABLE
GROUP BY COL1
HAVING EXISTS (SELECT *
FROM STAFF
WHERE EMPNUM = 'E1')
AND MAX(COL2) BETWEEN 10 AND 90
ORDER BY COL1;";
var stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "10");
Assert.AreEqual(stmtResult[0][1].ToString(), "20");
sql = "DROP TABLE VTABLE";
TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
CreateVTABLE();
sql = @"
SELECT SUM(COL1)
FROM VTABLE
WHERE 10 + COL1 > COL2
HAVING MAX(COL1) > 100;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "1000");
sql = @"
SELECT SUM(COL1)
FROM VTABLE
WHERE 1000 + COL1 >= COL2
HAVING MAX(COL1) > 100;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "1110");
}
public void dml073()
{
var sql = @"
SELECT AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM = 'E2'
AND STAFF.EMPNUM = WORKS.EMPNUM;";
var stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "60");
Assert.AreEqual(stmtResult[0][1].ToString(), "40");
sql = @"
SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND
STAFF.EMPNUM = WORKS.EMPNUM
GROUP BY STAFF.EMPNUM
HAVING COUNT(*) > 1
ORDER BY STAFF.EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
decimal DecNum = Convert.ToDecimal(stmtResult[0][1].ToString());
Assert.IsTrue(DecNum >= 30 && DecNum <= 31);
Assert.AreEqual(stmtResult[0][2].ToString(), "12");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
DecNum = Convert.ToDecimal(stmtResult[1][1].ToString());
Assert.IsTrue(DecNum >= 46 && DecNum <= 47);
Assert.AreEqual(stmtResult[1][2].ToString(), "20");
// TEST:0418. Removed DISTINCT from COUNT, with DISTINCT
// count shall be 3, without it 12.
sql = @"
SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4),
SUM(T2.COL4), COUNT(T1.COL4)
FROM VTABLE T1, VTABLE T2;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
DecNum = Convert.ToDecimal(stmtResult[0][0].ToString());
Assert.IsTrue(DecNum >= 147 && DecNum <= 148);
DecNum = Convert.ToDecimal(stmtResult[0][1].ToString());
Assert.IsTrue(DecNum >= 295 && DecNum <= 296);
Assert.AreEqual(stmtResult[0][2].ToString(), "1772");
Assert.AreEqual(stmtResult[0][3].ToString(), "12");
#if false
// dml075: TEST:0434. Just one interesting test, not
// adding another method.
// ERROR: "WHERE condition must be a blooean expression and no aggregation is allowed"
sql = @"
SELECT PNUM, SUM(HOURS) FROM WORKS
GROUP BY PNUM
HAVING EXISTS (SELECT PNAME FROM PROJ
WHERE PROJ.PNUM = WORKS.PNUM AND
SUM(WORKS.HOURS) > PROJ.BUDGET / 200);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(2, stmtResult.Count);
String Pnum1 = stmtResult[0][0].ToString();
String Pnum2 = stmtResult[1][0].ToString();
DecNum = Convert.ToDecimal(stmtResult[1][0].ToString());
decimal DecNum2 = Convert.ToDecimal(stmtResult[1][1].ToString());
/*
* -- PASS:0434 If 2 rows selected with values (in any order):?
* -- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80?
* -- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92?
*/
Assert.IsTrue((Pnum1 == "P1" && DecNum == 80 && Pnum2 == "P5" && DecNum2 == 92) || (Pnum1 == "P5" && DecNum == 92 && Pnum2 == "P1" && DecNum2 == 80));
#endif
// dml090
#if false
/* BUG */
sql = @"
SELECT MIN(PNAME)
FROM PROJ, WORKS, STAFF
WHERE PROJ.PNUM = WORKS.PNUM
AND WORKS.EMPNUM = STAFF.EMPNUM
AND BUDGET - GRADE * HOURS * 100 IN
(-4400, -1000, 4000);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "CALM");
#endif
sql = @"
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING (MAX(BUDGET) - MIN(BUDGET)) / 2
IN (2, 20000, 10000)
ORDER BY CITY DESC;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna");
Assert.AreEqual(stmtResult[0][1].ToString(), "2");
Assert.AreEqual(stmtResult[1][0].ToString(), "Deale");
Assert.AreEqual(stmtResult[1][1].ToString(), "3");
sql = @"
SELECT COUNT(*)
FROM PROJ
WHERE 24 * 1000 BETWEEN BUDGET - 5000 AND 50000 / 1.7;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(stmtResult[0][0].ToString(), "3");
#if false
/*
* BUG: Expected one row: 'IRM'
* Actual three rows: 'SDP'; 'SDP'; 'PAYR'
*/
sql = @"
SELECT PNAME
FROM PROJ
WHERE 'Tampa' NOT BETWEEN CITY AND 'Vienna'
AND PNUM > 'P2';";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "IRM");
#endif
sql = @"
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING 50000 + 2 BETWEEN 33000 AND SUM(BUDGET) - 20;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Deale");
Assert.AreEqual(stmtResult[0][1].ToString(), "3");
// dml158
#if false
/* BUG */
sql = @"
SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS
GROUP BY EMPNUM
HAVING EMPNUM IN (
SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF
ON WORKS.EMPNUM = STAFF.EMPNUM
AND HOURS < SUM (OWORKS.HOURS) / 3
AND GRADE > 10)
ORDER BY EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
Assert.AreEqual(stmtResult[0][1].ToString(), "184");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
Assert.AreEqual(stmtResult[0][1].ToString(), "140");
sql = @"
SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS
GROUP BY EMPNUM
HAVING EMPNUM IN (
SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF
ON WORKS.EMPNUM = STAFF.EMPNUM
AND HOURS >= 10 + AVG (OWORKS.HOURS)
AND CITY = 'Deale')
ORDER BY EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
Assert.AreEqual(stmtResult[0][1].ToString(), "184");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
Assert.AreEqual(stmtResult[0][1].ToString(), "140");
#endif
}
public void CreateBaseTables()
{
string sql = "CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));";
var stmtResult = TU.ExecuteSQL(sql);
Assert.IsNull(stmtResult);
Assert.AreEqual("", TU.error_);
sql = "CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL UNIQUE, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15));";
stmtResult = TU.ExecuteSQL(sql);
Assert.IsNull(stmtResult);
Assert.AreEqual("", TU.error_);
sql = "CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5), UNIQUE(EMPNUM,PNUM));";
stmtResult = TU.ExecuteSQL(sql);
Assert.IsNull(stmtResult);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P1',40);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P2',20);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P3',80);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P4',20);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P5',12);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E1','P6',12);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E2','P1',40);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E2','P2',80);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E3','P2',20);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E4','P2',20);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E4','P4',40);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
sql = "INSERT INTO WORKS VALUES ('E4','P5',80);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
/*
* this table gets new rows inserted and deleted, which we don't
* support. So, it gets created and populated seperately.
*/
CreateVTABLE(); // this table gets new rows inserted and deleted
}
public void dml022()
{
var sql = @"
SELECT EMPNUM
FROM STAFF
WHERE GRADE <
(SELECT MAX(GRADE)
FROM STAFF);";
TU.ExecuteSQL(sql, "E1;E2;E4");
Assert.AreEqual("", TU.error_);
sql = @"
SELECT *
FROM STAFF
WHERE GRADE <=
(SELECT AVG(GRADE)-1
FROM STAFF);";
var stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E2");
Assert.AreEqual(stmtResult[0][1].ToString(), "Betty");
sql = @"
SELECT EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM = 'P2')
ORDER BY EMPNAME;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(4, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Alice");
sql = @"
SELECT EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE = 'Design'));";
TU.ExecuteSQL(sql, "Alice;Betty;Don");
Assert.AreEqual("", TU.error_);
sql = @"
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE
FROM PROJ
WHERE PNUM IN
(SELECT PNUM
FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE = 'Design'))))))
ORDER BY EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(4, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
#if false
/*
* BUG: should return two rows but returns 12 rows
*/
sql = @"
SELECT DISTINCT EMPNUM
FROM WORKS WORKSX
WHERE NOT EXISTS
(SELECT *
FROM WORKS WORKSY
WHERE EMPNUM = 'E2'
AND NOT EXISTS
(SELECT *
FROM WORKS WORKSZ
WHERE WORKSZ.EMPNUM = WORKSX.EMPNUM
AND WORKSZ.PNUM = WORKSY.PNUM));";
TU.ExecuteSQL(sql, "E1;E2");
Assert.AreEqual("", TU.error_);
#endif
}
public void dml018()
{
var sql = @"
SELECT PNUM
FROM WORKS
WHERE PNUM > 'P1'
GROUP BY PNUM
HAVING COUNT(*) > 1;";
TU.ExecuteSQL(sql, "P2;P4;P5");
Assert.AreEqual("", TU.error_);
sql = @"
SELECT PNUM
FROM WORKS
GROUP BY PNUM
HAVING COUNT(*) > 2;";
var stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "P2");
sql = @"
SELECT EMPNUM, PNUM, HOURS
FROM WORKS
GROUP BY PNUM, EMPNUM, HOURS
HAVING MIN(HOURS) > 12 AND MAX(HOURS) < 80;";
TU.ExecuteSQL(sql, "E1,P1,40;E1,P2,20;E1,P4,20;E2,P1,40;E3,P2,20;E4,P2,20;E4,P4,40");
Assert.AreEqual("", TU.error_);
sql = @"
SELECT WORKS.PNUM
FROM WORKS
GROUP BY WORKS.PNUM
HAVING WORKS.PNUM IN (SELECT PROJ.PNUM
FROM PROJ
GROUP BY PROJ.PNUM
HAVING SUM(PROJ.BUDGET) > 25000);";
TU.ExecuteSQL(sql, "P2;P3;P6");
Assert.AreEqual("", TU.error_);
/*
* implemnt string compare operators.
*/
#if false
/*
* Aggregate on strings is not implemented. Throws runtime exception.
* */
sql = @"
SELECT SUM(HOURS)
FROM WORKS
HAVING MIN(PNUM) > 'P0';";
stmtResult = TU.ExecuteSQL(sql);
Assert.IsTrue(TU.error_.Contains("Operator '>' cannot be applied to"));
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "464");
#endif
sql = @"SELECT PNUM
FROM WORKS
WHERE PNUM > 'P1'
GROUP BY PNUM
HAVING COUNT(*) > 1;";
TU.ExecuteSQL(sql, "P2;P4;P5");
Assert.AreEqual("", TU.error_);
}
public void dml014()
{
var sql = @"
SELECT PNUM
FROM PROJ
WHERE BUDGET BETWEEN 40000 AND 60000;";
var stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "P6");
sql = @"
SELECT PNUM
FROM PROJ
WHERE BUDGET >= 40000 AND BUDGET <= 60000;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "P6");
/*
* BUG: Should return only one row, with 'Vienna'
* but returns four: Deale, Vienna, Deale, Akorn.
* Suppress Asserts for now.
*/
sql = @"
SELECT CITY
FROM STAFF
WHERE GRADE NOT BETWEEN 12 AND 13;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
// Assert.AreEqual(1, stmtResult.Count);
// Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna");
#if false
/* BUG or Unsupported WHER NOT ()? */
sql = @"
SELECT CITY
FROM STAFF
WHERE NOT(GRADE BETWEEN 12 AND 13);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna");
#endif
sql = @"
SELECT STAFF.EMPNAME
FROM STAFF
WHERE STAFF.EMPNUM IN
(SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.CITY='Tampa'));";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Alice");
/* BUG/Unsupported ? */
/* should return 1 row with 12 but returns 11 rows */
sql = @"
SELECT WORKS.HOURS
FROM WORKS
WHERE WORKS.PNUM NOT IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.BUDGET BETWEEN 5000 AND 40000);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
// Assert.AreEqual(1, stmtResult.Count);
// Assert.AreEqual(stmtResult[0][0].ToString(), "12");
sql = @"
SELECT WORKS.HOURS
FROM WORKS
WHERE NOT (WORKS.PNUM IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.BUDGET BETWEEN 5000 AND 40000));";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "12");
/*
* BUG/Unsupported?
* Should return one row with 80 but returns 11 rows.
*/
sql = @"
SELECT HOURS
FROM WORKS
WHERE PNUM NOT IN
(SELECT PNUM
FROM WORKS
WHERE PNUM IN ('P1','P2','P4','P5','P6'));";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
// Assert.AreEqual(1, stmtResult.Count);
// Assert.AreEqual(stmtResult[0][0].ToString(), "80");
sql = @"
SELECT HOURS
FROM WORKS
WHERE NOT (PNUM IN
(SELECT PNUM
FROM WORKS
WHERE PNUM IN ('P1','P2','P4','P5','P6')));";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "80");
/*
* BUG: Should return one row with Alice but retuns 5 rows.
*/
sql = @"
SELECT STAFF.EMPNAME
FROM STAFF
WHERE NOT EXISTS
(SELECT *
FROM PROJ
WHERE NOT EXISTS
(SELECT *
FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM
AND WORKS.PNUM=PROJ.PNUM));";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual("", TU.error_);
// Assert.AreEqual(1, stmtResult.Count);
// Assert.AreEqual(stmtResult[0][0].ToString(), "Alice");
}
public void dml073()
{
string sql = @"
SELECT AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM = 'E2'
AND STAFF.EMPNUM = WORKS.EMPNUM;";
System.Collections.Generic.List <physic.Row> stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "60");
Assert.AreEqual(stmtResult[0][1].ToString(), "40");
sql = @"
SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND
STAFF.EMPNUM = WORKS.EMPNUM
GROUP BY STAFF.EMPNUM
HAVING COUNT(*) > 1
ORDER BY STAFF.EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
decimal DecNum = Convert.ToDecimal(stmtResult[0][1].ToString());
Assert.IsTrue(DecNum >= 30 && DecNum <= 31);
Assert.AreEqual(stmtResult[0][2].ToString(), "12");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
DecNum = Convert.ToDecimal(stmtResult[1][1].ToString());
Assert.IsTrue(DecNum >= 46 && DecNum <= 47);
Assert.AreEqual(stmtResult[1][2].ToString(), "20");
// TEST:0418. Removed DISTINCT from COUNT, with DISTINCT
// count shall be 3, without it 12.
sql = @"
SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4),
SUM(T2.COL4), COUNT(T1.COL4)
FROM VTABLE T1, VTABLE T2;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
DecNum = Convert.ToDecimal(stmtResult[0][0].ToString());
Assert.IsTrue(DecNum >= 147 && DecNum <= 148);
DecNum = Convert.ToDecimal(stmtResult[0][1].ToString());
Assert.IsTrue(DecNum >= 295 && DecNum <= 296);
Assert.AreEqual(stmtResult[0][2].ToString(), "1772");
Assert.AreEqual(stmtResult[0][3].ToString(), "12");
#if false
// dml075: TEST:0434. Just one interesting test, not
// adding another method.
// ERROR: "WHERE condition must be a blooean expression and no aggregation is allowed"
sql = @"
SELECT PNUM, SUM(HOURS) FROM WORKS
GROUP BY PNUM
HAVING EXISTS (SELECT PNAME FROM PROJ
WHERE PROJ.PNUM = WORKS.PNUM AND
SUM(WORKS.HOURS) > PROJ.BUDGET / 200);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(2, stmtResult.Count);
String Pnum1 = stmtResult[0][0].ToString();
String Pnum2 = stmtResult[1][0].ToString();
DecNum = Convert.ToDecimal(stmtResult[1][0].ToString());
decimal DecNum2 = Convert.ToDecimal(stmtResult[1][1].ToString());
/*
* -- PASS:0434 If 2 rows selected with values (in any order):?
* -- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80?
* -- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92?
*/
Assert.IsTrue((Pnum1 == "P1" && DecNum == 80 && Pnum2 == "P5" && DecNum2 == 92) || (Pnum1 == "P5" && DecNum == 92 && Pnum2 == "P1" && DecNum2 == 80));
#endif
// dml090
#if false
/* BUG */
sql = @"
SELECT MIN(PNAME)
FROM PROJ, WORKS, STAFF
WHERE PROJ.PNUM = WORKS.PNUM
AND WORKS.EMPNUM = STAFF.EMPNUM
AND BUDGET - GRADE * HOURS * 100 IN
(-4400, -1000, 4000);";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "CALM");
#endif
sql = @"
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING (MAX(BUDGET) - MIN(BUDGET)) / 2
IN (2, 20000, 10000)
ORDER BY CITY DESC;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna");
Assert.AreEqual(stmtResult[0][1].ToString(), "2");
Assert.AreEqual(stmtResult[1][0].ToString(), "Deale");
Assert.AreEqual(stmtResult[1][1].ToString(), "3");
sql = @"
SELECT COUNT(*)
FROM PROJ
WHERE 24 * 1000 BETWEEN BUDGET - 5000 AND 50000 / 1.7;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(stmtResult[0][0].ToString(), "3");
#if false
/*
* BUG: Expected one row: 'IRM'
* Actual three rows: 'SDP'; 'SDP'; 'PAYR'
*/
sql = @"
SELECT PNAME
FROM PROJ
WHERE 'Tampa' NOT BETWEEN CITY AND 'Vienna'
AND PNUM > 'P2';";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "IRM");
#endif
sql = @"
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING 50000 + 2 BETWEEN 33000 AND SUM(BUDGET) - 20;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "Deale");
Assert.AreEqual(stmtResult[0][1].ToString(), "3");
// dml158
#if false
/* BUG */
sql = @"
SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS
GROUP BY EMPNUM
HAVING EMPNUM IN (
SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF
ON WORKS.EMPNUM = STAFF.EMPNUM
AND HOURS < SUM (OWORKS.HOURS) / 3
AND GRADE > 10)
ORDER BY EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
Assert.AreEqual(stmtResult[0][1].ToString(), "184");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
Assert.AreEqual(stmtResult[0][1].ToString(), "140");
sql = @"
SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS
GROUP BY EMPNUM
HAVING EMPNUM IN (
SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF
ON WORKS.EMPNUM = STAFF.EMPNUM
AND HOURS >= 10 + AVG (OWORKS.HOURS)
AND CITY = 'Deale')
ORDER BY EMPNUM;";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(2, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
Assert.AreEqual(stmtResult[0][1].ToString(), "184");
Assert.AreEqual(stmtResult[1][0].ToString(), "E4");
Assert.AreEqual(stmtResult[0][1].ToString(), "140");
#endif
// Try them in FROM
// -- TEST:0004 SELECT with UNION, ORDER BY integer DESC!
// -- PASS:0004 If 5 rows selected and last EMPNUM = 'E1'?
sql = @"
SELECT WEMPNUM
FROM (
SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM = 'P2'
UNION
SELECT STAFF.EMPNUM
FROM STAFF
WHERE STAFF.GRADE=13) WEMP(WEMPNUM)
ORDER BY 1 DESC
";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(5, stmtResult.Count);
Assert.AreEqual(stmtResult[4][0].ToString(), "E1");
// -- TEST:0005 SELECT with UNION ALL!
// -- PASS:0005 If 6 rows selected?
sql = @"
SELECT WEMPNUM
FROM (
SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM = 'P2'
UNION ALL
SELECT STAFF.EMPNUM
FROM STAFF
WHERE STAFF.GRADE = 13
) WEMP(WEMPNUM);
";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(6, stmtResult.Count);
#if false
// -- TEST:0158 SELECT with UNION and NOT EXISTS subquery!
// -- PASS:0158 If 21 rows selected?
/*
* EXCEPTION:
* PhysicMarkJoin.Exec(Action`1 callback) line 490
* PhysicProfiling.Exec(Action`1 callback) line 349
* PhysicFilter.Exec(Action`1 callback) line 1596
* PhysicProfiling.Exec(Action`1 callback) line 349
*/
sql = @"
SELECT EMPNAME,PNUM,HOURS
FROM STAFF,WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM
UNION
SELECT EMPNAME,PNUM,HOURS
FROM STAFF,WORKS
WHERE NOT EXISTS
(SELECT HOURS
FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM);
";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(21, stmtResult.Count);
// Assert.AreEqual failed. Expected:<>. Actual:<select_core->select_stmt->sql_stmt->sql_stmt_list->parse : 6|12|[@102,141:141='(',<6>,6:12]|extraneous input '(' expecting {K_SELECT, K_VALUES}>.
// -- TEST:0160 SELECT with parenthesized UNION, UNION ALL!
// -- PASS:0160 If 14 rows selected?
sql = @"
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=12
UNION ALL
(SELECT PNUM,EMPNUM,HOURS
FROM WORKS
UNION
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=80)
ORDER BY 2,1;
";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(14, stmtResult.Count);
// AGGREGATES on strings not supported.
sql = @"SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM (
SELECT PNUM, BUDGET, CITY
FROM PROJ OUTER_REF
WHERE BUDGET >= (SELECT AVG(BUDGET) FROM PROJ INNER_REF
WHERE OUTER_REF.CITY = INNER_REF.CITY)
UNION
SELECT 'MAX', MAX(BUDGET), MIN(CITY)
FROM PROJ
WHERE CITY > 'Deale') ABOVE_AVERAGE (COLUMN_1, COLUMN_2, COLUMN_3) ORDER BY COLUMN_1";
stmtResult = TU.ExecuteSQL(sql);
// -- PASS:0599 If 4 rows selected with ordered rows and column values: ?
// -- PASS:0599 MAX 30000 Tampa ?
// -- PASS:0599 P2 30000 Vienna ?
// -- PASS:0599 P3 30000 Tampa ?
// -- PASS:0599 P6 50000 Deale ?
#endif
sql = "select empnum, sum(hours) from (select empnum, hours from works group by hours, empnum) workweek(empnum, hours) where hours > 20 group by empnum having empnum = 'E1';";
stmtResult = TU.ExecuteSQL(sql);
Assert.AreEqual(1, stmtResult.Count);
Assert.AreEqual(stmtResult[0][0].ToString(), "E1");
Assert.AreEqual(stmtResult[0][1].ToString(), "120");
}