oracle 排名习题

1. 给定以下表格结构，请编写 SQL 查询语句，按照销售额从高到低排名，显示每个顾客的名称和销售额。

顾客表格：
CustomerID (int)
CustomerName (varchar)

订单表格：
OrderID (int)
CustomerID (int)
OrderDate (date)
TotalAmount (float)

答案：

SELECT c.CustomerName, SUM(o.TotalAmount) AS Sales
FROM Customer c
INNER JOIN Order o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
ORDER BY Sales DESC;

2. 给定以下表格结构，请编写 SQL 查询语句，按照总分从高到低排名，显示每个学生的姓名和总分。

学生表格：
StudentID (int)
StudentName (varchar)

成绩表格：
ScoreID (int)
StudentID (int)
Subject (varchar)
Score (int)

答案：

SELECT s.StudentName, SUM(sc.Score) AS TotalScore
FROM Student s
INNER JOIN Score sc ON s.StudentID = sc.StudentID
GROUP BY s.StudentName
ORDER BY TotalScore DESC;

3. 给定以下表格结构，请编写 SQL 查询语句，按照工资从高到低排名，显示每个员工的姓名和工资。

员工表格：
EmployeeID (int)
EmployeeName (varchar)
DepartmentID (int)
Salary (float)

部门表格：
DepartmentID (int)
DepartmentName (varchar)

答案：

SELECT e.EmployeeName, e.Salary
FROM Employee e
INNER JOIN Department d ON e.DepartmentID = d.DepartmentID
ORDER BY e.Salary DESC;

4. 给定以下表格结构，请编写 SQL 查询语句，按照销售额从高到低排名，显示每个销售代表的姓名和销售额。

销售代表表格：
SalesRepID (int)
SalesRepName (varchar)

订单表格：
OrderID (int)
SalesRepID (int)
OrderDate (date)
TotalAmount (float)

答案：

SELECT s.SalesRepName, SUM(o.TotalAmount) AS Sales
FROM SalesRep s
INNER JOIN Order o ON s.SalesRepID = o.SalesRepID
GROUP BY s.SalesRepName
ORDER BY Sales DESC;

5. 给定以下表格结构，请编写 SQL 查询语句，按照订阅量从高到低排名，显示每个频道的名称和订阅量。

频道表格：
ChannelID (int)
ChannelName (varchar)

订阅表格：
SubscriptionID (int)
ChannelID (int)
SubscriberID (int)
SubscriptionDate (date)

答案：

SELECT c.ChannelName, COUNT(s.SubscriptionID) AS Subscriptions
FROM Channel c
INNER JOIN Subscription s ON c.ChannelID = s.ChannelID
GROUP BY c.ChannelName
ORDER BY Subscriptions DESC;