Just got DB Optimizer 2.5 out the door two weeks ago and now work is going fast on 2.5.1 slated for beginning of August. Already added EXISTS and NOT EXISTS notation into the diagrams:
SELECT
cs.customerid,
cs.firstname,
cs.lastname,
mr.rentalid,
mr.duedate,
mr.totalcharge,
ri.itemnumber
FROM
MOVIES.customer cs,
MOVIES.movierental mr,
MOVIES.rentalitem ri
WHERE
LENGTH (cs.lastname) = 5 AND
cs.zip > 75062 AND
1 < cs.customerid + 2 AND
cs.phone BETWEEN 9625569900 AND 9999569900 AND
ROUND (ri.rentalid) > 10 AND
TRUNC (ri.itemnumber) > 1 AND
mr.totalcharge > (SELECT AVG (totalcharge)
FROM MOVIES.movierental) AND
ri.moviecopyid NOT IN (SELECT mc.moviecopyid
FROM MOVIES.moviecopy mc
WHERE
mc.copyformat = 'vhs' AND
mc.copycondition = 'old' AND
mc.movieid IN (SELECT mt.movieid
FROM MOVIES.movietitle mt
WHERE
mt.year < 1990 AND
mt.rating IN ('pg', 'r') AND
mt.categoryid IN (SELECT mc.categoryid
FROM MOVIES.moviecategory mc
WHERE mc.rentalprice = (SELECT MAX (rentalprice)
FROM MOVIES.moviecategory
WHERE categoryid = mc.categoryid)))) AND
mr.CUSTOMERID = cs.CUSTOMERID AND
ri.RENTALID = mr.RENTALID
Comments