SQLize Online / PHPize Online  /  SQLtest Online

create table Customer ( Id int primary key not null , FirstName nvarchar(40) not null, LastName nvarchar(40) not null, City nvarchar(40) null, Country nvarchar(40) null, Phone nvarchar(20) null ) ; create table Orders ( Id int primary key not null , OrderDate datetime not null , OrderNumber nvarchar(10) null, CustomerId int not null, TotalAmount decimal(12,2) null default 0 ) ; create table OrderItem ( Id int primary key not null , OrderId int not null, ProductId int not null, UnitPrice decimal(12,2) not null default 0, Quantity int not null default 1 ) ; create table Product ( Id int primary key not null , ProductName nvarchar(50) not null, SupplierId int not null, UnitPrice decimal(12,2) null default 0, Package nvarchar(30) null, IsDiscontinued bit not null default 0 ) ; create table Supplier ( Id int primary key not null , CompanyName nvarchar(40) not null, ContactName nvarchar(50) null, ContactTitle nvarchar(40) null, City nvarchar(40) null, Country nvarchar(40) null, Phone nvarchar(30) null, Fax nvarchar(30) null ) ; INSERT INTO Customer ( Id , FirstName , LastName , City , Country , Phone )VALUES(1,'Maria','Anders','Berlin','Germany','030-0074321'); INSERT INTO Customer ( Id , FirstName , LastName , City , Country , Phone )VALUES(2,'Ana','Trujillo','MÈxico D.F.','Mexico','(5) 555-4729'); INSERT INTO Customer ( Id , FirstName , LastName , City , Country , Phone )VALUES(3,'Antonio','Moreno','MÈxico D.F.','Mexico','(5) 555-3932'); INSERT INTO Customer ( Id , FirstName , LastName , City , Country , Phone )VALUES(4,'Jakub','Kaczmarek','Kair','Egypt','(5) 555-3932'); INSERT INTO Supplier ( Id , CompanyName , ContactName , City , Country , Phone , Fax )VALUES(1,'Exotic Liquids','Charlotte Cooper','London','UK','(171) 555-2222',NULL); INSERT INTO Supplier ( Id , CompanyName , ContactName , City , Country , Phone , Fax )VALUES(2,'New Orleans Cajun Delights','Shelley Burke','New Orleans','USA','(100) 555-4822',NULL); INSERT INTO Product ( Id , ProductName , SupplierId , UnitPrice , Package , IsDiscontinued )VALUES(1,'Chai',1,18.00,'10 boxes x 20 bags',0); INSERT INTO Product ( Id , ProductName , SupplierId , UnitPrice , Package , IsDiscontinued )VALUES(2,'Chang',1,19.00,'24 - 12 oz bottles',0); INSERT INTO Product ( Id , ProductName , SupplierId , UnitPrice , Package , IsDiscontinued )VALUES(3,'Aniseed Syrup',1,10.00,'12 - 550 ml bottles',0); INSERT INTO Product ( Id , ProductName , SupplierId , UnitPrice , Package , IsDiscontinued )VALUES(4,'Chef Anton''s Cajun Seasoning',2,22.00,'48 - 6 oz jars',0); INSERT INTO Product ( Id , ProductName , SupplierId , UnitPrice , Package , IsDiscontinued )VALUES(5,'Chef Anton''s Gumbo Mix',2,21.35,'36 boxes',1); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(1,'2018-07-04',1,440.00,'542378'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(2,'2018-07-04',2,1863.40,'542379'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(3,'2018-07-05',1,1813.00,'542380'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(4,'2018-07-05',1,670.80,'542381'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(5,'2018-07-06',2,3730.00,'542382'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(6,'2018-07-07',2,1444.80,'542383'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(7,'2018-07-08',1,625.20,'542384'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(8,'2018-07-09',2,2490.50,'542385'); INSERT INTO Orders ( Id , OrderDate , CustomerId , TotalAmount , OrderNumber )VALUES(9,'2018-07-15',1,517.80,'542386'); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(1,1,1,14.00,12); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(2,1,2,9.80,10); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(3,1,3,34.80,5); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(4,2,1,18.60,9); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(5,2,2,42.40,40); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(6,3,3,7.70,10); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(7,3,4,42.40,35); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(8,4,1,16.80,15); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(9,4,2,16.80,6); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(10,5,3,42.40,35); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(11,5,4,16.80,15); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(12,6,1,16.80,6); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(13,6,2,16.80,15); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(14,7,3,16.80,6); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(15,8,4,16.80,15); INSERT INTO OrderItem ( Id , OrderId , ProductId , UnitPrice , Quantity )VALUES(16,9,1,16.80,6); select * from Customer C right outer join Orders O on C.id=O.customerid