select
	s.name,
s.class_code,
s.grade_code,
t.name as head_teacher_name
from
	students s
join
	teachers t on s.class_code = t.head_teacher
order by
	s.student_id;

select
	class_code,
count(student_id) as students_count
from
	students
group by
	class_code
order by
	students_count desc;

select
	car_cls,
count(*) as trip_count
from
	didi_sht_rcd
group by
	car_cls
order by
	trip_count desc;

select city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
	concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%')  as p
from 
    weather_rcd_china
where 
    year(dt)=2021
group by 
    city
order by 
    3 desc

select
case when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other'
end as ser_typ,
count(1) as trx_cnt,
min(date(trx_time)) as first_date
from 
	cmb_usr_trx_rcd
where
	usr_id='5201314520'
and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by 1;

select
	usr_id,
mch_nm,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
min(trx_time) as first_time
from
	cmb_usr_trx_rcd
where
	usr_id = '5201314520'
and	trx_amt >= 288
group by
	usr_id, mch_nm
order by
	trx_cnt desc;

select
	mch_nm,
sum(trx_amt) as sum_trx_amt
from
	cmb_usr_trx_rcd
where
	year(trx_time) = '2024'
and usr_id = '5201314520'
group by mch_nm
order by sum_trx_amt desc;

select * from numbers_for_fun
where
	b * b - 4 * a * c = 0 and a <> 0
order by id;

select * from numbers_for_fun
where
	a > 0 and a * 0 + b * 0 + c = 0
order by id;

select * from numbers_for_fun
where
	a > 0 and b <> 0 and c = 0
order by id;

select * from numbers_for_fun
where
	a = 0 and 3 * b + c = -8 and b <> 0
order by id;

select * from numbers_for_fun
where
	a = 0 and b < 0 and c >= 0
order by id;

select * from apple_pchs_rcd
where
	product_price >= 3000
and payment_method = 'Apple Pay'
and order_channel = '官网'
order by order_id;

select * from apple_pchs_rcd
where
	product_price >= 3000
and payment_method = 'Apple Pay'
order by order_id;

select * from scores
where
	score >= 90
and subject in ('历史', '政治', '地理')
and exam_date = '2024-06-30'
order by
	score desc,
student_id,
subject;

select * from scores
where
subject = '英语'
and score between 100 and 110
and exam_date = '2024-06-30'
order by score desc;

select * from students
where
	class_code in ('C219', 'C220', 'C221')
and
	gender = 'f'
order by
	student_id

select * from hand_permutations
where
	card1 >'J' and card1 < 'K'
and
	card2 >'J' and card2 <'K'
order by
	id;

select * from singer_info
where
	gender = 'm' and year(birth_date) < '2000';

select
	student_id
from
	subject_score
where
	chinese + math + english >= 300;