Notes

create table SupplierNew(
_id int PRIMARY KEY,
CompanyName varchar(20),
ContactName varchar(20),
City varchar(30),
Country varchar(30),
Phone int,
Fax varchar(40)
);

create table CustomerNew(
_id int PRIMARY KEY,
FirstName varchar(20),
LastName varchar(20),
City varchar(30),
Country varchar(30),
Phone int
)
create table OrderNew(
_id int PRIMARY KEY,
OrderDate datetime,
Customer_id int FOREIGN KEY REFERENCES CustomerNew(_id),
TotalAmount varchar(15)
);
create table Product(
_id int PRIMARY KEY,
ProductName varchar(200),
Suipplier_id int FOREIGN KEY REFERENCES SupplierNew(_id),
UnitPrice varchar(15),
Package varchar(30),
isDiscontinued int CHECK(isDiscontinued IN(0,1))
);

create table OrderItem(
_id int PRIMARY KEY,
Order_id int FOREIGN KEY REFERENCES OrderNew(_id),
Product_id int FOREIGN KEY REFERENCES Product(_id),
UnitPrice varchar(15),
Quantity int
);



INSERT INTO SupplierNew(_id, CompanyName ,ContactName ,City ,Country ,Phone,Fax) Values (1, 'ABC', 'John','Koklkata','India', 746373893, 668682166);
INSERT INTO SupplierNew(_id, CompanyName ,ContactName ,City ,Country ,Phone,Fax) Values (2, 'DEF', 'Daniel','Sydney','USA', 876373893, 987682166);
INSERT INTO SupplierNew(_id, CompanyName ,ContactName ,City ,Country ,Phone,Fax) Values (3, 'GHI', 'Ron','California','USA', 946373893, 79682166);

INSERT INTO CustomerNew Values (4, 'SGSIH', 'hsjhs','lksasnslk','India', 75508459);
INSERT INTO CustomerNew Values (5, 'SuySIH', 'jvvh','kghyuy','vgtbjct', 66890898);
INSERT INTO CustomerNew Values (6, 'SuytH', 'ressesrds','oujiojij','India', 755084598);

INSERT INTO OrderNew Values (7,SYSDATETIME(),1002, '2000');
INSERT INTO OrderNew Values (8,SYSDATETIME(),1003, '2000');
INSERT INTO OrderNew Values (9,SYSDATETIME(),1002, '2000');

INSERT INTO Product Values (10,'Sport Shoes', 101,'2000','XYZ', 0);
INSERT INTO Product Values (11,'Running Shoes', 102,'3500','XYZ', 0);
INSERT INTO Product Values (12,'Walking Shoes', 103,'4500','XYZ', 1);

INSERT INTO OrderItem Values (13,1011, 001, '4000', 4);
INSERT INTO OrderItem Values (14,1012, 003, '4500', 3);
INSERT INTO OrderItem Values (15,1013, 004, '5000', 2);

Select * from Product;

Select A.ProductName, B.CompanyName from Product A LEFT JOIN SupplierNew B on A.Suipplier_id = B._id;
Select A.FirstName, B._id from CustomerNew A LEFT JOIN OrderNew B on A._id = B.Customer_id;
Select A._id, B.Productname from OrderItem A LEFT JOIN Product B on A.Product_id = B._id;
Select * from OrderNew A LEFT JOIN OrderItem B on A._id = B.Order_id;