ISI 2016 PCB C4

Question

Commodity items have some positive or negative changes in their
prices each week. Each trading company picks a portfolio of com-
modity items, that is, they have one or more items and they own
some non-zero quantity of each one. The database table for this
problem consists of the following two relations:

Commodity(item-number int, price-change float, week int),
Owns(company-name text, item-number int, quantity float).

Write an SQL query which returns the item-numbers of commodities for which, in any given week, the price change is greater than
or equal to that of all other items and there exists at least one
company selling that item only (i.e., not selling any other item)
in that week

Answer 1

I have made the following attempt. Can any please confirm if I am write?

SELECT
  item_number
FROM
  comodity
WHERE
  price_change =(
  SELECT MAX
    (price_change)
  FROM
    comodity
  WHERE WEEK
    = 1 AND item_number IN(
    SELECT
      item_number
    FROM
      (
      SELECT
        company,
        item_number,
        COUNT(item_number) AS ct
      FROM
        owns
      GROUP BY
        company
    ) AS t1
  WHERE
    t1.ct = 1
  )
)

Comments

why week =1??

---

Actually in the question it is said that "in any given week", so I have assumed it as "1"

---

your query seems correct to me..:)

Answer 2

select a.item-number from commodity a

where a.price-change >= ALL( select b.price-change from commodity b where b.week=a.week)

and

exists

( select company-name from owns

where company-name in ( select c.company-name from owns c where c.item-number=a.item-number)

group by company-name

having count(item-number)=1 

)