Question material:
Query requirements:
3. Query all records in the student table
4. Query the 2nd to 4th records of the student table
5. Query the student number (id), name (name) and department (department) information of all students from the student table
6. Query information about students in the Computer Department and English Department from the student table
7. Query the information of students aged 18~22 from the student table
8. Query how many people there are in each department from the student table
9. Query the highest score of each subject from the score table
10. Query Li Si’s exam subjects (c_name) and exam results (grade)
11. Query all students’ information and examination information through connection
12. Calculate the total score of each student
13. Calculate the average score for each exam subject
14. Query the information of students whose computer scores are lower than 95
15. Query the information of students who took both computer and English exams
16. Sort the computer test scores from high to low
17. Query the student number from the student table and score table, and then merge the query results
18. Query the name, department, examination subjects and scores of students with the surname Zhang or Wang
19. Query the names, ages, departments, examination subjects and scores of students from Hunan.
3. Query all records in the student table
mysql> select * from student; + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | id | name | sex | birth | department | address | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | | 903 | Zhang San | Female | 1990 | Department of Chinese | Yongzhou City, Hunan Province | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | | 905 | Wang Wu | Female | 1991 | English Department | Xiamen City, Fujian Province | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + 6 rows in set (0.00 sec)
4. Query the 2nd to 4th records of the student table
mysql> select * from student limit 1,3; + ----- + ----------- + ------ + ------- + ----------- + --- ------------------ + | id | name | sex | birth | department | address | + ----- + ----------- + ------ + ------- + ----------- + --- ------------------ + | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | | 903 | Zhang San | Female | 1990 | Department of Chinese | Yongzhou City, Hunan Province | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | + ----- + ----------- + ------ + ------- + ----------- + --- ------------------ + 3 rows in set (0.00 sec)
5. Query the student number (id), name (name) and department (department) information of all students from the student table
mysql> select id,name,department from student; + ----- + ----------- + -------------- + | id | name | department | + ----- + ----------- + -------------- + | 901 | Boss Zhang | Department of Computer Science | | 902 | Zhang Laoer | Chinese Department | | 903 | Zhang San | Chinese Department | | 904 | Li Si | English Department | | 905 | Wang Wu | English Department | | 906 | Wang Liu | Department of Computer Science | + ----- + ----------- + -------------- + 6 rows in set (0.00 sec)
6. Query information about students in the Computer Department and English Department from the student table
mysql> select * from student where department = 'Computer Department' or department = 'English Department'; + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | id | name | sex | birth | department | address | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | | 905 | Wang Wu | Female | 1991 | English Department | Xiamen City, Fujian Province | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + 4 rows in set (0.00 sec)
7. Query the information of students aged 28~32 from the student table
mysql> select * from student where (year(now())-birth) between 28 and 32; + ----- + -------- + ------ + ------- + ---------- + ------ --------------- + | id | name | sex | birth | department | address | + ----- + -------- + ------ + ------- + ------------ + ------ --------------- + | 905 | Wang Wu | Female | 1991 | English Department | Xiamen City, Fujian Province | + ----- + -------- + ------ + ------- + ------------ + ------ --------------- + 1 row in set (0.00 sec)
8. Query how many students there are in each department from the student table
mysql> select department,count(1) number of people from student group by department; + -------------- + -------- + | department | number of people | + -------------- + -------- + | Department of Computer Science | 2 | | Chinese Department | 2 | | Department of English | 2 | + -------------- + -------- + 3 rows in set (0.00 sec)
9. Query the highest score of each subject from the score table
mysql> select c_name subject,max(grade) highest score from score group by c_name; + ----------- + ----------- + | Subject | Highest score | + ----------- + ----------- + | Computer | 98 | | English | 94 | | Chinese | 95 | + ----------- + ----------- + 3 rows in set (0.01 sec)
10. Query Li Si’s test subjects (c_name) and test scores (grade)
mysql> select name,c_name,grade from student st inner join score sc on(st.id=sc.stu_id and st.name='李思'); + -------- + ----------- + ------- + | name | c_name | grade | + -------- + ----------- + ------- + | John Doe | Computer | 70 | | John Doe | English | 92 | + -------- + ----------- + ------- + 2 rows in set (0.00 sec)
11. Query all students’ information and examination information using the connection method
mysql> select * from student st inner join score sc on(st.id=sc.stu_id); + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + | id | name | sex | birth | department | address | id | stu_id | c_name | grade | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | 1 | 901 | Computer | 98 | | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | 2 | 901 | English | 80 | | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | 3 | 902 | Computer | 65 | | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | 4 | 902 | Chinese | 88 | | 903 | Zhang San | Female | 1990 | Department of Chinese | Yongzhou City, Hunan Province | 5 | 903 | Chinese | 95 | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | 6 | 904 | Computer | 70 | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | 7 | 904 | English | 92 | | 905 | Wang Wu | Female | 1991 | Department of English | Xiamen City, Fujian Province | 8 | 905 | English | 94 | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | 9 | 906 | Computer | 90 | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | 10 | 906 | English | 85 | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + 10 rows in set (0.00 sec)
12. Calculate the total score of each student
mysql> select st.name,sum(grade) total score from student st inner join score sc on(st.id=sc.stu_id) group by stu_id order by total score desc; + ----------- + ----------- + | name | total score | + ----------- + ----------- + | Boss Zhang | 178 | | Wang Liu | 175 | | John Doe | 162 | | Zhang Laoer | 153 | | Zhang San | 95 | |王五|94| + ----------- + ----------- + 6 rows in set (0.00 sec)
13. Calculate the average score of each examination subject
mysql> select c_name subject,avg(grade) average score from score group by c_name; + ---------- + --------------- + | Subject | Average Grade | + ---------- + --------------- + | Computer | 80.7500 | | English | 87.7500 | | Chinese | 91.5000 | + ---------- + --------------- + 3 rows in set (0.00 sec)
14. Query the information of students whose computer scores are lower than 95
mysql> select st.* from student st inner join score sc on(sc.stu_id=st.id and sc.c_name='Computer' and sc.grade<=95); + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | id | name | sex | birth | department | address | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + 3 rows in set (0.00 sec)
15. Query the information of students who took both computer and English exams
mysql> select st.* from student st,(select * from score where c_name = 'Computer') as st1 inner join (select * from score where c_name = 'English') as st2 on(st1.stu_id = st2 .stu_id) where st.id=st1.stu_id; + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | id | name | sex | birth | department | address | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + 3 rows in set (0.00 sec)
16. Sort computer test scores from high to low
mysql> select st.name,sc.grade from student st inner join score sc on(st.id=sc.stu_id) where c_name='computer' order by sc.grade desc; + ----------- + ------- + | name | grade | + ----------- + ------- + | Boss Zhang | 98 | | Wang Liu | 90 | | John Doe | 70 | | Zhang Laoer | 65 | + ----------- + ------- + 4 rows in set (0.00 sec)
17. Query the student number from the student table and score table, and then merge the query results
mysql> select * from student st inner join score sc on(st.id=sc.stu_id); + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + | id | name | sex | birth | department | address | id | stu_id | c_name | grade | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | 1 | 901 | Computer | 98 | | 901 | Boss Zhang | Male | 1985 | Department of Computer Science | Haidian District, Beijing | 2 | 901 | English | 80 | | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | 3 | 902 | Computer | 65 | | 902 | Zhang Laoer | Male | 1986 | Department of Chinese | Changping District, Beijing | 4 | 902 | Chinese | 88 | | 903 | Zhang San | Female | 1990 | Department of Chinese | Yongzhou City, Hunan Province | 5 | 903 | Chinese | 95 | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | 6 | 904 | Computer | 70 | | 904 | Li Si | Male | 1990 | English Department | Fuxin City, Liaoning Province | 7 | 904 | English | 92 | | 905 | Wang Wu | Female | 1991 | Department of English | Xiamen City, Fujian Province | 8 | 905 | English | 94 | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | 9 | 906 | Computer | 90 | | 906 | Wang Liu | Male | 1988 | Department of Computer Science | Hengyang City, Hunan Province | 10 | 906 | English | 85 | + ----- + ----------- + ------ + ------- + --------------- + - ------------------ + ---- + -------- + ---------- + ---- --- + 10 rows in set (0.00 sec)
18. Query the name, department, examination subjects and scores of students surnamed Zhang or Wang
mysql> select st.name,st.department,sc.c_name,sc.grade from student st inner join score sc on(st.id=sc.stu_id) where st.name like '王%' or st. name like '张%'; + ----------- + -------------- + ----------- + ------- + | name | department | c_name | grade | + ----------- + -------------- + ----------- + ------- + | Boss Zhang | Department of Computer Science | Computer | 98 | | Boss Zhang | Department of Computer Science | English | 80 | | Zhang Laoer | Chinese Department | Computer | 65 | | Zhang Laoer | Chinese Department | Chinese | 88 | | Zhang San | Department of Chinese | Chinese | 95 | | Wang Wu | Department of English | English | 94 | | Wang Liu | Department of Computer Science | Computer | 90 | | Wang Liu | Department of Computer Science | English | 85 | + ----------- + -------------- + ----------- + ------- + 8 rows in set (0.00 sec)
19. Query the names, ages, departments, examination subjects and scores of students from Hunan
mysql> select st.name,year(now())-birth age,department,sc.c_name,sc.grade from student st inner join score sc on(st.id=sc.stu_id) where address like ' Hunan%'; + -------- + -------- + -------------- + ---------- + ---- --- + | name | age | department | c_name | grade | + -------- + -------- + -------------- + ---------- + ---- --- + | Zhang San | 33 | Chinese Department | Chinese | 95 | | Wang Liu | 35 | Department of Computer Science | Computer | 90 | | Wang Liu | 35 | Department of Computer Science | English | 85 | + -------- + -------- + -------------- + ---------- + ---- --- + 3 rows in set (0.00 sec)