Several Typical SQL test questions

Several Typical SQL test questions (with answers)

(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

......

(You can insert records of other user experiments)

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

...... (Insert other test 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

Write SQL statements.

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)