Hello! Help with SQL$,$ please!

We have following tables from database $\_\_$adventureworkslt:

CREATE TABLE company$\_$abc $($

cid int$4$ NOT NULL,

salestotal numeric NULL,

cls varchar$(1)$ NULL,

year int$4$ NOT NULL,

CONSTRAINT company$\_$abc$\_$pk PRIMARY KEY $($cid$,$ year$)$

$)$;

CREATE TABLE public.salesorderheader $($

salesorderid int$4$ NOT NULL,

revisionnumber int$2$ DEFAULT $0$ NOT NULL,

orderdate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

duedate timestamp$(6)$ NOT NULL,

shipdate timestamp$(6)$ NULL,

status int$2$ DEFAULT $1$ NOT NULL,

onlineorderflag bool DEFAULT true NOT NULL,

salesordernumber varchar$(25)$ NULL,

purchaseordernumber varchar$(25)$ NULL,

accountnumber varchar$(15)$ NULL,

customerid int$4$ NOT NULL,

shiptoaddressid int$4$ NULL,

billtoaddressid int$4$ NULL,

shipmethod varchar$(50)$ NOT NULL,

creditcardapprovalcode varchar$(15)$ NULL,

subtotal numeric$(19,4)$ DEFAULT $0$ NOT NULL,

taxamt numeric$(19,4)$ DEFAULT $0$ NOT NULL,

freight numeric$(19,4)$ DEFAULT $0$ NOT NULL,

totaldue numeric$(19,4)$ NOT NULL,

"comment" varchar$(4000)$ NULL,

rowguid varchar$(36)$ NOT NULL,

modifieddate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

CONSTRAINT salesorderheader$\_$pkey PRIMARY KEY $($salesorderid$)$

$)$;

ALTER TABLE public.salesorderheader ADD CONSTRAINT salesorderheader$\_$fk FOREIGN KEY $($customerid$)$ REFERENCES public.customer$($customerid$)$;

CREATE TABLE public.customer $($

customerid int$4$ NOT NULL,

namestyle bool DEFAULT false NOT NULL,

title varchar$(8)$ NULL,

firstname varchar$(50)$ NULL,

middlename varchar$(50)$ NULL,

lastname varchar$(50)$ NULL,

suffix varchar$(10)$ NULL,

companyname varchar$(128)$ NULL,

emailaddress varchar$(50)$ NULL,

rowguid varchar$(36)$ NOT NULL,

modifieddate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

salespersonid int$4$ NULL,

gender varchar$(1)$ NULL,

totalchildren int$4$ NULL,

birthdate date NULL,

datefirstpurchase date NULL,

persontype varchar$(3)$ NULL,

CONSTRAINT customer$\_$pkey PRIMARY KEY $($customerid$)$

$)$;

CREATE TABLE company $($

id int$4$ NOT NULL GENERATED ALWAYS AS IDENTITY,

cname varchar$(200)$ NOT NULL,

countrycode varchar$(10)$ NULL,

city varchar$(30)$ NULL,

CONSTRAINT d$\_$company$\_$pk PRIMARY KEY $($id$)$

$)$;

CREATE TABLE public.salesorderdetail $($

salesorderid int$4$ NOT NULL,

salesorderdetailid int$4$ NOT NULL,

orderqty int$2$ NOT NULL,

productid int$4$ NOT NULL,

unitprice numeric$(19,4)$ NOT NULL,

unitpricediscount numeric$(19,4)$ DEFAULT $0$ NOT NULL,

linetotal numeric$(38,6)$ NOT NULL,

rowguid varchar$(36)$ NOT NULL,

modifieddate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

CONSTRAINT salesorderdetail$\_$pk PRIMARY KEY $($salesorderdetailid$)$

$)$;

CREATE INDEX salesorderdetail$\_$productid$\_$idx ON public.salesorderdetail USING btree $($productid$,$ salesorderid$)$;

ALTER TABLE public.salesorderdetail ADD CONSTRAINT salesorderdetail$\_$fk FOREIGN KEY $($productid$)$ REFERENCES public.product$($productid$)$;

ALTER TABLE public.salesorderdetail ADD CONSTRAINT salesorderdetail$\_$order$\_$fk FOREIGN KEY $($salesorderid$)$ REFERENCES public.salesorderheader$($salesorderid$)$;

CREATE MATERIALIZED VIEW public.product$2$

TABLESPACE pg$\_$default

AS SELECT pc$.$productcategoryid AS pcid,

p$.$productid,

pc$.$name AS pcname,

p$.$name AS pname

FROM product p

JOIN productsubcategory ps ON p$.$productsubcategoryid $=$ ps$.$productsubcategoryid

JOIN productcategory pc ON ps$.$productcategoryid $=$ pc$.$productcategoryid

WITH DATA;

Calculate quarterly sales amount before taxes in $2012$ and $2013$ individually. Fill the company$\_$sales

table using data about orders, companies, and classification results in the respective year.

The table below contains comments on filling the fields of the company$\_$sales table.

Field $|$ Description $|$ Rules$||$

$---------------------------------------$

cid $|$ Company key $|$ company.id Find company.id through customer and company tables$||$

salesamt $|$ Total amount sold $($before taxes$).$ Purchase volume of each product category made per quarter by

each company $|$ Calculate as sum$($salesorderdetail$.$linetotal$)$ for combinations of year&quarter, company, and category. The salesorderdetail table contains product sales data in quantity and cash.$||$

year $|$ Numeric year of orderDate $|$ Numeric year of salesorderheader.orderdate$||$

quarter$\_$yr $|$ Numeric quarter number of orderDate $|$ Numeric quarter number of salesorderheader.orderdate$||$

qr $|$ Quarter in text format. E$.$g$.,2022\mathrm{.}1$ or $2023\mathrm{.}4|$ String representation of quarter in form of YYYY$.$Q extracted from

salesorderheader.orderdate.$||$

categoryid $|$ Product category$$s key $|$ product$2.$pcid. Connect product$2$ with salesorderdetail$||$

ccls $|$ Company$$s class name. E$.$g$.,$ A$,$B or C$.|$ String name of a class to which a company belongs. Use company$\_$abc.cls for the year from salesorderheader.orderdate$||$