database table Operation Exercises

/*1, create a score table, the field includes: Student's name, Chinese achievement, mathematics result, English result inserting multiple data into the table; Enquiry: (1) query all students for their math scores and overall score (2) query all students for language and math scores and, by high to Low sort (3) query the name of the student with the highest total grade (4) The highest overall name of all students in the Class */--CREATE TABLE examres (ID int primary KEY AUT O_increment, name VARCHAR, Chinese float (5,2), Math float (5,2), English float (5,2));--inserting data insert INTO Examre s (name, Chinese, Math, 中文版) VALUES (' Egon ', 61.2,75.3,88), (' Alex ', 75,86,83), (' Yuanhao ', 98,96,50), (' Wupeiqi ', 86,9 0,87), (' Buer ', 100,100,100);--(1) query all student's math scores and total score Select Math,sum (chinese+math+english) as Toal_score from Examres GROUP by math;--(2) query all students for language and math scores and, sort from highest to lowest-cannot add name or *select sum (chinese+math) from Examres GRO Up by Chinese+math ORDER by Chinese+math desc;--(3) query the highest student name in the class select Name,max (Chinese+math+english) from Examres Grou P by name ORDER by Max (chinese+math+english) DESC LIMIT 1; SELECT Name,sum (chinese+math+english) as Toal_score from Examres GROUP by name ORDER by sum (CHinese+math+english) DESC LIMIT 1; Select Name,chinese+math+english as Toal_score from Examres where chinese+math+english= (select Max (chinese+math+ 中文版) from Examres);--(4) The highest overall name of the student in the inquiry class, alter TABLE examres CHARACTER SET UTF8; SELECT Name,chinese+math+english as Toal_score from Examres where name like ' e% ' ORDER by Chinese+math+english DESC LIMIT                1;/*2, create a shopping list of a supermarket, the fields include: Product name, shopping price, the date of the product raw tea, commodity classification; Insert multiple data into the table; query: (1) The total price of each category of goods (2) statistics on the number of each type of product (3) Statistics on how much the fruit cost (two ways to achieve) (4) The most expensive items in the goods produced by the 2017-01-12 date (including the products produced in 2017-01-12) (included in the data inserted) (5) Unified Buyer Total price of the product */--CREATE TABLE goods_list (ID INT PRIMARY KEY auto_increment, name VARCHAR, Price FLOAT (6,2 ), Pro_date date, Class VARCHAR ()) CHARACTER SET utf8;--insertion value INSERT into goods_list (name, Price, Pro_date, CLA                                             SS) VALUES (' Apple ', 20,20170612, ' fruit '), (' Banana ', 800,20170602, ' fruit '), (' Kettle ', 120,20170612, ' electrical '), (' Quilt ', 70,20170612, ' bedding '),                                             (' Sound ', 420,20170612, ' electrical '), (' Sheets ', 55,20170612, ' bedding '), (' Strawberry ', 34,20170612, ' fruit ');--(1) Total price for each category of goods select Class,sum from Goods_list GROUP by class;--(2) count the number of items in each category Select Class,count (Price) from Goods_list GROUP by class;--(3) Statistics how much the fruit cost (two ways to achieve) Select Class,sum (Price) From goods_list WHERE class= ' Fruit ' GROUP by class; Select SUM (price) from Goods_list where price in (SELECT price from goods_list where class= ' fruit ');--(4) statistics on the purchase of the 2017-06-12 day of production The most expensive item in the commodity (the inserted data includes the goods produced in 2017-01-12) SELECT id,name,price,pro_date from Goods_list WHERE pro_date=20170612 ORDER by Price DESC LIMIT 1;--(5) The total value of the unified purchase of Goods select SUM (prices) as Toal_price from Goods_list;

　　

database table Operation Exercises