Several classic SQL questions

(1) Table Name: shopping information
Number of commodity names of the shopper
A A 2
B 4
C 1
A Ding 2
B C 5
......
( Others You can insert user lab records on your own)

Records of buyers of two or more purchased items

Answer: Select * from shopping information where shopping person in (select shopping person from shopping information group by shopping person having count (*)> = 2 );

(2) Table Name: orders table
Name course score
Zhang San Language 81
James math 75
Li Si language 56
Li Si mathematics 90
Wang Wu language 81
Wang Wu, mathematics 100
Wang Wu English 49
......
(You can insert records of other user experiments)

Information about all qualified students (including names, courses, and scores) is provided. Note: The score above 60 is rated as qualified.

Answer: Select * from orders table where name not in (select distinct name from orders table where score <60)
Or:
Select * from orders table where name in (Select name from orders table group by name having min (score)> = 60)

(3) Table Name: item table
Name origin purchase price
Apple Yantai 2.5
Apple Yunnan 1.9
Apple Sichuan 3
Watermelon Jiangxi 1.5
Watermelon, Beijing 2.4
......
(You can insert records of other user experiments)

Name of the product whose average price is less than 2 yuan

Answer: Select name from commodity table group by name having AVG (purchase price) <2

(4) Table Name: College Entrance Examination Information table
Admission Ticket No. Score
2006001 language 119
2006001 mathematics 108
2006002 physical 142
2006001 chemistry 136
2006001 physical 127
2006002 mathematics 149
2006002 English 110
2006002 language 105
2006001 English 98
2006002 chemistry 129
......
(You can insert records of other user experiments)

The admission ticket number of the student whose total score is more than 600 is provided.

Answer: Select Admission Ticket No. From College Entrance Examination Information table group by Admission Ticket No. Having sum (score)> 600

(5) Table Name: College Entrance Examination Information table
Admission Ticket No. Mathematics, Chinese, English, physical chemistry
2006001 108 119 98 127 136
2006002 149 105 110 142 129
......
(You can insert records of other user experiments)

The admission ticket number of the student whose total score is more than 600 is provided.

Answer: Select Admission Ticket No. From College Entrance Examination Information table where (Mathematics + Chinese + English + physics + chemistry)> 600

(Part 4)
(1) Table Name: Club

Id gender age
67 m 19
68 F 30
69 F 27
70 F 16
71 m 32
...... (OtherTestInsert data on your own)

Query the total number of male and female members in the club.

A: select gender, count (ID) from Club group by gender

(2) Table Name: Team
ID (number type) name (varchar2 type)
1
2 B
3 B
4
5 C
6 c
Requirement: execute a delete statement. When the name column has the same value, only the column with a small value of ID is retained.
For example, the deletion result should be as follows:
ID (number type) name (varchar2 type)
1
2 B
5 C
Please writeSQLStatement.

Delete from team where id not in (select Min (ID) from team group by name)

(3) Table Name: Student

Name course score
Zhang Qing Chinese 72
Wang Hua mathematics 72
Zhang Hua English 81
Zhang Qing physical 67
Li chemistry 98
Zhang Yan physical 70
Zhang Qing chemical 76

The average score of students with the surname "Zhang" is more than 75.

A: Select * from student where name in (Select name from student
Where name like 'zhang % 'group by name having AVG (score)> 75)