CODEWITHSUNDEEP
sqloracle-database
Rodi Marcus
Rodi Marcus

Reputation: 107

oracle finding top selling product and viewing details

Question

Display the details (i.e. Manufacturer name, model name, type) of the top selling European car model.

Tables:

Manufacturer

(manufacturerID, name, region)

Model

(modelNo, name, type, previousModel, manufacturerID)

Car

(VIN, dateAcquired, yearBuilt, purchasedPrice, askingPrice,
currentMileage, modelNo)

SalesTransaction

(VIN, custID, agentID, dateOfSale, agreedPrice)

My query attempt:

select ma.name as "Manufacturer Name", mo.name as "Model Name", mo.type as "Model Type"
from manufacturer ma, model mo, 
inner join car c on mo.modelno = c.modelno
inner join salestransaction st on c.vin = st.vin
where ma.manufacturerid = mo.manufacturerid
having (
        select st.vin
        from salestransaction st
        inner join car c on st.vin = c.vin
        group by st.vin
        order by count(*) desc
        )
rownum=1;

I feel like I've made multiple mistakes due to not quite knowing exactly how to go about this.

Any help will be appreciated.

Upvotes: 0

Views: 316

Answers (1)

Tim3880
Tim3880

Reputation: 2583

I think your looking for something like:

SELECT f.name AS "Manufacturer", d.name AS Model, d.type 
FROM Manufacturer f
INNER JOIN model d
ON  d.manufacturerid = f.manufacturerid
INNER JOIN (
   SELECT modelno FROM (
    SELECT m.modelno  FROM model m  
    INNER JOIN manufacturer n 
    ON m.manufacturerid = n.manufacturerid 
    INNER JOIN car c 
    ON c.modelno = m.modelno
    INNER JOIN salestransaction t
    ON c.vin = t.vin
     WHERE n.region = 'europe'
     GROUP BY m.modelno 
     ORDER BY count(*) DESC
  ) WHERE ROWNUM<2
 ) top_model
 ON d.modelno = top_model.modelno

I didn't test so you need check the syntax and table names.

Upvotes: 1

Related Questions