SQL38 查找后降序排列

select device_id, gpa, age
from user_profile 
order by gpa desc, age desc
-- 默认升序，降序在属性后加上desc

SQL40 电话号码格式校验

select id, name, phone_number
from contacts
where phone_number regexp '^[1-9][0-9]{9}$|^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$'
order by id
-- ^ 开头 $ 结尾 | 或
-- [0-9] 从0到9 {3} 三个这样的字符

SQL19 分组过滤练习题

SQL语句执行顺序 FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

select university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20
-- group by 搭配 having，不可以用where
-- SQL语句执行顺序
-- FROM - ON - JOIN - WHERE 
-- GROUP BY - WITH - HAVING 
-- SELECT - DISTINCT - ORDER BY - LIMIT

SQL21 浙江大学用户题目回答情况

select device_id, question_id, result
from question_practice_detail
where device_id in (
    select device_id
    from user_profile
    where university = '浙江大学'
)
order by question_id

SQL22 统计每个学校的答过题的用户的平均答题数

好复杂哦，有没有别的写法

select university,
count(question_id) / count(distinct q.device_id) as avg_answer_cnt
from question_practice_detail as q
inner join user_profile as u 
on q.device_id = u.device_id
group by university
order by university
-- 答过题的用户