bakafox

-- ЗАДАНИЕ 1.1.
-- Get all the unique companies
SELECT DISTINCT COMPANY
FROM MANUFACTURER;

-- ЗАДАНИЕ 1.2.
-- Get the total number of companies
SELECT COUNT(COMPANY)
FROM MANUFACTURER;

bakafox

-- ЗАДАНИЕ 2.1.
-- Get all the unique companies producing Drinking water in alphabetic order.
SELECT DISTINCT MANUFACTURER.COMPANY
	FROM MANUFACTURER, PRODUCT
WHERE MANUFACTURER.BILL_ID = PRODUCT.BILL_ID
	AND PRODUCT.WARE = "Drinking water"
ORDER BY COMPANY ASC;

-- ЗАДАНИЕ 2.2 (синтаксис where).
-- Get all the companies producing wares in Raw food category. Result must contain unique pairs of companies and wares producing by them from the given category and must be sorted by the ware first and the company name next.

bakafox

-- ЗАДАНИЕ 3.1.
-- Get all the unique wares in the alphabetic order with the minimal and maximal prices for each.
SELECT PRODUCT.WARE,
MIN(PRODUCT.PRICE) AS MINPRICE, MAX(PRODUCT.PRICE) AS MAXPRICE
	FROM PRODUCT
GROUP BY PRODUCT.WARE;

-- ЗАДАНИЕ 3.2.
-- Show top 3 wares with the most difference between minimal and maximal prices.
SELECT PRODUCT.WARE,

bakafox

-- ЗАДАНИЕ 4.1 (SQLITE ONLY!) (0 подзапросов).
-- Show the product with the largest average price over the market.
SELECT PRODUCT.WARE, AVG(PRODUCT.PRICE) AS AVGPRICE
	FROM PRODUCT
GROUP BY PRODUCT.WARE
ORDER BY AVGPRICE DESC
LIMIT 1;

-- ЗАДАНИЕ 4.1 (1 подзапрос).
SELECT *