Mysql-5 Practice

--Create Product Type table CREATE TABLE Product_type (protype_id INT PRIMARY KEY auto_increment, Protype_name VARCHAR ( the) SELECT * from Product_type;insert product_type (protype_name) VALUES ('Household Appliances'); INSERT product_type (Protype_name) VALUES ('Mobile Phone Digital'); INSERT product_type (Protype_name) VALUES ('Computer Office'); INSERT product_type (Protype_name) VALUES ('Book Audio'); INSERT product_type (Protype_name) VALUES ('Home Furniture'); INSERT product_type (Protype_name) VALUES ('Apparel Accessories'); INSERT product_type (Protype_name) VALUES ('a protective makeup'); INSERT product_type (Protype_name) VALUES ('Sports Outdoor'); INSERT product_type (Protype_name) VALUES ('Car Supplies'); INSERT product_type (Protype_name) VALUES ('Food and Beverage'); INSERT product_type (Protype_name) VALUES ('Nutritional Care';D ROP Table product;--Create Product table CREATE TABLE product (proid INT PRIMARY KEY, Pro_name VARCHAR ( -), protype_id int, price int, Pinpai VARCHAR (Ten), Chandi VARCHAR (Ten), CONSTRAINT product_product_type_fk FOREIGN KEY (protype_id) REFERENCES Product_type (protype_id)) SELECT * from PR  Oduct; INSERT into product VALUES (1,'Konka (Knoka) 42-inch Full HD LCD TV',1,1999,'Konka','Shenzhen'); INSERT into product VALUES (2,'Sony (Sony) 4G mobile Phone (black)',2,3238,'Sony','Shenzhen'); INSERT into product VALUES (3,'Hisense (Hisense) 55 inch Smart TV',1,4199,'Hisense','Qingdao'); INSERT into product VALUES (4,'Lenovo (Lenovo) 14.0-inch notebook PC',3,5499,'Lenovo','Beijing'); INSERT into product VALUES (5,'Sony (Sony) 13.3-inch touch Super Ben',3,11499,'Sony','Tianjin'); INSERT into product VALUES ( One,'Sony (Sony) 60-inch Full HD LCD TV',1,6999,'Sony','Beijing') INSERT into product VALUES ( A,'Lenovo (Lenovo) 14.0-inch notebook PC',3,2999,'Lenovo','Beijing'); INSERT into product VALUES ( -,'Lenovo Dual SIM 3G mobile phone',2,988,'Lenovo','Beijing') INSERT into product VALUES ( the,'hp (HP) monochrome laser Printer',3,1169,'HP','Tianjin'); --1 query price between 1000~5000 the brand is Lenovo's product name, commodity price, product type Select Pro_name,price,protype_name from Product JOIN product_type on product. ' protype_id ' = product_type. ' protype_id 'WHERE Price between +and the;--2 query with ID 5 of the product type of the same brand, origin, number of products of this brand, SELECT Pinpai,chandi,count (Pinpai) from product WHERE protype_id = ( SELECT protype_id from product WHERE proid =5) GROUP by pinpai;--3Delete records in the Product type table with IDs greater than 7 delete from Product_type WHERE protype_id>7;--4 Modify ' home furniture ' for ' furniture supplies '; UPDATE product_type SET protype_name ='Furniture Supplies'WHERE protype_id =5;--5 Query the brand and price of all goods under ' home appliances ' select Pinpai,price from Product JOIN product_type on product.' protype_id ' = product. ' protype_id 'WHERE protype_name ='Household Appliances'；

Mysql-5 Practice