成都某公司笔试题sql

– 1.某公司需要将项目管理清单导入数据库，请建表，清单字段如下:项目编码，项目名称，项目开始时间，项目结束时间，周期(月)，项目金额，客户名称，客户地址，备注。并将项目编码设置为主键，表名为ods.project list_info。
CREATE DATABASE IF NOT EXISTS ods;
USE ods;
CREATE TABLE project_list_info (
project_code INT PRIMARY KEY,
project_name VARCHAR(255),
start_date DATE,
end_date DATE,
cycle INT,
project_amount DECIMAL(10,2),
client_name VARCHAR(255),
client_address VARCHAR(255),
remarks VARCHAR(255)
);
– 2.请查询公司工资排名前三姓“张”的员工信息
SELECT* FROM ods.client WHERE client_name LIKE ‘张%’ ORDER BY client_salary DESC LIMIT 3;
– 3.请统计公司[2021,2022]年入职的男性和女性员工数量(性别自行判断)
SELECT COUNT() AS ‘人数’, gender AS ‘性别’ FROM ods.client WHERE end_date BETWEEN ‘2021-01-01’ AND ‘2022-02-01’ GROUP BY gender;
– 4.请统计每个部门的平均工资 结果按工资降序排序
SELECT AVG(client_salary) AS ‘平均工资’, department AS ‘部门’ FROM ods.client GROUP BY department ORDER BY AVG(client_salary) DESC;
– 5.请统计公司各个年龄段员工数量(年龄段为(20-30]岁，(30-40]岁，40岁以上不包含40岁)–
SELECT COUNT() AS ‘员工数量’,
CASE WHEN age > 20 AND age <= 30 THEN ‘(20-30]’
WHEN age > 30 AND age <= 40 THEN ‘(30-40]’
WHEN age > 40 THEN ‘40岁以上不包含40岁’
END AS ‘年龄段’
FROM ods.client
GROUP BY CASE WHEN age > 20 AND age <= 30 THEN ‘(20-30]’
WHEN age > 30 AND age <= 40 THEN ‘(30-40]’
WHEN age > 40 THEN ‘40岁以上不包含40岁’
END;