FCP帆软交付工程师官网模拟题-SQL模块

Yours飞飞

已于 2024-06-19 08:48:01 修改

阅读量654

点赞数 10

分类专栏： FineReport 文章标签： sql 数据库

于 2024-06-19 08:45:28 首次发布

本文链接：https://blog.csdn.net/weixin_45397582/article/details/139788949

版权

FineReport 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

仅供学习参考思路
--------------------------------------------------------------------------------------

题目要求
模板要求
SQL:

WITH pm AS (  
    SELECT  
        CASE   
            WHEN a.货主省份 IN ('北京', '天津', '上海', '重庆') THEN '直辖市'  
            ELSE a.货主省份  
        END AS p_mapped,  
        a.订单ID AS m_order_id,  
        DATE(a.到货日期) AS a_date,  
        (b.数量 * (b.单价 * (1 - b.折扣) - b.进价)) AS profit  
    FROM 订单 a  
    INNER JOIN 订单明细 b ON a.订单ID = b.订单ID  
    WHERE a.货主国家 = '中国'  
),  
pc AS (   
    SELECT   
        a_date,
        m_order_id,
        p_mapped,   
        CAST(FLOOR(profit) AS INT) AS p_int,        
        SUBSTR(CAST(ROUND((profit - FLOOR(profit)), 2) AS VARCHAR), 2) AS p_de
    FROM pm
 
    UNION ALL
 
    SELECT   
        DATE('now') AS a_date,
        '90000' AS m_order_id,
        '四川' AS p_mapped,   
        0 AS p_int,        
        '.01' AS p_de
)
SELECT   
    a_date 到货日期,
    'C' || SUBSTR(m_order_id, 3, 3) AS 订单ID ,
    p_mapped 省份,   
    p_int 利润整数,
    p_de 利润小数
FROM pc;

题目要求
SQL:

# 使用mysql数据库,库名为northwind1
with 
t1 as (select 订单ID,客户ID,运货商,运货费 from 订单),
t2 as (select 订单ID,产品ID,(单价* (1-折扣)* 数量) as 销售额 from 订单明细),
t3 as (select 产品ID,类别ID,订购量 from 产品)
select 
t1.订单ID,
t1.客户ID,
t1.运货商,
t1.运货费,
t2.产品ID,
t2.销售额,
t3.类别ID,
t3.订购量
from t1 
left join t2 on t1.订单ID=t2.订单ID
left join t3 on t2.产品ID=t3.产品ID

题目要求
模板要求
SQL:

SELECT 客户ID,
	  SUM(应付金额) as 应付总金额
FROM 订单
GROUP BY 客户ID
HAVING SUM(应付金额)<1800;

题目要求
模板要求
SQL:

# 使用mysql数据库,库名为northwind1
/*1.查询出每个产品哪两年的销售额最多*/
#ROW_NUMBER() OVER(PARTITION BY 产品ID,年份 ORDER BY t.销售额 DESC) AS Row_Index --Access 无法用
WITH tmp1 as(
SELECT
oddt.产品ID AS 产品ID #product_id
,year(od.到货日期) AS 年份 #c_year
,SUM(oddt.数量 * (oddt.单价 * (1 - oddt.折扣))) as 销售额 #c_amount
FROM 订单 od
INNER JOIN 订单明细 oddt ON od.订单ID = oddt.订单ID
GROUP BY 产品ID, 年份
),tmp2 as(
SELECT *
FROM tmp1 t ORDER BY 销售额 desc,产品ID
)
#SELECT * FROM tmp2
,tmp3 as(
SELECT
t1.年份, t1.产品ID, t1.销售额,
COUNT(*) AS Row_Index
FROM tmp2 t1
LEFT JOIN tmp2 t2
ON t1.产品ID = t2.产品ID AND
t1.销售额 <= t2.销售额
GROUP BY t1.年份, t1.产品ID, t1.销售额
ORDER BY t1.产品ID, t1.销售额 DESC
)
SELECT t2.Row_Index,p.产品名称 ,t2.销售额,t2.年份
FROM tmp3 t2,产品 p
WHERE t2.产品ID=p.产品ID and Row_Index<=2
ORDER BY t2.产品ID, t2.销售额 DESC;


/*2.查询供应商中能够供应的产品种类最多的供应商*/
with a as (
select t1.供应商ID,t1.公司名称,count(1) as 产品种类数量 from 供应商 t1,产品 t2 
where t1.供应商ID=t2.供应商ID
group by t1.供应商ID,t1.公司名称)
select 供应商ID,公司名称,产品种类数量 from a
where 产品种类数量 in (select max(产品种类数量) from a)


/*3.按照订单送达时长由长到短排序，并返回该订单的供应商*/
WITH
t1 as (select 运货商,timestampdiff(day,发货日期,到货日期) as 到货时长 from 订单),
t2 as (select 运货商ID,公司名称 from 运货商)
select t2.公司名称,t1.到货时长 from t1 
join t2 on t1.运货商=t2.运货商ID
order by t1.到货时长 desc

题目要求
模板要求
SQL:

-- 建表语句
CREATE TABLE stu (
    学号 INT PRIMARY KEY,
    年级 INT,
    姓名 VARCHAR(5),
    分数 INT
);
INSERT INTO stu (学号,年级,姓名,分数) VALUES (1, 1, 'A', 65),(2, 1, 'B', 34),(3, 2, 'C', 53),(4, 2, 'D', 12);
 
-- 查询语句
WITH ranked_stu AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY 年级 ORDER BY 学号) AS rn
    FROM stu
)
SELECT 学号,年级,姓名,分数
FROM ranked_stu
WHERE rn = 1;

题目要求
模板要求
所需文件
SQL:

-- 建表语句
CREATE TABLE scores (
    姓名 VARCHAR(20),
    语文 INT,
    数学 INT,
    物理 INT
);
INSERT INTO scores (姓名, 语文, 数学, 物理) VALUES ('李四', 74, 84, 94),('刘五', 74, 83, 63);
 
-- 查询语句
SELECT 姓名, '语文' AS 课程, 语文 AS 分数 FROM scores
UNION ALL
SELECT 姓名, '数学' AS 课程, 数学 AS 分数 FROM scores
UNION ALL
SELECT 姓名, '物理' AS 课程, 物理 AS 分数 FROM scores;

题目要求
模板要求
所用文件
SQL:

-- 建表语句
CREATE TABLE `员工入职表` (
  `入职日期` date,
  `入职人数` int(11),
  `离职人数` int(11)
) ;
INSERT INTO `员工入职表` VALUES 
('2019-01', '55', '6'),('2019-02', '34', '4'),('2019-04', '65', '8'),
('2019-05', '29', '2'),('2019-07', '33', '3'),('2018-01', '42', '11'),
('2018-02', '28', '9'),('2018-03', '31', '2'),('2018-06', '21', '2'),
('2018-07', '19', '4'),('2018-08', '37', '7'),('2018-10', '32', '6'),
('2018-11', '22', '1'),('2018-12', '26', '1');

/*查找2019年各个月份的入职以及去年同期的入职*/
SELECT
	a.入职日期 as 年月,
	a.入职人数,
	c.上月入职人数,
	b.去年同期人数
FROM
	( SELECT SUBSTR(入职日期, 6, 2 )* 1 AS 日期,入职日期,入职人数 FROM 员工入职表 WHERE SUBSTR(入职日期, 1, 4 )= '2019' ) AS a
	LEFT JOIN ( SELECT SUBSTR(入职日期, 6, 2 )* 1 AS 日期,入职日期,入职人数 AS 去年同期人数 FROM 员工入职表 WHERE SUBSTR(入职日期, 1, 4 )= '2018' ) AS b ON a.日期 = b.日期
	LEFT JOIN (
	SELECT
		SUBSTR(入职日期, 6, 2 )+ 1 AS 日期,入职日期,入职人数 AS 上月入职人数 
	FROM
		员工入职表 
	WHERE
		SUBSTR(入职日期, 1, 4 )= '2019' UNION
	SELECT
		( CASE WHEN SUBSTR(入职日期, 6, 2 )* 1 = 12 THEN 1 END ) AS 日期,入职日期,入职人数 AS 上月入职人数 
	FROM
		员工入职表 
	WHERE
		SUBSTR(入职日期, 1, 4 )= '2018' 
	) AS c ON a.日期 = c.日期 
ORDER BY
	a.入职日期 ASC

题目要求
所用文件
SQL：

-- 建表语句
CREATE TABLE 产品销售表 (
    产品名称 VARCHAR(50),
    销售额 INT
);
 
INSERT INTO 产品销售表 (产品名称, 销售额) VALUES 
('产品A', 90),
('产品B', 84),
('产品C', 73),
('产品D', 100),
('产品E', 103),
('产品F', 89);
 
-- 查询前N个销售额高的产品，它们的合计销售额占全体销售额85%以上
WITH t1 AS (
    SELECT
        产品名称,
        销售额,
        SUM(销售额) OVER (ORDER BY 销售额 DESC) AS 累计销售额
    FROM
        产品销售表
),
t2 AS (
    SELECT SUM(销售额) AS 全体销售额 FROM 产品销售表
)
SELECT
    产品名称,
    销售额
FROM
   t1,t2 
WHERE
    ((t1.累计销售额) - (t1.销售额)) <= t2.全体销售额 * 0.85
ORDER BY
    销售额 DESC;

题目要求
模板要求
SQL：

-- 建表语句
CREATE TABLE fei_学生成绩表 (
    姓名 VARCHAR(50),
    课程 VARCHAR(50),
		分数 INT
);
 
INSERT INTO fei_学生成绩表 (姓名, 课程,分数) VALUES 
('张三','语文', 81),
('张三','数学', 75),
('李四','语文', 56),
('李四','数学', 90),
('王五','语文', 81),
('王五','数学', 44),
('王五','英语', 49)

select 姓名, 课程,case when 分数>=60 then '合格' else '不合格' end as 分数 
from fei_学生成绩表
where 课程 in ('语文','数学')

题目要求
模板要求
SQL：

SELECT  R1.类别, R1.月份,  SUM(R2.Revenue) 累计金额
FROM Revenue R1, Revenue R2
WHERE
R1.ID >= R2.ID
AND R1.类别 = R2.类别
GROUP BY  R1.月份, R1.类别
ORDER BY R1.ID, R1.类别 , R1.月份;

题目要求
SQL：

/*1.*/
with
t1 as (select PRODUCERID,CITY from PRODUCER where city in ('ShangHai','NewYork')),
t2 as (select PRODUCERID,PRODUCTNAME,COST from PRODUCT where COST>=3000 or COST<=500)
select 
t1.CITY as 地区,
t2.PRODUCTNAME as 产品名称,
case when t2.COST>=3000 then 1
     when t2.COST<=500 then 0
		 end as 标记列
from t1,t2 
where t1.PRODUCERID=t2.PRODUCERID
/*2.*/
with 
t1 as (select PRODUCERID,COUNTRY from PRODUCER),
t2 as (select PRODUCTNAME,PRODUCERID,COST*QUANTITY as 库存额,row_number() over (partition by PRODUCERID order by COST*QUANTITY desc) r from PRODUCT)
select t1.COUNTRY as 国家,t2.PRODUCTNAME as 产品名称,t2.库存额
from t1,t2 
where t1.PRODUCERID=t2.PRODUCERID and r=1

题目要求
SQL：

SELECT
	A.CLASSNO,
	A.COURSE,
	COUNT( A.CLASSNO ) 人数
FROM
	Stscore A
	INNER JOIN ( SELECT CLASSNO, COURSE, AVG( GRADE ) AVG_ FROM Stscore GROUP BY CLASSNO, COURSE ) B ON A.CLASSNO = B.CLASSNO 
	AND A.COURSE = B.COURSE 
WHERE
	A.GRADE < B.AVG_ 
GROUP BY
	A.CLASSNO,
	B.AVG_,
	A.COURSE

题目要求
SQL：

-- MYSQL数据库
-- (1) 获取系统当前时间
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS 系统当前时间;
 
-- (2) 在系统当前时间的基础上截取月份
SELECT DATE_FORMAT(NOW(), '%m') AS 当前月份;


--SQLite数据库
-- (1) 获取系统当前时间
select date('now') AS 系统当前时间;
 
-- (2) 在系统当前时间的基础上截取月份
select strftime('%m','now') AS 当前月份;