sql 面试题

create database 20190930lx;
use 20190930lx;

create table table_1
(company char(4),
id varchar(5),
age int,
salary int(10),
sex varchar(2));

insert into table_1
values
(“A”,“001”,13, 50000,“F”),
(“A”,“002”,25, 100000,“F”),
(“B”,“003”,50, 200000,“M”),
(“C”,“005”,40, 100000,“M”),
(“B”,“003”,40, 150000,“F”);

select * from table_1;

– 各公司平均年龄，且保留平均年龄大于30岁以上的公司
select company, avg(age)
from table_1
group by company
having avg(age)>30;

– 收入分组（0-5万），5-10，10-20，20万以上人数统计

select sum(case when salary<=50000 then 1 else 0 end) as “0-5万”,
sum(case when salary>50000 and salary<=100000 then 1 else 0 end) as “5-10万”,
sum(case when salary>100000 and salary<=200000 then 1 else 0 end) as “10-20万”,
sum(case when salary>200000 then 1 else 0 end) as “20万以上”
from table_1;

SELECT ‘0-5万’ 收入区间, COUNT() 人数
FROM Table_1
WHERE salary BETWEEN 0 AND 50000
UNION ALL
SELECT ‘5-10万’ 收入区间, COUNT() 人数
FROM Table_1
WHERE salary BETWEEN 50000 AND 100000
UNION ALL
SELECT ‘10-20万’ 收入区间, COUNT() 人数
FROM Table_1
WHERE salary BETWEEN 100000 AND 200000
UNION ALL
SELECT ‘20万以上’ 年龄段, COUNT() 人数
FROM Table_1
WHERE salary > 200000 ;