SQL Query for Multiple Table SELECT, INSERT, UPDATE and DELETE WITH JOIN CLAUSE

Order Database

A. SELECT with multiple table in JOIN clause:

--Select records form OrderItem table for a given OrderNumber 
SELECT * 
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GO

Result:

B. INSERT with multiple table in JOIN clause:

--Insert records in Invoice table for a given OrderNumber 
INSERT INTO dbo.Invoice (OrderId, ItemCount, ItemTotalAmount)
SELECT dbo.[Order].Id as  OrderId, 
	Sum(dbo.OrderItem.Quantity) as ItemCount, 
	Sum(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPrice) as ItemTotalAmount
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GROUP BY dbo.[Order].Id
GO

Result:

C. UPDATE with multiple table in JOIN clause:

--Updating LastModifiedDate in OrderItem table for a given OrderNumber 
UPDATE dbo.OrderItem
SET dbo.OrderItem.LastModifiedDate = GETDATE()
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GO

--Select records form OrderItem table for a given OrderNumber 
SELECT *
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GO

Result:

D. DELETE with multiple table in JOIN clause:

--Deleting record from OrderItem table for a given OrderNumber 
DELETE dbo.OrderItem
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GO

--Select records form OrderItem table for a given OrderNumber 
SELECT dbo.OrderItem.*
FROM dbo.OrderItem 
	INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id 
WHERE dbo.[Order].OrderNumber = 542393
GO

Result: