MYSQL section Exercises

On the basis of three tables:

CREATE TABLE Customer (

ID varchar (primary key),
Name varchar (16),
Sex varchar (2),
Mobile varchar (11),
Address varchar (50),

);

CREATE TABLE Orderbook (

CSID varchar (10),
Cmid varchar (12),
Count Int (11),
Bookdate datetime,
Takedate datetime,
Check (takedate>bookdate),
Check (count>0),
Foreign KEY (CSID) references Customer (ID),
Foreign KEY (CMID) references commodity (ID),
Primary KEY (Csid,cmid)

);

CREATE TABLE Commodity (

ID varchar (primary key),
Name varchar () is not NULL,
Manufacture varchar (20),
Price Decimal (4,2)

);

1. Insert customer tuples into basic table customer ("0421f901","WU"," female", 13980011001)

INSERT into customer values ("0421f901", "WU", "female", 13980011001);

2. Insert a product record into the basic table commodity ("03110408591", "toothpaste", "cleaning Company",5.00)

INSERT into commodity values ("03110408591", "toothpaste", "cleaning Company", 5.00);

3. Modify the "Wangyan" customer to order the product's record delivery date is 2005-12-25.

Update orderbook set takedate= ' 2005-12-25 ' where csid= (select ID from custmoer where name = "Wangyan");

4. Delete the order of the "Nestle Milk powder" item.

Delete count,bookdate,takedate from Orderbook where csid= (select ID from
Commodity where name= "Nestle milk powder");

5. Check the phone number and address of the "ANAN" customer.

Select Mobile,address from customer where name= "ANAN";

6. The average price of a product is higher than the name of the manufacturer of the RMB.

Select manufacture from commodity where price in (select AVG (price) from commodity and AVG (price) >75);

7. Check the customer's order information, and order date in ascending order, if the order date is the same, ordered by the number of descending order.

SELECT * from Orderbook ORDER by bookdate Asc,count desc;

MYSQL section Exercises