-- Shows information about station with highest amount of likes
SELECT *
FROM "Petrol_stations"
WHERE likes = (SELECT MAX(likes)
              FROM "Petrol_stations");

-- Shows information about station with highest amount of unlikes
SELECT *
FROM "Petrol_stations"
WHERE unlikes = (SELECT MAX(unlikes)
                FROM "Petrol_stations");

-- Shows information about stations in Wrocław
SELECT *
FROM "Petrol_stations"
WHERE city = 'Wrocław';

-- Shows information about stations with PB95 distributor
SELECT *
FROM "Petrol_stations"
WHERE pb95_price IS NOT NULL;

-- Shows information about station with lowest ON price
SELECT *
FROM "Petrol_stations"
WHERE on_price = (SELECT MIN(on_price)
                  FROM "Petrol_stations");

-- Shows users with more than 2 cars
SELECT "Users".nickname, COUNT(*) AS "Number of cars"
FROM "Users", "Cars"
WHERE "Users".id = "Cars".owner_id
GROUP BY "Users".nickname
HAVING COUNT("Cars".id) >= 2;

-- Shows users who refuelled cars in Wrocław
SELECT "Users".nickname, COUNT(*) AS "No. of refuelling in Wrocław"
FROM "Users", "Cars", "Petrol_stations", "Refuellings"
WHERE "Users".id = "Cars".owner_id AND "Cars".id = "Refuellings".car_id AND "Refuellings".petrol_station_id = "Petrol_stations".id
GROUP BY "Users".nickname, "Petrol_stations".city
HAVING "Petrol_stations".city = 'Wrocław'

-- Shows average cost of refuel
SELECT "Users".nickname, AVG("Refuellings".total_price) AS "Average cost of refuel"
FROM "Users", "Cars", "Refuellings"
WHERE "Users".id = "Cars".owner_id AND "Cars".id = "Refuellings".car_id
GROUP BY "Users".nickname