sql query using companies and traders table
-
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 the 3 columns, country, export, import, which contain the sums of the values of the exported(sold to other countries) amd 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.
-
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 the 3 columns, country, export, import, which contain the sums of the values of the exported(sold to other countries) amd 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.
-
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 the 3 columns, country, export, import, which contain the sums of the values of the exported(sold to other countries) amd 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.
Also tell your teacher that he can simply specify constraints without having to resort to "assumptions". Working on assumptions is a very bad habit.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.