大数据笔试题_第一阶段配套笔试题01

目录

1. 查询没有订购的客户

2. 只出现一次的最大数字

3. 存款客户与账户统计

   • 3.1 当前总客户数及存款总金额

   • 3.2 当前有存款余额的客户总数

4. 学生考试统计

   • 4.1 各年级科目考试总人数及平均成绩

   • 4.2 各年级参加考试的学生总数

   • 4.3 二年级全科80分以上学生信息

1. 查询没有订购的客户

题目描述

• 表结构：

  • Customers 表：Id（客户ID）、Name（客户姓名）

  • Orders 表：Id（订单ID）、CustomerId（客户ID）

• 目标：找出从未下过订单的客户。

参考答案

sql

SELECT Name AS Customers
FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.Id IS NULL;

2. 只出现一次的最大数字

题目描述

• 表结构：

  • my_numbers 表：num（数字，含重复值）

• 目标：找到只出现一次的最大数字。

参考答案

sql

SELECT MAX(num) AS num
FROM my_numbers
WHERE num IN (
    SELECT num
    FROM my_numbers
    GROUP BY num
    HAVING COUNT(*) = 1
);

3. 存款客户与账户统计

3.1 当前总客户数及存款总金额

表结构

• 客户信息表：CUST_ID（客户ID）、CUST_NAME（客户姓名）

• 存款余额表：ACCT_ID（账户ID）、CUST_ID（客户ID）、BAL（余额）

参考答案

sql

SELECT 
    COUNT(DISTINCT CUST_ID) AS 总客户数,
    SUM(BAL) AS 存款总金额
FROM 存款余额表;

3.2 当前有存款余额的客户总数

参考答案

sql

SELECT COUNT(DISTINCT CUST_ID) AS 有存款客户数
FROM 存款余额表
WHERE BAL > 0;

4. 学生考试统计

表结构

• Students：学号、姓名、年龄、年级

• Score：学号、课程、分数

• Class：年级、课程

4.1 各年级科目考试总人数及平均成绩

参考答案

sql

SELECT 
    C.年级,
    C.课程,
    COUNT(DISTINCT S.学号) AS 总人数,
    AVG(S.分数) AS 平均成绩
FROM Class C
LEFT JOIN Score S ON C.课程 = S.课程
GROUP BY C.年级, C.课程;

4.2 各年级参加考试的学生总数

参考答案

sql

SELECT 
    C.年级,
    COUNT(DISTINCT S.学号) AS 考试学生数
FROM Class C
LEFT JOIN Score S ON C.课程 = S.课程
GROUP BY C.年级;

4.3 二年级全科80分以上学生信息

参考答案

sql

SELECT DISTINCT S.学号, ST.姓名
FROM Score S
JOIN Students ST ON S.学号 = ST.学号
WHERE ST.年级 = 2
AND S.学号 NOT IN (
    SELECT 学号
    FROM Score
    WHERE 分数 < 80
);

总结

1. LEFT JOIN + IS NULL：适用于查找“不存在关联记录”的场景（如未订购客户）。

2. GROUP BY + HAVING：用于统计频率（如只出现一次的数字）。

3. DISTINCT + 聚合函数：确保统计时不重复计数。

4. 子查询过滤：解决“全满足条件”问题（如所有科目≥80分）。

注意事项：

• 多表连接时注意字段别名和关联条件。

• 使用AVG时注意空值处理，需明确是否需要过滤无效数据。