Next: UPDATES IN SQL
Up: QUERYING IN SQL
Previous: Full Select Statement Syntax
- First the product of all tables in the from clause is
- The where clause is then evaluated to eliminate rows that
do not satisfy the search_condition.
- Next, the rows are grouped using the columns in the
group by clause.
- Then, Groups that do not satisfy the search_condition in
the having clause are eliminated.
- Next, the expressions in the select clause target list
- If the distinct keyword in present in the select clause,
duplicate rows are now eliminated.
- The union is taken after each sub-select is evaluated.
- Finally, the resulting rows are sorted according to the
columns specified in the order by clause.
(1) Get names and ids of customers and agents along with
total dollar sales for that pair. Order the result
from largest to smallest total sales. Also retain
only those pairs for which total dollar sales is
at least 900.00.
SQL> select c.cname, c.cid, a.aname, a.aid, sum(o.dollars)
from customers c, orders o, agents a
where c.cid = o.cid and o.aid = a.aid
group by c.cname, c.cid, a.aname, a.aid
having sum(o.dollars) >= 900.00
order by 5 desc;
CNAME CID ANAME AID SUM(O.DOLLARS)
------------- ---- ------------- --- --------------
Allied c003 Brown a03 2208
Tiptop c001 Otasi a05 1440
Tiptop c001 Smith a01 900
Tue Apr 1 16:15:10 PST 1997