MySQL exercises-Find out about students who are taking both computer and English exams-land tornado

(-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