sql - Inception situation with oracle (sub query stuffs) -

for oracle, these tables,

table 1 customer : has cust_fname, cust_lname, cust_id

table 2 salesorder : has so_number, so_custid

table 3 item : has item_qty, item_sonum

while cust_id = so_custid , so_number = item_sonum (fk relationship)

i want show customer name full name (means cust_fname+cust_lname) whereas customer (just one) 1 had ordered highest numbers of items (means has item_qty).

how can write code task?

thank you

try

select cust_fname, cust_lname (select c.cust_fname, c.cust_lname customer c join salesorder on so.so_custid = c.cust_id join item on i.item_sonum = so.so_number group c.cust_fname, cust_lname order sum(i.item_qty) desc) rownum = 1 

here sqlfiddle demo

select cust_fname, cust_lname (select c.cust_fname, c.cust_lname, rank() on (order sum(i.item_qty) desc) rnk customer c join salesorder on so.so_custid = c.cust_id join item on i.item_sonum = so.so_number group c.cust_fname, cust_lname ) rnk = 1;