Title: A database of existing stores, recording customers and their purchases, consists of the following three tables: Product product (product number ProductID, product name ProductName, Unit price UnitPrice, category of commodity, supplier provider) Customers customer (Customer number CustomerID, name name, address location), purchase Purcase (Customer number CustomerID, product number ProductID, purchase quantity quantity), each customer can buy more than one item, Each item can be purchased by multiple customers and is a many-to-many relationship. Try SQL language to complete the following functions:1 table, in the definition of the requirement to declare the following constraints: (1) The primary foreign key of each table, (2) the customer's name and product name cannot be null, (3) The unit price must be greater than 0, the purchase quantity must be 0 to 20 between;2 Insert data into table: Goods (M01, crest, 8.00, toothpaste, Procter and Gamble; m02, Colgate, 6.50, toothpaste, Colgate; m03, Jie nuo, 5.00, toothpaste, Unilever, m04, Shu Jia, 3.00, soap, Procter and Gamble; &NBSP;&NBSP;&NBSP;M05, Hazeline, 5.00, SOAP, Unilever, m06, carving, 2.50, detergent, nice m07, Zhonghua, 3.50, toothpaste, Unilever, m08, stain, 3.00, detergent, Procter and Gamble; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;M09, Green Wave, 4.00, detergent, Procter and Gamble, customer (C01,dennis, Haidian; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;C02, John, Chaoyang, c03,tom, Dongcheng, c04,jenny, Dongcheng; c05,rick, Xicheng;) Buy (C01,M01,3;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;C01, m05,2; c01,m08,2; c02,m02,5; C02,M06,4; C03,M01,1; C03,M05,1; C03,M06,3; C03,M08,1; C04,M03,7; C04,M04,3; C05,M06,2; c05,m07,8;) Merchandise has 9 , customers have 5, purchase has 13 3 with PL/SQL block programming to complete the following query requirements: (1) to purchase the supplier "Procter and Gamble" the products of all customers, (2) to purchase the product contains the customer "Dennis" The customer (name) of all goods purchased and (3) the supplier who sells the most quantity of toothpaste. 4 increased the unit price of all toothpaste products by 10%. (SQL statement) 5 Delete an item record that has never been purchased. (SQL statement) analysis: First step: Create a table, determine the data type, create a constraint--delete the data table drop tables purcase;drop table Product;drop table customer; ---to create a data table---Analysis of problem-solving thinking:---FirstStep: Create a table, determine the data type, establish a constraint----create a commodity table Product create table product ( productid VARCHAR2 (Ten), productname varchar2 () not null, unitprice number, category varchar2 (), provider varchar2 (, ) constraint Pk_productid primary KEY (ProductID), constraint Ck_unitprice CHECK ( unitprice>0) --create Customer table Customer:create table Customer ( CustomerID VARCHAR2 (Ten), name varchar2 (not null, location) VARCHAR2 (), constraint Pk_customerid primary KEY (CustomerID)); --Create purchase record form Purcase:create table Purcase ( customerid varchar2 (Ten), productid VARCHAR2 (Ten), quantity number, constraint fk_customerid FOREIGN KEY ( CustomerID) REFEREnces Customer (CustomerID) on delete cascade, constraint Fk_productid FOREIGN KEY (ProductID) REFERENCES Product (ProductID) on delete cascade, constraint ck_quantity CHECK (quantity between 0 and)); ---Test data writing: INSERT into product (Productid,productname,unitprice,category,provider) VALUES (' M01 ', ' Crest ', 8.00, ' toothpaste ', ' Procter and Gamble '); insert into product (Productid,productname,unitprice,category,provider) VALUES (' M02 ', ' Colgate ', 6.50, ' toothpaste ', ' Colgate '); insert into product (Productid,productname,unitprice,category,provider) VALUES (' M03 ', ' Jie nuo ', 5.00, ' toothpaste ', ' Unilever '); insert into product (Productid,productname,unitprice,category,provider) VALUES (' M04 ', ' Shu Jia ', 3.00, ' soap ', ' Procter and Gamble '); insert into product (Productid,productname,unitprice,category,provider) VALUES (' M05 ', ' hazeline ', 5.00, ' soap ', ' Unilever '); insert into product (Productid,productname,unitprice,category,provider) VALUES ( ' M06 ', ' engraving ', 8.00, ' detergent ', ' nice '); insert into product (PRODUCTID,PRODUCTNAME,UNITPRICE,CATegory,provider) VALUES (' M07 ', ' Zhonghua ', 3.50, ' toothpaste ', ' Unilever '), insert into product (Productid,productname,unitprice, Category,provider) VALUES (' M08 ', ' stain ', 3.00, ' detergent ', ' Procter and Gamble '), insert into product (Productid,productname,unitprice, Category,provider) VALUES (' M09 ', ' Green Wave ', 4.00, ' detergent ', ' Procter and Gamble '), insert into customer (CustomerID, name, location) VALUES (' C01 ', ' Dennis ', ' Haidian '), insert into the customer (CustomerID, name, location) VALUES (' C02 ', ' John ', ' Chaoyang '); insert into Customer (CustomerID, name, location) VALUES (' C03 ', ' Tom ', ' Dongcheng '); insert into Customer (CustomerID, name, location) VALUES (' C04 ', ' Jenny ', ' Dongcheng '), insert into customer (CustomerID, Name, Location) VALUES (' C05 ', ' Rick ', ' West Side '), insert into Purcase (customerid,productid,quantity) VALUES (' C01 ', ' M01 ', 3); insert into Purcase (customerid,productid,quantity) VALUES (' C01 ', ' M05 ', 2); insert into Purcase ( customerid,productid,quantity) VALUES (' C01 ', ' M08 ', 2); insert into Purcase (Customerid,productid,Quantity) VALUES (' C02 ', ' M02 ', 5); insert into Purcase (customerid,productid,quantity) VALUES (' C02 ', ' M06 ', 4); insert into Purcase (customerid,productid,quantity) VALUES (' C03 ', ' M01 ', 1); insert into Purcase (customerid , productid,quantity) VALUES (' C03 ', ' M05 ', 1); insert into Purcase (customerid,productid,quantity) VALUES (' C03 ', ' M06 ', 3); insert into Purcase (customerid,productid,quantity) VALUES (' C03 ', ' M08 ', 1); insert into Purcase (customerid,productid,quantity) VALUES (' C04 ', ' M03 ', 7); insert into Purcase (Customerid,productid, Quantity) VALUES (' C04 ', ' M04 ', 3); insert into Purcase (customerid,productid,quantity) VALUES (' C05 ', ' M06 ', 2); insert into Purcase (customerid,productid,quantity) VALUES (' C05 ', ' M07 ', 8); ---COMMIT TRANSACTION commit; --- Problem Analysis-(1) All customers who purchased the supplier's "Procter and Gamble" products; 1, determine the table to use &NBSP;&NBSP;&NBSP;&NBSP;PRODUCT table: Supplier Information Customers table: Customer Information purcase table: customer's purchase record 2, determine the correlation relationship purcase.customerid =customer.customerid; purcase.productid=customer.productid; First step: First determine the supplier for the ' Procter and Gamble ' products of all products id select ProductID from product where provider= ' Procter and Gamble '; the second step: Find the customer who purchased the ' Procter and Gamble ' product in the purchase history form Id select CustomerID from Purcase where ProductID in (select ProductID from product where provider= ' Procter and Gamble '); third step: In the Customer table, find the corresponding customer information select * from Cust Omer where CustomerID in (SELECT CustomerID from Purcase where ProductID on (select ProductID from product where provider= ' Procter and Gamble ')); ---(2) the purchase of the product contains the customer "Dennis" purchase of all items of customers (name); bought all the customer information for the goods purchased by Dennis 1, determined the table customer table to be used: Customer ID, customer name Purcase table: Customer purchase records, find the items purchased ID&NBSP;&NBSP;&NBSP;2, determine the relationship purcase.customerid= Customer.customerid; purcase.productid=product.productid; 1, Determine Dennis purchased goods Idselect CustomerID from customer where name= ' Dennis '; select ProductID from Purcase where CustomeriD = (SELECT CustomerID from customer where name= ' Dennis '); 2, find out the customer information of the goods purchased Dennis, The only way to do this is to find the same thing that Dennis bought. SELECT DISTINCT CustomerID from Purcase where ProductID in (' M01 ', ' M05 ', ' M08 '); select name from the customer where CustomerID in (SELECT DISTINCT CustomerID from Purcase where ProductID in (select PR Oductid from purcase WHERE CustomerID = (SELECT CustomerID from customer where name= ' Dennis ')); update Purcase Set PR Oductid= ' M06 ' where customerid= ' C05 ' and quantity=2; 2, the title means to find the customers who bought all the things they bought Dennis, using the mathematical calculation of the difference, The data for the difference and no longer the SELECT * from Customer Cawhere NOT exists ( select ProductID from Purcase where customer Id= ( select customerid from customer where name= ' Dennis ') minus select PRODUCTID from Purcase WHERE customerid=ca. Customerid and ca.name<> ' Dennis '); ---(3) for toothpaste to sell the largest number of suppliers. 1. Determine the table to be used &NBSP;&NBSP;&NBsp;purcase table: Customer's purchase record, according to the product ID to find the most sold Products product table: Find toothpaste Product ID, supplier &NBSP;&NBSP;&NBSP;2, Determine the associated relationship purcase.productid=product.productid; 1, first find out the idselect of the toothpaste productid,provider from product where category= ' toothpaste '; 2, and then associated two tables, according to the supplier group to find the largest number of values select temp.provider, sum (quantity) Hefrom purcase p, (select Productid,provider from product where category= ' toothpaste ') tempwhere p.productid in (select ProductID from product where Categor y= ' toothpaste ') And p.productid=temp.productidgroup by temp.provider; update purcase set productid= ' M07 ' where customerid= ' C05 ' and Quantity=8; select Max (he) from (Select temp.provider pro ,sum (quantity) Hefrom purcase p, (select Productid,provider from product where category= ' toothpaste ') tempwhere P.productid In (select ProductID from product where category= ' toothpaste ') And p.productid=temp.productidgroup by Temp.provider) ; select providerfrom productwhere productid= ( &Nbsp;select Productid from Purcase where ProductID in ( select ProductID from product where category= ' toothpaste ' ) group by productid having sum (quantity) = ( select max (sum (quantity)) from purcase where ProductID in ( select ProductID from product where category= ' toothpaste ' ) group by ProductID));
Existing database of a store, record customers and their purchases, answer questions as required
