*** The answer lies in At the bottom ***
（1） Table name ： Shopping information
Shopper Trade name number
A nail 2
B B 4
C C 1
A Ding 2
B C 5
……
（ Other user experiment records can be inserted by you ）
Give the records of all buyers who buy two or more kinds of goods
（2） Table name ： Score sheet
full name curriculum fraction
Zhang San chinese 81
Zhang San mathematics 75
Li Si chinese 56
Li Si mathematics 90
Wang Wu chinese 81
Wang Wu mathematics 100
Wang Wu English 49
……
（ Other user experiment records can be inserted by you ）
Give the information of all students who are qualified （ Include name , curriculum , fraction ）, notes ： Score in 60 The above assessment is qualified
（3） Table name ： Commodity list
name Place of Origin Purchase price
Apple Yantai 2.5
Apple Yunnan 1.9
Apple Sichuan 3
watermelon Jiangxi 1.5
watermelon Beijing 2.4
……
（ Other user experiment records can be inserted by you ）
The average purchase price is given in 2 Commodity name below RMB
（4） Table name ： College entrance examination information form
Admission card No subject achievement
2006001 chinese 119
2006001 mathematics 108
2006002 Physics 142
2006001 Chemistry 136
2006001 Physics 127
2006002 mathematics 149
2006002 English 110
2006002 chinese 105
2006001 English 98
2006002 Chemistry 129
……
（ Other user experiment records can be inserted by you ）
Give the total score of college entrance examination in 600 Above student admission number
（5） Table name ： College entrance examination information form
Admission card No mathematics chinese English Physics Chemistry
2006001 108 119 98 127 136
2006002 149 105 110 142 129
……
（ Other user experiment records can be inserted by you ）
Give the total score of college entrance examination in 600 Above student admission number
（6） Table name ：club
id gender age
67 M 19
68 F 30
69 F 27
70 F 16
71 M 32
……（ Please insert other test data by yourself ）
Find out the total number of male and female members in the club
（7） Table name ：team
ID(number type ) Name(varchar2 type )
1 a
2 b
3 b
4 a
5 c
6 c
requirement : Execute a delete statement , When Name When the column has the same , Keep only ID This column has small values
for example ： The result of deletion should be as follows :
ID(number type ) Name(varchar2 type )
1 a
2 b
5 c
Please write SQL sentence .
（8） Table name ：student
name course score
Zhang Qing chinese 72
Wang Hua mathematics 72
Zhang Hua English 81
Zhang Qing Physics 67
Li Li Chemistry 98
Zhang Yan Physics 70
Zhang Qing Chemistry 76
Find out “ Zhang ” The average score of students with surname is greater than 75 Student information
================== answer =====================
（1） answer ：select * from Shopping information where Shopper in (select Shopper from Shopping information group by Shopper having
count(*) >= 2);
（2） answer ：select * from Score sheet where full name not in (select distinct full name from Score sheet where fraction <
60)
perhaps ：
select * from Score sheet where full name in (select full name from Score sheet group by full name having min( fraction )
>=60)
（3） answer ：select name from Commodity list group by name having avg( Purchase price ) < 2
（4） answer ：select Admission card No from College entrance examination information form group by Admission card No having sum( achievement ) > 600
（5） answer ：select Admission card No from College entrance examination information form where ( mathematics + chinese + English + Physics + Chemistry ) > 600
（6） answer ：select gender,count(id) from club group by gender
（7） answer ：delete from team where id not in (select min(id) from team group by name)
（8） answer ：select * from student where name in (select name from student
where name like ' Zhang %' group by name having avg(score) > 75)
Technology