CREATE TABLE Manufacturer
(
    manufacturerId INTEGER PRIMARY KEY,
    city VARCHAR(255)
);

CREATE TABLE Product
(
    productId INTEGER PRIMARY KEY,
    weight FLOAT
);

CREATE TABLE Shop
(
    shopId INTEGER PRIMARY KEY,
    city VARCHAR(255)
);

CREATE TABLE ManufacturerProduct
(
    manufacturerId INTEGER,
    productId INTEGER
);

CREATE TABLE ShopProduct
(
    shopId INTEGER,
    productId INTEGER
);

INSERT INTO Manufacturer (city)
VALUES
(
       "Moscow"
);

INSERT INTO Manufacturer (city)
VALUES
(
       "Oslo"
);

INSERT INTO Manufacturer (city)
VALUES
(
       "Paris"
);

INSERT INTO Shop (city)
VALUES
(
       "Moscow"
);

INSERT INTO Shop (city)
VALUES
(
       "Oslo"
);

INSERT INTO Shop (city)
VALUES
(
       "Paris"
);

INSERT INTO Product (weight)
VALUES
(
    15.7
);

INSERT INTO Product (weight)
VALUES
(
    16.1
);

INSERT INTO Product (weight)
VALUES
(
    19.5
);


INSERT INTO ManufacturerProduct(manufacturerId, productId)
VALUES
(
    3, 1
);

INSERT INTO ShopProduct(shopId, productId)
VALUES
(
    1, 1
);
SELECT * FROM Manufacturer;
SELECT * FROM Product;


/*
SELECT ManufacturerProduct.manufacturerId FROM
    ManufacturerProduct JOIN Product ON (ManufacturerProduct.productId = Product.productId)
WHERE ManufacturerProduct.productId IN
    (
        SELECT productId FROM
        (
            SELECT productId, COUNT(DISTINCT City) AS M FROM
                ShopProduct JOIN Shop ON ShopProduct.shopId = Shop.shopId
            GROUP BY productId
        ) X

        WHERE X.M = (SELECT COUNT(DISTINCT City) from Shop)
    );

SELECT ShopProduct.shopId FROM
     (ShopProduct JOIN ManufacturerProduct ON (ShopProduct.productId = ManufacturerProduct.productId)) SM JOIN Manufacturer ON (SM.manufacturerId = Manufacturer.manufacturerId) JOIN Product ON (ShopProduct.productId = Product.productId)
WHERE Product.weight > 15 AND (Manufacturer.city = "Berlin" OR Manufacturer.city = "Paris");*/

SELECT X.shopId FROM
    (
        SELECT ShopProduct.shopId, COUNT(DISTINCT ShopProduct.productId) AS Products FROM
            ShopProduct
        GROUP BY productId
    ) X
WHERE X.Products =
(
    SELECT COUNT(DISTINCT ShopProduct.productId) AS T FROM
            ShopProduct
        JOIN
            (
                SELECT ShopProduct.productId FROM
                    ShopProduct
                WHERE ShopProduct.shopId = 1
            ) XX
        ON
            (ShopProduct.productId = XX.productId)
        WHERE ShopProduct.shopId = X.shopId
    GROUP BY ShopProduct.shopId
)

;