Write the following database modifications, based on the

database schema:

CREATE TABLE region $($

r$\_$regionkey decimal$(2,0)$ not null,

r$\_$name char$(25)$ not null,

r$\_$comment varchar$(152)$

$)$;

CREATE TABLE nation $($

n$\_$nationkey decimal$(3,0)$ not null,

n$\_$name char$(25)$ not null,

n$\_$regionkey decimal$(2,0)$ not null,

n$\_$comment varchar$(152)$

$)$;

CREATE TABLE part $($

p$\_$partkey decimal$(10,0)$ not null,

p$\_$name varchar$(55)$ not null,

p$\_$mfgr char$(25)$ not null,

p$\_$brand char$(10)$ not null,

p$\_$type varchar$(25)$ not null,

p$\_$size decimal$(2,0)$ not null,

p$\_$container char$(10)$ not null,

p$\_$retailprice decimal$(6,2)$ not null,

p$\_$comment varchar$(23)$ not null

$)$;

CREATE TABLE supplier $($

s$\_$suppkey decimal$(8,0)$ not null,

s$\_$name char$(25)$ not null,

s$\_$address varchar$(40)$ not null,

s$\_$nationkey decimal$(3,0)$ not null,

s$\_$phone char$(15)$ not null,

s$\_$acctbal decimal$(7,2)$ not null,

s$\_$comment varchar$(101)$ not null

$)$;

CREATE TABLE partsupp $($

ps$\_$partkey decimal$(10,0)$ not null,

ps$\_$suppkey decimal$(8,0)$ not null,

ps$\_$availqty decimal$(5,0)$ not null,

ps$\_$supplycost decimal$(6,2)$ not null,

ps$\_$comment varchar$(199)$ not null

$)$;

CREATE TABLE customer $($

c$\_$custkey decimal$(9,0)$ not null,

c$\_$name varchar$(25)$ not null,

c$\_$address varchar$(40)$ not null,

c$\_$nationkey decimal$(3,0)$ not null,

c$\_$phone char$(15)$ not null,

c$\_$acctbal decimal$(7,2)$ not null,

c$\_$mktsegment char$(10)$ not null,

c$\_$comment varchar$(117)$ not null

$)$;

CREATE TABLE orders $($

o$\_$orderkey decimal$(12,0)$ not null,

o$\_$custkey decimal$(9,0)$ not null,

o$\_$orderstatus char$(1)$ not null,

o$\_$totalprice decimal$(8,2)$ not null,

o$\_$orderdate date not null,

o$\_$orderpriority char$(15)$ not null,

o$\_$clerk char$(15)$ not null,

o$\_$shippriority decimal$(1,0)$ not null,

o$\_$comment varchar$(79)$ not null

$)$;

CREATE TABLE lineitem $($

l$\_$orderkey decimal$(12,0)$ not null,

l$\_$partkey decimal$(10,0)$ not null,

l$\_$suppkey decimal$(8,0)$ not null,

l$\_$linenumber decimal$(1,0)$ not null,

l$\_$quantity decimal$(2,0)$ not null,

l$\_$extendedprice decimal$(8,2)$ not null,

l$\_$discount decimal$(3,2)$ not null,

l$\_$tax decimal$(3,2)$ not null,

l$\_$returnflag char$(1)$ not null,

l$\_$linestatus char$(1)$ not null,

l$\_$shipdate date not null,

l$\_$commitdate date not null,

l$\_$receiptdate date not null,

l$\_$shipinstruct char$(25)$ not null,

l$\_$shipmode char$(10)$ not null,

l$\_$comment varchar$(44)$ not null

$)$;

a$)$ Compute, for every country, the value of economic exchange, i$.$e$.,$ the difference between the number

of items from suppliers in that country sold to customers in other countries and the number of items

bought by local customers from foreign suppliers in $1997($l shipdate$).$

b$)$ Compute the change in the economic exchange for every country between $1996$ and $1998.$ There should

be two columns in the output for every country: $1997$ and $1998.$ Hint: use CASE to select the values

in the result.

c$)$ Find the distinct parts $($p name$)$ ordered by customers from ASIA that are supplied by exactly $3$ suppliers

from AFRICA.