Write a query that selects for every customer, the number of orders he has places and the number of items he has bought, including zeros for customers with no orders Using the query above populate the table Customers Statistics (Use Insert Statement) Extra What is going to happen if you rollback Now add a new column to the table called Customer Status that is a string of maximum length 20 Extra What is going to happen if you rollback Now lets update the table so that Customer Status has the following values ok when the number of orders is 0 or 1 good when the number of orders is 2 or more great when the number of orders is 5 and the number of items is 5 Alter the table so that Number of Items cannot be NULL Delete from the table Customers Statistics all the customers that have not placed any orders Truncate table Customers Statistics Drop table Customers Statistics l CUSTOMER T CUSTOMER D CUSTOMERNAME CUSTOMERADDRESS CUSTOMERCITY CUSTOMERSTATE CUSTOMERPOSTALCODE CUSTOMERS CUSTOMER ID CUSTOMER FIRST NAME E CUSTOMER LAST NAME CUSTOMERADDRESS CUSTOMER CITY CUSTOMER STATE CUSTOMERZIP CUSTOMERPHONE CUSTOMER FAX B CUSTOMERS STATISTICS CUSTOMERSHIPADDRESST B DOESBUSINESSINT EMPLOYEE T EMPLOYEESKILLST E LE GENERAL LEDGER ACCOUNTS INVOICE LINE ITEMS ITEMS ITEM D TITLE ARTIST UNIT PRICE E ORDER DETAILS ORDER ID 1 ITEM ID 0 ORDERI INET ORDERLINEID E ORDERID PRODUCTID ORDEREDQUANTITY ORDERS CUSTOMERID ORDER DATE SHIPPED DATE B PAYMENT T PAYMENTTYPET PRODUCEDINT B PRODUCTT B PRODUCTLINET B RAWMATERIALT E SHIPPED T TERMS E TERRITORY T USES T SUPPLIES T VENDOR CONTACTS VENDORT E VENDORS B WORKCENTERT WORKSIN T

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

Write a query that selects for every customer, the number of orders he has places and the number of items he has bought, including zeros

Write a query that selects for every customer, the number of orders he has places and the number of items he has bought, including zeros for customers with no orders.
Using the query above populate the table Customers_Statistics (Use Insert Statement)

Extra: What is going to happen if you rollback?

Now add a new column to the table called Customer_Status that is a string of maximum length 20; Extra: What is going to happen if you rollback?

Now lets update the table so that Customer_Status has the following values:

ok when the number of orders is 0 or 1

good when the number of orders is 2 or more

great when the number of orders is > 5 and the number of items is >5

Alter the table so that Number_of_Items cannot be NULL
Delete from the table Customers_Statistics all the customers that have not placed any orders
Truncate table Customers_Statistics
Drop table Customers_Statistics

========================================================================================

l = CUSTOMER-T CUSTOMER!D CUSTOMERNAME CUSTOMERADDRESS CUSTOMERCITY CUSTOMERSTATE CUSTOMERPOSTALCODE .. CUSTOMERS CUSTOMER-ID CUSTOMER-FIRST-NAME E CUSTOMER-LAST-NAME CUSTOMERADDRESS CUSTOMER CITY - CUSTOMER_STATE CUSTOMERZIP CUSTOMERPHONE CUSTOMER FAX - - B-CUSTOMERS STATISTICS CUSTOMERSHIPADDRESST B- DOESBUSINESSINT EMPLOYEE-T EMPLOYEESKILLST E- LE GENERAL-LEDGER-ACCOUNTS INVOICE LINE ITEMS ITEMS ITEM_D TITLE ARTIST : UNIT-PRICE | E ORDER DETAILS ORDER ID 1 ITEM-ID 0 ORDERI INET ORDERLINEID E ORDERID PRODUCTID ORDEREDQUANTITY ORDERS CUSTOMERID - ORDER_DATE SHIPPED-DATE B- PAYMENT T PAYMENTTYPET PRODUCEDINT B- PRODUCTT B-PRODUCTLINET B- RAWMATERIALT - E- SHIPPED T .. TERMS E TERRITORY-T .. USES_T SUPPLIES-T VENDOR-CONTACTS VENDORT - .. E VENDORS B- WORKCENTERT WORKSIN T