1.找出所有从不订购任何东西的客户 ---建表语句 CREATE TABLE Customers (Id NUMBER,Name VARCHAR2(10)); INSERT INTO Customers VALUES(1,'Joe'); INSERT INTO Customers VALUES(2,'Henry'); INSERT INTO Customers VALUES(3,'Sam'); INSERT INTO Customers VALUES(4,'Max'); COMMIT; SELECT * FROM Customers; CREATE TABLE Orders (order_Id NUMBER ,CustomerId NUMBER ,order_amt NUMBER ); INSERT INTO Orders VALUES(111,3,300); INSERT INTO Orders VALUES(222,1,50); COMMIT; SELECT * FROM Orders; SELECT * FROM Customers;

查询语句：

SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.Id = o.CustomerId
WHERE o.order_Id IS NULL;

解释：

首先使用 LEFT JOIN 连接 Customers 表和 Orders 表，以便找出那些订购了东西的客户。

然后在 WHERE 子句中查询 order_Id 为空的记录，即没有订购任何东西的客户。

最终结果将会返回所有从不订购任何东西的客户，即 Henry 和 Max。

相关问题

2023/6/8 00:22:13 CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(50), customer_sex CHAR(1), phone_number VARCHAR(20), address VARCHAR(100), email VARCHAR(50), checked_in_room INT REFERENCES Rooms(room_id) ); 往表里写入10条数据

好的，以下是往表里写入10条数据的SQL语句：

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (1, '张三', '男', '13888888888', '北京市海淀区', 'zhangsan@example.com', 101);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (2, '李四', '男', '13999999999', '上海市黄浦区', 'lisi@example.com', 102);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (3, '王五', '女', '13666666666', '广州市天河区', 'wangwu@example.com', 103);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (4, '赵六', '男', '13444444444', '深圳市福田区', 'zhaoliu@example.com', 104);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (5, '钱七', '女', '13555555555', '杭州市西湖区', 'qianqi@example.com', 105);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (6, '孙八', '男', '13333333333', '南京市鼓楼区', 'sunba@example.com', 106);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (7, '周九', '女', '13111111111', '成都市锦江区', 'zhoujiu@example.com', 107);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (8, '吴十', '男', '13222222222', '武汉市江汉区', 'wushi@example.com', 108);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (9, '郑一', '男', '13911112222', '重庆市渝北区', 'zhengyi@example.com', 109);

INSERT INTO Customers (customer_id, customer_name, customer_sex, phone_number, address, email, checked_in_room)
VALUES (10, '秦二', '女', '13822221111', '天津市和平区', 'qiner@example.com', 110);

注意，以上SQL语句仅供参考，具体的数据应根据实际情况进行修改。

ORACLE 使用SQL语句，如何修改模式来跟踪客户对产品的评论和评级?

要跟踪客户对产品的评论和评级，您可以使用以下SQL语句来修改ORACLE数据库中的模式：

1. 创建一个名为“products”的表，其中包含产品的详细信息，如产品ID、名称、描述等。

CREATE TABLE products (
    product_id NUMBER(10) PRIMARY KEY,
    product_name VARCHAR2(100),
    product_description VARCHAR2(200)
);

2. 创建一个名为“customers”的表，其中包含客户的详细信息，如客户ID、姓名、地址等。

CREATE TABLE customers (
    customer_id NUMBER(10) PRIMARY KEY,
    customer_name VARCHAR2(100),
    customer_address VARCHAR2(200)
);

3. 创建一个名为“reviews”的表，其中包含客户对产品的评论和评级信息，如产品ID、客户ID、评论、评级等。

CREATE TABLE reviews (
    review_id NUMBER(10) PRIMARY KEY,
    product_id NUMBER(10),
    customer_id NUMBER(10),
    review_text VARCHAR2(500),
    rating NUMBER(1)
);

4. 为“reviews”表创建外键约束，以确保只有存在于“products”和“customers”表中的产品和客户ID可以添加到“reviews”表中。

ALTER TABLE reviews ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products (product_id);
ALTER TABLE reviews ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

现在，您可以使用INSERT语句将新的评论和评级添加到“reviews”表中，并使用SELECT语句检索客户对产品的评论和评级信息。例如：

INSERT INTO reviews (review_id, product_id, customer_id, review_text, rating)
VALUES (1, 100, 200, '这是一个很棒的产品！', 5);

SELECT r.review_text, r.rating, p.product_name, c.customer_name
FROM reviews r
JOIN products p ON r.product_id = p.product_id
JOIN customers c ON r.customer_id = c.customer_id;

这将返回一个表格，其中包含客户对产品的评论和评级信息以及产品和客户的详细信息。