Notes

-- Online SQL Editor to Run SQL Online.
-- Use the editor to create new tables, insert data and all other SQL operations.
-- Create the "salesman" table
--CREATE TABLE salesman (
-- salesman_id INT PRIMARY KEY,
-- name VARCHAR(255),
-- city VARCHAR(255),
--commission DECIMAL(5, 2)
--);
-- Create the "salesman" table
--CREATE TABLE salesman (
-- salesman_id INT PRIMARY KEY,
-- name VARCHAR(255),
-- city VARCHAR(255),
--commission DECIMAL(5, 2)
--);

-- Insert sample data into the "salesman" table
---INSERT INTO salesman (salesman_id, name, city, commission)
--VALUES
--(1, 'John', 'New York', 0.15),
--(2, 'Alice', 'Chicago', 0.12),
--(3, 'Bob', 'Los Angeles', 0.10),
--(4, 'Carol', 'Houston', 0.14),
--(5, 'David', 'San Francisco', 0.13),
--(6, 'john', 'texas', 0.16),
--(7, 'libert', 'washington', 0.17),
--(8, 'albert', 'lahore', 0.18);
-- Create the "orders" table
--CREATE TABLE orders (
-- order_no INT PRIMARY KEY,
-- purchase_amount DECIMAL(10, 2),
--order_date DATE,
--customer_id INT,
--salesman_id INT
--);
-- Insert sample data into the "orders" table
--INSERT INTO orders (order_no, purchase_amount, order_date, customer_id, salesman_id)
--VALUES
-- (101, 1500.00, '2023-09-01', 1001, 1),
--(102, 2200.00, '2023-09-02', 1002, 2),
--(103, 1800.00, '2023-09-03', 1003, 1),
--(104, 3000.00, '2023-09-04', 1004, 3),
--(105, 3500.00, '2023-09-05', 1005, 1),
--(106, 5200.00, '2023-09-06', 1006, 2),
--(107, 2800.00, '2023-09-07', 1007, 1),
-- (108, 4000.00, '2023-09-08', 1008, 3);
-- SELECT o.order_no, o.order_date, o.customer_id
--FROM orders o
--WHERE o.salesman_id = (SELECT salesman_id FROM salesman WHERE name = 'Salesman_Name');
--SELECT o.customer_id, o.order_no
--FROM orders o
--INNER JOIN salesman s ON o.salesman_id = s.salesman_id
--WHERE s.city = 'Paris';
--SELECT o.customer_id, o.order_no
--FROM orders o
--INNER JOIN salesman s ON o.salesman_id = s.salesman_id
--WHERE s.city = 'Paris';
--SELECT o.order_no, o.purchase_amount, s.commission
--FROM orders o
--INNER JOIN salesman s ON o.salesman_id = s.salesman_id
--WHERE o.order_no = 'Order_Number';
--SELECT s.salesman_id, s.name, s.city, MAX(s.commission) AS max_commission
--FROM salesman s
--INNER JOIN orders o ON s.salesman_id = o.salesman_id
--WHERE o.purchase_amount > 0.7
--GROUP BY s.salesman_id, s.name, s.city
--ORDER BY max_commission DESC
--LIMIT 1;

--SELECT o.customer_id, s.salesman_id, s.name AS salesman_name, s.city
--FROM orders o
--INNER JOIN salesman s ON o.salesman_id = s.salesman_id;
--SELECT s.salesman_id, s.name, s.city
--FROM salesman s
--INNER JOIN orders o ON s.salesman_id = o.salesman_id
--GROUP BY s.salesman_id, s.name, s.city
--HAVING COUNT(s.salesman_id) > 1;
--ALTER TABLE salesman
--ADD COLUMN product VARCHAR(255);
--SELECT o.order_no, o.purchase_amount, o.order_date
--FROM orders o
--LEFT JOIN salesman s ON o.salesman_id = s.salesman_id
--ORDER BY o.order_date;