| >>--------------------------------------------------------------------- |
| >>-- 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 HAVING |
| >>-- 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 ... HAVING ... IN ... SUBQUERY |
| >>-- testcase A1: simple comparison operators: SELECT ... HAVING ... |
| >>-- (<,<=,=,<>,>,>=) ... SUBQUERY |
| >>-- testcase A2: quantified comparison operators: >=ANY,=ALL,etc. |
| >>-- testcase A3: multiple nested subqueries, multiple subqueries |
| >>-- connected with AND,OR, and SELECTs with subqueries |
| >>-- (uncorrelated) in both the WHERE clause and HAVING |
| >>-- clause |
| >>-- testcase A4: subqueries with for read committed access, |
| >>-- for read committed access, for read committed access |
| >>-- testcase A5: SELECT aggregate functions with aggregates in |
| >>-- subquery |
| >> |
| >> |
| >>-- All testcases are documented further below. |
| >> |
| >>-- *********************** end test unit comments *********************** |
| >> |
| >> |
| >>-- <testunit-summary> |
| >>-- select #8 pos/loc ufi subq unc having |
| >> |
| >>-- <testunit-specs> |
| >> |
| >>-- mode-type ufi |
| >>-- test-type functional |
| >>-- form-type pos/loc |
| >>-- select-test |
| >> |
| >>-- <testcase A0> |
| >> |
| >>-- <detail> |
| >>-- uncorrelated subquery in having 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 numbers for suppliers who supply part 4102. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 15 |
| 6 |
| 8 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> Get supplier numbers for suppliers who DON'T supply part 4102. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum NOT in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 3 |
| 10 |
| 1 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <comment> Get employee numbers who are regional managers (notice no > |
| >>-- <comment> where clause in subquery) |
| >>-- <ufi-input> |
| >> select empnum |
| +> from employee |
| +> group by empnum |
| +> having empnum in |
| +> (select manager |
| +> from region); |
| |
| EMPNUM |
| ------ |
| |
| 29 |
| 104 |
| 72 |
| 343 |
| 43 |
| 87 |
| 1 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <comment> get part numbers, for parts supplied by supplier 15 |
| >>-- <comment> and supplied by at least one other supplier. |
| >>-- <ufi-input> |
| >> select partnum |
| +> from fromsup |
| +> where suppnum <> 15 |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from fromsup |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get part numbers, for parts supplied by supplier 15 |
| >>-- <comment> and supplied by at least one other supplier, eliminate |
| >>-- <comment> duplicate part numbers |
| >>-- <ufi-input> |
| >> select distinct partnum |
| +> from fromsup |
| +> where suppnum <> 15 |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from fromsup |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- <comment> same as above, only use aliases. |
| >>-- <ufi-input> |
| >> select distinct partnum |
| +> from fromsup X |
| +> where X.suppnum <> 15 |
| +> group by partnum |
| +> having X.partnum in |
| +> (select Y.partnum |
| +> from fromsup Y |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get salesman numbers and order numbers for salesman |
| >>-- <comment> who have orders for parts costing > 50000 |
| >>-- <ufi-input> |
| >> select salesman,ordernum |
| +> from orders |
| +> group by ordernum, salesman |
| +> having orders.ordernum in |
| +> (select odetail.ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> and price > 50000); |
| |
| SALESMAN ORDERNUM |
| -------- -------- |
| |
| 204 32 |
| 205 21 |
| 205 66 |
| 207 41 |
| 210 51 |
| 212 25 |
| 212 45 |
| 218 38 |
| 221 122 |
| 222 30 |
| 225 48 |
| 231 35 |
| |
| --- 12 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by salesman,ordernum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> and price > 50000); |
| |
| SALESMAN |
| -------- |
| |
| 204 |
| 205 |
| 207 |
| 210 |
| 212 |
| 218 |
| 221 |
| 222 |
| 225 |
| 231 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- <comment> get parts numbers with a cost > 10000 |
| >>-- <ufi-input> |
| >> select partnum |
| +> from parts |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from expfroms); |
| |
| PARTNUM |
| ------- |
| |
| 4102 |
| 3103 |
| 4103 |
| 5505 |
| 1403 |
| 212 |
| 244 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by state, suppnum |
| +> having state IN |
| +> (select state |
| +> from supplier |
| +> where suppnum = 1); |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >>-- <testcase A1> |
| >> |
| >>-- <detail> |
| >>-- uncorrelated subquery in having 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 |
| +> group by suppnum, state |
| +> having state = |
| +> (select state |
| +> from supplier |
| +> where suppnum = 1); |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who are in the same state |
| >>-- <comment> as supplier 1 (use aliases). |
| >> select suppnum |
| +> from supplier X |
| +> group by suppnum, X.state |
| +> having X.state = |
| +> (select Y.state |
| +> from supplier Y |
| +> where suppnum = 1); |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get partnumbers for parts whose cost is 6000, and are not |
| >>-- <comment> supplied by supplier #10. |
| >>-- <ufi-input> |
| >> select partnum |
| +> from parts |
| +> group by partnum |
| +> having partnum = |
| +> (select distinct partnum |
| +> from fromsup |
| +> where partcost = 6000 |
| +> and suppnum <> 10); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get customer numbers for customers who have not ordered parts |
| >>-- <comment> stored at location 'V67' |
| >>-- <ufi-input> |
| >> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum <> |
| +> (select col_12 |
| +> from partsfor |
| +> where col_2 = 'V67'); |
| |
| CUSTNUM |
| ------- |
| |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 7777 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> get customer numbers for customers who have not ordered parts |
| >>-- <comment> stored at location 'V67', order by customer number |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum <> |
| +> (select col_12 |
| +> from partsfor |
| +> where col_2 = 'V67') |
| +> order by custnum; |
| |
| CUSTNUM |
| ------- |
| |
| 21 |
| 123 |
| 143 |
| 324 |
| 543 |
| 926 |
| 1234 |
| 3333 |
| 5635 |
| 7654 |
| 7777 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> same as first, only use a join instead of the view partsfor |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum <> |
| +> (select custnum |
| +> from orders,odetail,parts |
| +> where ( orders.ordernum = odetail.ordernum) and |
| +> ( odetail.partnum = parts.partnum) and |
| +> (location = 'V67')); |
| |
| CUSTNUM |
| ------- |
| |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 7777 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> same as first, but eliminate customers in Texas |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> where state <> 'Texas' |
| +> group by custnum |
| +> having custnum <> |
| +> (select col_12 |
| +> from partsfor |
| +> where col_2 = 'V67'); |
| |
| CUSTNUM |
| ------- |
| |
| 21 |
| 123 |
| 143 |
| 324 |
| 543 |
| 926 |
| 1234 |
| 3333 |
| 5635 |
| 7654 |
| 7777 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by suppnum,partnum,partcost |
| +> having partcost <= |
| +> (select partcost |
| +> from fromsup |
| +> where partnum = 4102 |
| +> and suppnum = 8) |
| +> AND partnum = 4102; |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 8 |
| 15 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> same as above, but AND condition before the subquery |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from fromsup |
| +> group by suppnum,partnum,partcost |
| +> having partnum = 4102 |
| +> and partcost <= |
| +> (select partcost |
| +> from fromsup |
| +> where partnum = 4102 |
| +> and suppnum = 8); |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 8 |
| 15 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >> |
| >>-- <testcase A2> |
| >> |
| >>-- <detail> |
| >>-- uncorrelated subquery in having 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 numbers for suppliers who supply part 4102 |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102); |
| |
| SUPPNUM |
| ------- |
| |
| 15 |
| 6 |
| 8 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> same as above , with SOME instead of ANY |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum =SOME |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102); |
| |
| SUPPNUM |
| ------- |
| |
| 15 |
| 6 |
| 8 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> Get supplier numbers for suppliers who DON'T supply part 4102. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum <>ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 8 |
| 10 |
| 1 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum <>ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 8 |
| 10 |
| 1 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <comment> Get supplier names for suppliers who DON'T supply part 4102. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum <>SOME |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 8 |
| 10 |
| 1 |
| |
| --- 7 row(s) selected. |
| >> |
| >> |
| >>-- <comment> Get employee numbers who are regional managers (notice no |
| >>-- <comment> where clause in subquery) |
| >>-- <ufi-input> |
| >> select empnum |
| +> from employee |
| +> group by empnum |
| +> having empnum =ANY |
| +> (select manager |
| +> from region); |
| |
| EMPNUM |
| ------ |
| |
| 29 |
| 104 |
| 72 |
| 343 |
| 43 |
| 87 |
| 1 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <comment> get part numbers, for parts supplied by supplier 15 |
| >>-- <comment> and supplied by at least one other supplier |
| >>-- <ufi-input> |
| >> select partnum |
| +> from fromsup |
| +> where suppnum <> 15 |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from fromsup |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get part numbers, for parts supplied by supplier 15 |
| >>-- <comment> and supplied by at least one other supplier, eliminate |
| >>-- <comment> duplicate part numbers |
| >>-- <ufi-input> |
| >> select distinct partnum |
| +> from fromsup |
| +> where suppnum <> 15 |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from fromsup |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- <comment> same as above, only use aliases. |
| >>-- <ufi-input> |
| >> select distinct partnum |
| +> from fromsup X |
| +> where X.suppnum <> 15 |
| +> group by partnum |
| +> having X.partnum in |
| +> (select Y.partnum |
| +> from fromsup Y |
| +> where suppnum = 15); |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >> |
| >>-- <comment> get salesman numbers and order numbers for salesman |
| >>-- <comment> who have orders for parts costing > 50000 |
| >>-- <ufi-input> |
| >> select salesman,ordernum |
| +> from orders |
| +> group by ordernum,salesman |
| +> having orders.ordernum =SOME |
| +> (select odetail.ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> and price > 50000); |
| |
| SALESMAN ORDERNUM |
| -------- -------- |
| |
| 204 32 |
| 205 21 |
| 205 66 |
| 207 41 |
| 210 51 |
| 212 25 |
| 212 45 |
| 218 38 |
| 221 122 |
| 222 30 |
| 225 48 |
| 231 35 |
| |
| --- 12 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by ordernum,salesman |
| +> having ordernum =ANY |
| +> (select distinct ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> and price > 5000); |
| |
| SALESMAN |
| -------- |
| |
| 204 |
| 205 |
| 207 |
| 210 |
| 212 |
| 218 |
| 221 |
| 222 |
| 225 |
| 231 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- <comment> get part numbers with a cost > 10000 |
| >>-- <ufi-input> |
| >> select partnum |
| +> from parts |
| +> group by partnum |
| +> having partnum =SOME |
| +> (select partnum |
| +> from expfroms); |
| |
| PARTNUM |
| ------- |
| |
| 4102 |
| 3103 |
| 4103 |
| 5505 |
| 1403 |
| 212 |
| 244 |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by state, suppnum |
| +> having state =ANY |
| +> (select state |
| +> from supplier |
| +> where suppnum = 1); |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by suppnum, partnum, partcost |
| +> having partcost <SOME |
| +> (select partcost |
| +> from fromsup |
| +> where partnum = 4102) |
| +> and partnum = 4102; |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 15 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- <comment> same as above, with ANY instead of SOME |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from fromsup |
| +> group by suppnum, partnum, partcost |
| +> having partcost <ANY |
| +> (select partcost |
| +> from fromsup |
| +> where partnum = 4102) |
| +> and partnum = 4102; |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 15 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- <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 |
| +> group by suppnum,partnum,partcost |
| +> having partcost <=ALL |
| +> (select partcost |
| +> from fromsup |
| +> where partnum = 4102) |
| +> and partnum = 4102; |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers who are in the same state as supplier r |
| >>-- <comment> 1 (this tests the use of =ALL with a subquery that only |
| >>-- <comment> returns one record). |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by state, suppnum |
| +> having state =ALL |
| +> (select state |
| +> from supplier |
| +> where suppnum = 1); |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by salesman, ordernum |
| +> having ordernum =ALL |
| +> (select ordernum |
| +> from odetail |
| +> where quantity > 25); |
| |
| SALESMAN |
| -------- |
| |
| 221 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> same as previous, with where ordernum > 100 |
| >>-- <ufi-input> |
| >> select salesman |
| +> from orders |
| +> where ordernum > 100 |
| +> group by salesman, ordernum |
| +> having ordernum =ALL |
| +> (select ordernum |
| +> from odetail |
| +> where quantity > 25); |
| |
| SALESMAN |
| -------- |
| |
| 221 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- <comment> same as first, but with 'select DISTINCT ordernum' |
| >>-- <ufi-input> |
| >> select salesman |
| +> from orders |
| +> group by salesman, ordernum |
| +> having ordernum =ALL |
| +> (select distinct ordernum |
| +> from odetail |
| +> where quantity > 25); |
| |
| SALESMAN |
| -------- |
| |
| 221 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get supplier names whose state is not equal to some |
| >>-- <comment> customer state |
| >>-- <ufi-input> |
| >> select S.suppname |
| +> from supplier S |
| +> group by state, suppname |
| +> having state <>ANY |
| +> (select C.state |
| +> from customer C); |
| |
| SUPPNAME |
| ------------------ |
| |
| TANDEM COMPUTERS |
| DATA TERMINAL CO |
| DISPLAY INC |
| INFOMATION STORAGE |
| MAGNETICS CORP |
| STEELWORK INC |
| DATADRIVE |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- <comment> get supplier names whose state is not equal to ALL |
| >>-- <comment> customer states |
| >>-- <ufi-input> |
| >> select suppname |
| +> from supplier |
| +> group by state, suppname |
| +> having state <>ALL |
| +> (select state |
| +> from customer); |
| |
| SUPPNAME |
| ------------------ |
| |
| INFOMATION STORAGE |
| MAGNETICS CORP |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- <comment> same as above, add group by,having clauses |
| >>-- <ufi-input> |
| >> select suppname |
| +> from supplier |
| +> group by state, suppname |
| +> having state <>ALL |
| +> (select state |
| +> from customer |
| +> group by state |
| +> having state <> 'Texas'); |
| |
| SUPPNAME |
| ------------------ |
| |
| INFOMATION STORAGE |
| MAGNETICS CORP |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- <comment> get the salesmens' names who sold the part whose price |
| >>-- <comment> is greater than or equal to all parts |
| >>-- <ufi-input> |
| >> select col_11 |
| +> from partsfor |
| +> group by col_11, col_3 |
| +> having col_3 >=ALL |
| +> (select col_3 |
| +> from partsfor); |
| |
| COL_11 |
| ------ |
| |
| 212 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >>-- <testcase A3> |
| >> |
| >>-- <detail> |
| >>-- uncorrelated subquery in having clause test unit - this tests |
| >>-- multiply nested subqueries and multiple subqueries connected |
| >>-- with and/or. |
| >> |
| >>-- <templates> |
| >>-- US00 |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 4102 |
| >>-- <comment> and part 5504 |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having (suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102)) |
| +> and (suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504)); |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 15 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 4102 |
| >>-- <comment> and part 5504 - replace AND condition in having clause |
| >>-- <comment> with a where clause |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> where suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504 |
| +> ) |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 15 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 4102 |
| >>-- <comment> OR 5504 -- same as above, replace 'and' with 'or' |
| >>-- <ufi-input> |
| >> |
| >> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having (suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102)) |
| +> or (suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504)); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 6 |
| 8 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who DON'T supply parts |
| >>-- <comment> 4102 or 5504 -- same as previous, add NOT before entire |
| >>-- <comment> predicate. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having NOT ((suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102)) |
| +> or (suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504))) ; |
| |
| SUPPNUM |
| ------- |
| |
| 3 |
| 10 |
| 1 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who DON'T supply parts |
| >>-- <comment> 4102 or 5504 -- same as previous, but use both a subquery |
| >>-- <comment> in a where clause and in a having clause. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> where suppnum <>ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504 |
| +> ) |
| +> group by suppnum |
| +> having suppnum NOT in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 3 |
| 10 |
| 1 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply parts 4102, |
| >>-- <comment> 5504, and 5505. |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having (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)); |
| |
| SUPPNUM |
| ------- |
| |
| 15 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get employee numbers who have salary >= 30000 and age < 30 |
| >>-- <ufi-input> |
| >> |
| >> select empnum |
| +> from emppub |
| +> group by empnum |
| +> having empnum in |
| +> (select empnum |
| +> from employee |
| +> where salary >= 30000) |
| +> and empnum in |
| +> (select empnum |
| +> from employee |
| +> where age < 30000); |
| |
| EMPNUM |
| ------ |
| |
| 1 |
| 23 |
| 29 |
| 32 |
| 39 |
| 43 |
| 65 |
| 72 |
| 75 |
| 87 |
| 89 |
| 93 |
| 104 |
| 109 |
| 111 |
| 129 |
| 178 |
| 180 |
| 205 |
| 212 |
| 213 |
| 214 |
| 215 |
| 216 |
| 218 |
| 220 |
| 221 |
| 222 |
| 225 |
| 227 |
| 228 |
| 229 |
| 231 |
| 232 |
| 321 |
| 337 |
| 343 |
| |
| --- 37 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get job titles for employees who work in the Chicago branch |
| >>-- <comment> of the Central region, excluding programmers, order by job title b |
| >>-- <ufi-input> |
| >> |
| >> select job |
| +> from employee |
| +> where job <> 'PROGRAMMER' |
| +> group by job, regnum, branchnum |
| +> having (regnum in |
| +> (select regnum |
| +> from region |
| +> where regname = 'CENTRAL')) |
| +> and (branchnum in |
| +> (select branchnum |
| +> from branch |
| +> where branchname = 'CHICAGO' |
| +> group by branchnum)) |
| +> order by job; |
| |
| JOB |
| ------------ |
| |
| MANAGER |
| SALESMAN |
| SECRETARY |
| |
| --- 3 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply at least |
| >>-- <comment> one part whose inventory is negative (less than 0) |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> group by suppnum,partnum |
| +> having partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply at least |
| >>-- <comment> one part whose inventory is negative (less than 0) - use |
| >>-- <comment> subquery in having clause , subquery in where clause (mixed) |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get supplier names for suppliers who supply at least |
| >>-- <comment> one part whose inventory is negative (less than 0) - use |
| >>-- <comment> subquery in having clause, subquery in where clause (mixed) |
| >>-- <ufi-input> |
| >> select suppname |
| +> from supplier |
| +> where suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> group by suppnum,partnum |
| +> having partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> ); |
| |
| SUPPNAME |
| ------------------ |
| |
| DATA TERMINAL CO |
| DATADRIVE |
| DISPLAY INC |
| INFOMATION STORAGE |
| TANDEM COMPUTERS |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> same as first, except use =SOME instead of IN |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum =SOME |
| +> (select suppnum |
| +> from fromsup |
| +> group by suppnum,partnum |
| +> having partnum =ANY |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 'LP__900_LPM' |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum IN |
| +> (select suppnum |
| +> from fromsup |
| +> group by suppnum, partnum |
| +> having partnum = |
| +> (select partnum |
| +> from parts |
| +> where partname = 'LP 900 LPM')); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 6 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 'LP__900_LPM' |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum IN |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = |
| +> (select partnum |
| +> from parts |
| +> where partname = 'LP 900 LPM')); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 6 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by suppnum, partnum |
| +> having partnum = |
| +> (select partnum |
| +> from parts |
| +> where partname = 'LP 900 LPM')); |
| |
| SUPPNAME |
| ------------------ |
| |
| DATA TERMINAL CO |
| DATADRIVE |
| INFOMATION STORAGE |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- <comment> get age of those employees whose salary is less than or |
| >>-- <comment> equal to the salary of the youngest employee |
| >>-- <ufi-input> |
| >> select age |
| +> from employee |
| +> group by age, salary |
| +> having salary <= |
| +> (select salary |
| +> from employee |
| +> group by age, salary |
| +> having age <= ALL |
| +> (select age |
| +> from employee |
| +> ) |
| +> ); |
| |
| AGE |
| --- |
| |
| 24 |
| 26 |
| 19 |
| 22 |
| 24 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get age of those employees whose salary is less than or |
| >>-- <comment> equal to the salary of the youngest employee |
| >>-- <ufi-input> |
| >> select age |
| +> from employee |
| +> where salary <= |
| +> (select salary |
| +> from employee |
| +> group by age, salary |
| +> having age <= ALL |
| +> (select age |
| +> from employee |
| +> ) |
| +> ); |
| |
| AGE |
| --- |
| |
| 24 |
| 26 |
| 19 |
| 22 |
| 24 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get age of those employees whose salary is less than or |
| >>-- <comment> equal to the salary of the youngest employee |
| >>-- <ufi-input> |
| >> select X.age |
| +> from employee X |
| +> group by age, salary |
| +> having X.salary <= |
| +> (select salary |
| +> from employee Y |
| +> group by age, salary |
| +> having Y.age <= ALL |
| +> (select Z.age |
| +> from employee Z |
| +> ) |
| +> ); |
| |
| AGE |
| --- |
| |
| 24 |
| 26 |
| 19 |
| 22 |
| 24 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by salesman, ordernum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail,parts |
| +> group by ordernum, odetail.partnum, |
| +> parts.partnum, price |
| +> having odetail.partnum = parts.partnum |
| +> and price < |
| +> (select distinct price |
| +> from parts |
| +> where price = 8000)); |
| |
| SALESMAN |
| -------- |
| |
| 204 |
| 205 |
| 207 |
| 210 |
| 212 |
| 218 |
| 221 |
| 222 |
| 225 |
| 231 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by salesman, ordernum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> group by ordernum, price |
| +> having price < |
| +> (select distinct price |
| +> from parts |
| +> where price = 8000)); |
| |
| SALESMAN |
| -------- |
| |
| 204 |
| 205 |
| 207 |
| 210 |
| 212 |
| 218 |
| 221 |
| 222 |
| 225 |
| 231 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- <comment> get customer numbers who have orders for parts supplied by |
| >>-- <comment> suppliers in MASS. |
| >>-- <ufi-input> |
| >> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum in |
| +> (select custnum |
| +> from orders |
| +> group by ordernum, custnum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail |
| +> group by ordernum, partnum |
| +> having partnum in |
| +> (select distinct partnum |
| +> from fromsup |
| +> group by partnum, suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from supplier |
| +> where state = 'MASS' |
| +> ) |
| +> ) |
| +> ) |
| +> ); |
| |
| CUSTNUM |
| ------- |
| |
| 3210 |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get customer numbers who have orders for parts supplied by |
| >>-- <comment> suppliers in Mass. |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum in |
| +> (select custnum |
| +> from orders |
| +> where ordernum in |
| +> (select distinct ordernum |
| +> from odetail |
| +> group by ordernum, partnum |
| +> having partnum in |
| +> (select distinct partnum |
| +> from fromsup |
| +> where suppnum in |
| +> (select suppnum |
| +> from supplier |
| +> where state = 'MASS' |
| +> ) |
| +> ) |
| +> ) |
| +> ); |
| |
| CUSTNUM |
| ------- |
| |
| 3210 |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by ordernum, custnum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail |
| +> group by ordernum, partnum |
| +> having partnum in |
| +> (select distinct partnum |
| +> from fromsup |
| +> group by partnum, suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from supplier |
| +> where state = 'MASS' |
| +> ) |
| +> ) |
| +> ) |
| +> ); |
| |
| CUSTNAME |
| ------------------ |
| |
| BESTFOOD MARKETS |
| BROWN MEDICAL CO |
| CENTRAL UNIVERSITY |
| DATASPEED |
| DEUTSCHE STAHL |
| FRESNO STATE BANK |
| METALL-AG. |
| MOTOR DISTRIBUTING |
| PREMIER INSURANCE |
| STEVENS SUPPLY |
| VEREINIGTE CHEMIE |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> get customer numbers who have orders for parts supplied by y |
| >>-- <comment> suppliers in Mass, where the order month is less than 4 |
| >>-- <ufi-input> |
| >> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum in |
| +> (select custnum |
| +> from orders |
| +> where ordernum in |
| +> (select distinct ordernum |
| +> from odetail |
| +> group by ordernum, partnum |
| +> having partnum in |
| +> (select distinct partnum |
| +> from fromsup |
| +> group by partnum, suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from supplier |
| +> where state = 'MASS' |
| +> ) |
| +> ) |
| +> ) |
| +> and omonth < 4 |
| +> ); |
| |
| CUSTNUM |
| ------- |
| |
| 123 |
| 21 |
| 1234 |
| 543 |
| 926 |
| 7654 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get salesman names for salesman who work in the Central region |
| >>-- <ufi-input> |
| >> select empname |
| +> from salecust |
| +> group by empname |
| +> having empname in |
| +> (select empname |
| +> from emppub |
| +> group by empname, regnum |
| +> having regnum in |
| +> (select regnum |
| +> from region |
| +> where regname = 'CENTRAL' |
| +> ) |
| +> ); |
| |
| EMPNAME |
| ------------------ |
| |
| MARK FOLEY |
| RICHARD BARTON |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply at least |
| >>-- <comment> one part whose inventory is negative, eliminate duplicates |
| >>-- <comment> with group by |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> group by suppnum |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> same as above, but eliminate supplier #2 |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0) |
| +> group by suppnum |
| +> having suppnum <> 2 |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <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 age <> 22 |
| +> group by age, salary |
| +> having salary <= |
| +> (select salary |
| +> from employee |
| +> group by salary, age |
| +> having age <=ALL |
| +> (select age |
| +> from employee |
| +> group by age |
| +> having age <40 |
| +> ) |
| +> ) |
| +> order by age; |
| |
| AGE |
| --- |
| |
| 19 |
| 24 |
| 24 |
| 26 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >>-- <testcase A4> |
| >> |
| >>-- <detail> |
| >>-- uncorrelated subquery in having clause test - this tests the use of |
| >>-- for read committed access, for read committed access, |
| >>-- and for read committed access in uncorrelated subqueries. |
| >> |
| >> |
| >>-- <templates> |
| >>-- US00 |
| >> |
| >>-- <comment> get part numbers, for parts supplied by supplier 15 |
| >>-- <comment> and supplied by at least one other supplier. |
| >>-- <ufi-input> |
| >> select partnum |
| +> from fromsup |
| +> where suppnum <> 15 |
| +> group by partnum |
| +> having partnum in |
| +> (select partnum |
| +> from fromsup |
| +> where suppnum = 15 |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| PARTNUM |
| ------- |
| |
| 4101 |
| 4102 |
| 4103 |
| 5101 |
| 5103 |
| 5504 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get salesman numbers and order numbers for salesman |
| >>-- <comment> who have orders for parts costing > 50000 |
| >>-- <ufi-input> |
| >> select salesman,ordernum |
| +> from orders |
| +> group by ordernum, salesman |
| +> having orders.ordernum in |
| +> (select odetail.ordernum |
| +> from odetail,parts |
| +> where odetail.partnum = parts.partnum |
| +> and price > 50000 |
| +> for read committed access |
| +> ); |
| |
| SALESMAN ORDERNUM |
| -------- -------- |
| |
| 204 32 |
| 205 21 |
| 205 66 |
| 207 41 |
| 210 51 |
| 212 25 |
| 212 45 |
| 218 38 |
| 221 122 |
| 222 30 |
| 225 48 |
| 231 35 |
| |
| --- 12 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who are in the same state |
| >>-- <comment> as supplier 1 (use aliases). |
| >> select suppnum |
| +> from supplier X |
| +> group by suppnum, X.state |
| +> having X.state = |
| +> (select Y.state |
| +> from supplier Y |
| +> where suppnum = 1 |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| SUPPNUM |
| ------- |
| |
| 1 |
| 2 |
| 3 |
| 10 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- <comment> get customer numbers for customers who have not ordered parts |
| >>-- <comment> stored at location 'V67' |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum <> |
| +> (select col_12 |
| +> from partsfor |
| +> where col_2 = 'V67' |
| +> for read committed access |
| +> ); |
| |
| CUSTNUM |
| ------- |
| |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 7777 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> same as above, only use a join instead of the view partsfor |
| >>-- <ufi-input> |
| >> select custnum |
| +> from customer |
| +> group by custnum |
| +> having custnum <> |
| +> (select custnum |
| +> from orders,odetail,parts |
| +> where ( orders.ordernum = odetail.ordernum) and |
| +> ( odetail.partnum = parts.partnum) and |
| +> (location = 'V67') |
| +> for read committed access |
| +> ); |
| |
| CUSTNUM |
| ------- |
| |
| 123 |
| 21 |
| 1234 |
| 3333 |
| 543 |
| 926 |
| 7654 |
| 324 |
| 7777 |
| 143 |
| 5635 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply part 4102 |
| >>-- <comment> and part 5504 |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having (suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 4102 |
| +> for read committed access) |
| +> ) |
| +> and (suppnum =ANY |
| +> (select suppnum |
| +> from fromsup |
| +> where partnum = 5504 |
| +> for read committed access) |
| +> ); |
| |
| SUPPNUM |
| ------- |
| |
| 6 |
| 15 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- <comment> get supplier numbers for suppliers who supply at least |
| >>-- <comment> one part whose inventory is negative (less than 0) |
| >>-- <ufi-input> |
| >> select suppnum |
| +> from supplier |
| +> group by suppnum |
| +> having suppnum in |
| +> (select suppnum |
| +> from fromsup |
| +> group by suppnum,partnum |
| +> having partnum in |
| +> (select partnum |
| +> from parts |
| +> where inventory < 0 |
| +> for read committed access |
| +> ) |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| SUPPNUM |
| ------- |
| |
| 2 |
| 15 |
| 3 |
| 6 |
| 1 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <comment> get age of those employees whose salary is less than or |
| >>-- <comment> equal to the salary of the youngest employee |
| >>-- <ufi-input> |
| >> select X.age |
| +> from employee X |
| +> group by age, salary |
| +> having X.salary <= |
| +> (select salary |
| +> from employee Y |
| +> group by age, salary |
| +> having Y.age <= ALL |
| +> (select Z.age |
| +> from employee Z |
| +> for read committed access |
| +> ) |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| AGE |
| --- |
| |
| 24 |
| 26 |
| 19 |
| 22 |
| 24 |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- <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 |
| +> group by salesman, ordernum |
| +> having ordernum in |
| +> (select distinct ordernum |
| +> from odetail,parts |
| +> group by ordernum, odetail.partnum, |
| +> parts.partnum, price |
| +> having odetail.partnum = parts.partnum |
| +> and price < |
| +> (select distinct price |
| +> from parts |
| +> where price = 8000 |
| +> for read committed access |
| +> ) |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| SALESMAN |
| -------- |
| |
| 204 |
| 205 |
| 207 |
| 210 |
| 212 |
| 218 |
| 221 |
| 222 |
| 225 |
| 231 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- <comment> get salesman names for salesman who work in the Central region |
| >>-- <ufi-input> |
| >> select empname |
| +> from salecust |
| +> group by empname |
| +> having empname in |
| +> (select empname |
| +> from emppub |
| +> group by empname, regnum |
| +> having regnum in |
| +> (select regnum |
| +> from region |
| +> where regname = 'CENTRAL' |
| +> for read committed access |
| +> ) |
| +> for read committed access |
| +> ) |
| +> for read committed access; |
| |
| EMPNAME |
| ------------------ |
| |
| MARK FOLEY |
| RICHARD BARTON |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >> |
| >>-- <testcase A5> |
| >> |
| >>-- <detail> |
| >>-- select uncorrelated subquery in HAVING 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 region #, branch # and the average payroll for the |
| >>-- <comment> branch(es) with the highest average payroll |
| >>-- <ufi-input> |
| >> select regnum,branchnum, avg(salary) |
| +> from employee |
| +> group by regnum, branchnum |
| +> having avg(salary) >=ALL |
| +> (select avg(salary) |
| +> from employee |
| +> group by regnum, branchnum |
| +> ); |
| |
| REGNUM BRANCHNUM (EXPR) |
| ------ --------- -------------------- |
| |
| 4 1 38750 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get region #, branch # and the average payroll for the |
| >>-- <comment> branch(es) with the highest TOTAL payroll |
| >>-- <ufi-input> |
| >> select regnum,branchnum, avg(salary) |
| +> from employee |
| +> group by regnum, branchnum |
| +> having sum(salary) >=ALL |
| +> (select sum(salary) |
| +> from employee |
| +> group by regnum, branchnum |
| +> ); |
| |
| REGNUM BRANCHNUM (EXPR) |
| ------ --------- -------------------- |
| |
| 99 1 28191 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <comment> get job, average salary, and average age for jobs whose average |
| >>-- <comment> salary is greater than the average salary of the job with the |
| >>-- <comment> lowest average employee age. |
| >>-- <ufi-input> |
| >> select job, avg(salary), avg(age) |
| +> from employee |
| +> group by job |
| +> having avg(salary) >=ALL |
| +> (select avg(salary) |
| +> from employee |
| +> group by job |
| +> having avg(age) <=ALL |
| +> (select avg(age) |
| +> from employee |
| +> group by job |
| +> ) |
| +> ); |
| |
| JOB (EXPR) (EXPR) |
| ------------ -------------------- -------------------- |
| |
| SALESMAN 29071 35 |
| SYS.-ANAL. 27600 31 |
| MANAGER 37000 36 |
| ENGINEER 29333 30 |
| PROGRAMMER 22666 31 |
| SYST.-ANAL 31407 35 |
| SECRETARY 22142 30 |
| |
| --- 7 row(s) selected. |
| >> -- ++++++ may take a long (an hour) time to run |
| >> |
| >>?ignore |
| >>-- <comment> get job, average salary, and average distinct age for jobs |
| >>-- <comment> whose average salary is greater than the average salary of the |
| >>-- <comment> job with the lowest average distinct employee age. |
| >>-- <ufi-input> |
| >> select job, avg(salary), avg(distinct age) |
| +> from employee |
| +> group by job |
| +> having avg(salary) >=ALL |
| +> (select avg(salary) |
| +> from employee |
| +> group by job |
| +> having avg(distinct age) <=ALL |
| +> (select avg(distinct age) |
| +> from employee |
| +> group by job |
| +> ) |
| +> ); |
| >> -- +++++ may take a long (an hour) time to run |
| >>?ignore |
| >> |
| >>-- <comment> get the salaries and minimum age of employees with that salary |
| >>-- <comment> where the salary is greater than the average salary for all |
| >>-- <comment> employees. |
| >>-- <ufi-input> |
| >> select salary, min(age) |
| +> from employee |
| +> group by salary |
| +> having salary > |
| +> (select avg(salary) |
| +> from employee |
| +> ); |
| |
| SALARY (EXPR) |
| ---------- ------ |
| |
| 39500 37 |
| 50000 21 |
| 37000 34 |
| 39000 31 |
| 31000 32 |
| 32222 36 |
| 36000 39 |
| 35000 35 |
| 32000 28 |
| 33000 31 |
| 38000 39 |
| 40000 41 |
| |
| --- 12 row(s) selected. |
| >> |
| >>-- <comment> get the salaries and minimum age of employees with that salary |
| >>-- <comment> where the salary is greater than the 'median' salary for all |
| >>-- <comment> employees. |
| >>-- <ufi-input> |
| >> select salary, min(age) |
| +> from employee |
| +> group by salary |
| +> having salary > |
| +> (select min(salary) + ((max(salary) - min(salary)) / 2) |
| +> from employee |
| +> ); |
| |
| SALARY (EXPR) |
| ---------- ------ |
| |
| 39500 37 |
| 50000 21 |
| 37000 34 |
| 39000 31 |
| 32222 36 |
| 36000 39 |
| 35000 35 |
| 32000 28 |
| 33000 31 |
| 38000 39 |
| 40000 41 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- <comment> get the salaries and minimum age of employees with that |
| >>-- <comment> salary where the minimum age is greater than the average age |
| >>-- <comment> of all employees |
| >>-- <ufi-input> |
| >> select salary, min(age) |
| +> from employee |
| +> group by salary |
| +> having min(age) > |
| +> (select avg(age) |
| +> from employee |
| +> ); |
| |
| SALARY (EXPR) |
| ---------- ------ |
| |
| 39500 37 |
| 22000 55 |
| 32222 36 |
| 36000 39 |
| 35000 35 |
| 27000 37 |
| 38000 39 |
| 40000 41 |
| |
| --- 8 row(s) selected. |
| >> |
| >>-- <comment> get the salaries and the number of employees who make that |
| >>-- <comment> salary, such that more employees make that salary than any |
| >>-- <comment> other |
| >>-- <ufi-input> |
| >> select salary, count(*) |
| +> from employee |
| +> group by salary |
| +> having count(*) >=ALL |
| +> (select count(*) |
| +> from employee |
| +> group by salary |
| +> ); |
| |
| SALARY (EXPR) |
| ---------- -------------------- |
| |
| 32000 6 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- <end-input> |
| >> |
| >> |
| >>-- <end-test> |
| >>LOG; |
