create table t_student(
	s_id int PRIMARY KEY auto_increment,
	s_name VARCHAR(30) ,
	sex VARCHAR(2)
);

create table t_course(
	c_id int PRIMARY KEY auto_increment,
	c_name VARCHAR(30) 
);

create table stu_cou(
	sc_id int PRIMARY KEY auto_increment,
	c_id int,
	s_id int
);

INSERT INTO t_student(s_name,sex)VALUES
('张三','男'), ('李四','男'), ('王五','男'), ('赵柳','女'),
('田七','男'), ('王霸','男'), ('南丰','男'), ('梅梅','女'),
('小气','女'), ('诺手','女'), ('阿萨德','男'), ('阿斯顿','男');

INSERT into t_course(c_name) VALUES
('语文'),('计算机'),('英语'),('数学');


INSERT into stu_cou(s_id,c_id) VALUES
(1,1),(1,3),(1,2),(2,1),(2,2),(2,4),(3,2),(3,1),(3,3),(4,1),
(4,2),(4,4),(5,1),(5,2),(6,1),(6,3),(7,3),(7,2),(8,1),(9,2),
(10,4),(9,1),(10,2),(11,4),(12,1),(12,3);


#男女生各多少
SELECT
	sex,
	COUNT(*) 
FROM
	t_student 
GROUP BY
	sex;



#男女生分别占总人数的比例
SELECT
	sex,
	CONCAT( ROUND( COUNT(*) / ( SELECT COUNT(*) FROM t_student )* 100, 2 ), '%' ) 
FROM
	t_student 
GROUP BY
	sex;


#没门课程下的学生人数
SELECT
	c.c_name,
	count(*) 
FROM
	t_course c
	LEFT JOIN stu_cou sc ON sc.c_id = c.c_id
	LEFT JOIN t_student s ON sc.s_id = s.s_id 
GROUP BY
	c_name