You have details of some companies that trade internationally and strengthen the economy of their countries. You know the nationality of each company and you have a list of trades between the various companies. Your task is to generate a summary that consists of sums of the value of goods imported and exported by every country. Note that when a company buys some goods, it contributes to its country's total import, and when the company sells some goods, it contributes to its country's total export.You are given two tables: companies and trades, with the following structure: create table companies ( name varchar(30) not null, country varchar(30) not null, unique(name) ); create table trades ( id integer not null, seller varchar(30) not null, buyer varchar(30) not null, value integer not null, unique(id) );A row in table companies contains the name of a company and the name of the nationality of the company. A row in table trades contains the unique ID of the trade, the name of the selling company, the name of the buying company and the value of the traded goods.Write an SQL query that returns a table consisting of three columns, country, export, import, which contain the sums of the values of the exported (sold to other countries) and imported (purchased from other countries) goods for every country. Each country should appear in this table. The result table should be sorted increasingly by country.For example, for: companies: +-------------+--------------------+ | name | country | +-------------+--------------------+ | Alice s.p. | Wonderland | | Y-zap | Wonderland | | Absolute | Mathlands | | Arcus t.g. | Mathlands | | Lil Mermaid | Underwater Kingdom | | None at all | Nothingland | +-------------+--------------------+ trades: +----------+-------------+------------+-------+ | id | seller | buyer | value | +----------+-------------+------------+-------+ | 20121107 | Lil Mermaid | Alice s.p. | 10 | | 20123112 | Arcus t.g. | Y-zap | 30 | | 20120125 | Alice s.p. | Arcus t.g. | 100 | | 20120216 | Lil Mermaid | Absolute | 30 | | 20120217 | Lil Mermaid | Absolute | 50 | +----------+-------------+------------+-------+your query should return: +--------------------+--------+--------+ | country | export | import | +--------------------+--------+--------+ | Mathlands | 30 | 180 | | Nothingland | 0 | 0 | | Underwater Kingdom | 90 | 0 | | Wonderland | 100 | 40 | +--------------------+--------+--------+Assume that:There is no trade between companies within a single country;Every company in the table trades also appears in the table companies;Every company appears in table companies exactly once.

SELECT co.country, 
       IFNULL(ex.export, 0) AS export, 
       IFNULL(im.import, 0) AS import
FROM companies co
LEFT JOIN (
    SELECT c.country, SUM(t.value) AS export
    FROM companies c
    JOIN trades t ON c.name = t.seller
    GROUP BY c.country
) ex ON co.country = ex.country
LEFT JOIN (
    SELECT c.country, SUM(t.value) AS import
    FROM companies c
    JOIN trades t ON c.name = t.buyer
    GROUP BY c.country
) im ON co.country = im.country
ORDER BY co.country;