| LOG aqatdml08 Clear; |
| --------------------------------------------------------------------- |
| -- File: SQLQAT17 Formerly $cats.testest.qat014 |
| -- Component: NonStop SQL Regression Test Suite |
| -- Description: |
| -- This test unit is a positive local test for the select statement. |
| --It tests SELECT statements with uncorrelated subqueries in their WHERE |
| --clauses. |
| -- This test unit uses the order-entry database (sqldd01d). Each |
| --test case in this test unit tests a particular relationship between |
| --the outer query(s) and the subquery(s). The following attributes |
| --are varied inside of testcases. Note that all of these attributes |
| --are not tested in every testcase - some of these attributes only apply |
| --to certain types of tests. |
| -- Attributes varied: |
| -- objects: joins |
| -- subquery and outer query on same table |
| -- views |
| |
| -- columns selected: one column |
| -- * (one column table if not inside exists) |
| -- distinct -- subquery only |
| -- -- outer query only |
| -- -- both |
| -- name qualifications: necessary |
| -- unnecessary |
| |
| -- no where clause in subquery |
| -- group by/having in subquery |
| -- subquery returning only one record with IN,=ANY,=ALL |
| |
| -- The test cases in this test unit are as follows: |
| -- testcase A0: SELECT ... WHERE ... IN ... SUBQUERY |
| -- testcase A1: simple comparison operators: SELECT ... WHERE ... |
| -- (<,<=,=,<>,>,>=) ... SUBQUERY |
| -- testcase A2: quantified comparison operators: >=ANY,=ALL,etc. |
| -- testcase A3: multiple nested subqueries and multiple subqueries |
| -- connected with AND,OR |
| -- testcase A4: subqueries with for browse access, for stable access, |
| -- for repeatable access |
| -- testcase A5: SELECT aggregate functions with aggregates in |
| -- subquery |
| |
| --All testcases are documented further below. |
| |
| --*********************** end test unit comments *********************** |
| |
| --<testunit-summary> |
| --select #7 pos/loc ufi subq unc where |
| |
| --<testunit-specs> |
| |
| --mode-type ufi |
| --test-type functional |
| --form-type pos/loc |
| --select-test |
| |
| --<testcase A0> |
| |
| -- <detail> |
| -- uncorrelated subquery in where clause test - tests the use of the |
| -- IN operator connecting the outer query with the subquery. Vary |
| -- attributes as described in testunit comments above. Test the |
| -- use of NOT IN. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> Get supplier names for suppliers who supply part 4102. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| ); |
| |
| -- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum NOT in |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| ); |
| |
| -- <comment> Get employee names who are regional managers (notice no |
| -- <comment> where clause in subquery) |
| -- <ufi-input> |
| select empname |
| from employee |
| where empnum in |
| (select manager |
| from region); |
| |
| -- <comment> Get employee names who do not work at first branches |
| -- <comment> (eliminate duplicates) |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum in |
| (select branchnum |
| from branch |
| where branchnum <> 1 |
| group by branchnum); |
| |
| -- <comment> same as previous query, but use DISTINCT instead of |
| -- <comment> GROUP BY to eliminate duplicates |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum in |
| (select distinct branchnum |
| from branch |
| where branchnum <> 1 |
| ); |
| |
| -- <comment> same as query before last, but use HAVING with GROUP BY |
| -- <comment> instead of WHERE to eliminate branches with branchnum = 1 |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum in |
| (select branchnum |
| from branch |
| group by branchnum |
| having branchnum <> 1); |
| |
| -- <comment> get region names where the manager's employee number is |
| -- <comment> less than 100 |
| -- <ufi-input> |
| select regname |
| from region |
| where manager in |
| (select * |
| from empone |
| where empnum < 100); |
| |
| -- <comment> get supplier numbers, for suppliers who supply at least |
| -- <comment> one part supplied by supplier 15. |
| -- <ufi-input> |
| select distinct suppnum |
| from fromsup |
| where partnum in |
| (select partnum |
| from fromsup |
| where suppnum = 15); |
| |
| -- <comment> same as above, only use aliases. |
| -- <ufi-input> |
| select distinct suppnum |
| from fromsup X |
| where X.partnum in |
| (select Y.partnum |
| from fromsup Y |
| where suppnum = 15); |
| |
| -- <comment> get salesman numbers and order numbers for salesman |
| -- <comment> who have orders for parts costing > 50000 |
| -- <ufi-input> |
| select salesman,ordernum |
| from orders |
| where orders.ordernum in |
| (select odetail.ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price > 50000); |
| |
| -- <comment> get salesman numbers who have orders for parts prices > 50000 |
| -- <comment> (eliminate duplicate salesman numbers and order numbers) |
| -- <ufi-input> |
| select distinct salesman |
| from orders |
| where ordernum in |
| (select distinct ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price > 50000); |
| |
| -- <comment> get parts records with a cost > 10000 |
| -- <ufi-input> |
| select * |
| from parts |
| where partnum in |
| (select partnum |
| from expfroms); |
| |
| -- <comment> get supplier numbers for suppliers who are in the same |
| -- <comment> state as supplier 1. (This tests the use of IN with a |
| -- <comment> subquery that returns only one record). |
| -- <ufi-input> |
| select suppnum |
| from supplier |
| where state IN |
| (select state |
| from supplier |
| where suppnum = 1); |
| |
| -- <end-input> |
| |
| |
| -- <testcase A1> |
| |
| -- <detail> |
| -- uncorrelated subquery in where clause test - this tests the use of |
| -- simple comparison operators (<,<=,=,<>,.,>=) connecting the outer |
| -- query with the subquery. Note that the subquery can return at most |
| -- one record. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> get supplier numbers for suppliers who are in the same state |
| -- <comment> as supplier 1. |
| -- <ufi-input> |
| select suppnum |
| from supplier |
| where state = |
| (select state |
| from supplier |
| where suppnum = 1); |
| |
| -- <comment> get supplier numbers for suppliers who are in the same state |
| -- <comment> as supplier 1 (use aliases). |
| -- <ufi-input> |
| select suppnum |
| from supplier X |
| where X.state = |
| (select X.state |
| from supplier X |
| where suppnum = 1); |
| |
| -- <comment> get the region's name whose manager's employee number is |
| -- <comment> greater than one. |
| -- <ufi-input> |
| select regname |
| from region |
| where manager > |
| (select * |
| from empone |
| where empnum = 1); |
| |
| -- <comment> get partnames for parts whose cost is 6000, and are not |
| -- <comment> supplied by supplier #10. |
| -- <ufi-input> |
| select partname |
| from parts |
| where partnum = |
| (select distinct partnum |
| from fromsup |
| where partcost = 6000 |
| and suppnum <> 10); |
| |
| -- <comment> get customer names for customers who have not ordered parts |
| -- <comment> stored at location 'V67' |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum <> |
| (select col_12 |
| from partsfor |
| where col_2 = 'V67'); |
| |
| -- <comment> same as above, only use a join instead of the view partsfor |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum <> |
| (select custnum |
| from orders,odetail,parts |
| where ( orders.ordernum = odetail.ordernum) and |
| ( odetail.partnum = parts.partnum) and |
| (location = 'V67')); |
| |
| -- <comment> get states for customers who have not ordered parts stored |
| -- <comment> at location 'V67', group by state. |
| -- <ufi-input> |
| select state |
| from customer |
| where custnum <> |
| (select col_12 |
| from partsfor |
| where col_2 = 'V67') |
| group by state; |
| |
| -- <comment> same as above, but eliminate customers in Texas |
| -- <ufi-input> |
| select state |
| from customer |
| where custnum <> |
| (select col_12 |
| from partsfor |
| where col_2 = 'V67') |
| group by state |
| having state <> 'Texas'; |
| |
| -- <comment> same as above, but order by state |
| -- <ufi-input> |
| select state |
| from customer |
| where custnum <> |
| (select col_12 |
| from partsfor |
| where col_2 = 'V67') |
| group by state |
| having state <> 'Texas' |
| order by state; |
| |
| -- <comment> get supplier numbers whose price for part #4102 is less |
| -- <comment> than or equal to supplier number 8 |
| -- <ufi-input> |
| select suppnum |
| from fromsup |
| where partcost <= |
| (select partcost |
| from fromsup |
| where partnum = 4102 |
| and suppnum = 8) |
| AND partnum = 4102; |
| |
| -- <comment> same as above, but AND condition before the subquery |
| -- <ufi-input> |
| select suppnum |
| from fromsup |
| where partnum = 4102 |
| and partcost <= |
| (select partcost |
| from fromsup |
| where partnum = 4102 |
| and suppnum = 8); |
| |
| -- <end-input> |
| |
| |
| |
| -- <testcase A2> |
| |
| -- <detail> |
| -- uncorrelated subquery in where clause test case - this tests the use |
| -- of the quantified comparison operators (=ANY,>=ALL,etc.). These |
| -- operators can be used with subqueries that return sets, not just |
| -- single records. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> get supplier names for suppliers who supply part 4102 |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 4102); |
| |
| -- <comment> same as above , with SOME instead of ANY |
| -- <comment> test use of white space between = and SOME |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum = SOME |
| (select suppnum |
| from fromsup |
| where partnum = 4102); |
| |
| -- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum <> ANY |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| ); |
| |
| -- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| -- <comment> test use of white space between <> and ANY |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum <> |
| ANY |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| ); |
| |
| -- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum <>SOME |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| ); |
| |
| -- <comment> Get employee names who are regional managers (notice no |
| -- <comment> where clause in subquery) |
| -- <ufi-input> |
| select empname |
| from employee |
| where empnum =ANY |
| (select manager |
| from region); |
| |
| -- <comment> Get employee names who do not work at first branches |
| -- <comment> (eliminate duplicates) |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum =SOME |
| (select branchnum |
| from branch |
| where branchnum <> 1 |
| group by branchnum); |
| |
| -- <comment> same as previous query, but use DISTINCT instead of |
| -- <comment> GROUP BY to eliminate duplicates |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum =ANY |
| (select distinct branchnum |
| from branch |
| where branchnum <> 1 |
| ); |
| |
| -- <comment> same as query before last, but use HAVING with GROUP BY |
| -- <comment> instead of WHERE to eliminate branches with branchnum = 1 |
| -- <ufi-input> |
| select empname |
| from employee |
| where branchnum =SOME |
| (select branchnum |
| from branch |
| group by branchnum |
| having branchnum <> 1); |
| |
| -- <comment> get region names where the manager's employee number is |
| -- <comment> less than 100 |
| -- <ufi-input> |
| select regname |
| from region |
| where manager =ANY |
| (select * |
| from empone |
| where empnum < 100); |
| |
| -- <comment> get supplier numbers, for suppliers who supply at least |
| -- <comment> one part supplied by supplier 15. |
| -- <ufi-input> |
| select distinct suppnum |
| from fromsup |
| where partnum =SOME |
| (select partnum |
| from fromsup |
| where suppnum = 15); |
| |
| -- <comment> same as above, only use aliases. |
| -- <ufi-input> |
| select distinct suppnum |
| from fromsup X |
| where X.partnum =ANY |
| (select Y.partnum |
| from fromsup Y |
| where suppnum = 15); |
| |
| -- <comment> get salesman numbers and order numbers for salesman |
| -- <comment> who have orders for parts costing > 50000 |
| -- <ufi-input> |
| select salesman,ordernum |
| from orders |
| where orders.ordernum =SOME |
| (select odetail.ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price > 50000); |
| |
| -- <comment> get salesman numbers who have orders for parts prices > 50000 |
| -- <comment> (eliminate duplicate salesman numbers and order numbers) |
| -- <ufi-input> |
| select distinct salesman |
| from orders |
| where ordernum =ANY |
| (select distinct ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price > 5000); |
| |
| -- <comment> get parts records with a cost > 10000 |
| -- <ufi-input> |
| select * |
| from parts |
| where partnum =SOME |
| (select partnum |
| from expfroms); |
| |
| -- <comment> get supplier numbers for suppliers who are in the same |
| -- <comment> state as supplier 1. (This tests the use of =ANY with a |
| -- <comment> subquery that returns only one record). |
| -- <ufi-input> |
| select suppnum |
| from supplier |
| where state =ANY |
| (select state |
| from supplier |
| where suppnum = 1); |
| |
| -- <comment> get supplier numbers whose price for part 4102 is less |
| -- <comment> than some other supplier who supplies part 4102 |
| -- <ufi-input> |
| select suppnum |
| from fromsup |
| where partcost <SOME |
| (select partcost |
| from fromsup |
| where partnum = 4102) |
| and partnum = 4102; |
| |
| -- <comment> same as above, with ANY instead of SOME |
| -- <ufi-input> |
| select suppnum |
| from fromsup |
| where partcost <ANY |
| (select partcost |
| from fromsup |
| where partnum = 4102) |
| and partnum = 4102; |
| |
| -- <comment> get supplier numbers whose price for part 4102 is less |
| -- <comment> than or equal to ALL suppliers who supply part 4102 |
| -- <ufi-input> |
| select suppnum |
| from fromsup |
| where partcost <=ALL |
| (select partcost |
| from fromsup |
| where partnum = 4102) |
| and partnum = 4102; |
| |
| -- <comment> get supplier numbers who are in the same state as supplier |
| -- <comment> 1 (this tests the use of =ALL with a subquery that only |
| -- <comment> returns one record). |
| -- <ufi-input> |
| select suppnum |
| from supplier |
| where state =ALL |
| (select state |
| from supplier |
| where suppnum = 1); |
| |
| -- <comment> get the salesmans' empnum (if any) who has the only order |
| -- <comment> where at least one part has been ordered in quanities > 25. |
| -- <ufi-input> |
| select salesman |
| from orders |
| where ordernum =ALL |
| (select ordernum |
| from odetail |
| where quantity > 25); |
| |
| -- <comment> same as above, but with group by ordernum |
| -- <ufi-input> |
| -- THIS QUERY MAKES NO SENSE. |
| -- select salesman |
| -- from orders |
| -- where ordernum =ALL |
| -- (select ordernum |
| -- from odetail |
| -- where quantity > 25) |
| -- group by ordernum; |
| |
| -- <comment> same as previous, with having ordernum > 100 |
| -- <ufi-input> |
| -- THIS QUERY MAKES NO SENSE. |
| -- select salesman |
| -- from orders |
| -- where ordernum =ALL |
| -- (select ordernum |
| -- from odetail |
| -- where quantity > 25) |
| -- group by ordernum |
| -- having ordernum > 100; |
| |
| -- <comment> same as first, but with 'select DISTINCT ordernum' |
| -- <ufi-input> |
| select salesman |
| from orders |
| where ordernum =ALL |
| (select distinct ordernum |
| from odetail |
| where quantity > 25); |
| |
| -- <comment> get supplier names whose state is not equal to some |
| -- <comment> customer state |
| -- <ufi-input> |
| select SS.suppname |
| from supplier SS |
| where state <>ANY |
| (select CC.state |
| from customer CC); |
| |
| -- <comment> get supplier names whose state is not equal to ALL |
| -- <comment> customer states |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where state <>ALL |
| (select state |
| from customer); |
| |
| -- <comment> same as above, add group by,having clauses |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where state <>ALL |
| (select state |
| from customer |
| group by state |
| having state <> 'Texas'); |
| |
| -- <comment> get the salesmens' names who sold the part whose price |
| -- <comment> is greater than or equal to all parts |
| -- <ufi-input> |
| select distinct col_11 |
| from partsfor |
| where col_3 >=ALL |
| (select col_3 |
| from partsfor); |
| |
| -- <end-input> |
| |
| |
| -- <testcase A3> |
| |
| -- <detail> |
| -- uncorrelated subquery in where clause test unit - this tests |
| -- multiply nested subqueries and multiple subqueries connected |
| -- with and/or. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> get supplier names for suppliers who supply part 4102 |
| -- <comment> and part 5504 |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where (suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102)) |
| and (suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 5504)); |
| |
| -- <comment> get supplier names for suppliers who supply part 4102 |
| -- <comment> OR 5504 -- same as above, replace 'and' with 'or' |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where (suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102)) |
| or (suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 5504)); |
| |
| -- <comment> get supplier names for suppliers who DON'T supply parts |
| -- <comment> 4102 or 5504 -- same as previous, add NOT before entire |
| -- <comment> predicate. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where NOT ((suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102)) |
| or (suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 5504))) ; |
| |
| -- <comment> get supplier names for suppliers who supply parts 4102, |
| -- <comment> 5504, and 5505. |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where (suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102)) |
| and (suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 5504)) |
| and (suppnum =SOME |
| (select suppnum |
| from fromsup |
| where partnum = 5505)); |
| |
| -- <comment> get employee names who have salary >= 30000 and age < 30 |
| -- <ufi-input> |
| select empname |
| from emppub |
| where empnum in |
| (select empnum |
| from employee |
| where salary >= 30000) |
| and empnum in |
| (select empnum |
| from employee |
| where age < 30000); |
| |
| -- <comment> get job titles for employees who work in the Chicago branch |
| -- <comment> of the Central region, excluding programmers, order by job title |
| -- <ufi-input> |
| select job |
| from employee |
| where (regnum not in |
| (select regnum |
| from region |
| where location <> 'Central')) |
| and (branchnum not in |
| (select branchnum |
| from branch |
| where branchname <> 'Chicago' |
| group by branchnum)) |
| group by job |
| having job <> 'programmer' |
| order by job; |
| |
| -- <comment> same as above, except eliminate 'group bys' and add 'distinct' |
| -- <ufi-input> |
| select distinct job |
| from employee |
| where (regnum not in |
| (select regnum |
| from region |
| where location <> 'Central')) |
| and (branchnum not in |
| (select distinct branchnum |
| from branch |
| where branchname <> 'Chicago')) |
| and job <> 'programmer' |
| order by job; |
| |
| -- <comment> get supplier names for suppliers who supply at least |
| -- <comment> one part whose inventory is negative (less than 0) |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum in |
| (select partnum |
| from parts |
| where inventory < 0) |
| ); |
| |
| -- <comment> same as above, except use '=SOME' instead of IN |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum =SOME |
| (select partnum |
| from parts |
| where inventory < 0) |
| ); |
| |
| -- <comment> get supplier names for suppliers who supply part 'LP__900_LPM' |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum IN |
| (select suppnum |
| from fromsup |
| where partnum = |
| (select partnum |
| from parts |
| where partname = 'LP 900 LPM')); |
| |
| -- <comment> get age of those employees whose salary is less than or |
| -- <comment> equal to the salary to the youngest employee |
| -- <ufi-input> |
| -- takes a long time in executor |
| select age |
| from employee |
| where salary <= |
| (select salary |
| from employee |
| where age <= ALL |
| (select age |
| from employee |
| ) |
| ); |
| |
| -- <comment> get salesman names who have orders for parts priced < 8000 |
| -- <comment> (eliminate duplicate salesman names,order numbers, and |
| -- <comment> records where the price = 8000) |
| -- <ufi-input> |
| select distinct salesman |
| from orders |
| where ordernum in |
| (select distinct ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price < |
| (select distinct price |
| from parts |
| where price = 8000)); |
| |
| -- <comment> get customer names who have orders for parts supplied by |
| -- <comment> suppliers in Mass. |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum in |
| (select custnum |
| from orders |
| where ordernum in |
| (select distinct ordernum |
| from odetail |
| where partnum in |
| (select distinct partnum |
| from fromsup |
| where suppnum in |
| (select suppnum |
| from supplier |
| where state = 'Mass' |
| ) |
| ) |
| ) |
| ); |
| |
| -- <comment> same as above, eliminate 'distinct' |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum in |
| (select custnum |
| from orders |
| where ordernum in |
| (select ordernum |
| from odetail |
| where partnum in |
| (select partnum |
| from fromsup |
| where suppnum in |
| (select suppnum |
| from supplier |
| where state = 'Mass' |
| ) |
| ) |
| ) |
| ); |
| |
| -- <comment> get customer names who have orders for parts supplied by |
| -- <comment> suppliers in Mass, where the order month is less than 4 |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum in |
| (select custnum |
| from orders |
| where ordernum in |
| (select distinct ordernum |
| from odetail |
| where partnum in |
| (select distinct partnum |
| from fromsup |
| where suppnum in |
| (select suppnum |
| from supplier |
| where state = 'Mass' |
| ) |
| ) |
| ) |
| and omonth < 4 |
| ); |
| |
| -- <comment> get salesman names and their customer names for salesman |
| -- <comment> who work in the Central region |
| -- <ufi-input> |
| select empname,custname |
| from salecust |
| where empname in |
| (select empname |
| from emppub |
| where regnum in |
| (select regnum |
| from region |
| where regname = 'Central' |
| ) |
| ); |
| |
| -- <comment> get supplier names for suppliers who supply at least |
| -- <comment> one part whose inventory is negative, eliminate duplicates |
| -- <comment> with group by |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum in |
| (select partnum |
| from parts |
| where inventory < 0) |
| group by suppnum |
| ); |
| |
| -- <comment> same as above, but eliminate supplier #2 |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum in |
| (select partnum |
| from parts |
| where inventory < 0) |
| group by suppnum |
| having suppnum <> 2 |
| ); |
| |
| -- <comment> get age of those employees who are not 22 and whose salary |
| -- <comment> is less than or equal to the salary of the youngest employee |
| -- <comment> under 40. |
| -- <ufi-input> |
| select age |
| from employee |
| where salary <= |
| (select salary |
| from employee |
| where age <=ALL |
| (select age |
| from employee |
| group by age |
| having age <40 |
| ) |
| ) |
| group by age |
| having age <> 22 |
| order by age; |
| -- <end-input> |
| |
| |
| -- <testcase A4> |
| |
| -- <detail> |
| -- uncorrelated subquery in where clause test - this tests the use of |
| -- for browse access, for stable access, and |
| -- for repeatable access in uncorrelated subqueries. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> get supplier numbers, for suppliers who supply at least |
| -- <comment> one part supplied by supplier 15. |
| -- <ufi-input> |
| select distinct suppnum |
| from fromsup X |
| where X.partnum in |
| (select Y.partnum |
| from fromsup Y |
| where suppnum = 15 |
| for browse access |
| ) |
| for repeatable access; |
| |
| -- <comment> get salesman numbers and order numbers for salesman |
| -- <comment> who have orders for parts costing > 50000 |
| -- <ufi-input> |
| select salesman,ordernum |
| from orders |
| where orders.ordernum in |
| (select odetail.ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price > 50000 |
| for repeatable access |
| ); |
| |
| -- <comment> get supplier numbers for suppliers who are in the same state |
| -- <comment> as supplier 1 (use aliases). |
| -- <ufi-input> |
| select suppnum |
| from supplier X |
| where X.state = |
| (select Y.state |
| from supplier Y |
| where suppnum = 1 |
| for browse access |
| ) |
| for repeatable access; |
| |
| -- <comment> get customer names for customers who have not ordered parts |
| -- <comment> stored at location 'V67' |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum <> |
| (select col_12 |
| from partsfor |
| where col_2 = 'V67' |
| for browse access |
| ); |
| |
| -- <comment> same as above, only use a join instead of the view partsfor |
| -- <ufi-input> |
| select custname |
| from customer |
| where custnum <> |
| (select custnum |
| from orders,odetail,parts |
| where ( orders.ordernum = odetail.ordernum) and |
| ( odetail.partnum = parts.partnum) and |
| (location = 'V67') |
| for repeatable access |
| ); |
| |
| -- <comment> get supplier names for suppliers who supply part 4102 |
| -- <comment> and part 5504 |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum = 4102 |
| for browse access |
| ) |
| and suppnum =ANY |
| (select suppnum |
| from fromsup |
| where partnum = 5504 |
| for stable access |
| ); |
| |
| |
| -- <comment> get supplier names for suppliers who supply at least |
| -- <comment> one part whose inventory is negative (less than 0) |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| where partnum in |
| (select partnum |
| from parts |
| where inventory < 0 |
| for browse access |
| ) |
| for repeatable access |
| ) |
| for stable access; |
| |
| -- <comment> get age of those employees whose salary is less than or |
| -- <comment> equal to the salary of the youngest employee (use aliases) |
| -- <ufi-input> |
| select X.age |
| from employee X |
| where X.salary <= |
| (select salary |
| from employee Y |
| where Y.age <= ALL |
| (select Z.age |
| from employee Z |
| for repeatable access |
| ) |
| for stable access |
| ) |
| for browse access; |
| |
| -- <comment> get salesman names who have orders for parts priced < 8000 |
| -- <comment> (eliminate duplicate salesman names,order numbers, and |
| -- <comment> records where the price = 8000) |
| -- <ufi-input> |
| select distinct salesman |
| from orders |
| where ordernum in |
| (select distinct ordernum |
| from odetail,parts |
| where odetail.partnum = parts.partnum |
| and price < |
| (select distinct price |
| from parts |
| where price = 8000 |
| for browse access |
| ) |
| for repeatable access |
| ) |
| for stable access; |
| |
| -- <comment> get salesman names and their customer names for salesman |
| -- <comment> who work in the Central region |
| -- <ufi-input> |
| select empname,custname |
| from salecust |
| where empname in |
| (select empname |
| from emppub |
| where regnum in |
| (select regnum |
| from region |
| where regname = 'Central' |
| for stable access |
| ) |
| for repeatable access |
| ) |
| for browse access; |
| |
| -- <end-input> |
| |
| |
| |
| -- <testcase A5> |
| |
| -- <detail> |
| -- select uncorrelated subquery in WHERE clause test - this tests the |
| -- selection of aggregate functions (COUNT, AVG, MAX, MIN, SUM). |
| -- Aggregates are tested in the SELECT clause and in the HAVING clause. |
| |
| -- <templates> |
| -- US00 |
| |
| -- <comment> Get employee names whose salary is greater than the average |
| -- <comment> salary of all employees |
| -- <ufi-input> |
| select empname |
| from employee |
| where salary > |
| (select avg(salary) |
| from employee |
| ); |
| |
| -- <comment> Get part name(s) which have the highest profit margin |
| -- <ufi-input> |
| select distinct partname |
| from parts, fromsup |
| where parts.partnum = fromsup.partnum |
| and (price - partcost) = |
| (select max(price - partcost) |
| from parts, fromsup |
| where parts.partnum = fromsup.partnum |
| ); |
| |
| -- <comment> get employee names whose salary is greater than all the |
| -- <comment> branches average salary |
| -- <ufi-input> |
| select empname |
| from employee |
| where salary >ALL |
| (select avg(salary) |
| from employee |
| group by regnum, branchnum |
| ); |
| |
| -- <comment> get the average and total salary of employees whose salary |
| -- <comment> is greater than all branches average salary |
| -- <ufi-input> |
| select avg(salary),sum(salary) |
| from employee |
| where salary >ALL |
| (select avg(salary) |
| from employee |
| group by regnum, branchnum |
| ); |
| |
| -- <comment> get the average and total salary of employees whose salary |
| -- <comment> is greater than all branches average salary (use distinct |
| -- <comment> salaries for the average in both the outer and subquery) |
| -- <ufi-input> |
| select avg(distinct salary),sum(salary) |
| from employee |
| where salary >ALL |
| (select avg(distinct salary) |
| from employee |
| group by regnum, branchnum |
| ); |
| |
| -- <comment> get employee names whose salary is greater than the average |
| -- <comment> salary of branch #1 in region #1 |
| -- <ufi-input> |
| select empname |
| from employee |
| where salary > |
| (select avg(salary) |
| from employee |
| where regnum = 1 and branchnum = 1 |
| ); |
| |
| -- <comment> get employee names whose salary is greater than the average |
| -- <comment> salary of all branches whose minimum salary is greater than |
| -- <comment> 20000 |
| -- <ufi-input> |
| select empname |
| from employee |
| where salary >ALL |
| (select avg(salary) |
| from employee |
| group by regnum, branchnum |
| having min(salary) > 20000 |
| ); |
| |
| -- <comment> get the partnames and their prices for the most expensive |
| -- <comment> part and the least expensive part |
| -- <ufi-input> |
| select partname,price |
| from parts |
| where price = |
| (select max(price) |
| from parts |
| ) |
| or price = |
| (select min(price) |
| from parts |
| ); |
| -- <comment> get supplier names for suppliers whose total cost of all |
| -- <comment> parts they supply exceeds 50000 |
| -- <ufi-input> |
| select suppname |
| from supplier |
| where suppnum in |
| (select suppnum |
| from fromsup |
| group by suppnum |
| having sum(partcost) > 50000 |
| ); |
| |
| -- <comment> get employee names for employees who make the same salary |
| -- <comment> as at least 2 other employees |
| -- <ufi-input> |
| select empname |
| from employee |
| where salary in |
| (select salary |
| from employee |
| group by salary |
| having count(*) >= 3 |
| ); |
| |
| -- <comment> get part names whose price exceeds the 'median' price |
| -- <comment> min(price) + ((max(price) - min(price)) / 2) |
| -- <ufi-input> |
| select partname |
| from parts |
| where price > |
| (select min(price) + ((max(price) - min(price)) / 2) |
| from parts |
| ); |
| |
| -- <end-input> |
| |
| |
| -- <end-test> |
| LOG; |
