本次更新的内容为中级题10道，依然以题目+考核知识点+解题代码的内容输出。
话不多说，上题：
#30.使用子查询的方式找出属于Action分类的所有电影对应的title,description吗?
考点：先left join再子查询，还是直接子查询？
SELECT f.title,
f.description
FROM film f
WHERE f.film_id IN (SElECT fc.film_id
FROM film_category fc
INNER JOIN category c ON fc.category_id=c.category_id
WHERE c.name="Action")
#50.将employees表中的所有员工的last_name和first_name通过(')连接起来
考点：sqlite连接是|| ||，mysql连接是 concat
sqlite的用法：
SELECT last_name||"'"||first_name
FROM employees;
mysql的用法：
SELECT CONCAT(last_name,"'",first_name)
FROM employees;
#51.查找字符串'10,A,B' 中逗号','出现的次数cnt
考点：length，replace
SELECT (LENGTH("10,A,B")-LENGTH(REPLACE("10,A,B",",","")));
#53.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
考点：聚合函数group_concat
SELECT dept_no,
group_concat(emp_no) as employees
FROM dept_emp
GROUP BY dept_no;
#54.查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary
解法一：
SELECT avg(salary)
FROM salaries
WHERE to_date='9999-01-01'
AND salary!=(SELECT MAX(salary)
FROM salaries
WHERE to_date='9999-01-01')
AND salary!=(SELECT MIN(salary)
FROM salaries
WHERE to_date='9999-01-01');
解法二：
SELECT (SUM(salary) - MAX(salary) - MIN(salary)) (COUNT(1)-2) avg_salary
FROM salaries where to_date = '9999-01-01';
#55.分页查询employees表，每5行一页，返回第2页的数据
考点：limit，limit n offset m
解法一：
SELECT *
FROM employees
LIMIT 5,5;
解法二：
SELECT *
FROM employees
LIMIT 5 OFFSET 5
#57.使用含有关键字exists查找未分配具体部门的员工的所有信息
考点：子查询 exists，子查询where，子查询from的区别
解法一：
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no
FROM dept_emp
WHERE employees.emp_no=dept_emp.emp_no)
解法二：
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp);
解法三：
SELECT *
FROM employees e
LEFT JOIN dept_emp d ON e.emp_no=d.emp_no
WHERE d.dept_no is null;
#63.输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列
考点：dense_rank，ranking，row_number的区别
SELECT id,
number,
dense_rank() over( order by number desc) as dk
FROM passing_number
ORDER BY number DESC,id asc;
#73.查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序
考点：avg，笛卡尔积的运用
select g.id,
g.job,
g.score
from grade g ,(select id,
avg(score) over(partition by job) as avgscore
from grade ) a
where g.id=a.id and g.score>a.avgscore
order by g.id;
#78.写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序
考点：要求较多，注意细节
解法一：
select o.user_id
from order_info o
where o.product_name in ("C++","Java" ,"Python")
and o.date>"2025-10-15" and o.status="completed"
group by o.user_id
having count(*)>1
order by o.user_id;
解法二：
select distinct(user_id)
from order_info
where date>"2025-10-15"
and product_name in("C++","Java","Python")
and status="completed"
and user_id in (select user_id
from order_info
where status="completed" and date>"2025-10-15"
and product_name in("C++","Java","Python")
group by user_id
having count(*)>1)
order by user_id;
解法三：
select user_id
from order_info
where status="completed" and date>"2025-10-15" and product_name in("C++","Java","Python")
group by user_id
having count(*)>1;
