For MySQL database practice testing 01/28 Update SLTechnology News&Howtos

For MySQL database practice testing

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. Create student and score tables

CREATE TABLE student (id INT (10) NOT NULL UNIQUE PRIMARY KEY, name VARCHAR (20) NOT NULL, sex VARCHAR (4), birth YEAR,department VARCHAR (20), address VARCHAR (50))

Create a score table. The SQL code is as follows:

CREATE TABLE score (id INT (10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, stu_id INT (10) NOT NULL, c_name VARCHAR (20), grade INT (10)

two。 Add records to student and score tables

The INSERT statement to insert a record into the student table is as follows:

INSERT INTO student VALUES (901, 'Zhang Boss', male, 1985, 'computer Department', 'Haidian District, Beijing'); INSERT INTO student VALUES (902, Zhang Laoer, male, 1986, Chinese Department, Changping District, Beijing); INSERT INTO student VALUES (903, Zhang San, female, 1990, Yongzhou, Hunan Province) INSERT INTO student VALUES (904, Li Si, male, 1990, English Department, Fuxin, Liaoning Province); INSERT INTO student VALUES (905, Wang Wu, female, 1991, English Department, Xiamen, Fujian Province); INSERT INTO student VALUES (906, Wang Liu, male, 1988 computer Department, Hengyang City, Hunan Province)

The INSERT statement to insert a record into the score table is as follows:

INSERT INTO score VALUES (NULL,901, computer, 98); INSERT INTO score VALUES (NULL,901, English, 80); INSERT INTO score VALUES (NULL,902, computer, 65); INSERT INTO score VALUES (NULL,902, Chinese, 88); INSERT INTO score VALUES (NULL,903, Chinese, 95); INSERT INTO score VALUES (NULL,904, computer, 70); INSERT INTO score VALUES (NULL,904, English, 92) INSERT INTO score VALUES (NULL,905, English, 94); INSERT INTO score VALUES (NULL,906, computer, 90); INSERT INTO score VALUES (NULL,906, English, 85)

3. Query all records of the student table

4. Query articles 2 to 4 records of the student table

5. Query the information of all students' student numbers (id), names (name) and departments (department) from the student table

6. Query the information of computer and English majors from the student table

7. Query the information of students aged 18 to 22 from the student table

Mysql > SELECT id,name,sex,2013-birth AS age,department,address

-> FROM student

-> WHERE 2013-birth BETWEEN 18 AND 22

+-+ +

| | id | name | sex | age | department | address | |

+-+ +

+-+ +

Mysql > SELECT id,name,sex,2013-birth AS age,department,address

-> FROM student

-> WHERE 2013-birth > = 18 AND 2013-birth SELECT department, COUNT (id) FROM student GROUP BY department

+-+ +

| | department | COUNT (id) |

+-+ +

| Department of computer Science | 2 |

| | English Department | 2 |

| | Chinese Department | 2 |

+-+ +

9. Query the highest score for each subject from the score table

Mysql > SELECT cymnameMAX (grade) FROM score GROUP BY cymnamethorax + | c_name | MAX (grade) | +-+-+ | computer | 98 | | English | 94 | Chinese | 95 | +-+-+ |

10. Check Li Si's examination subjects (c_name) and test results (grade)

Mysql > SELECT c_name, grade-> FROM score WHERE stu_id=-> (SELECT id FROM student-> WHERE name='Li Si'); +-+-+ | c_name | grade | +-+-+ | computer | 70 | | English | 92 | +-+-+

11. Query all the students' information and examination information by connection.

twelve。 Calculate the total score of each student

Mysql > SELECT student.id,name,SUM (grade) FROM student,score-> WHERE student.id=score.stu_id-> GROUP BY id +-+ | id | name | SUM (grade) | +-+ | 901 | Zhang Laobao | 178 | 902 | Zhang Laoer | 153 | 903 | Zhang San | 95 | 904 | Li Si | 162 | 905 | Wang Wu | 94 | | 906 | Wang Liu | 175 | +-+

13. Calculate the average score of each examination subject

Mysql > SELECT cymnamePowerAVGAVG (grade) FROM score GROUP BY cymnametre maitrawi + | c_name | AVG (grade) | +-+-+ | computer | 80.7500 | | English | 87.7500 | Chinese | 91.5000 | +-+-+ |

14. Query the information of students whose computer scores are less than 95.

Mysql > SELECT a.* FROM student a, score b, score c

-> WHERE a.id=b.stu_id

-> AND b.clocknameplate 'computer'

-> AND a.id=c.stu_id

-> AND c.clockname 'English'

+-+ +

+-+ +

16. Sort computer test scores from high to low

Mysql > SELECT stu_id, grade-> FROM score WHERE clockname = 'computer'-> ORDER BY grade DESC;+-+-+ | stu_id | grade | +-+-+ | 901 | 98 | | 906 | 90 | | 904 | 70 | | 902 | 65 | +-+-+ |

17. Query the student's student number from student table and score table, and then merge the query results

Mysql > SELECT id FROM student-> UNION-> SELECT stu_id FROM score;+-+ | id | +-+ | 901 | | 902 | | 904 | | 905 | | 906 | +-+ |

18. Inquire about the names, departments, examination subjects and scores of students surnamed Zhang or Wang

19. The inquiries are all about the names, ages, departments, examination subjects and scores of the students in Hunan.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.