SQL真题

合集 - SQL学习(14)

1.SQL连接，三表连接02-262.SQL连接，内连接外连接实操02-263.SQL语句执行顺序02-264.SQL知识点：group by分组对null值的处理、avg函数、sum函数02-265.SQL知识点：order by、百分比计算、count、case、if02-266.SQL知识点：统计当月数据、统计近7天、近30天02-267.SQL知识点：日期边界值02-268.SQL知识点：min和max函数用于取第一02-269.SQL知识点：返回null值02-2610.SQL知识点：外键02-2611.SQL知识点：聚合函数常见错误02-2612.SQL知识点：条件判断用法（case when和if）02-2613.SQL知识点：连续三行数据比较02-26

14.SQL真题02-26

收起

案例1：
Course(CId,Cname,TId)
Student(SId,Sname,Sage,Ssex)
Teacher(TId,Tname)
Student_score （SId 学生编号,CId 课程编号,score 分数）
查询平均成绩大于等于60分的同学的学生编号和学生姓名

点击查看代码

select
    s.SId,
    s.Sname,
    avg(ss.Cid) As avg_score
from
    Student s
left join
    Student_score ss
on
    s.SId=ss.SId
group by
    s.SId,s.Sname
having
    avg(ss.Cid)>60

查询课程最多的学生的信息

点击查看代码

with rank_students AS(
    select
    s.SId,
    s.Sname,
    dense_rank() over (order by count(ss.CId) desc) as num
from
    Student s
left join
    Student_score ss
on
    s.SId=ss.SId
group by
    s.SId,s.Sname    
)

select
    SId,Sname
from
    rank_students
where
    num='1'                                    

案例二
sql
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(50)
);
-- 创建 Orders 表
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
-- 创建 OrderItems 表
CREATE TABLE OrderItems (
OrderItemID INT PRIMARY KEY,
OrderID INT,
ProductName VARCHAR(50),
Quantity INT,
Price DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
任务
请编写一条SQL查询，完成以下要求：
查询每个顾客在2023年下单的总金额（Order Total Amount），并按总金额降序排序。
每个顾客的总金额计算方式为：所有订单项的 之和Quantity * Price 。
如果总金额大于2000则设置vip_flag 为1，否则为0
结果应包含顾客的名字（Name）、电子邮件（Email）和总金额（TotalAmount），vip标识（vip_flag）

点击查看代码

select
    c.name,
    c.email,
    ifnull(sum(oi.Quantity*oi.Price),0) as Order_Total_Amount,
    case
        when oi.Quantity*oi.Price>2000 then 1
        else 0
    end as vip_flag
from
    Customers c
left join
    Orders o on c.CustomerID=o.CustomerID
left join
    OrderItems oi on o.OrderID=oi.OrderID
group by
    c.CustomerID,c.name
order by 
    Order_Total_Amount desc

案例三
一行SQL查询2023年有订单且总金额超过500的用户（仅限在2023年之前注册的用户），显示以下信息：
1.用户名
2.总订单金额（包括所有状态的订单）
3.有效订单数（状态为“completed”的订单）

-- 用户表
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
register_date DATE
);

-- 订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10, 2),
status VARCHAR(20),
order_date DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

点击查看代码

select
    u.username,
    ifnull(sum(o.amount),0) as total_amount,
    sum(
        case
            when o.status='completed' then 1
            else 0
        end                                                                    
    ) as order_count
from
    users u
left join
    orders o
on
    u.user_id=o.user_id
where
    u.register_date<'2024-01-01'
and
    o.order_date between '2023-01-01' and '2024-01-01'
group by
    u.user_id,username
having
    sum(o.amount)>'500'