SQL 经典题

1、针对库中的所有表生成select count(*)对应的SQL语句

在 SQLite 系统表 sqlite_master 中可以获得所有表的索引,其中字段 name 是所有表的名字,而且对于自己创建的表而言,字段 type 永远是 ‘table‘,详情可参考:

SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = ‘table‘
# 在Mysql中,针对库中的所有表生成select count(*)对应的SQL语句:
select concat("select count(*) from ",table_name,";") as cnts 
from information_schema.tables where table_schema=‘niuke‘;  #在Mysql中,针对所有数据库中的所有表生成select count(*)对应的SQL语句:select concat("select count(*) from ",table_name,";") as cntsfrom (select table_name from information_schema.tables) as new;
  
# MySQL中获得所有表的名称:
select table name
from information schema.tables where table schema=‘mydata‘;2、查找字符串‘10,A,B‘ 中逗号‘,‘出现的次数cnt。 

select length("10,A,B")-length(replace("10,A,B",",","")) as cnt
3、获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
select first_name
from employees
order by substr(first_name,length(first_name)-1)
4、按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees

聚合函数group_concat(X,Y),其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组,将每个分组中不同的emp_no用逗号连接起来(即可省略Y)。

select dept_no,group_concat(emp_no) employees 
from dept_emp
group by dept_no
5、查找排除当前最大、最小salary之后的员工的平均工资avg_salary。
///错误原因
select avg(salary) as avg_salaryfrom salaries where salary not in( select min(salary),max(salary) from salaries)and to_date = ‘9999-01-01‘;

/*select min(salary),max(salary) from salaries;这个语句最后输出的是两列,但是not in的话,只对一列的值进行操作,所以会报错.*/
select avg(salary) as avg_salary 
from salaries
where to_date=‘9999-01-01‘ and salary not in
(
    (
        select max(salary)
        from salaries
    ),
    (
        select min(salary)
        from salaries
    )
)
6、分页查询employees表,每5行一页,返回第2页的数据
select * 
from employees limit 5,5

# 查询10条数据,索引从0到9,第1条记录到第10条记录
select * from t_user limit 10;
select * from t_user limit 0,10;

# 查询8条数据,索引从5到12,第6条记录到第13条记录
select * from t_user limit 5,8;
7、获取select * from employees对应的执行计划
explain  用于获得表的所有细节
EXPLAIN SELECT

    *

FROM

    employees;

 8、给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01‘

select E.emp_no,E.first_name,E.last_name,EB.btype,S.salary,
(
    CASE EB.btype
    WHEN 1 THEN S.salary*0.1
    WHEN 2 THEN S.salary*0.2
    ELSE S.salary*0.3
    END
)
AS bonus
from employees E,salaries S
ON E.emp_no=S.emp_no
inner join emp_bonus EB
on E.emp_no=EB.emp_no
WHERE S.to_date=‘9999-01-01‘

9、累积递增求和:照salary的累计和running_total,其中running_total为前两个员工的salary累计和,其他以此类推。 具体结果如下Demo展示。

 SQL 经典题
select S1.emp_no,S1.salary,
(
    SELECT sum(S2.salary)
    FROM salaries S2
    WHERE S1.emp_no>=S2.emp_no
    AND S2.to_date = ‘9999-01-01‘
)
as running_total
FROM salaries S1
WHERE S1.to_date = ‘9999-01-01‘
ORDER BY S1.emp_no

相关推荐