Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Hello! Help with SQL , please! We have the following database: advemturewokslt CREATE TABLE public.customer ( customerid int 4 NOT NULL, namestyle bool NOT NULL
Hello! Help with SQL please!
We have the following database: advemturewokslt
CREATE TABLE public.customer
customerid int NOT NULL,
namestyle bool NOT NULL DEFAULT false,
title varchar NULL,
firstname varchar NULL,
middlename varchar NULL,
lastname varchar NULL,
suffix varchar NULL,
companyname varchar NULL,
emailaddress varchar NULL,
rowguid varchar NOT NULL,
modifieddate timestamp NOT NULL DEFAULT CURRENTTIMESTAMP,
salespersonid int NULL,
gender varchar NULL,
totalchildren int NULL,
birthdate date NULL,
datefirstpurchase date NULL,
persontype varchar NULL,
CONSTRAINT customerpkey PRIMARY KEY customerid
;
ALTER TABLE public.customeraddress ADD CONSTRAINT customeraddresscfk FOREIGN KEY customerid REFERENCES public.customercustomerid;
ALTER TABLE public.customeraddress ADD CONSTRAINT customeraddressfk FOREIGN KEY addressid REFERENCES public.addressaddressid;
CREATE TABLE public.product
productid serial NOT NULL,
"name" varchar NOT NULL,
productnumber varchar NOT NULL,
color varchar NULL,
listprice numeric NOT NULL,
"size" varchar NULL,
sizeunitmeasurecode bpchar NULL,
weightunitmeasurecode bpchar NULL,
weight numeric NULL,
productline bpchar NULL,
"class" bpchar NULL,
"style" bpchar NULL,
productsubcategoryid int NULL,
productmodelid int NULL,
sellstartdate timestamp NOT NULL,
sellenddate timestamp NULL,
discontinueddate timestamp NULL,
CONSTRAINT productpkey PRIMARY KEY productid
;
CREATE INDEX idxproductcolor ON public.product USING btree color;
CREATE TABLE public.productcategory
productcategoryid serial NOT NULL,
"name" varchar NOT NULL,
CONSTRAINT productcategorypkey PRIMARY KEY productcategoryid
;
CREATE TABLE public.productsubcategory
productsubcategoryid serial NOT NULL,
productcategoryid serial NOT NULL,
"name" varchar NOT NULL,
CONSTRAINT productsubcategorypkey PRIMARY KEY productsubcategoryid
;
CREATE TABLE public.salesorderdetail
salesorderid int NOT NULL,
salesorderdetailid int NOT NULL,
orderqty int NOT NULL,
productid int NOT NULL,
unitprice numeric NOT NULL,
unitpricediscount numeric NOT NULL DEFAULT
linetotal numeric NOT NULL,
rowguid varchar NOT NULL,
modifieddate timestamp NOT NULL DEFAULT CURRENTTIMESTAMP,
CONSTRAINT salesorderdetailpk PRIMARY KEY salesorderdetailid
;
CREATE INDEX salesorderdetailproductididx ON public.salesorderdetail USING btree productid salesorderid;
ALTER TABLE public.salesorderdetail ADD CONSTRAINT salesorderdetailfk FOREIGN KEY productid REFERENCES public.productproductid;
ALTER TABLE public.salesorderdetail ADD CONSTRAINT salesorderdetailorderfk FOREIGN KEY salesorderid REFERENCES public.salesorderheadersalesorderid;
CREATE TABLE public.salesorderheader
salesorderid int NOT NULL,
revisionnumber int NOT NULL DEFAULT
orderdate timestamp NOT NULL DEFAULT CURRENTTIMESTAMP,
duedate timestamp NOT NULL,
shipdate timestamp NULL,
status int NOT NULL DEFAULT
onlineorderflag bool NOT NULL DEFAULT true,
salesordernumber varchar NULL,
purchaseordernumber varchar NULL,
accountnumber varchar NULL,
customerid int NOT NULL,
shiptoaddressid int NULL,
billtoaddressid int NULL,
shipmethod varchar NOT NULL,
creditcardapprovalcode varchar NULL,
subtotal numeric NOT NULL DEFAULT
taxamt numeric NOT NULL DEFAULT
freight numeric NOT NULL DEFAULT
totaldue numeric NOT NULL,
"comment" varchar NULL,
rowguid varchar NOT NULL,
modifieddate timestamp NOT NULL DEFAULT CURRENTTIMESTAMP,
CONSTRAINT salesorderheaderpkey PRIMARY KEY salesorderid
;
ALTER TABLE public.salesorderheader ADD CONSTRAINT salesorderheaderfk FOREIGN KEY customerid REFERENCES public.customercustomerid;
Write a query to prepare a report containing the following fields. Use laglead window functions:
year of order due date;
month of order due date;
product category name;
number of orders in current product category;
changes in number of orders from preceding month or zero if there is no preceding data;
Customize the results layout so that records are sorted by category and date in chronological order.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started