2025年SQL語(yǔ)句模擬沖刺卷考試時(shí)間：______分鐘總分：______分姓名：______一、表`Employees`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||EmployeeID|INT|PRIMARYKEY||FirstName|VARCHAR|NOTNULL||LastName|VARCHAR|NOTNULL||Department|VARCHAR|||HireDate|DATE|NOTNULL||Salary|DECIMAL(10,2)|NOTNULL|表`Departments`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||DepartmentID|INT|PRIMARYKEY||DepartmentName|VARCHAR|NOTNULL||ManagerID|INT||`EmployeeID`在`Employees`表中是主鍵。`DepartmentID`在`Employees`表中是外鍵，關(guān)聯(lián)到`Departments`表的`DepartmentID`。`ManagerID`在`Departments`表中是外鍵，關(guān)聯(lián)到`Employees`表的`EmployeeID`。查詢`Employees`表中所有員工的`FirstName`,`LastName`,`DepartmentName`和`Salary`。如果員工沒(méi)有部門，則`DepartmentName`應(yīng)顯示為'None'。二、表`Sales`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||SaleID|INT|PRIMARYKEY||ProductID|INT|NOTNULL||SaleDate|DATE|NOTNULL||Quantity|INT|NOTNULL||Amount|DECIMAL(10,2)|NOTNULL|表`Products`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||ProductID|INT|PRIMARYKEY||ProductName|VARCHAR|NOTNULL||Category|VARCHAR|NOTNULL|查詢2023年每個(gè)產(chǎn)品類別（`Category`）的總銷售額（`Amount`）。結(jié)果按總銷售額降序排列。三、表`Orders`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||OrderID|INT|PRIMARYKEY||CustomerID|INT|NOTNULL||OrderDate|DATE|NOTNULL||TotalAmount|DECIMAL(10,2)|NOTNULL|表`Customers`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||CustomerID|INT|PRIMARYKEY||CustomerName|VARCHAR|NOTNULL||City|VARCHAR|NOTNULL|查詢每個(gè)城市的客戶數(shù)量以及這些城市客戶的總訂單金額。只顯示客戶數(shù)量大于5的城市。結(jié)果按客戶數(shù)量降序排列。四、表`Employees`的結(jié)構(gòu)同第一題。查詢每個(gè)部門的平均工資（`Salary`）。如果某個(gè)部門沒(méi)有員工，則顯示該部門的平均工資為0。五、表`Employees`的結(jié)構(gòu)同第一題。表`EmployeeSkills`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||EmployeeID|INT|PRIMARYKEY||Skill|VARCHAR|NOTNULL|查詢所有擁有'SQL'技能的員工的`FirstName`,`LastName`和`Skill`。六、表`Products`的結(jié)構(gòu)同第二題。查詢每個(gè)產(chǎn)品（`ProductName`）的庫(kù)存量。庫(kù)存量等于該產(chǎn)品的`Quantity`之和，其中`SaleDate`在2023年1月1日之前的記錄表示入庫(kù)，`SaleDate`在2023年1月1日及之后的記錄表示出庫(kù)。假設(shè)2023年1月1日之前所有記錄都是入庫(kù)。七、表`Orders`的結(jié)構(gòu)同第三題。表`OrderDetails`的結(jié)構(gòu)如下：|ColumnName|DataType|Constraints||:----------|:--------|:----------------||OrderDetailID|INT|PRIMARYKEY||OrderID|INT|NOTNULL||ProductID|INT|NOTNULL||Quantity|INT|NOTNULL|查詢2023年每個(gè)產(chǎn)品的總銷售數(shù)量。結(jié)果只包含銷售數(shù)量大于100的產(chǎn)品。按銷售數(shù)量降序排列。八、表`Departments`的結(jié)構(gòu)同第一題。表`Employees`的結(jié)構(gòu)同第一題。查詢每個(gè)部門的管理者的工資（`Salary`）。如果某個(gè)部門沒(méi)有管理者，則顯示工資為0。九、表`Employees`的結(jié)構(gòu)同第一題。更新`Employees`表，將所有在2020年1月1日之前入職（`HireDate`）的員工的`Salary`提高10%。如果員工的`Salary`提高后超過(guò)100000，則將其設(shè)置為100000。十、表`Orders`的結(jié)構(gòu)同第三題。刪除`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`函數(shù)處理`LEFTJOIN`可能產(chǎn)生的空值。如果`DepartmentName`為NULL（即員工沒(méi)有部門），則顯示'None'。3.使用`FROMEmployeese`指定主表為`Employees`并使用別名`e`。4.使用`LEFTJOINDepartmentsdONe.Department=d.DepartmentID`將`Employees`表與`Departments`表進(jìn)行左連接。使用左連接是因?yàn)橐笏袉T工信息都要顯示，即使他們沒(méi)有部門（此時(shí)連接條件`e.Department=d.DepartmentID`不會(huì)滿足，員工信息仍會(huì)出現(xiàn)在結(jié)果中，但`DepartmentName`會(huì)是NULL）。5.`Department`字段名需要根據(jù)實(shí)際表結(jié)構(gòu)替換。二、```sqlSELECTp.Category,SUM(s.Amount)ASTotalAmountFROMSalessJOINProductspONs.ProductID=p.ProductIDWHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'GROUPBYp.CategoryORDERBYTotalAmountDESC;```解析思路:1.使用`SELECT`子句指定輸出列：產(chǎn)品類別`p.Category`和計(jì)算出的總銷售額`SUM(s.Amount)`，并給總銷售額起別名`TotalAmount`。2.使用`FROMSaless`指定主表為`Sales`并使用別名`s`。3.使用`JOINProductspONs.ProductID=p.ProductID`將`Sales`表與`Products`表進(jìn)行內(nèi)連接。內(nèi)連接是因?yàn)橹魂P(guān)心有銷售記錄且對(duì)應(yīng)產(chǎn)品的記錄。4.使用`WHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'`篩選出2023年的銷售記錄。5.使用`GROUPBYp.Category`按產(chǎn)品類別進(jìn)行分組，以便計(jì)算每個(gè)類別的總銷售額。6.使用`ORDERBYTotalAmountDESC`按總銷售額降序排列結(jié)果。三、```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`，客戶數(shù)量`COUNT(DISTINCTo.CustomerID)`，總訂單金額`SUM(o.TotalAmount)`。2.使用`FROMOrderso`指定主表為`Orders`并使用別名`o`。3.使用`JOINCustomerscONo.CustomerID=c.CustomerID`將`Orders`表與`Customers`表進(jìn)行內(nèi)連接。內(nèi)連接是因?yàn)橹魂P(guān)心有訂單記錄的客戶。4.使用`WHEREo.OrderDate>='2023-01-01'`篩選出2023年及之后的訂單記錄。5.使用`GROUPBYc.City`按城市進(jìn)行分組，以便計(jì)算每個(gè)城市的客戶數(shù)量和總訂單金額。6.使用`HAVINGCOUNT(DISTINCTo.CustomerID)>5`過(guò)濾分組結(jié)果，只保留客戶數(shù)量大于5的城市。使用`DISTINCT`確保計(jì)數(shù)時(shí)每個(gè)客戶只被計(jì)算一次。7.使用`ORDERBYCustomerCountDESC`按客戶數(shù)量降序排列結(jié)果。四、```sqlSELECTd.DepartmentName,COALESCE(AVG(e.Salary),0)ASAverageSalaryFROMEmployeeseLEFTJOINDepartmentsdONe.Department=d.DepartmentIDGROUPBYd.DepartmentName;```解析思路:1.使用`SELECT`子句指定輸出列：部門名稱`d.DepartmentName`和計(jì)算出的平均工資`AVG(e.Salary)`，并給平均工資起別名`AverageSalary`。2.使用`FROMEmployeese`指定主表為`Employees`并使用別名`e`。3.使用`LEFTJOINDepartmentsdONe.Department=d.DepartmentID`將`Employees`表與`Departments`表進(jìn)行左連接。這是為了確保即使部門沒(méi)有員工，也能在結(jié)果中顯示該部門（平均工資為0）。4.使用`GROUPBYd.DepartmentName`按部門名稱進(jìn)行分組，以便計(jì)算每個(gè)部門的平均工資。5.使用`COALESCE(AVG(e.Salary),0)`處理空值。如果某個(gè)部門沒(méi)有員工，則`AVG(e.Salary)`的結(jié)果為NULL，此時(shí)將其轉(zhuǎn)換為0。五、```sqlSELECTe.FirstName,e.LastName,es.SkillFROMEmployeeseJOINEmployeeSkillsesONe.EmployeeID=es.EmployeeIDWHEREes.Skill='SQL';```解析思路:1.使用`SELECT`子句指定輸出列：?jiǎn)T工的`FirstName`,`LastName`和技能`es.Skill`。2.使用`JOIN`語(yǔ)句將`Employees`表和`EmployeeSkills`表連接起來(lái)。連接條件是`e.EmployeeID=es.EmployeeID`，這會(huì)將擁有技能的員工與其技能關(guān)聯(lián)起來(lái)。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`子句指定輸出列：產(chǎn)品名稱`p.ProductName`和計(jì)算出的庫(kù)存量`StockQuantity`。2.使用`SUM()`函數(shù)計(jì)算庫(kù)存量。庫(kù)存量是入庫(kù)量減去出庫(kù)量。3.`SUM()`函數(shù)內(nèi)部使用`CASEWHEN`語(yǔ)句區(qū)分入庫(kù)和出庫(kù)：*`WHENs.SaleDate<'2023-01-01'THENs.Quantity`:如果銷售日期在2023年1月1日之前，表示入庫(kù)，庫(kù)存量加`Quantity`。*`ELSE-s.QuantityEND`:如果銷售日期在2023年1月1日及之后，表示出庫(kù)，庫(kù)存量減`Quantity`。4.使用`FROMProductsp`指定主表為`Products`并使用別名`p`。5.使用`JOINSalessONp.ProductID=s.ProductID`將`Products`表與`Sales`表進(jìn)行內(nèi)連接。內(nèi)連接是基于產(chǎn)品ID，意味著只計(jì)算有銷售記錄的產(chǎn)品庫(kù)存。6.使用`WHEREp.ProductIDIN(...)`篩選出在2023年1月1日之前有入庫(kù)記錄的產(chǎn)品。子查詢`SELECTProductIDFROMSalesWHERESaleDate<'2023-01-01'`找出所有有過(guò)入庫(kù)（假設(shè)）的產(chǎn)品ID。7.使用`GROUPBYp.ProductName`按產(chǎn)品名稱分組，因?yàn)榭赡芤粋€(gè)產(chǎn)品有多次入庫(kù)或出庫(kù)記錄。七、```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`子句指定輸出列：產(chǎn)品名稱`p.ProductName`和計(jì)算出的總銷售數(shù)量`SUM(od.Quantity)`，并給總銷售數(shù)量起別名`TotalQuantity`。2.使用`FROMSaless`指定主表為`Sales`并使用別名`s`。3.首先使用`JOINProductspONs.ProductID=p.ProductID`將`Sales`表與`Products`表連接，獲取產(chǎn)品信息。4.然后使用`JOINOrderDetailsodONs.SaleID=od.OrderID`將結(jié)果與`OrderDetails`表連接。`Sales`表的`SaleID`是`OrderDetails`表的`OrderID`的外鍵（根據(jù)表結(jié)構(gòu)推斷）。5.使用`WHEREs.SaleDateBETWEEN'2023-01-01'AND'2023-12-31'`篩選出2023年的銷售記錄。6.使用`GROUPBYp.ProductName`按產(chǎn)品名稱分組，以便計(jì)算每個(gè)產(chǎn)品的總銷售數(shù)量。7.使用`HAVINGSUM(od.Quantity)>100`過(guò)濾分組結(jié)果，只保留銷售數(shù)量大于100的產(chǎn)品。8.使用`ORDERBYTotalQuantityDESC`按銷售數(shù)量降序排列結(jié)果。八、```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`連接到一個(gè)派生的`Employees`表，這個(gè)派生表只包含`EmployeeID`和`Salary`兩列（通過(guò)`SELECTEmployeeID,SalaryFROMEmployees`)。使用別名`e`。4.連接條件是`d.ManagerID=e.EmployeeID`，這會(huì)將部門與其管理者（如果存在）關(guān)聯(lián)起來(lái)。5.使用`GROUPBYd.DepartmentName`按部門名稱分組。雖然這里只有一個(gè)`e.Salary`列，但分組是必要的，因?yàn)閌LEFTJOIN`可能產(chǎn)生包含NULL`ManagerID`的部門行，這些行也會(huì)被分組到`d.DepartmentName`。6.使用`COALESCE(e.Salary,0)`處理空值。如果某個(gè)部門沒(méi)有管理者（`d.ManagerID`是NULL，導(dǎo)致`e.EmployeeID`是NULL，`e.Salary`也會(huì)是NULL），則顯示工資為0。九、```sqlUPDATEEmployeesSETSalary=CASEWHENSalary*1.10>100000THEN100000ELSESalary*1.10ENDWHEREHireDate<='2020-01-01';```解析