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
c.CUST_NAME,
c.CITY,
o.ORD_NO,
o.ORD_DATE,
o.PURCH_AMT
FROM CUSTOMER c
LEFT JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
ORDER BY c.CUST_NAME ASC;
Telegram: DB developers channel