(-1) write in front
article refer to Http://blog.sina.com.cn/willcaty.
For one of the exercises to come up with two other answers, I hope users give more answers.
(0) basic data
Student Table
+-----+--------+------+-------+------------+--------------+
| ID | name | sex | Birth | Department | Address |
+-----+--------+------+-------+------------+--------------+
| 901 | Zhang Lu | male | 1985 | Computer Systems | Haidian District, Beijing |
| 904 | John Doe | male | 1990 | English Department | Fuxin, Liaoning province |
| 905 | Harry | female | 1991 | English Department | Xiamen, Fujian province |
| 906 | Wangliuqi | male | 1988 | Computer Systems | Hengyang, Hunan province |
+-----+--------+------+-------+------------+--------------+
Score Table
+----+--------+-----------+-------+
| Id | stu_id | C_name | Grade |
+----+--------+-----------+-------+
| 23 | 901 | Computer | 98 |
| 24 | 901 | English | |
| 25 | 902 | Computer | |
| 26 | 902 | English | |
| 27 | 903 | English | |
| 28 | 904 | Computer | |
| 29 | 904 | English | |
| 30 | 905 | English | 94 |
| 31 | 906 | Computer | |
| 32 | 906 | English | |
+----+--------+-----------+-------+
(1) find information about students who participate in both computer and English exams
Way One :
SELECT a.* from student A, score B, score C
WHERE a.id=b.stu_id
and B.c_name= ' computer '
and a.id=c.stu_id
and C.c_name= ' English ';
mode two :
SELECT * FROM Student
WHERE ID =any
(SELECT stu_id from score
WHERE stu_id in (
SELECT stu_id from
Score WHERE c_name= ' computer ')
and C_name= ' English ');
Way Three :
SELECT * from student where ID in (
Select s.stu_id from (select stu_id from score where c_name = ' computer ') s
(select stu_id from score where c_name= ' English ') as T where s.stu_id=t.stu_id)
mode four :
SELECT * from student where ID in (
Select stu_id from score where c_name = ' computer ' and stu_id in (
Select stu_id from score where c_name = ' computer ');
(2) correct answer
+-----+--------+------+-------+------------+--------------+
| ID | name | sex | Birth | Department | Address |
+-----+--------+------+-------+------------+--------------+
| 901 | Boss Zhang | Male | 1985 | Computer Systems | Haidian District, Beijing |
| 904 | John Doe | Male | 1990 | English Department | Liaoning Province Fuxin |
| 906 | Wangliuqi | Male | 1988 | Computer Systems | Hunan Province Hengyang |
+-----+--------+------+-------+------------+--------------+
MySQL exercises-Find out about students who are taking both computer and English exams-land tornado