Slide 10.18: Method example II (cont.)

Method Example II (Cont.)

Find the minimum areas of the covering rectangles for each point.

^†

GROUP BY

min

Identify the minimal rectangles as well by using a correlated subquery to calculate the minimal area rectangle for a point and use that in the outer SELECT.

^†The outer SELECT may find more than one rectangle of the same minimal size, for some point.

SQL> SELECT  p.x, p.y, min(r.area( ))  FROM  rects r, points p 
  2    WHERE  r.inside( value(p) ) > 0 
  3    GROUP BY  p.x, p.y; 

         X             Y    MIN(R.AREA())
----------    ----------    -------------
         1             4                9
         2             3                9
         4             4                9


SQL> SELECT  value(p), value(r), r.area( )  FROM  rects r, points p 
  2    WHERE  r.area( ) = ( 
  3      SELECT  min(r1.area( ))  FROM  rects r1 
  4        WHERE  r1.inside( value(p) ) > 0 ) 
  5      AND r.inside( value(p) ) > 0 
  6    ORDER BY  p.x, p.y; 

value(P)(X, Y)     value(R)(PT1(X, Y), PT2(X, Y))                R.AREA( )
---------------    ------------------------------------------    ---------
POINT_T(1, 4)      RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5))         9
POINT_T(2, 3)      RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5))         9
POINT_T(4, 4)      RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5))         9

^†Like standard PL/SQL procedures and functions, method functions can be called from within SQL methods, as long as they do not write to the database, create any objects, or write to any object attributes.

^‡The methods of rectangle_t object did not actually extend the power of the queries. However, without using the methods, the above query is extremely long and complicated if interactive SQL is used.