CREATE TABLE SALESMAN (
SALESMAN_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
CITY VARCHAR2(100),
COMMISSION NUMBER(5,2)
);
CREATE TABLE CUSTOMER (
CUSTOMER_ID NUMBER PRIMARY KEY,
CUST_NAME VARCHAR2(100),
CITY VARCHAR2(100),
GRADE NUMBER,
SALESMAN_ID NUMBER,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID)
);
CREATE TABLE ORDERS (
ORD_NO NUMBER PRIMARY KEY,
PURCH_AMT NUMBER(10,2),
ORD_DATE DATE,
CUSTOMER_ID NUMBER,
SALESMAN_ID NUMBER,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID)
);
INSERT INTO SALESMAN VALUES (5001, 'Abdul', 'New York', 0.15);
INSERT INTO SALESMAN VALUES (5002, 'John', 'Chicago', 0.10);
INSERT INTO SALESMAN VALUES (5003, 'Steve', 'Dallas', 0.20);
INSERT INTO CUSTOMER VALUES (3001, 'Wahid', 'New York', 200, 5001);
INSERT INTO CUSTOMER VALUES (3002, 'Nancy', 'Chicago', 300, 5002);
INSERT INTO CUSTOMER VALUES (3003, 'Tom', 'Dallas', 150, 5003);
INSERT INTO ORDERS VALUES (7001, 1500.00, TO_DATE('2023-01-12', 'YYYY-MM-DD'), 3001, 5001);
INSERT INTO ORDERS VALUES (7002, 2000.00, TO_DATE('2023-02-10', 'YYYY-MM-DD'), 3002, 5002);
INSERT INTO ORDERS VALUES (7003, 2500.00, TO_DATE('2023-03-08', 'YYYY-MM-DD'), 3003, 5003);
COMMIT;
SELECT
s.SALESMAN_ID,
s.NAME AS SALESMAN_NAME,
c.CUSTOMER_ID,
c.CUST_NAME AS CUSTOMER_NAME
FROM SALESMAN s
JOIN CUSTOMER c ON s.SALESMAN_ID = c.SALESMAN_ID
ORDER BY s.SALESMAN_ID;
Telegram: DB developers channel