My database professor decided to try and throw a weird/hard problem at me but after some playing with Oracle I was able to figure it out.
Basically, I have a table of customers, each customer has a name and an annual revenue. What my professor wanted was to select the customers who’s annual revenue was higher than the average of all of the annual revenues of all customers less themselves. Sounds somewhat difficult. Not really.
SELECT cust_name, cust_annual_rev
FROM customer c
WHERE cust_annual_rev >
(SELECT AVG(cust_annual_rev)
FROM customer
WHERE cust_id <> c.cust_id);
Wasn’t too difficult… Maybe I’ll get some extra credit or something.