-- Active: 1695101156320@@127.0.0.1@3306@test0101


-- create database and table


-- Create the Salesman table
CREATE TABLE Salesman (
    s_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    city VARCHAR(255),
    commission DECIMAL(10, 2),
    salary DECIMAL(10, 2)
);

INSERT INTO Salesman (s_id, name, city, commission, salary)
VALUES
    (1, 'John Smith', 'New York', 0.15, 50000.00),
    (2, 'Alice Johnson', 'Los Angeles', 0.12, 48000.50),
    (3, 'Robert Brown', 'Chicago', 0.14, 52000.75),
    (4, 'Mary Davis', 'Houston', 0.13, 49000.25),
    (5, 'Michael Lee', 'San Francisco', 0.16, 51000.00);

-- Create the Customer table
CREATE TABLE Customer (
    c_id INT PRIMARY KEY,
    c_name VARCHAR(255) NOT NULL,
    city VARCHAR(255),
    grade INT,
    s_id INT,
    FOREIGN KEY (s_id) REFERENCES Salesman(s_id)
);
INSERT INTO Customer (c_id, c_name, city, grade, s_id)
VALUES
    (1, 'Alice Johnson', 'Los Angeles', 4, 2),
    (2, 'Bob Smith', 'New York', 3, 1),
    (3, 'Charlie Brown', 'Chicago', 2, 3),
    (4, 'David Davis', 'Houston', 3, 4),
    (5, 'Eve Williams', 'San Francisco', 4, 5);

INSERT INTO Orders (o_no, purchase_amount, order_date, c_id, s_id)
VALUES
    (101, 250.00, '2023-07-15', 1, 1),
    (102, 150.50, '2023-07-20', 2, 2),
    (103, 300.75, '2023-07-25', 3, 3),
    (104, 175.25, '2023-07-30', 4, 4),
    (105, 200.00, '2023-08-05', 5, 5);


-- Create the Order table
CREATE TABLE Orders (
    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 Orders (o_no, purchase_amount, order_date, c_id, s_id)
VALUES
    (113, 250.00, '2023-07-15', 1, 1),
    (1001, 150.50, '2023-07-20', 2, 2),
    (1060, 300.75, '2023-07-25', 3, 3),
    (106, 175.25, '2023-07-30', 4, 4),
    (1017, 200.00, '2023-08-05', 5, 5);








SELECT name, city
FROM Salesman
WHERE city = 'Chittagong';



SELECT C.c_name, O.o_no, O.purchase_amount
FROM Customer C
JOIN Orders O ON C.c_id = O.c_id
WHERE O.order_date NOT IN ('2023-07-27', '2023-07-29');

SELECT *
FROM Salesman
WHERE commission BETWEEN 0.12 AND 0.14;

UPDATE Customer
SET c_name = 'Karim'
WHERE c_name = 'Rahim';


SELECT name
FROM Salesman
ORDER BY salary DESC
LIMIT 1 OFFSET 1;


SELECT * FROM salesman;
SELECT * FROM customer;

SELECT * FROM orders;