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;
时间: 2024-04-23 10:26:57 浏览: 54
查询语句:
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数据库怎么建表
要在 Oracle 数据库中建表,可以遵循以下步骤:
1. 使用管理员用户登录到 Oracle 数据库。
2. 使用 CREATE TABLE 语句来创建表,语法如下:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
在这里,table_name 是你想创建的表的名称,column1、column2、column3 等是表中的列名,datatype 是数据类型,constraint 是列级约束。
例如,创建一个名为 customers 的表,其中包含 customer_id、customer_name 和 customer_email 三列,数据类型分别为数字、文本和文本,可以使用以下 SQL 语句:
CREATE TABLE customers (
customer_id number(10) NOT NULL,
customer_name varchar2(50),
customer_email varchar2(50) UNIQUE
);
此处,customer_id 列的数据类型为数字,最大长度为 10,NOT NULL 约束指示该列是必需的;customer_name 和 customer_email 列的数据类型均为 varchar2,其最大长度为 50,其中 customer_email 具有 UNIQUE 约束,这意味着其中的每个值都必须是唯一的。
3. 创建完表之后,可以使用 INSERT 语句来向表中插入数据,例如:
INSERT INTO customers (customer_id, customer_name, customer_email)
VALUES (1, 'Alice', 'alice@example.com');
这将在 customers 表中插入一行,其中包括 customer_id 为 1、customer_name 为 "Alice",customer_email 为 "alice@example.com"。
这是 Oracle 数据库建表的基本步骤,你可以根据实际需要进行调整并添加更多细节。
阅读全文