(1) Implement the following query as a SELECT statement with WITH clause. Find the product numbers and the total numbe

[answerhappygod]

(1) Implement the following query as a SELECT
statement with WITH clause.
Find the product numbers and the total number of keywords
provided to each project,
including the product that has no keywords.

The query must be implemented as a sequence of at least two
subquery
definitions following WITH keyword and ended with the final
SELECT.
*/
/*
(2) Create a new relational table CPURCHASE that can
store information about the full
information in the table Customer and the total number of purchases
of each
customer by using a single advanced CREATE TABLE statement. The
column name
of the total number of purchases of each customer must be
total_purchase.

Enforce the appropriate consistency constraints on the new table,
such as the primary
key and foreign key.
Next, delete all rows from the new table CPURCHASE by TRUNCATE
statement.
Insert into the new table CPURCHASE information about the
customer information
and the total number of purchases of that customer by using an
advanced INSERT
statement. If the customer makes no purchases, the total_purchase
is zero.
Delete all customers living at VIC state and having 0 total
purchases.
Finally, drop the relational table created in this
subtask.
*/
/*
(3) Create a new empty relational table
MANUFACTURER_REVIEW that can store
information about manufacturer names, total number of products, and
total number
of customers. Enforce the appropriate consistency constrains on the
new table, such
as the primary key.

Create a view MofCREVIEWP from tables Product, Customer, and
Cevaluation to find product number and rank on each product,
together with
customer number, first and last names who provide ranks, and
product manufacturer
including having 0 product reviews.

Insert into MANUFACTURER_REVIEW the name of each manufacturer, the
total
number of customers placing a review, and the total number of
reviewed products.
The same customer placing reviews to more than one product of the
same
manufacturer is counted as 1.

Display the contains of the table MANUFACTURER_REVIEW.

Finally, drop the relational table and view created in this
subtask.
*/
/*
(3) Create a new empty relational table
MANUFACTURER_REVIEW that can store
information about manufacturer names, total number of products, and
total number
of customers. Enforce the appropriate consistency constrains on the
new table, such
as the primary key.

Create a view MofCREVIEWP from tables Product, Customer, and
Cevaluation to find product number and rank on each product,
together with
customer number, first and last names who provide ranks, and
product manufacturer
including having 0 product reviews.

Insert into MANUFACTURER_REVIEW the name of each manufacturer, the
total
number of customers placing a review, and the total number of
reviewed products.
The same customer placing reviews to more than one product of the
same
manufacturer is counted as 1.

Display the contains of the table MANUFACTURER_REVIEW.

Finally, drop the relational table and view created in this
subtask.
*/
/*
(5) Use an advanced DELETE statement to delete the
product purchases from the
database about all that purchases created after ‘2022-01-01
00:00:00’.

Delete all Pbasket that finalised time and date is after
‘2022-01-01 00:00:00’.
*/
Database
/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
/*
SHOP



*/
/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
CREATE TABLE Product(
pNumber
DECIMAL(8) NOT NULL, /*
Product number */
price
DECIMAL(5,2) NOT NULL, /* Price per
item */
manufacturer VARCHAR(30),
/*
Manufacturer name */
pcomment VARCHAR(30),
/* Brief
comments */
CONSTRAINT Product_PK PRIMARY KEY(pNumber)
);
CREATE TABLE Keyword(
pNumber DECIMAL(8)
NOT NULL, /* product
number */
kword VARCHAR(30)
NOT NULL, /* Keyword
*/
CONSTRAINT Keyword_PK PRIMARY KEY(pNumber,
kword),
CONSTRAINT Keyword_FK1 FOREIGN KEY(pNumber) REFERENCES
Product(pNumber)
);
CREATE TABLE Customer(
cNumber DECIMAL(10)
NOT NULL, /* Customer
number */
fname VARCHAR(30)
NOT NULL, /* First name
*/
lname VARCHAR(30)
NOT NULL, /* Last name
*/
phone VARCHAR(20)
NOT NULL, /* Phone
number
*/
email VARCHAR(50)
NOT NULL, /* E-mail
address */
fax DECIMAL(20),

/* Fax number
*/
country VARCHAR(30)
NOT NULL, /* Country part of
address */
state VARCHAR(30),
/* State
part of address */
city VARCHAR(30)
NOT NULL, /* City part of
address */
pcode VARCHAR(10),
/* Post
code part of address */
street VARCHAR(30)
NOT NULL, /* Street part of
address */
houseNumber DECIMAL(6)
NOT NULL, /* House number part of
adress */
flatNumber DECIMAL(6),
/* Flat
number part of address */
CONSTRAINT Customer_PK PRIMARY KEY(cNumber)
);
CREATE TABLE Pbasket(
whencreated DATE
NOT NULL, /* Transferred from
Abasket */
whenfinalised TIMESTAMP NOT NULL,
/* Date time when finalised */
ccard
DECIMAL(16) NOT NULL, /* Credit card
used */
cNumber
DECIMAL(10) NOT NULL, /* Customer
number */
CONSTRAINT Pbasket_PK PRIMARY
KEY(whenfinalised),
CONSTRAINT PBasket_FK1 FOREIGN KEY(cNumber) REFERENCES
Customer(cNumber)
);
CREATE TABLE PP(
whenfinalised TIMESTAMP NOT NULL,
/* Date time when finalised */
pNumber
DECIMAL(8) NOT NULL, /* Product
number */
CONSTRAINT PP_PK PRIMARY KEY(whenfinalised,
pNumber),
CONSTRAINT PP_FK1 FOREIGN KEY(whenfinalised)
REFERENCES
Pbasket(whenfinalised),
CONSTRAINT PP_FK2 FOREIGN KEY(pNumber) REFERENCES
Product(pNumber)
);
CREATE TABLE Cevaluation(
cNumber DECIMAL(10) NOT
NULL, /* Customer number
*/
etext VARCHAR(50) NOT
NULL, /* Text of evaluation
*/
erank DECIMAL(1) NOT
NULL, /* Rank provided by customer
*/
pNumber DECIMAL(8) NOT
NULL, /* Product number
*/
CONSTRAINT Cevaluation_PK PRIMARY KEY(cNumber,
pNumber),
CONSTRAINT Cevaluation_FK1 FOREIGN KEY(cNumber)
REFERENCES Customer(cNumber),
CONSTRAINT Cevaluation_FK2 FOREIGN KEY(pNumber)
REFERENCES Product(pNumber),
CONSTRAINT Cevaluation_CHK CHECK( erank IN (0, 1, 2,
3, 4, 5) )
);
/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/