Google Git
Sign in
apache / trafodion / refs/tags/2.3.0rc1 / . / core / sql / regress / qat / eqatdml10
blob: 9f0195ae1f813ca4c0fe32f9d541744e6b31f189 [file] [log] [blame]
>>---------------------------------------------------------------------
>>-- Component: NonStop SQL Regression Test Suite
>>-- Description:
>>-- This test unit is a positive local test for the select statement.
>>--It tests SELECT statements with correlated subqueries in their WHERE
>>--clauses.
>>-- This test unit uses the sqldd01d.orders-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: correlated subquery with IN clause between outer query
>>-- and subquery
>>-- testcase A1: correlated subquery with simple and quantified comparison
>>-- operators between outer query and subquery
>>-- testcase A2: correlated subquery with translated IN form of EXISTS/
>>-- NOT EXISTS
>>-- testcase A3: correlated subquery with FOR ALL/implication form of
>>-- EXISTS/NOT EXISTS
>>-- testcase A4: multiple nesting,multiple subqueries connected with
>>-- and/or,mixed correlated/uncorrelated nested subqueries
>>-- testcase A5: use of for read committed access,
>>-- for read committed access,for read committed access
>>-- testcase A6: SELECT aggregate functions with aggregates in
>>-- subquery
>>
>>--all testcases are documented further below.
>>
>>--*************** end test unit comments ***************************
>>
>>--<testunit-summary>
>>--select #9 pos/loc ufi subq cor where
>>
>>--<testunit-specs>
>>
>>--mode-type ufi
>>--test-type functional
>>--form-type pos/loc
>>--select-test
>>
>>--<testcase A0>
>>
>>-- <detail>
>>-- correlated 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 4102 in
+> (select partnum
+> from fromsup
+> where supplier.suppnum = suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> same as above , add fromsup as qualifier for suppnum
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where supplier.suppnum = fromsup.suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who DON'T supply part 4102
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 NOT in
+> (select partnum
+> from fromsup
+> where supplier.suppnum = suppnum);
SUPPNAME
------------------
DATA TERMINAL CO
DISPLAY INC
STEELWORK INC
TANDEM COMPUTERS
--- 4 row(s) selected.
>>
>>-- <comment> same as first, except use alias
>>-- <ufi-input>
>> select suppname
+> from supplier X
+> where 4102 in
+> (select partnum
+> from fromsup
+> where X.suppnum = suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> get part names whose price is equal to its' cost from
>>-- <comment> at least one supplier
>>-- <ufi-input>
>> select partname
+> from parts
+> where price IN
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>-- <comment> same as above, except eliminate parts costing 20100
>>-- <ufi-input>
>> select partname
+> from parts
+> where price IN
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> having partcost <> 20100
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>
>>-- <comment> same as above, except eliminate 'group by' and use 'distinct'
>>-- <ufi-input>
>> select partname
+> from parts
+> where price IN
+> (select distinct partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>-- <comment> get region names where the manager's employee # is equal
>>-- <comment> to 1.
>>-- <ufi-input>
>> select regname
+> from region
+> where 1 in
+> (select *
+> from empone
+> where empone.empnum = region.manager);
REGNAME
------------
HEADQUARTERS
--- 1 row(s) selected.
>>
>>-- <comment> same as above, without qualifiers
>>-- <ufi-input>
>> select regname
+> from region
+> where 1 in
+> (select *
+> from empone
+> where empnum = manager);
REGNAME
------------
HEADQUARTERS
--- 1 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>
>>-- <comment> same as above, with 'distinct Y.partnum'
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select distinct Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, with 'group by Y.partnum'
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, with 'group by X.partnum' instead of
>>-- <comment> 'distinct X.partnum'
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> )
+> group by X.partnum;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, but with two group by clauses
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> )
+> group by X.partnum;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>
>>-- <comment> use having clause to eliminate part # 4101 in subquery
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> having Y.partnum <> 4101
+> )
+> group by X.partnum;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> use having clause to eliminate part #4101 in outer query
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> )
+> group by X.partnum
+> having X.partnum <> 4101;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> use having clause in both outer and subquery
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> having Y.partnum <> 4101
+> )
+> group by X.partnum
+> having X.partnum <> 4101;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> get salesman #'s and order #'s for salesman who have orders
>>-- <comment> for parts stored at location H76
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' in
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as above, use 'distinct parts.location'
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' in
+> (select distinct parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as first, use 'group by parts.location' to eliminate
>>-- <comment> duplicates
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' in
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum)
+> group by parts.location );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as first, except order by ordernum
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' in
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) )
+> order by ordernum;
SALESMAN ORDERNUM
-------- --------
222 30
204 32
218 38
207 41
212 45
225 48
205 66
221 122
--- 8 row(s) selected.
>>
>>-- <comment> get part names whose cost is greater than 10000 and whose
>>-- <comment> price is equal to its' cost from at least one supplier
>>-- <comment> NOTE: the subquery here returns 0 records sometimes.
>>-- <ufi-input>
>> select partname
+> from parts
+> where price IN
+> (select partcost
+> from expfroms
+> where parts.partnum = expfroms.partnum
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DISC CONT DUAL
--- 4 row(s) selected.
>>
>>-- <comment> get salesman numbers for salesman who have already sold
>>-- <comment> a previous order (a lower order number is a previous
>>-- <comment> order)
>>-- <ufi-input>
>> select salesman
+> from orders X
+> where salesman in
+> (select salesman
+> from orders Y
+> where Y.ordernum < X.ordernum
+> );
SALESMAN
--------
205
212
--- 2 row(s) selected.
>>
>>-- <comment> get employee names who do not work at first branches
>>-- <comment> (eliminate duplicates)
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 not in
+> (select branchnum
+> from branch
+> where employee.branchnum = branch.branchnum
+> group by branchnum
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> same as above, use DISTINCT instead of GROUP BY
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 not in
+> (select distinct branchnum
+> from branch
+> where employee.branchnum = branch.branchnum );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> same as query before last, but use HAVING with GROUP BY
>>-- <comment> instead of WHERE
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 not in
+> (select branchnum
+> from branch
+> group by branchnum
+> having branchnum = employee.branchnum
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <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 15 in
+> (select suppnum
+> from fromsup Y
+> where Y.partnum = X.partnum
+> );
SUPPNUM
-------
6
15
8
2
--- 4 row(s) selected.
>>
>>-- <comment> get supplier numbers for suppliers who are in the same
>>-- <comment> state as supplier 1.
>>-- <ufi-input>
>> select suppnum
+> from supplier X
+> where 1 in
+> (select suppnum
+> from supplier Y
+> where Y.state = X.state
+> );
SUPPNUM
-------
1
2
3
10
--- 4 row(s) selected.
>>
>>-- <comment> get customer names for customers who have not ordered parts
>>-- <comment> stored at location 'V67'
>>-- <ufi-input>
>> select custname
+> from customer
+> where 'V67' not in
+> (select col_2
+> from partsfor
+> where custnum = col_12
+> );
CUSTNAME
------------------
BROWN MEDICAL CO
CENTRAL UNIVERSITY
DATASPEED
DEUTSCHE STAHL
FRESNO STATE BANK
METALL-AG.
MOTOR DISTRIBUTING
PREMIER INSURANCE
SLEEPWELL HOTELS
STEVENS SUPPLY
VEREINIGTE CHEMIE
--- 11 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>
>>-- <testcase A1>
>>
>>-- <detail>
>>-- correlated subquery in where clause test - this tests the use
>>-- of simple comparison operators (<,<=,=,<>,>,>=) and quantified
>>-- comparison operators (=ANY,>ALL,etc.) as the relational operator
>>-- between the outer and subqueries. Note that some of the subqueries
>>-- in this testcase return 0 records (empty sets ) sometimes.
>>
>>-- <templates>
>>-- US00
>>
>>-- <comment> get employee name who is manager of region 1
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1=
+> (select regnum
+> from region
+> where manager = empnum);
EMPNAME
------------------
JACK RAYMOND
--- 1 row(s) selected.
>>
>>
>>-- <comment> same as above, add qualifications
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1=
+> (select regnum
+> from region
+> where region.manager = employee.empnum);
EMPNAME
------------------
JACK RAYMOND
--- 1 row(s) selected.
>>
>>-- <comment> get customer names whose salesman is not salesman #212
>>-- <ufi-input>
>> select custname
+> from customer
+> where 212 not in
+> (select col_11
+> from partsfor
+> where customer.custnum = partsfor.col_12
+> );
CUSTNAME
------------------
BESTFOOD MARKETS
BROWN MEDICAL CO
CENTRAL UNIVERSITY
DATASPEED
DEUTSCHE STAHL
FRESNO STATE BANK
METALL-AG.
MOTOR DISTRIBUTING
STEVENS SUPPLY
VEREINIGTE CHEMIE
--- 10 row(s) selected.
>>
>>-- <comment> same as above, use join instead of view partsfor
>>-- <ufi-input>
>> select custname
+> from customer
+> where 212 not in
+> (select salesman
+> from orders,odetail,parts
+> where ( orders.ordernum = odetail.ordernum) and
+> ( odetail.partnum = parts.partnum) and
+> ( customer.custnum = orders.custnum) );
CUSTNAME
------------------
BESTFOOD MARKETS
BROWN MEDICAL CO
CENTRAL UNIVERSITY
DATASPEED
DEUTSCHE STAHL
FRESNO STATE BANK
METALL-AG.
MOTOR DISTRIBUTING
STEVENS SUPPLY
VEREINIGTE CHEMIE
--- 10 row(s) selected.
>>
>>-- <comment> get part names for parts whose price is less than 2000
>>-- <comment> more than its' cost from all suppliers
>>-- <ufi-input>
>> select partname
+> from parts
+> where (price - 2000) <all
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
MAG TAPE DR 800BPI
MAG TAPE DR 8/16
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
TERM CRT CHAR
TERM CRT PAGE
TERM HARD COPY
POWER MODULE
--- 21 row(s) selected.
>>
>>-- <comment> get part names for parts who have the same price as at
>>-- <comment> least one other part
>>-- <ufi-input>
>> select col_1
+> from partsfor X
+> where X.col_3 = SOME
+> (select Y.col_3
+> from partsfor Y
+> where X.col_1 <> Y.col_1
+> );
COL_1
------------------
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
MAG TAPE CONT 8/16
DISC 10MB
DISC 10MB
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
TERM CRT CHAR
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
--- 27 row(s) selected.
>>
>>
>>-- <comment> get supplier numbers and part number for suppliers whose
>>-- <comment> cost for that part is less than or equal to some other
>>-- <comment> suppliers cost for that part
>>-- <comment> note: subquery returns 0 records sometimes
>>-- <ufi-input>
>> select suppnum,partnum
+> from fromsup X
+> where partcost <=SOME
+> (select partcost
+> from fromsup Y
+> where X.suppnum <> Y.suppnum
+> and X.partnum = Y.partnum
+> );
SUPPNUM PARTNUM
------- -------
6 4101
15 4101
6 4102
15 4102
8 4103
15 4103
8 5101
8 5103
2 5504
6 5504
3 6401
2 6402
--- 12 row(s) selected.
>>
>>-- <comment> same as above, use <=ANY instead of SOME
>>-- <ufi-input>
>> select suppnum,partnum
+> from fromsup X
+> where partcost <=ANY
+> (select partcost
+> from fromsup Y
+> where X.suppnum <> Y.suppnum
+> and X.partnum = Y.partnum
+> );
SUPPNUM PARTNUM
------- -------
6 4101
15 4101
6 4102
15 4102
8 4103
15 4103
8 5101
8 5103
2 5504
6 5504
3 6401
2 6402
--- 12 row(s) selected.
>>
>>-- <comment> same as above, use <ALL instead
>>-- <ufi-input>
>> select suppnum,partnum
+> from fromsup X
+> where partcost <ALL
+> (select partcost
+> from fromsup Y
+> where X.suppnum <> Y.suppnum
+> and X.partnum = Y.partnum
+> );
SUPPNUM PARTNUM
------- -------
1 212
1 244
1 1403
1 2001
1 2002
1 2003
1 2402
1 2403
1 3102
1 3103
1 3201
1 3302
6 4102
8 4103
8 5101
8 5103
2 5502
6 5504
15 5505
1 6201
1 6301
1 6302
3 6401
2 6402
2 6603
10 7102
1 7301
--- 27 row(s) selected.
>>
>>-- <comment> get supplier numbers for other suppliers who supply
>>-- <comment> at least one part supplied by supplier 15.
>>-- <comment> note: subquery returns 0 records (an empty set) sometimes
>>-- <ufi-input>
>> select distinct suppnum
+> from fromsup X
+> where X.partnum =SOME
+> (select Y.partnum
+> from fromsup Y
+> where (X.suppnum <> Y.suppnum)
+> and (Y.suppnum = 15)
+> );
SUPPNUM
-------
6
8
2
--- 3 row(s) selected.
>>
>>-- <comment> get supplier numbers who are in the same state as supplier 1
>>-- <ufi-input>
>> select suppnum
+> from supplier X
+> where 1 =ANY
+> (select suppnum
+> from supplier Y
+> where X.state = Y.state
+> );
SUPPNUM
-------
1
2
3
10
--- 4 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who supply part 4102
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 =SOME
+> (select partnum
+> from fromsup
+> where supplier.suppnum = suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> same as above , add fromsup as qualifier for suppnum
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 =ANY
+> (select partnum
+> from fromsup
+> where supplier.suppnum = fromsup.suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who DON'T supply part 4102
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 <>SOME
+> (select partnum
+> from fromsup
+> where supplier.suppnum = suppnum);
SUPPNAME
------------------
DATA TERMINAL CO
DATADRIVE
DISPLAY INC
INFOMATION STORAGE
MAGNETICS CORP
STEELWORK INC
TANDEM COMPUTERS
--- 7 row(s) selected.
>>
>>-- <comment> same as first, except use alias
>>-- <ufi-input>
>> select suppname
+> from supplier X
+> where 4102 =ANY
+> (select partnum
+> from fromsup
+> where X.suppnum = suppnum);
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> get part names whose price is equal to its' cost from
>>-- <comment> at least one supplier
>>-- <ufi-input>
>> select partname
+> from parts
+> where price =SOME
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>-- <comment> same as above, except eliminate parts costing 20100
>>-- <ufi-input>
>> select partname
+> from parts
+> where price =ANY
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> having partcost <> 20100
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>
>>-- <comment> same as above, except eliminate 'group by' and use 'distinct'
>>-- <ufi-input>
>> select partname
+> from parts
+> where price =SOME
+> (select distinct partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
--- 15 row(s) selected.
>>
>>-- <comment> get region names where the manager's employee # is equal
>>-- <comment> to 1.
>>-- <ufi-input>
>> select regname
+> from region
+> where 1 =ANY
+> (select *
+> from empone
+> where empone.empnum = region.manager);
REGNAME
------------
HEADQUARTERS
--- 1 row(s) selected.
>>
>>-- <comment> same as above, without qualifiers
>>-- <ufi-input>
>> select regname
+> from region
+> where 1 =SOME
+> (select *
+> from empone
+> where empnum = manager);
REGNAME
------------
HEADQUARTERS
--- 1 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum =ANY
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>
>>-- <comment> same as above, with 'distinct Y.partnum'
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum =SOME
+> (select distinct Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, with 'group by Y.partnum'
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum =ANY
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, with 'group by X.partnum' instead of
>>-- <comment> 'distinct X.partnum'
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum =SOME
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> )
+> group by X.partnum;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> same as first, but with two group by clauses
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum =ANY
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> )
+> group by X.partnum;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>
>>-- <comment> use having clause to eliminate part # 4101 in subquery
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum =SOME
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> having Y.partnum <> 4101
+> )
+> group by X.partnum;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> use having clause to eliminate part #4101 in outer query
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum =ANY
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> )
+> group by X.partnum
+> having X.partnum <> 4101;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> use having clause in both outer and subquery
>>-- <ufi-input>
>> select X.partnum
+> from fromsup X
+> where X.partnum =SOME
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> group by Y.partnum
+> having Y.partnum <> 4101
+> )
+> group by X.partnum
+> having X.partnum <> 4101;
PARTNUM
-------
4102
4103
5101
5103
5504
6401
6402
--- 7 row(s) selected.
>>
>>-- <comment> get salesman #'s and order #'s for salesman who have orders
>>-- <comment> for parts stored at location H76
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' =ANY
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as above, use 'distinct parts.location'
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' =SOME
+> (select distinct parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as first, use 'group by parts.location' to eliminate
>>-- <comment> duplicates
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' =ANY
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum)
+> group by parts.location );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> same as first, except order by ordernum
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' =SOME
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) )
+> order by ordernum;
SALESMAN ORDERNUM
-------- --------
222 30
204 32
218 38
207 41
212 45
225 48
205 66
221 122
--- 8 row(s) selected.
>>
>>-- <comment> get part names whose cost is greater than 10000 and whose
>>-- <comment> price is equal to its' cost from at least one supplier
>>-- <comment> NOTE: the subquery here returns 0 records sometimes.
>>-- <ufi-input>
>> select partname
+> from parts
+> where price =ANY
+> (select partcost
+> from expfroms
+> where parts.partnum = expfroms.partnum
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DISC CONT DUAL
--- 4 row(s) selected.
>>
>>-- <comment> get salesman numbers for salesman who have already sold
>>-- <comment> a previous order (a lower order number is a previous
>>-- <comment> order)
>>-- <ufi-input>
>> select salesman
+> from orders X
+> where salesman =SOME
+> (select salesman
+> from orders Y
+> where Y.ordernum < X.ordernum
+> );
SALESMAN
--------
205
212
--- 2 row(s) selected.
>>
>>-- <comment> get employee names who do not work at first branches
>>-- <comment> (eliminate duplicates)
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 <>ANY
+> (select branchnum
+> from branch
+> where employee.branchnum = branch.branchnum
+> group by branchnum
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> same as above, use DISTINCT instead of GROUP BY
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 <>SOME
+> (select distinct branchnum
+> from branch
+> where employee.branchnum = branch.branchnum );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> same as query before last, but use HAVING with GROUP BY
>>-- <comment> instead of WHERE
>>-- <ufi-input>
>> select empname
+> from employee
+> where 1 <>ANY
+> (select branchnum
+> from branch
+> group by branchnum
+> having branchnum = employee.branchnum
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> get region names where the managers' employee number is
>>-- <comment> less than 100
>>-- <ufi-input>
>> select regname
+> from region
+> where 100 >
+> (select empnum
+> from empone
+> where empone.empnum = region.manager
+> );
REGNAME
------------
EAST
WEST
GERMANY
ENGLAND
HEADQUARTERS
--- 5 row(s) selected.
>>
>>-- <comment> get customer names for customers who have not ordered parts
>>-- <comment> stored at location 'V67'
>>-- <ufi-input>
>> select custname
+> from customer
+> where 'V67' <>SOME
+> (select col_2
+> from partsfor
+> where custnum = col_12
+> );
CUSTNAME
------------------
BESTFOOD MARKETS
BROWN MEDICAL CO
CENTRAL UNIVERSITY
DATASPEED
DEUTSCHE STAHL
FRESNO STATE BANK
METALL-AG.
MOTOR DISTRIBUTING
PREMIER INSURANCE
SLEEPWELL HOTELS
STEVENS SUPPLY
VEREINIGTE CHEMIE
--- 12 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>
>>-- <testcase A2>
>>
>>-- <detail>
>>-- correlated subquery in where clause test - this tests the use of
>>-- a correlated subquery with translated IN form of EXISTS/NOT EXISTS
>>-- quanitifiers.
>>
>>-- <templates>
>>-- US00
>>
>>-- <comment> get supplier names for suppliers who supply part 4102
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where exists
+> (select *
+> from fromsup
+> where supplier.suppnum = suppnum
+> and partnum = 4102
+> );
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> same as above, change 'select *' to 'select partnum'
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where exists
+> (select partnum
+> from fromsup
+> where supplier.suppnum = suppnum
+> and partnum = 4102
+> );
SUPPNAME
------------------
DATADRIVE
INFOMATION STORAGE
MAGNETICS CORP
--- 3 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who DON'T supply part 4102
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where not exists
+> (select *
+> from fromsup
+> where supplier.suppnum = suppnum
+> and partnum = 4102
+> );
SUPPNAME
------------------
DATA TERMINAL CO
DISPLAY INC
STEELWORK INC
TANDEM COMPUTERS
--- 4 row(s) selected.
>>
>>-- <comment> get region names where the manager's employee # is equal
>>-- <comment> to 1.
>>-- <ufi-input>
>> select regname
+> from region
+> where exists
+> (select *
+> from empone
+> where empone.empnum = region.manager
+> and empnum = 1
+> );
REGNAME
------------
HEADQUARTERS
--- 1 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than
>>-- <comment> one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where exists
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> and X.partnum = Y.partnum
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> get salesman #'s and order #'s for salesman who have orders
>>-- <comment> for parts stored at location H76
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where exists
+> (select *
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum) and
+> ( parts.location = 'H76') );
SALESMAN ORDERNUM
-------- --------
204 32
205 66
207 41
212 45
218 38
221 122
222 30
225 48
--- 8 row(s) selected.
>>
>>-- <comment> get part names whose cost is greater than 10000 and whose
>>-- <comment> price is equal to its' cost from at least one supplier
>>-- <ufi-input>
>> select partname
+> from parts
+> where exists
+> (select *
+> from expfroms
+> where ( parts.partnum = expfroms.partnum)
+> and (price = partcost)
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DISC CONT DUAL
--- 4 row(s) selected.
>>
>>-- <comment> get salesman numbers for salesman who have already sold
>>-- <comment> a previous order (a lower order number is a previous order)
>>-- <ufi-input>
>> select salesman
+> from orders X
+> where exists
+> (select salesman
+> from orders Y
+> where (Y.ordernum < X.ordernum)
+> and (X.salesman = Y.salesman)
+> );
SALESMAN
--------
205
212
--- 2 row(s) selected.
>>
>>-- <comment> get employee names who do not work at first branches
>>-- <comment> (eliminate duplicates)
>>-- <ufi-input>
>> select empname
+> from employee
+> where not exists
+> (select branchnum
+> from branch
+> where ( employee.branchnum = branch.branchnum)
+> and (branchnum = 1)
+> group by branchnum
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <comment> get employee names who do not work at first branches
>>-- <comment> eliminate duplicates and use having clause
>>-- <ufi-input>
>> select empname
+> from employee
+> where not exists
+> (select branchnum
+> from branch
+> where ( employee.branchnum = branch.branchnum)
+> group by branchnum
+> having branchnum = 1
+> );
EMPNAME
------------------
THOMAS RUDLOFF
KLAUS SAFFERT
PETER SMITH
DONALD TAYLOR
STEVE COOK
SHERRIE WONG
TONY CRAFT
GEORGE FORSTER
RICHARD BARTON
JIMMY SCHNEIDER
WALTER LANCASTER
JOHN JONES
MARLENE BONNY
GEORGE FRENCHMAN
DAVID TERRY
KARL HELMSTED
HEIDI WEIGL
XAVER SEDLMEYER
DAVE CLARK
--- 19 row(s) selected.
>>
>>-- <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 exists
+> (select *
+> from fromsup Y
+> where (Y.partnum = X.partnum)
+> and (suppnum = 15)
+> );
SUPPNUM
-------
6
15
8
2
--- 4 row(s) selected.
>>
>>-- <comment> get supplier numbers for suppliers who are in the same state
>>-- <comment> as supplier 1
>>-- <ufi-input>
>> select suppnum
+> from supplier X
+> where exists
+> (select suppnum
+> from supplier Y
+> where (Y.state = X.state)
+> and (suppnum = 1)
+> );
SUPPNUM
-------
1
2
3
10
--- 4 row(s) selected.
>>
>>-- <comment> get customer names for customers who have not ordered parts
>>-- <comment> stored at location V67
>>-- <ufi-input>
>> select custname
+> from customer
+> where not exists
+> (select *
+> from partsfor
+> where (custnum = col_12)
+> and (col_2 = 'V67')
+> );
CUSTNAME
------------------
BROWN MEDICAL CO
CENTRAL UNIVERSITY
DATASPEED
DEUTSCHE STAHL
FRESNO STATE BANK
METALL-AG.
MOTOR DISTRIBUTING
PREMIER INSURANCE
SLEEPWELL HOTELS
STEVENS SUPPLY
VEREINIGTE CHEMIE
--- 11 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>
>>-- <testcase A3>
>>
>>-- <detail>
>>-- correlated subquery in where clause test - this tests the use
>>-- of EXIST/NOT EXISTS to simulate the use of FOR ALL.
>>
>>-- <templates>
>>-- US00
>>
>>-- <comment> get suppliers names for suppliers who supply all parts
>>-- <comment> with part numbers between 4000 and 5200.
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where not exists
+> (select *
+> from parts
+> where (partnum between 4000 and 5200)
+> and not exists
+> (select *
+> from fromsup
+> where ( parts.partnum = fromsup.partnum)
+> and ( supplier.suppnum = fromsup.suppnum)
+> )
+> );
SUPPNAME
------------------
DATADRIVE
--- 1 row(s) selected.
>>
>>
>>-- <comment> same as above, except use 'distinct *'
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where not exists
+> (select distinct *
+> from parts
+> where (partnum between 4000 and 5200)
+> and not exists
+> (select distinct *
+> from fromsup
+> where ( parts.partnum = fromsup.partnum)
+> and ( supplier.suppnum = fromsup.suppnum)
+> )
+> );
SUPPNAME
------------------
DATADRIVE
--- 1 row(s) selected.
>>
>>
>>-- <comment> same as above, except use 'distinct partnum'
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where not exists
+> (select distinct partnum
+> from parts
+> where (partnum between 4000 and 5200)
+> and not exists
+> (select distinct partnum
+> from fromsup
+> where ( parts.partnum = fromsup.partnum)
+> and ( supplier.suppnum = fromsup.suppnum)
+> )
+> );
SUPPNAME
------------------
DATADRIVE
--- 1 row(s) selected.
>>
>>-- <comment> get supplier numbers for suppliers who supply at least
>>-- <comment> all those parts supplied by supplier 6.
>>-- <ufi-input>
>> select distinct suppnum
+> from fromsup X
+> where not exists
+> (select partnum
+> from fromsup Y
+> where suppnum = 6
+> and not exists
+> (select *
+> from fromsup Z
+> where X.suppnum = Z.suppnum
+> and Z.partnum = Y.partnum
+> )
+> );
SUPPNUM
-------
6
15
--- 2 row(s) selected.
>>
>>
>>-- <comment> same as above, except use 'group by suppnum' instead of
>>-- <comment> select distinct suppnum to eliminate duplicates
>>-- <ufi-input>
>> select suppnum
+> from fromsup X
+> where not exists
+> (select *
+> from fromsup Y
+> where suppnum = 6
+> and not exists
+> (select *
+> from fromsup Z
+> where X.suppnum = Z.suppnum
+> and Z.partnum = Y.partnum
+> )
+> )
+> group by suppnum;
SUPPNUM
-------
6
15
--- 2 row(s) selected.
>>
>>-- <comment> get customer numbers for customers who have not
>>-- <comment> (ordered any parts which are in locations beginning
>>-- <comment> with 'k' and are supplied by suppliers located in
>>-- <comment> 'California')
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select *
+> from odetail
+> where orders.ordernum = odetail.ordernum
+> and partnum in
+> (select partnum
+> from parts
+> where location like 'k%'
+> )
+> and partnum in
+> (select partnum
+> from fromsup,supplier
+> where fromsup.suppnum = supplier.suppnum
+> and supplier.state = 'California'
+> )
+> );
CUSTNUM
-------
21
123
143
324
543
926
1234
3210
3333
5635
7654
7777
--- 12 row(s) selected.
>>
>>-- <comment> same as above, translate 'IN' to 'EXISTS'
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select *
+> from odetail
+> where orders.ordernum = odetail.ordernum
+> and exists
+> (select partnum
+> from parts
+> where location like 'k%'
+> and odetail.partnum = parts.partnum
+> )
+> and exists
+> (select partnum
+> from fromsup,supplier
+> where fromsup.suppnum = supplier.suppnum
+> and supplier.state = 'California'
+> and odetail.partnum = fromsup.partnum
+> )
+> );
CUSTNUM
-------
21
123
143
324
543
926
1234
3210
3333
5635
7654
7777
--- 12 row(s) selected.
>>
>>-- <comment> get part numbers for parts supplied to all customers
>>-- <comment> in California
>>-- <ufi-input>
>> select distinct partnum
+> from odetail X
+> where not exists
+> (select *
+> from orders, customer
+> where orders.custnum = customer.custnum
+> and customer.state = 'California'
+> and not exists
+> (select *
+> from odetail Y
+> where Y.partnum = X.partnum
+> and orders.ordernum = Y.ordernum
+> )
+> );
PARTNUM
-------
244
2001
2403
4103
5103
6301
6402
2002
2003
3102
4101
6302
2402
3202
3302
4102
5504
6201
3103
5503
5502
1403
7301
212
7102
5505
5101
6401
3201
--- 29 row(s) selected.
>>
>>-- <comment> same as above, except use nested subquery instead of join
>>-- <ufi-input>
>> select distinct partnum
+> from odetail X
+> where not exists
+> (select *
+> from orders
+> where orders.custnum in
+> (select custnum
+> from customer
+> where state = 'California'
+> )
+> and not exists
+> (select *
+> from odetail Y
+> where Y.partnum = X.partnum
+> and orders.ordernum = Y.ordernum
+> )
+> );
PARTNUM
-------
244
2001
2403
4103
5103
6301
6402
2002
2003
3102
4101
6302
2402
3202
3302
4102
5504
6201
3103
5503
5502
1403
7301
212
7102
5505
5101
6401
3201
--- 29 row(s) selected.
>>
>>-- <comment> get customer numbers for customers that have ordered
>>-- <comment> all parts supplied by supplier 6.
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select partnum
+> from fromsup
+> where suppnum = 6
+> and not exists
+> (select *
+> from odetail
+> where odetail.partnum = fromsup.partnum
+> and orders.ordernum = odetail.ordernum )
+> );
CUSTNUM
-------
3210
--- 1 row(s) selected.
>>
>>-- <comment> same as above,only more complex
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select partnum
+> from fromsup X
+> where exists
+> (select *
+> from fromsup Y
+> where Y.suppnum = 6
+> and Y.partnum = X.partnum
+> )
+> and not exists
+> (select *
+> from odetail
+> where odetail.partnum = x.partnum
+> and orders.ordernum = odetail.ordernum )
+> );
CUSTNUM
-------
3210
--- 1 row(s) selected.
>>
>>
>>-- <comment> same as above, but use EXISTS identity to move 'and' inside
>>-- <comment> the preceding subquery
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select partnum
+> from fromsup X
+> where exists
+> (select *
+> from fromsup Y
+> where Y.suppnum = 6
+> and Y.partnum = X.partnum
+> and not exists
+> (select *
+> from odetail
+> where odetail.partnum = X.partnum
+> and orders.ordernum = odetail.ordernum )
+> )
+> );
CUSTNUM
-------
3210
--- 1 row(s) selected.
>>
>>-- <comment> get customer numbers for customers such that every
>>-- <comment> part they have ordered is supplied by supplier 1 or
>>-- <comment> supplier 6.
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select *
+> from odetail
+> where orders.ordernum = odetail.ordernum
+> and not exists
+> (select partnum
+> from fromsup
+> where fromsup.partnum = odetail.partnum
+> and ((suppnum = 1) or (suppnum = 6))
+> )
+> );
CUSTNUM
-------
324
926
1234
7654
--- 4 row(s) selected.
>>
>>-- <comment> get customer numbers for customers who get parts
>>-- <comment> from all suppliers
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select distinct suppnum
+> from fromsup X
+> where not exists
+> (select *
+> from odetail, fromsup Y
+> where orders.ordernum = odetail.ordernum
+> and Y.partnum = odetail.partnum
+> and Y.suppnum = X.suppnum
+> )
+> );
CUSTNUM
-------
143
3210
--- 2 row(s) selected.
>>
>>-- <comment> same as above, except use additional subquery instead of
>>-- <comment> a join
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select distinct suppnum
+> from fromsup X
+> where not exists
+> (select *
+> from odetail
+> where orders.ordernum = odetail.ordernum
+> and partnum in
+> (select partnum
+> from fromsup Y
+> where Y.suppnum = X.suppnum
+> )
+> )
+> );
CUSTNUM
-------
3210
143
--- 2 row(s) selected.
>>
>>-- <comment> same as first, except use view FSDETAIL, which is a join of
>>-- <comment> odetail and fromsup through partnum, instead of using that
>>-- <comment> join explicitly
>>-- <ufi-input>
>> select custnum
+> from orders
+> where not exists
+> (select distinct suppnum
+> from fromsup
+> where not exists
+> (select *
+> from fsdetail
+> where orders.ordernum = col_4
+> and fsdetail.col_2 = fromsup.suppnum
+> )
+> );
--- 0 row(s) selected.
>>
>>-- <comment> get supplier numbers for which all customers use at
>>-- <comment> least one of their parts
>>-- <ufi-input>
>> select suppnum
+> from fromsup X
+> where not exists
+> (select custnum
+> from orders
+> where not exists
+> (select *
+> from odetail,fromsup Y
+> where odetail.ordernum = orders.ordernum
+> and odetail.partnum = Y.partnum
+> and Y.suppnum = X.suppnum
+> )
+> );
SUPPNUM
-------
1
1
1
1
15
15
15
15
1
1
1
1
1
15
1
1
1
1
1
1
15
15
1
--- 23 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>
>>-- <testcase A4>
>>
>>-- <detail>
>>-- correlated subquery in where clause test - 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 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> and 5504 =ANY
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> );
SUPPNAME
------------------
INFOMATION STORAGE
DATADRIVE
--- 2 row(s) selected.
>>
>>-- <comment> same as above,except one subquery correlated, other uncorrelated
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> and suppnum =ANY
+> (select suppnum
+> from fromsup
+> where partnum = 5504
+> );
SUPPNAME
------------------
INFOMATION STORAGE
DATADRIVE
--- 2 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who supply part 4102
>>-- <comment> OR 5504
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> OR 5504 =ANY
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> );
SUPPNAME
------------------
DATADRIVE
MAGNETICS CORP
DATA TERMINAL CO
INFOMATION STORAGE
--- 4 row(s) selected.
>>
>>-- <comment> same as above, except one subquery correlated, other uncorrelated
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> OR suppnum =ANY
+> (select suppnum
+> from fromsup
+> where partnum = 5504
+> );
SUPPNAME
------------------
DATADRIVE
DATA TERMINAL CO
MAGNETICS CORP
INFOMATION STORAGE
--- 4 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who DON'T supply parts
>>-- <comment> 4102 or 5504
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where NOT (4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> OR suppnum =ANY
+> (select suppnum
+> from fromsup
+> where partnum = 5504
+> ));
SUPPNAME
------------------
DISPLAY INC
STEELWORK INC
TANDEM COMPUTERS
--- 3 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who supply parts 4102,
>>-- <comment> 5504, and 5505
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> and 5504 =ANY
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> )
+> and suppnum =SOME
+> (select suppnum
+> from fromsup
+> where partnum = 5505
+> );
SUPPNAME
------------------
DATADRIVE
--- 1 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who supply at least
>>-- <comment> one part whose inventory is negative
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where suppnum in
+> (select suppnum
+> from fromsup
+> where 0 >
+> (select inventory
+> from parts
+> where parts.partnum = fromsup.partnum
+> )
+> );
SUPPNAME
------------------
DATA TERMINAL CO
DATADRIVE
DISPLAY INC
INFOMATION STORAGE
TANDEM COMPUTERS
--- 5 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
+> where 'LP 900 LPM' in
+> (select partname
+> from parts
+> where parts.partnum = fromsup.partnum
+> )
+> );
--- 0 row(s) selected.
>>
>>-- <comment> get employee names and their jobs for employees who do
>>-- <comment> not work at branches which are in the same city as the
>>-- <comment> regional headquarters
>>-- <ufi-input>
>> select empname,job
+> from employee
+> where branchnum not in
+> (select branchnum
+> from branch
+> where branch.regnum = employee.regnum
+> and branchname =
+> (select location
+> from region
+> where region.regnum = employee.regnum
+> )
+> );
EMPNAME JOB
------------------ ------------
THOMAS RUDLOFF MANAGER
KLAUS SAFFERT MANAGER
PETER SMITH MANAGER
DONALD TAYLOR MANAGER
STEVE COOK MANAGER
SHERRIE WONG MANAGER
TONY CRAFT MANAGER
GEORGE FORSTER SALESMAN
RICHARD BARTON SALESMAN
JIMMY SCHNEIDER SYS.-ANAL.
WALTER LANCASTER SALESMAN
JOHN JONES SYS.-ANAL.
MARLENE BONNY SYS.-ANAL.
GEORGE FRENCHMAN SALESMAN
DAVID TERRY PROGRAMMER
KARL HELMSTED SALESMAN
HEIDI WEIGL SECRETARY
XAVER SEDLMEYER SYST.-ANAL
DAVE CLARK MANAGER
--- 19 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than
>>-- <comment> two suppliers
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> and Y.partnum in
+> (select Z.partnum
+> from fromsup Z
+> where Z.suppnum <> Y.suppnum
+> and Z.suppnum <> X.suppnum
+> )
+> );
PARTNUM
-------
4102
4103
5504
--- 3 row(s) selected.
>>
>>-- <comment> same as above, use view fsdetail instead
>>-- <ufi-input>
>> select distinct X.col_1
+> from fsdetail X
+> where X.col_1 in
+> (select Y.col_1
+> from fsdetail Y
+> where Y.col_2 <> X.col_2
+> and Y.col_1 in
+> (select Z.col_1
+> from fsdetail Z
+> where Z.col_2 <> Y.col_2
+> and Z.col_2 <> X.col_2
+> )
+> );
COL_1
-----
4103
--- 1 row(s) selected.
>>
>>-- <comment> same as above, except use table fromsup instead of fsdetail
>>-- <comment> for one of the fsdetail occurences
>>-- <ufi-input>
>> select distinct partnum
+> from fromsup
+> where partnum in
+> (select Y.col_1
+> from fsdetail Y
+> where Y.col_2 <> fromsup.suppnum
+> and Y.col_1 in
+> (select Z.col_1
+> from fsdetail Z
+> where Z.col_2 <> Y.col_2
+> and Z.col_2 <> fromsup.suppnum
+> )
+> );
PARTNUM
-------
4103
--- 1 row(s) selected.
>>
>>-- <comment> get salesman numbers and order numbers for salesman who
>>-- <comment> have orders for parts stored at location H76 and which
>>-- <comment> have a cost less than 2500 from all suppliers that supply
>>-- <comment> that part
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where 'H76' in
+> (select parts.location
+> from odetail,parts
+> where ( odetail.partnum = parts.partnum)
+> and ( orders.ordernum = odetail.ordernum)
+> and 2500 >ALL
+> (select partcost
+> from fromsup
+> where fromsup.partnum = odetail.partnum
+> )
+> );
SALESMAN ORDERNUM
-------- --------
205 66
207 41
212 45
221 122
225 48
--- 5 row(s) selected.
>>
>>-- <comment> same as above, except use additional subquery instead of join
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where ordernum in
+> (select ordernum
+> from odetail
+> where 'H76' in
+> (select location
+> from parts
+> where partnum = odetail.partnum
+> and 2500 >ALL
+> (select partcost
+> from fromsup
+> where fromsup.partnum = odetail.partnum
+> )
+> )
+> );
SALESMAN ORDERNUM
-------- --------
205 66
207 41
212 45
221 122
225 48
--- 5 row(s) selected.
>>-- <comment> subquery
>>-- <ufi-input>
>> select salesman,ordernum
+> from orders
+> where ordernum in
+> (select ordernum
+> from odetail
+> where 'H76' in
+> (select location
+> from parts
+> where partnum = odetail.partnum
+> )
+> and 2500 >ALL
+> (select partcost
+> from fromsup
+> where fromsup.partnum = odetail.partnum
+> )
+> );
SALESMAN ORDERNUM
-------- --------
205 66
207 41
212 45
221 122
225 48
--- 5 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>
>>-- <testcase A5>
>>
>>-- <detail>
>>-- correlated subquery in where clause test - this tests the use of
>>-- for read committed access, for read committed access,
>>-- and for read committed access inside of subqueries.
>>
>>-- <templates>
>>-- US00
>>
>>-- <comment> get part numbers for all parts supplied by more than one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select distinct Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> for read committed access
+> )
+> for read committed access;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select distinct Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> for read committed access
+> )
+> for read committed access;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select distinct Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> for read committed access
+> )
+> for read committed access;
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> get part names whose cost is greater than 10000 and whose
>>-- <comment> price is equal to its' cost from at least one supplier
>>-- <comment> NOTE: the subquery here returns 0 records sometimes.
>>-- <ufi-input>
>> select partname
+> from parts
+> where price IN
+> (select partcost
+> from expfroms
+> where parts.partnum = expfroms.partnum
+> for read committed access
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DISC CONT DUAL
--- 4 row(s) selected.
>>
>>-- <comment> get part names for parts whose price is less than 2000
>>-- <comment> more than its' cost from all suppliers
>>-- <ufi-input>
>> select partname
+> from parts
+> where (price - 2000) <all
+> (select partcost
+> from fromsup
+> where parts.partnum = fromsup.partnum
+> group by partcost
+> for read committed access
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
MAG TAPE DR 800BPI
MAG TAPE DR 8/16
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
TERM CRT CHAR
TERM CRT PAGE
TERM HARD COPY
POWER MODULE
--- 21 row(s) selected.
>>
>>-- <comment> get part names for parts who have the same price as at
>>-- <comment> least one other part
>>-- <ufi-input>
>> select col_1
+> from partsfor X
+> where X.col_3 =SOME
+> (select Y.col_3
+> from partsfor Y
+> where X.col_1 <> Y.col_1
+> for read committed access
+> )
+> for read committed access;
COL_1
------------------
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
MAG TAPE CONT 8/16
DISC 10MB
DISC 10MB
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
TERM CRT CHAR
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
--- 27 row(s) selected.
>>
>>-- <comment> get part names for parts who have the same price as at
>>-- <comment> least one other part
>>-- <ufi-input>
>> select col_1
+> from partsfor X
+> where X.col_3 =SOME
+> (select Y.col_3
+> from partsfor Y
+> where X.col_1 <> Y.col_1
+> for read committed access
+> )
+> for read committed access;
COL_1
------------------
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
MAG TAPE CONT 8/16
DISC 10MB
DISC 10MB
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
TERM CRT CHAR
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
--- 27 row(s) selected.
>>
>>-- <comment> get part names for parts who have the same price as at
>>-- <comment> least one other part
>>-- <ufi-input>
>> select col_1
+> from partsfor X
+> where X.col_3 =SOME
+> (select Y.col_3
+> from partsfor Y
+> where X.col_1 <> Y.col_1
+> for read committed access
+> )
+> for read committed access;
COL_1
------------------
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DECIMAL ARITH
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
DISC CONT SINGLE
MAG TAPE CONT 8/16
DISC 10MB
DISC 10MB
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
MAG TAPE DR 8/16
TERM CRT CHAR
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
TERM CRT PAGE
--- 27 row(s) selected.
>>
>>
>>-- <comment> get part numbers for all parts supplied by more than
>>-- <comment> one supplier
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where exists
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> and X.partnum = Y.partnum
+> for read committed access
+> );
PARTNUM
-------
4101
4102
4103
5101
5103
5504
6401
6402
--- 8 row(s) selected.
>>
>>-- <comment> get supplier numbers for suppliers who supply at least
>>-- <comment> all those parts supplied by supplier 6.
>>-- <ufi-input>
>> select distinct suppnum
+> from fromsup X
+> where not exists
+> (select partnum
+> from fromsup Y
+> where suppnum = 6
+> and not exists
+> (select *
+> from fromsup Z
+> where X.suppnum = Z.suppnum
+> and Z.partnum = Y.partnum
+> for read committed access
+> )
+> for read committed access
+> )
+> for read committed access;
SUPPNUM
-------
6
15
--- 2 row(s) selected.
>>
>>-- <comment> get part numbers for parts supplied to all customers
>>-- <comment> in California
>>-- <ufi-input>
>> select distinct partnum
+> from odetail X
+> where not exists
+> (select *
+> from orders, customer
+> where orders.custnum = customer.custnum
+> and customer.state = 'California'
+> and not exists
+> (select *
+> from odetail Y
+> where Y.partnum = X.partnum
+> and orders.ordernum = Y.ordernum
+> for read committed access
+> )
+> for read committed access
+> )
+> for read committed access;
PARTNUM
-------
244
2001
2403
4103
5103
6301
6402
2002
2003
3102
4101
6302
2402
3202
3302
4102
5504
6201
3103
5503
5502
1403
7301
212
7102
5505
5101
6401
3201
--- 29 row(s) selected.
>>
>>-- <comment> get supplier names for suppliers who supply part 4102
>>-- <comment> and part 5504
>>-- <ufi-input>
>> select suppname
+> from supplier
+> where 4102 in
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> for read committed access
+> )
+> and 5504 =ANY
+> (select partnum
+> from fromsup
+> where fromsup.suppnum = supplier.suppnum
+> for read committed access
+> );
SUPPNAME
------------------
INFOMATION STORAGE
DATADRIVE
--- 2 row(s) selected.
>>
>>-- <comment> get part numbers for all parts supplied by more than
>>-- <comment> two suppliers
>>-- <ufi-input>
>> select distinct X.partnum
+> from fromsup X
+> where X.partnum in
+> (select Y.partnum
+> from fromsup Y
+> where Y.suppnum <> X.suppnum
+> and Y.partnum in
+> (select Z.partnum
+> from fromsup Z
+> where Z.suppnum <> Y.suppnum
+> and Z.suppnum <> X.suppnum
+> for read committed access
+> )
+> for read committed access
+> )
+> for read committed access;
PARTNUM
-------
4102
4103
5504
--- 3 row(s) selected.
>>
>>-- <comment> same as above, use view fsdetail instead
>>-- <ufi-input>
>> select distinct X.col_1
+> from fsdetail X
+> where X.col_1 in
+> (select Y.col_1
+> from fsdetail Y
+> where Y.col_2 <> X.col_2
+> and Y.col_1 in
+> (select Z.col_1
+> from fsdetail Z
+> where Z.col_2 <> Y.col_2
+> and Z.col_2 <> X.col_2
+> for read committed access
+> )
+> for read committed access
+> )
+> for read committed access;
COL_1
-----
4103
--- 1 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <testcase A6>
>>
>>-- <detail>
>>-- select correlated 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 partnames whose price is less than 2000 more than it's
>>-- <comment> average cost from all suppliers who supply it
>>-- <ufi-input>
>> select partname
+> from parts
+> where price <
+> (select avg(partcost) + 2000
+> from fromsup
+> where partnum = parts.partnum
+> );
PARTNAME
------------------
SYSTEM 192KB CORE
SYSTEM 192KB SEMI
PROC 96KB SEMI
DECIMAL ARITH
ENSCRIBE MICRO
COBOL MICRO
MEM MOD 64K MOS
MEM MOD 96K MOS
DISC CONT SINGLE
DISC CONT DUAL
MAG TAPE CONT 8/16
LINE PRINTER CONT
MAG TAPE DR 800BPI
MAG TAPE DR 8/16
SYNC CONTROLLER
ASYNC CONTROLLER
ASYNC EXTENSION
TERM CRT CHAR
TERM CRT PAGE
TERM HARD COPY
POWER MODULE
--- 21 row(s) selected.
>>
>>-- <comment> get branchnames of branches whose total yearly payroll
>>-- <comment> exceeds 150000
>>-- <ufi-input>
>> select branchname
+> from branch
+> where exists
+> (select regnum
+> from employee
+> where employee.regnum = branch.regnum
+> and employee.branchnum = branch.branchnum
+> group by regnum, branchnum
+> having sum(salary) > 150000
+> );
BRANCHNAME
--------------
CHICAGO
DALLAS
FRANKFURT
CUPERTINO
--- 4 row(s) selected.
>>
>>-- <comment> get employee names whose salaries are greater than the
>>-- <comment> average salary for their branch
>>-- <ufi-input>
>> select empname
+> from employee X
+> where salary >
+> (select avg(salary)
+> from employee Y
+> where Y.branchnum = X.branchnum
+> and Y.regnum = X.regnum
+> );
EMPNAME
------------------
ROGER GREEN
JERRY HOWARD
JACK RAYMOND
THOMAS RUDLOFF
KLAUS SAFFERT
PAUL WINTER
GLENN THOMAS
TIM WALKER
ERIC BROWN
PETER SMITH
DAVID STRAND
STEVE COOK
SHERRIE WONG
TONY CRAFT
MANFRED CONRAD
TOM HALL
JULIA KELLY
GEORGE FRENCHMAN
JOHANN HUBER
OTTO SCHNABL
MARTIN SCHAEFER
KARL HELMSTED
PETE WELLINGTON
GEORGE STRICKER
HERB ALBERT
THOMAS SPINNER
TED MCDONNALDS
DAVE CLARK
ALAN TERRY
--- 29 row(s) selected.
>>
>>-- <comment> get employee names whose salaries are greater than the
>>-- <comment> 'median' salary for their branch
>>-- <ufi-input>
>> select empname
+> from employee X
+> where salary >
+> (select min(salary) + ((max(salary) - min(salary)) / 2)
+> from employee Y
+> where Y.branchnum = X.branchnum
+> and Y.regnum = X.regnum
+> );
EMPNAME
------------------
ROGER GREEN
JERRY HOWARD
JACK RAYMOND
THOMAS RUDLOFF
KLAUS SAFFERT
PAUL WINTER
TIM WALKER
ERIC BROWN
PETER SMITH
DAVID STRAND
STEVE COOK
SHERRIE WONG
TONY CRAFT
MANFRED CONRAD
LARRY CLARK
TOM HALL
JULIA KELLY
GEORGE FRENCHMAN
JOHANN HUBER
OTTO SCHNABL
MARTIN SCHAEFER
KARL HELMSTED
PETE WELLINGTON
GEORGE STRICKER
HERB ALBERT
THOMAS SPINNER
TED MCDONNALDS
DAVE CLARK
ALAN TERRY
--- 29 row(s) selected.
>>
>>-- <comment> get employee names of those employees whose age is greater
>>-- <comment> than or equal to the max. age of the branch in their region
>>-- <comment> with the highest average age
>>-- <ufi-input>
>> select empname
+> from employee X
+> where age >=ALL
+> (select max(age)
+> from employee Y
+> where Y.regnum = X.regnum
+> group by branchnum
+> having avg(age) >=ALL
+> (select avg(age)
+> from employee Z
+> where Z.regnum = X.regnum
+> group by branchnum
+> )
+> );
EMPNAME
------------------
JACK RAYMOND
SUSAN HENDERSON
STEVE COOK
SHERRIE WONG
LINDA JONES
GEORGE FORSTER
SUE CRAMER
RICHARD BARTON
WALTER LANCASTER
HEIDI WEIGL
PETE WELLINGTON
HERB ALBERT
--- 12 row(s) selected.
>>
>>-- <comment> get employee names of those employees whose age is greater
>>-- <comment> than or equal to the max. age of the branch in their region
>>-- <comment> with the highest average age (eliminate middle WHERE clause)
>>-- <ufi-input>
>> select empname
+> from employee X
+> where age >=ALL
+> (select max(age)
+> from employee Y
+> group by regnum, branchnum
+> having Y.regnum = X.regnum
+> and avg(age) >=ALL
+> (select avg(age)
+> from employee Z
+> where Z.regnum = X.regnum
+> group by branchnum
+> )
+> );
EMPNAME
------------------
JACK RAYMOND
SUSAN HENDERSON
STEVE COOK
SHERRIE WONG
LINDA JONES
GEORGE FORSTER
SUE CRAMER
RICHARD BARTON
WALTER LANCASTER
HEIDI WEIGL
PETE WELLINGTON
HERB ALBERT
--- 12 row(s) selected.
>>
>>-- <comment> get employee names who make more than the average salary
>>-- <comment> for their branch, excluding the branchs' managers' salary
>>-- <ufi-input>
>> select empname
+> from employee X
+> where salary >
+> (select avg(salary)
+> from employee Y, branch
+> where Y.regnum = branch.regnum
+> and Y.branchnum = branch.branchnum
+> and Y.empnum <> branch.manager
+> and X.regnum = Y.regnum
+> and X.branchnum = Y.branchnum
+> );
EMPNAME
------------------
PAUL WINTER
MARTIN SCHAEFER
OTTO SCHNABL
JOHANN HUBER
MANFRED CONRAD
TONY CRAFT
JACK RAYMOND
TOM HALL
LARRY CLARK
TIM WALKER
STEVE COOK
SHERRIE WONG
WALTER LANCASTER
DAVE CLARK
PETER SMITH
GEORGE FRENCHMAN
KLAUS SAFFERT
KARL HELMSTED
THOMAS RUDLOFF
ROGER GREEN
TED MCDONNALDS
THOMAS SPINNER
HERB ALBERT
GEORGE STRICKER
PETE WELLINGTON
GLENN THOMAS
JULIA KELLY
JERRY HOWARD
DAVE FISHER
DAVID STRAND
--- 30 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <end-test>
>>LOG;