-- 1.查询班级名称为WWT105的班级，在2023年2月15日未签到的学生名单
select users.`Name`,attendances.Date from users INNER JOIN attendances 
on users.Id=attendances.UserId
INNER JOIN class 
on attendances.ClassId=class.Id
where class.`Name`='WWT105'
and attendances.Date='2023-02-15'
and attendances.SignIn=0
GROUP BY users.`Name`

-- 2.查询班级名称为WWT105的班级的男女比例
SELECT (SELECT COUNT(*) from users where users.Sex=0)/
			 (SELECT COUNT(*) from users where users.Sex=1) as '男女比例'
from users INNER JOIN class 
on users.ClassId=class.Id
where class.`Name`='WWT105' 
GROUP BY '男女比例'

-- 3.查询班级名称为WWT105的班级，在学习阶段为“功能测试阶段”，未参加考试的名单(即，分数=null)
select * from users,class,score 
where users.ClassId=class.Id
and users.Id=score.UserId
and class.`Name`='WWT105' 
and score.Stage='功能测试阶段' 
and users.Grade is null 

-- 4.查询2023年2月1日，全员都签到的班级名称
select users.`Name`,attendances.Date from users INNER JOIN attendances 
on users.Id=attendances.UserId
INNER JOIN class 
on attendances.ClassId=class.Id
where attendances.Date='2023-2-1'
and attendances.SignIn=1
GROUP BY users.`Name`

-- 5.查询全校的男生平均年龄、女生平均年龄、全校平均年龄
SELECT
(select avg(Age) from users where Sex=1)as '男生',
(select avg(Age) from users where Sex=0)as '女生',
(select avg(Age) from users)as '全校'
from users


-- 6.查询班级名称为WWT105的班级，成绩ABCDE每个等级的人数分别有多少
SELECT
(SELECT COUNT(users.Grade) from users where users.Grade='A')as 'A级',
(SELECT COUNT(users.Grade) from users where users.Grade='B')as 'B级',
(SELECT COUNT(users.Grade) from users where users.Grade='C')as 'C级',
(SELECT COUNT(users.Grade) from users where users.Grade='D')as 'D级',
(SELECT COUNT(users.Grade) from users where users.Grade='E')as 'E级'
FROM users INNER JOIN class
on users.ClassId=class.Id
where class.`Name`='WWT105'
GROUP BY 'A级'


-- 7.查询班级名称WWT105班级里，名为“阿龙”的同学参加考试的所有成绩单平均分
SELECT 
		AVG(score.Score)
FROM users 
INNER JOIN class on users.ClassId=class.Id
INNER JOIN score on users.Id=score.UserId
where class.`Name`='WWT105'
and users.`Name`='along'
-- 8.假设分数100~90=A级、89~75=B级、74~60=C级、59~45=D级、44~O=E级，输录入WWT105的“阿龙”同学成绩单，并更新相关表(需更新score成绩表、User学生用户表、Class班级表)
-- insert into 表名(列名1) VALUES(值1)  

 select users.`Name`,score.Stage,
		CASE 
		WHEN score.Score <=44 THEN 'E级'
		WHEN score.Score <=59 THEN 'D级'
		WHEN score.Score <=74 THEN 'C级'
		WHEN score.Score <=89 THEN 'B级'
		WHEN score.Score <=100 THEN 'A级'
		ELSE '无评级'	
	END as '评判等级'
 from users INNER JOIN score 
 on users.Id = score.UserId
 where users.`Name`='along'