CREATE DATABASE Mid;

CREATE TABLE Salesman (
    s_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    commission DECIMAL(10, 2)
  
);
CREATE TABLE Customer (
    c_id INT PRIMARY KEY,
    c_name VARCHAR(50),
    city VARCHAR(50),
    grade INT,
    s_id INT,
    FOREIGN KEY (s_id) REFERENCES Salesman(s_id)
);
CREATE TABLE Order_1 (
    o_no INT PRIMARY KEY,
    purchase_amount DECIMAL(10, 2),
    order_date DATE,
    c_id INT,
    s_id INT,
    FOREIGN KEY (c_id) REFERENCES Customer(c_id),
    FOREIGN KEY (s_id) REFERENCES Salesman(s_id)
);

INSERT INTO salesman VALUES  (1, 'jannat', 'dhaka', 0.22); 
INSERT INTO salesman VALUES (2, 'nahid', 'barisallah', 0.23); 
INSERT INTO salesman VALUES (3, 'rifat', 'kustia', 0.24);
INSERT INTO salesman VALUES (4, 'atik', 'sal', 0.25);


INSERT INTO Customer (c_id, c_name, city, grade, s_id)
VALUES
    (1, 'A', 'dhaka', 4, 2),
    (2, 'B', 'kustia', 3, 1),
    (3, 'C', 'barisal', 2, 3),
    (4, 'D', 'sal', 3, 4);

INSERT INTO order_1 (o_no, purchase_amount, order_date, c_id, s_id)
VALUES
    (101, 250.00, '2023-07-27', 1, 1),
    (102, 150.50, '2023-07-28', 2, 2),
    (103, 300.75, '2023-07-29', 3, 3);