SQL语句练习

题目

question.png

要求：
查出“计算机系”的所有学生信息。
查出“韩顺平”所在的院系信息。
查出在“行政楼”办公的院系名称。
查出男生女生各多少人。
查出人数最多的院系信息。
查出人数最多的院系的男女生各多少人。
查出跟“罗弟华”同籍贯的所有人。
查出有“河北”人就读的院系信息。
查出跟“河北女生”同院系的所有学生的信息。

php_stu.png

t_col.png

 1. select php_stu.* from php_stu join t_col on t_col.id = php_stu.c_id where t_col.id = 1; 

 2. select * from t_col where id = (select c_id from php_stu where s_name = '韩顺平');

 3. select * from t_col where c_add like '行政楼%'

 4. select count(*),gender from php_stu GROUP BY gender;

 5. select t_col.* ,COUNT(c_id)from t_col join php_stu on c_id = t_col.id 
 GROUP BY c_id HAVING COUNT(c_id) order by COUNT(c_id) desc limit 1;

 select * from t_col where id = (select c_id from php_stu GROUP BY c_id limit 1);

 6. select gender,COUNT(*) from php_stu where c_id = (select c_id from php_stu GROUP BY c_id limit 1) GROUP BY gender;

 7. select * from php_stu where addr = (select addr from php_stu where s_name = '罗弟华') and s_name != '罗弟华';

 select a.* from php_stu as a join php_stu as b on a.addr = b.addr and b.s_name = '罗弟华';

 8. select * from t_col where id in (select c_id from php_stu where addr = '河北')

 9. select b.* from php_stu as a join php_stu as b on a.c_id = b.c_id and a.addr = '河北' and a.gender = '女 '