JOIN

Aryan · May 04, 2021 · SQL
-- create a table
CREATE TABLE Seller (
S_id INTEGER PRIMARY KEY,
S_name TEXT NOT NULL,
S_city TEXT NOT NULL,
Commission INTEGER
);
CREATE TABLE Orders (
Order_no INTEGER PRIMARY KEY,
Purch_amt Float NOT NULL,
Order_date FORMAT 'dd.mm.yyyy' NOT NULL,
C_id INTEGER,
Seller_id INTEGER
);
CREATE TABLE Customer (
Cust_id INTEGER PRIMARY KEY,
Cust_name TEXT NOT NULL,
Cust_city TEXT NOT NULL,
S_id INTEGER
);
-- insert some values
INSERT INTO Seller VALUES (101, 'Aryan', 'Rourkela', 1500);
INSERT INTO Seller VALUES (102, 'Sagar', 'Bhubaneswar', 2000);
INSERT INTO Seller VALUES (103, 'Ashish', 'Ranchi', 4000);
INSERT INTO Seller VALUES (104, 'Ankit', 'Sambalpur', 2500);
INSERT INTO Seller VALUES (105, 'Satyabrata', 'Cuttack', 3000);


INSERT INTO Orders VALUES (1, 1500, '2021-05-01', 501, 101);
INSERT INTO Orders VALUES (2, 2500, '2021-04-30', 502, 102);
INSERT INTO Orders VALUES (3, 3035, '2021-03-12', 503, 103);
INSERT INTO Orders VALUES (4, 1699, '2021-04-26', 504, 104);
INSERT INTO Orders VALUES (5, 1220, '2021-04-24', 505, 105);


INSERT INTO Customer VALUES (501, 'Soumya', 'Angul', 101);
INSERT INTO Customer VALUES (502, 'Ayush', 'Ranchi', 105);
INSERT INTO Customer VALUES (503, 'Rakesh', 'Bhubaneswar', 108);
INSERT INTO Customer VALUES (504, 'Dinesh', 'Konark', 104);
INSERT INTO Customer VALUES (505, 'Aman', 'Rourkela', 110);

-- fetch some values
SELECT Orders.Order_no,
Orders.Order_date,
Orders.purch_amt,
Customer.cust_name AS "Customer Name",
Seller.S_name AS "Salesman",
Seller.commission
FROM Orders
INNER JOIN Customer
ON Orders.C_id=Customer.Cust_id
INNER JOIN Seller
ON Orders.Seller_id=Seller.S_id;

Comments

Please sign up or log in to contribute to the discussion.