A few classic SQL written questions

A few classic SQL written questions (with answers)

(1) Table name: Shopping Information

Number of shopper product names

A 2

B Ethyl 4

C-Propyl 1

A-Butyl 2

B-C 5

......

(The record of other user's experiment can be inserted by oneself)

Give all purchased items for two or more shopper records

Answer: SELECT * from shopping information where shopper in (select Shopper from shopping information Group by shopper having COUNT (*) >= 2);

(2) Table name: Score Table

Name Course Score

Zhang San language 81

Zhang San Mathematics 75

John Doe Language 56

John Doe Mathematics 90

Harry Language 81

Harry Mathematics 100

Harry English 49

......

(The record of other user's experiment can be inserted by oneself)

Give the student information (including name, course, score) of all qualified students, note: score above 60 rated as qualified

Answer: SELECT * from the score table where name not in (select distinct name from score table where scores < 60)

Or:

SELECT * from score table where name in (select name from score table GROUP by name have min (score) >=60)

(3) Table name: Commodity table

Name of origin in the price

Apple Yantai 2.5

Apple Yunnan 1.9

Apple Sichuan 3

Watermelon Jiangxi 1.5

Watermelon Beijing 2.4

......

(The record of other user's experiment can be inserted by oneself)

Give the name of the product with an average price below 2 yuan

Answer: Select name from commodity List group by name have AVG (price) < 2

(4) Table name: College Entrance Examination Information Form

Admission ticket number of subject score

2006001 Languages 119

2006001 Mathematics 108

2006002 Physics 142

2006001 Chemistry 136

2006001 Physics 127

2006002 Mathematics 149

2006002 English 110

2006002 Languages 105

2006001 English 98

2006002 Chemistry 129

......

(The record of other user's experiment can be inserted by oneself)

Give the student's ticket number of the college entrance examination score above 600

A: Select ticket number from the college Entrance Examination Information Form GROUP BY ticket number having sum (score) > 600

(5) Table name: College Entrance Examination Information Form

Admission number Math Chinese English Physical Chemistry

2006001 108 119 98 127 136

2006002 149 105 110 142 129

......

(The record of other user's experiment can be inserted by oneself)

Give the student's ticket number of the college entrance examination score above 600

Answer: Select ticket number from the college Entrance Examination information table where (math + language + english + physics + chemistry) > 600

(Four parts)

(a) Table name: Club

ID gender age

19 M

30 F

27 F

16 F

M 32

...... (Please insert the remaining test data yourself)

Find out the total number of male and female members in the club

Answer: SELECT Gender,count (ID) from the club group by gender

(b) Table name: Team

ID (number Type) Name (type VARCHAR2)

1 A

2 b

3 b

4 A

5 C

6 C

Requirements: Executes a DELETE statement, when the Name column has the same, only the ID of the column on the value of the small

For example, the result after deletion should be as follows:

ID (number Type) Name (type VARCHAR2)

1 A

2 b

5 C

Please write out the SQL statement.

Delete from the team where ID not in (the Select min (ID) from the team group by name)

(c) Table name: Student

Name Course Score

Zhang Qing Language 72

Wang Hua Mathematics 72

Zhang Hua English 81

Zhang Qing Physics 67

Li Li Chemical 98

Zhang Yan Physics 70

Zhang Qing Chemical 76

Check out the student information of "Zhang" student with average score greater than 75

Answer: SELECT * from student where name in (select name from student

Where name like ' Zhang% ' group by name have AVG (score) > 75)

A few classic SQL written questions