mysql---面试题2

两张表，

CREATE TABLE student(
id int not null PRIMARY KEY AUTO_INCREMENT,
sname  varchar(20) not null 

)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE course1(
stu_id int not null,
courseId int not NULL ,
cname varchar(20) not null 

)ENGINE=INNODB CHARSEt=utf8;

student(学生表)

course（课程表）

1.查询学生选了两门课以上的学生信息

SELECT s.id,s.sname from student s where s.id in  ( SELECT c.stu_id  from course c   GROUP BY c.stu_id HAVING count(c.stu_id)>=2)

2.查询学生选课的情况，如果为null，用0代替

SELECT
	s.id,
	s.sname,c.cname,c.courseId,c.stu_id,(
	CASE sum(c.stu_id)
	WHEN sum(c.stu_id) is null THEN
		0
	ELSE
		count(c.cname)
END ) 选课数量

	FROM
		student s
	LEFT JOIN course c ON s.id = c.stu_id GROUP BY c.stu_id