Using SQL CASE Expressions and DML Operations and given the database description below:

Tb$\_$Supplier$($Supp$\_$ID$,$ Name, City$)$

Tb$\_$Consumer$($Con$\_$ID$,$ Name, City$)$

Tb$\_$Product$($Prod$\_$ID$,$ Name, MU$)$

Tb$\_$Offers$($Supp$\_$ID$,$ Prod$\_$ID$,$ Quantity, Price$)$

Tb$\_$Requests$($Con$\_$ID$,$ Prod$\_$ID$,$ Quantity, Price$)$

Tb$\_$Transactions$($T$\_$ID$,$ Supp$\_$ID$,$ Con$\_$ID$,$ Prod$\_$ID$,$ Quantity, Price$)$

Solve and test the following queries:

$1)$ Supplier cities and amount of auto, trucks, TV and airplanes offered in each city?

$2)$ For each product list the total quantities purchased in the of the following cities:

Wausau, Chicago, Madison, New York? $($solve in $3$ versions: $(1)$ using CASE

expressions, $(2)$ using correlated subqueries, $(3)$ using the PIVOT function$).$

$3)$ Supplier$-$consumer pairs having at least one transaction and values for oil,

orange, and TV traded between the two?

$4)$ Suppliers located in Wausau or Chicago or having less than $3$ offers or not having

offers for trucks?

$5)$ For each product list quantity sold by suppliers in Madison to consumers in

Chicago versus quantity sold by suppliers in Chicago to consumers in Madison

$($result columns will be: product name, quantity Madison$\_$Chicago, quantity

Chicago$\_$Madison$)?$

$6)$ Increase by $15\%$ the price of all offers made by suppliers located in Chicago.

$7)$ Increase by $10\%$ the price of all offers for products offered by less than $3$

suppliers.

$8)$ Modify requests prices as follows: increase by $9\%$ the request prices for

computers in cities where there is only one computer supplier, increase by $4\mathrm{.}5\%$

requests prices for TV and auto and reduce by $7\mathrm{.}5\%$ request prices for oil if

request is made by consumer in Chicago.

$9)$ Increase by $40\%$ the offer price of all products for which demand is higher than

the offer $($the quantity requested is higher than the quantity offered$).$