2025年SQL语句模拟冲刺卷

2025年SQL语句模拟冲刺卷考试时间：______分钟总分：______分姓名：______一、表`Employees`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||EmployeeID|INT|PRIMARYKEY||FirstName|VARCHAR|NOTNULL||LastName|VARCHAR|NOTNULL||Department|VARCHAR|||HireDate|DATE|NOTNULL||Salary|DECIMAL(10,2)|NOTNULL|表`Departments`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||DepartmentID|INT|PRIMARYKEY||DepartmentName|VARCHAR|NOTNULL||ManagerID|INT||`EmployeeID`在`Employees`表中是主键。`DepartmentID`在`Employees`表中是外键，关联到`Departments`表的`DepartmentID`。`ManagerID`在`Departments`表中是外键，关联到`Employees`表的`EmployeeID`。查询`Employees`表中所有员工的`FirstName`,`LastName`,`DepartmentName`和`Salary`。如果员工没有部门，则`DepartmentName`应显示为'None'。二、表`Sales`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||SaleID|INT|PRIMARYKEY||ProductID|INT|NOTNULL||SaleDate|DATE|NOTNULL||Quantity|INT|NOTNULL||Amount|DECIMAL(10,2)|NOTNULL|表`Products`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||ProductID|INT|PRIMARYKEY||ProductName|VARCHAR|NOTNULL||Category|VARCHAR|NOTNULL|查询2023年每个产品类别（`Category`）的总销售额（`Amount`）。结果按总销售额降序排列。三、表`Orders`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||OrderID|INT|PRIMARYKEY||CustomerID|INT|NOTNULL||OrderDate|DATE|NOTNULL||TotalAmount|DECIMAL(10,2)|NOTNULL|表`Customers`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||CustomerID|INT|PRIMARYKEY||CustomerName|VARCHAR|NOTNULL||City|VARCHAR|NOTNULL|查询每个城市的客户数量以及这些城市客户的总订单金额。只显示客户数量大于5的城市。结果按客户数量降序排列。四、表`Employees`的结构同第一题。查询每个部门的平均工资（`Salary`）。如果某个部门没有员工，则显示该部门的平均工资为0。五、表`Employees`的结构同第一题。表`EmployeeSkills`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||EmployeeID|INT|PRIMARYKEY||Skill|VARCHAR|NOTNULL|查询所有拥有'SQL'技能的员工的`FirstName`,`LastName`和`Skill`。六、表`Products`的结构同第二题。查询每个产品（`ProductName`）的库存量。库存量等于该产品的`Quantity`之和，其中`SaleDate`在2023年1月1日之前的记录表示入库，`SaleDate`在2023年1月1日及之后的记录表示出库。假设2023年1月1日之前所有记录都是入库。七、表`Orders`的结构同第三题。表`OrderDetails`的结构如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||OrderDetailID|INT|PRIMARYKEY||OrderID|INT|NOTNULL||ProductID|INT|NOTNULL||Quantity|INT|NOTNULL|查询2023年每个产品的总销售数量。结果只包含销售数量大于100的产品。按销售数量降序排列。八、表`Departments`的结构同第一题。表`Employees`的结构同第一题。查询每个部门的管理者的工资（`Salary`）。如果某个部门没有管理者，则显示工资为0。九、表`Employees`的结构同第一题。更新`Employees`表，将所有在2020年1月1日之前入职（`HireDate`）的员工的`Salary`提高10%。如果员工的`Salary`提高后超过100000，则将其设置为100000。十、表`Orders`的结构同第三题。删除`Orders`表中所有在2022年12月31日之前下单（`OrderDate`）且`TotalAmount`小于50的订单。试卷答案一、```sqlSELECTe.FirstName,e.LastName,COALESCE(d.DepartmentName,'None')ASDepartmentName,e.SalaryFROMEmployeeseLEFTJOINDepartmentsdONe.Department=d.DepartmentID;```解析思路:1.使用`SELECT`子句指定需要输出的列：`FirstName`,`LastName`,`Salary`。2.使用`COALESCE`函数处理`LEFTJOIN`可能产生的空值。如果`DepartmentName`为NULL（即员工没有部门），则显示'None'。3.使用`FROMEmployeese`指定主表为`Employees`并使用别名`e`。4.使用`LEFTJOINDepartmentsdONe.Department=d.DepartmentID`将`Employees`表与`Departments`表进行左连接。使用左连接是因为要求所有员工信息都要显示，即使他们没有部门（此时连接条件`e.Department=d.DepartmentID`不会满足，员工信息仍会出现在结果中，但`DepartmentName`会是NULL）。5.`Department`字段名需要根据实际表结构替换。二、```sqlSELECTp.Category,SUM(s.Amount)ASTotalAmountFROMSalessJOINProductspONs.ProductID=p.ProductIDWHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'GROUPBYp.CategoryORDERBYTotalAmountDESC;```解析思路:1.使用`SELECT`子句指定输出列：产品类别`p.Category`和计算出的总销售额`SUM(s.Amount)`，并给总销售额起别名`TotalAmount`。2.使用`FROMSaless`指定主表为`Sales`并使用别名`s`。3.使用`JOINProductspONs.ProductID=p.ProductID`将`Sales`表与`Products`表进行内连接。内连接是因为只关心有销售记录且对应产品的记录。4.使用`WHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'`筛选出2023年的销售记录。5.使用`GROUPBYp.Category`按产品类别进行分组，以便计算每个类别的总销售额。6.使用`ORDERBYTotalAmountDESC`按总销售额降序排列结果。三、```sqlSELECTc.City,COUNT(DISTINCTo.CustomerID)ASCustomerCount,SUM(o.TotalAmount)ASTotalOrderAmountFROMOrdersoJOINCustomerscONo.CustomerID=c.CustomerIDWHEREo.OrderDate>='2023-01-01'GROUPBYc.CityHAVINGCOUNT(DISTINCTo.CustomerID)>5ORDERBYCustomerCountDESC;```解析思路:1.使用`SELECT`子句指定输出列：城市`c.City`，客户数量`COUNT(DISTINCTo.CustomerID)`，总订单金额`SUM(o.TotalAmount)`。2.使用`FROMOrderso`指定主表为`Orders`并使用别名`o`。3.使用`JOINCustomerscONo.CustomerID=c.CustomerID`将`Orders`表与`Customers`表进行内连接。内连接是因为只关心有订单记录的客户。4.使用`WHEREo.OrderDate>='2023-01-01'`筛选出2023年及之后的订单记录。5.使用`GROUPBYc.City`按城市进行分组，以便计算每个城市的客户数量和总订单金额。6.使用`HAVINGCOUNT(DISTINCTo.CustomerID)>5`过滤分组结果，只保留客户数量大于5的城市。使用`DISTINCT`确保计数时每个客户只被计算一次。7.使用`ORDERBYCustomerCountDESC`按客户数量降序排列结果。四、```sqlSELECTd.DepartmentName,COALESCE(AVG(e.Salary),0)ASAverageSalaryFROMEmployeeseLEFTJOINDepartmentsdONe.Department=d.DepartmentIDGROUPBYd.DepartmentName;```解析思路:1.使用`SELECT`子句指定输出列：部门名称`d.DepartmentName`和计算出的平均工资`AVG(e.Salary)`，并给平均工资起别名`AverageSalary`。2.使用`FROMEmployeese`指定主表为`Employees`并使用别名`e`。3.使用`LEFTJOINDepartmentsdONe.Department=d.DepartmentID`将`Employees`表与`Departments`表进行左连接。这是为了确保即使部门没有员工，也能在结果中显示该部门（平均工资为0）。4.使用`GROUPBYd.DepartmentName`按部门名称进行分组，以便计算每个部门的平均工资。5.使用`COALESCE(AVG(e.Salary),0)`处理空值。如果某个部门没有员工，则`AVG(e.Salary)`的结果为NULL，此时将其转换为0。五、```sqlSELECTe.FirstName,e.LastName,es.SkillFROMEmployeeseJOINEmployeeSkillsesONe.EmployeeID=es.EmployeeIDWHEREes.Skill='SQL';```解析思路:1.使用`SELECT`子句指定输出列：员工的`FirstName`,`LastName`和技能`es.Skill`。2.使用`JOIN`语句将`Employees`表和`EmployeeSkills`表连接起来。连接条件是`e.EmployeeID=es.EmployeeID`，这会将拥有技能的员工与其技能关联起来。3.使用`WHEREes.Skill='SQL'`筛选出拥有'SQL'技能的记录。六、```sqlSELECTp.ProductName,SUM(CASEWHENs.SaleDate<'2023-01-01'THENs.QuantityELSE-s.QuantityEND)ASStockQuantityFROMProductspJOINSalessONp.ProductID=s.ProductIDWHEREp.ProductIDIN(SELECTProductIDFROMSalesWHERESaleDate<'2023-01-01')GROUPBYp.ProductName;```解析思路:1.使用`SELECT`子句指定输出列：产品名称`p.ProductName`和计算出的库存量`StockQuantity`。2.使用`SUM()`函数计算库存量。库存量是入库量减去出库量。3.`SUM()`函数内部使用`CASEWHEN`语句区分入库和出库：*`WHENs.SaleDate<'2023-01-01'THENs.Quantity`:如果销售日期在2023年1月1日之前，表示入库，库存量加`Quantity`。*`ELSE-s.QuantityEND`:如果销售日期在2023年1月1日及之后，表示出库，库存量减`Quantity`。4.使用`FROMProductsp`指定主表为`Products`并使用别名`p`。5.使用`JOINSalessONp.ProductID=s.ProductID`将`Products`表与`Sales`表进行内连接。内连接是基于产品ID，意味着只计算有销售记录的产品库存。6.使用`WHEREp.ProductIDIN(...)`筛选出在2023年1月1日之前有入库记录的产品。子查询`SELECTProductIDFROMSalesWHERESaleDate<'2023-01-01'`找出所有有过入库（假设）的产品ID。7.使用`GROUPBYp.ProductName`按产品名称分组，因为可能一个产品有多次入库或出库记录。七、```sqlSELECTp.ProductName,SUM(od.Quantity)ASTotalQuantityFROMSalessJOINProductspONs.ProductID=p.ProductIDJOINOrderDetailsodONs.SaleID=od.OrderIDWHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'GROUPBYp.ProductNameHAVINGSUM(od.Quantity)>100ORDERBYTotalQuantityDESC;```解析思路:1.使用`SELECT`子句指定输出列：产品名称`p.ProductName`和计算出的总销售数量`SUM(od.Quantity)`，并给总销售数量起别名`TotalQuantity`。2.使用`FROMSaless`指定主表为`Sales`并使用别名`s`。3.首先使用`JOINProductspONs.ProductID=p.ProductID`将`Sales`表与`Products`表连接，获取产品信息。4.然后使用`JOINOrderDetailsodONs.SaleID=od.OrderID`将结果与`OrderDetails`表连接。`Sales`表的`SaleID`是`OrderDetails`表的`OrderID`的外键（根据表结构推断）。5.使用`WHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'`筛选出2023年的销售记录。6.使用`GROUPBYp.ProductName`按产品名称分组，以便计算每个产品的总销售数量。7.使用`HAVINGSUM(od.Quantity)>100`过滤分组结果，只保留销售数量大于100的产品。8.使用`ORDERBYTotalQuantityDESC`按销售数量降序排列结果。八、```sqlSELECTd.DepartmentName,COALESCE(e.Salary,0)ASManagerSalaryFROMDepartmentsdLEFTJOIN(SELECTEmployeeID,SalaryFROMEmployees)ASeONd.ManagerID=e.EmployeeIDGROUPBYd.DepartmentName;```解析思路:1.使用`SELECT`子句指定输出列：部门名称`d.DepartmentName`和管理者的工资`e.Salary`（或0），并给管理者工资起别名`ManagerSalary`。2.使用`FROMDepartmentsd`指定主表为`Departments`并使用别名`d`。3.使用`LEFTJOIN`连接到一个派生的`Employees`表，这个派生表只包含`EmployeeID`和`Salary`两列（通过`SELECTEmployeeID,SalaryFROMEmployees`)。使用别名`e`。4.连接条件是`d.ManagerID=e.EmployeeID`，这会将部门与其管理者（如果存在）关联起来。5.使用`GROUPBYd.DepartmentName`按部门名称分组。虽然这里只有一个`e.Salary`列，但分组是必要的，因为`LEFTJOIN`可能产生包含NULL`ManagerID`的部门行，这些行也会被分组到`d.DepartmentName`。6.使用`COALESCE(e.Salary,0)`处理空值。如果某个部门没有管理者（`d.ManagerID`是NULL，导致`e.EmployeeID`是NULL，`e.Salary`也会是NULL），则显示工资为0。九、```sqlUPDATEEmployeesSETSalary=CASEWHENSalary*1.10>100000THEN100000ELSESalary*1.10ENDWHEREHireDate<='2020-01-01';```解析