MySQL问题（三）

目录

简答题（共14题，100分）

1. (简答题, 30分) 创建如下五个表格，要求将表中的外码，主码都进行相对应的设置：

2. (简答题, 5分) 查询订单表order和顾客表customer中，订单总金额大于1000的顾客姓名和订单号。

3. (简答题, 5分) 查询学生表student和课程表course中，选择了课程的学生姓名和课程名称。

4. (简答题, 5分) 查询部门表dept和员工表emp中，每个部门的员工数量。（提示，可能这个部门没有员工呢，left join)

5. (简答题, 5分) 查询订单表order和顾客表customer中，未下订单的顾客姓名。(可能顾客没有订单呢？）

6. (简答题, 10分) 查询订单表order和顾客表customer中，订单总金额最大的顾客姓名和订单号。

7. (简答题, 5分) 查询员工表emp和部门表dept中，员工薪资比所在部门平均薪资高的员工姓名和部门名称。

8. (简答题, 5分) 查询学生表student和课程表course中，每个学生的选修课程数。

9. (简答题, 5分) 查询雇员表employee和工作表job中，不同工作的员工数。

10. (简答题, 5分) 查询雇员表employee和经理表manager中，员工的直接上级姓名。

11. (简答题, 5分) 查询每个客户的订单数量（customer表和order表）

12. (简答题, 5分) 查询购买某个产品的所有客户的姓名和联系方式（customer表和order表）：

13. (简答题, 5分) 查询没有购买过任何产品的客户（customer表和order表）：

14. (简答题, 5分) 查询薪资高于平均薪资的员工（employee表）

---

简答题（共14题，100分）

1. (简答题, 30分) 创建如下五个表格，要求将表中的外码，主码都进行相对应的设置：

customer表：customer_id (int), customer_name (varchar), customer_email (varchar)

order表：order_id (int), customer_id (int), product_id (int), quantity (int), order_date (date)

employee表：employee_id (int), employee_name (varchar), department_id (int), salary (int), hire_date (date),

product表：product_id (int), product_name (varchar), price (int)

student表：student_id (int), student_name (varchar), class_id (int), score (int)

-- customer表
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    customer_email VARCHAR(50)
);

-- order表
CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- employee表
CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    salary INT,
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- product表
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price INT
);

-- student表
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    class_id INT,
    score INT,
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

在以上表格中，customer表的customer_id为主键，并且order表的customer_id为其外键；employee表的employee_id为主键，并且employee表的department_id为其外键，指向department表中的department_id；product表的product_id为主键，并且order表的product_id为其外键；student表的student_id为主键，并且student表的class_id为其外键，指向class表中的class_id。这些约束能够帮助我们保持表格之间的一致性和完整性，避免数据的冗余和不一致。

-- 初始化数据

-- customer表
INSERT INTO customer (customer_id, customer_name, customer_email)
VALUES (1, '张三', 'zhangsan@example.com'),
       (2, '李四', 'lisi@example.com'),
       (3, '王五', 'wangwu@example.com');

-- order表
INSERT INTO order (order_id, customer_id, product_id, quantity, order_date)
VALUES (1, 1, 1, 2, '2022-01-01'),
       (2, 1, 3, 1, '2022-01-05'),
       (3, 2, 2, 3, '2022-01-10'),
       (4, 3, 1, 1, '2022-01-15'),
       (5, 3, 2, 2, '2022-01-20');

-- employee表
INSERT INTO employee (employee_id, employee_name, department_id, salary, hire_date)
VALUES (1, '张三', 1, 5000, '2020-01-01'),
       (2, '李四', 2, 6000, '2020-01-02'),
       (3, '王五', 2, 5500, '2020-01-03');

-- product表
INSERT INTO product (product_id, product_name, price)
VALUES (1, '苹果', 5),
       (2, '橙子', 3),
       (3, '香蕉', 4);

-- student表
INSERT INTO student (student_id, student_name, class_id, score)
VALUES (1, '张三', 1, 80),
       (2, '李四', 1, 85),
       (3, '王五', 2, 75),
       (4, '赵六', 2, 90);

2. (简答题, 5分) 查询订单表order和顾客表customer中，订单总金额大于1000的顾客姓名和订单号。

表：order

字段：order.order_id, order.order_total, order.customer_id

表：customer

字段：customer.customer_id, customer.customer_name

SELECT customer.customer_name, order.order_id
FROM customer
JOIN order
ON customer.customer_id = order.customer_id
WHERE order.order_total > 1000;

这个SQL查询语句使用了JOIN操作来合并两个表格，并且用ON子句指定了顾客表和订单表之间的连接条件。同时，在WHERE子句中过滤了订单总金额大于1000的订单记录。最后，查询结果只返回了所需的顾客姓名和订单号两个字段。

3. (简答题, 5分) 查询学生表student和课程表course中，选择了课程的学生姓名和课程名称。

表：student

字段：student.student_id, student.student_name

表：course

字段：course.course_id, course.course_name

表：sc

字段：sc.student_id, sc.course_id

SELECT student.stude