MySQL Lesson 10: SQL面试50-Part4

1.查询出只有两门课程的全部学生的学号和姓名

SELECT S_id ,S_name,S_age,S_sex   
FROM Total   
GROUP BY S_id     
HAVING count(C_id)=2    

2.查询男生、女生人数

写法一：

SELECT sum(a) as '男生人数',sum(b) as '女生人数'  FROM  
 (SELECT CASE S_sex WHEN '男' THEN 1 ELSE 0 END AS a,
    CASE S_sex WHEN '女' THEN 1 ELSE 0 END AS b
  FROM Student) 

 写法二：

SELECT S_sex as '性别',count(*) as '人数' 
FROM Student 
GROUP BY S_sex; 

 3.查询名字中含有"风"字的学生信息

SELECT * 
FROM Student
WHERE S_name LIKE '%风'