-- 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)
);

-- 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)
);

-- Create the Order table
CREATE TABLE Order (
    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)
);




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

SELECT C.c_name, O.o_no, O.purchase_amount
FROM Customer C
JOIN Order O ON C.c_id = O.c_id
WHERE O.order_date NOT IN ('27/7/23', '29/7/23');


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;