2025年中职软件工程（数据库删除操作）试题及答案.doc

2025年中职软件工程（数据库删除操作）试题及答案

（考试时间：90分钟 满分100分）
班级______ 姓名______

第I卷（选择题 共40分）
答题要求：本大题共20小题，每小题2分，共40分。在每小题列出的四个选项中，只有一项符合题目要求，请将其选出。

1. 在SQL中，删除表中所有记录的语句是（ ）
A. DELETE TABLE
B. DELETE FROM TABLE
C. DELETE FROM 表名
D. DELETE TABLE 表名
2. 要删除名为“students”表中年龄大于20岁的所有记录，正确的SQL语句是（ ）
A. DELETE FROM students WHERE age > 20
B. DELETE FROM students WHERE age >= 20
C. DELETE students WHERE age > 20
D. DELETE students WHERE age >= 20
3. 若要删除名为“orders”表中订单金额小于100的所有订单记录，SQL语句为（ ）
A. DELETE FROM orders WHERE amount < 100
B. DELETE FROM orders WHERE amount <= 100
C. DELETE orders WHERE amount < 100
D. DELETE orders WHERE amount <= 100
4. 在SQL Server中，删除视图“v_customers”的语句是（ ）
A. DELETE VIEW v_customers
B. DROP VIEW v_customers
C. DELETE FROM v_customers
D. DROP FROM v_customers
5. 要删除存储过程“proc_get_data”，使用的SQL语句是（ ）
A. DELETE PROC proc_get_data
B. DROP PROC proc_get_data
C. DELETE FROM proc_get_data
D. DROP FROM proc_get_data
6. 若要删除数据库中名为“testdb”的数据库，正确的语句是（ ）
A. DELETE DATABASE testdb
B. DROP DATABASE testdb
C. DELETE FROM testdb
D. DROP FROM testdb
7. 在MySQL中，删除名为“employees”表中部门编号为“D001”的所有员工记录，SQL语句是（ ）
A. DELETE FROM employees WHERE department_id = 'D001'
B. DELETE FROM employees WHERE department_id LIKE 'D001'
C. DELETE employees WHERE department_id = 'D001'
D. DELETE employees WHERE department_id LIKE 'D001'
8. 要删除名为“products”表中价格为NULL的所有产品记录，SQL语句为（ ）
A. DELETE FROM products WHERE price IS NULL
B. DELETE FROM products WHERE price = NULL
C. DELETE products WHERE price IS NULL
D. DELETE products WHERE price = NULL
9. 在SQL中，删除名为“students”表中姓“王”的所有学生记录，语句是（ ）
A. DELETE FROM students WHERE name LIKE '王%'
B. DELETE FROM students WHERE name = '王%'
C. DELETE students WHERE name LIKE '王%'
D. DELETE students WHERE name = '王%'
10. 若要删除名为“orders”表中订单日期在2025年1月1日之前的所有订单记录，SQL语句是（ ）
A. DELETE FROM orders WHERE order_date < '2025-01-01'
B. DELETE FROM orders WHERE order_date <= '2025-01-01'
C. DELETE orders WHERE order_date < '2025-01-01'
D. DELETE orders WHERE order_date <= '2025-01-01'
11. 在SQL Server中，删除索引“idx_customers_name”的语句是（ ）
A. DELETE INDEX idx_customers_name
B. DROP INDEX idx_customers_name
C. DELETE FROM idx_customers_name
D. DROP FROM idx_customers_name
12. 要删除名为“employees”表中入职时间最早的一条记录，SQL语句可以是（ ）
A. DELETE FROM employees WHERE hire_date = (SELECT MIN(hire_date) FROM employees)
B. DELETE FROM employees WHERE hire_date = MIN(hire_date)
C. DELETE employees WHERE hire_date = (SELECT MIN(hire_date) FROM employees)
D. DELETE employees WHERE hire_date = MIN(hire_date)
13. 若要删除名为“books”表中出版社为“清华大学出版社”且库存数量小于50的所有书籍记录，SQL语句是（ ）
A. DELETE FROM books WHERE publisher = '清华大学出版社' AND stock < '50'
B. DELETE FROM books WHERE publisher = '清华大学出版社' AND stock < 50
C. DELETE books WHERE publisher = '清华大学出版社' AND stock < '50'
D. DELETE books WHERE publisher = '清华大学出版社' AND stock < 50
14. 在MySQL中，删除名为“users”表中用户名包含“admin”的所有用户记录，SQL语句是（ ）
A. DELETE FROM users WHERE username LIKE '%admin%'
B. DELETE FROM users WHERE username = '%admin%'
C. DELETE users WHERE username LIKE '%admin%'
D. DELETE users WHERE username = '%admin%'
15. 要删除名为“tasks”表中任务状态为“已完成”的所有任务记录，SQL语句为（ ）
A. DELETE FROM tasks WHERE status = '已完成'
B. DELETE FROM tasks WHERE status LIKE '已完成'
C. DELETE tasks WHERE status = '已完成'
D. DELETE tasks WHERE status LIKE '已完成'
16. 若要删除名为“departments”表中部门人数最少的一个部门记录，SQL语句可以是（ ）
A. DELETE FROM departments WHERE department_id = (SELECT MIN(department_id) FROM departments GROUP BY department_id HAVING COUNT() = (SELECT MIN(count()) FROM departments GROUP BY department_id))
B. DELETE FROM departments WHERE department_id = MIN(department_id)
C. DELETE departments WHERE department_id = (SELECT MIN(department_id) FROM departments GROUP BY department_id HAVING COUNT() = (SELECT MIN(count()) FROM departments GROUP BY department_id))
D. DELETE departments WHERE department_id = MIN(department_id)
17. 在SQL Server中，删除名为“customers”表中城市为“北京”且消费金额小于1000的所有客户记录，语句是（ ）
A. DELETE FROM customers WHERE city = '北京' AND consumption < 1000
B. DELETE FROM customers WHERE city = '北京' AND consumption <= 1000
C. DELETE customers WHERE city = '北京' AND consumption < 1000
D. DELETE customers WHERE city = '北京' AND consumption <= 1000
18. 要删除名为“products”表中产品名称长度超过20个字符的所有产品记录，SQL语句为（ ）
A. DELETE FROM products WHERE LENGTH(product_name) > 20
B. DELETE FROM products WHERE CHAR_LENGTH(product_name) > 20
C. DELETE products WHERE LENGTH(product_name) > 20
D. DELETE products WHERE CHAR_LENGTH(product_name) > 20
19. 若要删除名为“students”表中数学成绩小于60分且语文成绩小于60分的所有学生记录，SQL语句是（ ）
A. DELETE FROM students WHERE math_score < 60 AND chinese_score < 60
B. DELETE FROM students WHERE math_score <= 60 AND chinese_score <= 60
C. DELETE students WHERE math_score < 60 AND chinese_score < 60
D. DELETE students WHERE math_score <= 60 AND chinese_score <= 60
20. 在MySQL中，删除名为“orders”表中订单数量大于10且订单日期在2025年3月1日之后的所有订单记录，SQL语句是（ ）
A. DELETE FROM orders WHERE quantity > 10 AND order_date > '2025-03-01'
B. DELETE FROM orders WHERE quantity >= 10 AND order_date > '2025-03-01'
C. DELETE orders WHERE quantity > 10 AND order_date > '2025-03-01'
D. DELETE orders WHERE quantity >= 10 AND order_date > '2025-03-01'

第II卷（非选择题 共60分）
（一）简答题（共20分）
答题要求：本大题共4小题，每小题5分，共20分。请简要回答问题。

1. 简述DELETE语句和DROP语句在删除对象时的区别。
2. 如何删除表中满足特定条件的部分记录？请举例说明。
3.. 在删除数据库表中的记录时，怎样确保数据的完整性和一致性？
4. 当要删除一个表时，应该注意什么？

（二）程序填空题（共10分）
答题要求：本大题共2小题，每小题5分，共10分。请根据题目要求，在给定的程序代码中填写合适的内容。

1. 以下是一段SQL代码，用于删除“employees”表中入职时间早于2020年1月1日的员工记录，请补充缺失的部分。
```sql
DELETE FROM employees
WHERE hire_date < '2020-01-01';
```
2. 若要删除名为“products”表中价格大于100且库存数量小于20的产品记录，请补充完整以下SQL代码。
```sql
DELETE FROM products
WHERE price > 100 AND stock < 20;
```

（三）程序改错题（共10分）
答题要求：本大题共2小题，每小题5分，共10分。以下程序代码存在错误，请指出错误并改正。

1. 以下SQL代码试图删除名为“students”表中姓“李”的学生记录，但存在错误。
```sql
DELETE students
WHERE name = '李%';
```
2. 这段SQL代码用于删除名为“orders”表中订单金额大于500的订单记录，有错误，请修正。
```sql
DELETE FROM orders
WHERE amount => 500;
```

（四）综合应用题（共15分）
答题要求：阅读以下材料，回答问题。
材料：某电商公司有一个名为“products”的数据库表，用于存储商品信息，表结构如下：
| 字段名 | 类型 | 描述 |
| ---- | ---- | ---- |
| product_id | int | 商品ID |
| product_name | varchar(50) | 商品名称 |
| price | decimal(10, 2) | 商品价格 |
| stock | int | 库存数量 |
| category_id | int | 商品类别ID |

1. 请编写SQL语句，删除“products”表中价格小于50且库存数量小于10的所有商品记录。（5分）
2. 假设该电商公司要淘汰所有类别ID为3的商品，编写SQL语句实现删除操作。（5分）
3. 若要删除“products”表中商品名称包含“过时”字样的所有商品记录，写出对应的SQL语句。（5分）

（五）案例分析题（共5分）
答题要求：阅读以下案例，回答问题。
案例：某公司数据库中有一个名为“employees”的表，用于存储员工信息。由于公司业务调整，需要删除一些不再符合要求的员工记录。已知要删除的员工记录满足以下条件：入职时间早于2018年1月1日且部门编号为2。

请写出删除这些员工记录的SQL语句。（5分）

答案：
1. C
2. A
3. A
4. B
5. B
6. B
7. A
8. A
9. A
10. A
11. B
12. A
13. B
14. A
15. A
16. A
17. A
18. B
19. A
20. A

（一）简答题
1. DELETE语句用于删除表中的记录，不会删除表结构；DROP语句用于删除表、视图、索引等对象，会删除整个对象结构。
2. 例如DELETE FROM students WHERE age > 20; 即可删除“students”表中年龄大于20岁的记录。
3. 可以通过事务来确保数据的完整性和一致性，在删除操作前进行必要的检查和验证，确保数据符合业务规则。
4. 要注意备份重要数据，确认删除操作的影响范围，确保没有其他依赖该表数据的程序或业务逻辑受到影响。

（二）程序填空题
1. 原代码无误。
2.原代码无误。

（三）程序改错题
1. 错误在于匹配姓“李”的方式错误，应改为DELETE FROM students WHERE name LIKE '李%';
2. 错误在于比较运算符错误，应改为DELETE FROM orders WHERE amount > 500;

（四）综合应用题
```sql
1. DELETE FROM products WHERE price < 50 AND stock < 10;
2. DELETE FROM products WHERE category_id = 3;
3. DELETE FROM products WHERE product_name LIKE '%过时%';
```

（五）案例分析题
DELETE FROM employees WHERE hire_date < '2018-01-01' AND department_id = 2;