create database AAA;
use AAA;
-- Create customers table
CREATE TABLE customers (
customers_id INT NOT NULL,
Account_id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
telephone INT NOT NULL,
Date_of_birth DATE NOT NULL,
Primary_language VARCHAR(50) NOT NULL,
PRIMARY KEY (customers_id),
FOREIGN KEY (Account_id) REFERENCES Account(Account_id)
);
-- Insert data into the customers table
INSERT INTO customers (customers_id, Account_id, Name, telephone, Date_of_birth, Primary_language)
VALUES
(1, 1001, 'John Doe', 5551234567, '1990-05-15', 'English'),
(2, 1002, 'Jane Smith', 5559876543, '1985-09-20', 'Spanish'),
(3, 1003, 'Mike Johnson', 5555555555, '1995-03-10', 'French');
-- Create Account table
CREATE TABLE Account (
Account_id INT NOT NULL,
Payment_dates DATE NOT NULL,
Type VARCHAR(50) NOT NULL,
Payment_amount INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY (Account_id),
FOREIGN KEY (customer_id) REFERENCES customers(customers_id)
);
-- Insert data into the Account table
INSERT INTO Account (Account_id, Payment_dates, Type, Payment_amount, customer_id)
VALUES
(1001, '2023-10-01', 'Credit', 500, 1),
(1002, '2023-09-15', 'Debit', 300, 2),
(1003, '2023-09-30', 'Credit', 800, 3);
-- Create feedback table
CREATE TABLE feedback (
customers_id INT NOT NULL,
feedback_date DATE,
Feedback_type VARCHAR(50) NOT NULL,
FOREIGN KEY (customers_id) REFERENCES customers(customers_id)
);
-- Insert data into the feedback table
INSERT INTO feedback (customers_id, feedback_date, Feedback_type)
VALUES
(1, '2023-10-05', 'Complaint'),
(2, '2023-10-03', 'Suggestion'),
(3, '2023-10-02', 'Compliment');
-- Create 65_employees table
CREATE TABLE 65_employees (
employees_id INT NOT NULL,
Dependent_id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Title VARCHAR(50) NOT NULL,
Salary INT NOT NULL,
skill VARCHAR(50) NOT NULL,
age INT NOT NULL,
Date_of_hire DATE NOT NULL,
telephone INT NOT NULL,
Date_of_birth DATE NOT NULL,
Primary_language VARCHAR(50) NOT NULL,
PRIMARY KEY (employees_id),
FOREIGN KEY (Dependent_id) REFERENCES dependents(depebdent_id)
);
-- Insert data into the 65_employees table
INSERT INTO 65_employees (employees_id, Dependent_id, Name, Title, Salary, skill, age, Date_of_hire, telephone, Date_of_birth, Primary_language)
VALUES
(1, 1001, 'John Smith', 'Manager', 75000, 'Management', 35, '2023-01-15', 5551234567, '1988-05-10', 'English'),
(2, 1002, 'Lux Ren', 'Engineer', 60000, 'Engineering', 28, '2023-02-20', 5559876543, '1995-09-12', 'Chinese'),
(3, 1003, 'Mike Johnson', 'Analyst', 55000, 'Analysis', 30, '2023-03-10', 5555555555, '1993-03-05', 'French');
-- Create sales_record table
CREATE TABLE sales_record (
orders_id INT NOT NULL,
sales_id INT NOT NULL,
employees_id INT NOT NULL,
Income DOUBLE NOT NULL,
Date_of_sale DATE NOT NULL,
PRIMARY KEY (sales_id),
FOREIGN KEY (employees_id) REFERENCES 65_employees(employees_id),
FOREIGN KEY (orders_id) REFERENCES orders(orders_id)
);
-- Insert data into the sales_record table
INSERT INTO sales_record (orders_id, sales_id, employees_id, Income, Date_of_sale)
VALUES
(1, 1001, 1, 250.50, '2023-10-05'),
(2, 1002, 2, 350.75, '2023-10-03'),
(3, 1003, 3, 150.25, '2023-10-02');
-- Create the dependents table
CREATE TABLE dependents (
depebdent_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
relationship VARCHAR(50) NOT NULL,
PRIMARY KEY (depebdent_id)
);
-- Insert data into the dependents table
INSERT INTO dependents (depebdent_id, name, age, relationship)
VALUES
(1001, 'Mary Smith', 12, 'Daughter'),
(1002, 'James Ren', 8, 'Son'),
(1003, 'Emily Johnson', 10, 'Daughter');
-- Creating the orders table
CREATE TABLE orders (
orders_id INT NOT NULL,
customer_id INT NOT NULL,
orders_date DATE NOT NULL,
Credit_authorization_status VARCHAR(50) NOT NULL,
PRIMARY KEY (orders_id),
FOREIGN KEY (customer_id) REFERENCES customers(customers_id)
);
-- Insert data into the orders table
INSERT INTO orders (orders_id, customer_id, orders_date, Credit_authorization_status)
VALUES
(1, 1, '2023-10-05', 'Approved'),
(2, 2, '2023-10-03', 'Pending'),
(3, 3, '2023-10-02', 'Approved');
-- Create Address_for_customer table
CREATE TABLE Address_for_customer (
customer_id INT NOT NULL,
city VARCHAR(50) NOT NULL,
province_or_state VARCHAR(50) NOT NULL,
postal_or_zip_code INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customers_id)
);
-- Insert data into Address_for_customer table
INSERT INTO Address_for_customer (customer_id, city, province_or_state, postal_or_zip_code)
VALUES
(1, 'New York', 'New York', 11111),
(2, 'Los Angeles', 'California', 91111),
(3, 'Chicago', 'Illinois', 666661);
-- Create Address_for_employees table
CREATE TABLE Address_for_employees (
employees_id INT NOT NULL,
city VARCHAR(50) NOT NULL,
province_or_state VARCHAR(50) NOT NULL,
postal_or_zip_code INT NOT NULL,
FOREIGN KEY (employees_id) REFERENCES 65_employees(employees_id)
);
-- Insert data into Address_for_employees table
INSERT INTO Address_for_employees (employees_id, city, province_or_state, postal_or_zip_code)
VALUES
(1, 'New York', 'New York', 10001),
(2, 'Los Angeles', 'California', 90001),
(3, 'Chicago', 'Illinois', 60001);
-- Create branch table
CREATE TABLE branch (
branch_id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
employees_id INT NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (employees_id) REFERENCES 65_employees(employees_id),
FOREIGN KEY (customer_id) REFERENCES customers(customers_id)
);
-- Insert data into the branch table
INSERT INTO branch (branch_id, Name, employees_id, customer_id)
VALUES
(1, 'Branch A', 1, 1),
(2, 'Branch B', 2, 2),
(3, 'Branch C', 3, 3);
-- create table_for_item_and_order table
CREATE TABLE table_for_item_and_order (
orders_id INT NOT NULL,
items_id INT NOT NULL,
FOREIGN KEY (orders_id) REFERENCES orders(orders_id),
FOREIGN KEY (items_id) REFERENCES items(items_id)
);
-- Insert data into table_for_item_and_order table
INSERT INTO table_for_item_and_order (orders_id, items_id)
VALUES
(1, 101),
(2, 102),
(3, 103);
-- Create items table
CREATE TABLE items (
items_id INT NOT NULL,
supplier_id INT NOT NULL,
description VARCHAR(50) NOT NULL,
color VARCHAR(50) NOT NULL,
size VARCHAR(50) NOT NULL,
pattern VARCHAR(50) NOT NULL,
type VARCHAR(50) NOT NULL,
PRIMARY KEY (items_id),
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
-- Insert data into the items table
INSERT INTO items (items_id, supplier_id, description, color, size, pattern, type)
VALUES
(101, 1, 'Product A', 'Red', 'Medium', 'Striped', 'Clothing'),
(102, 2, 'Product B', 'Blue', 'Large', 'Polka Dot', 'Accessories'),
(103, 3, 'Product C', 'Green', 'Small', 'Solid Color', 'Footwear');
-- Create suppliers table
CREATE TABLE suppliers (
supplier_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
address_of_factory VARCHAR(50) NOT NULL,
contract_information VARCHAR(50) NOT NULL,
PRIMARY KEY (supplier_id)
);
-- Insert data into suppliers table
INSERT INTO suppliers (supplier_id, name, address_of_factory, contract_information)
VALUES
(1, 'Supplier A', '123 Main St, CityA', 'Contact: John Doe, Phone: 555-123-4567'),
(2, 'Supplier B', '456 Elm St, CityB', 'Contact: Jane Smith, Phone: 555-987-6543'),
(3, 'Supplier C', '789 Oak St, CityC', 'Contact: Mike Johnson, Phone:
```sql
实在不清楚为啥报错