SQL Join Query - Building Complex Join Query - Day 5 #100days of SQL

 Hi ,

In this blog post will check the join queries

SQL Join function below diagram shows difference possibilities

 

Below queries has been executed on the video podcast

-- will see about join query from the db table schema diagram

-- will check about product and transaction history table on join  query

select * from Production.Product

 -- 504 records

select * from Production.TransactionHistory 

-- 113443 records

-- inner join

select * from Production.Product p inner join Production.TransactionHistory t

on

p.ProductID = t.ProductID 

-- full join or full outer join

select * from Production.Product p full outer join Production.TransactionHistory t

on

p.ProductID = t.ProductID

-- right join or right outer join

select * from Production.Product p right outer join Production.TransactionHistory t

on

p.ProductID = t.ProductID

-- left join or left outer join

select * from Production.Product p left outer join Production.TransactionHistory t

on

p.ProductID = t.ProductID

 -- now will complex join and other sql query operations

select 

p.ProductID, p.StandardCost, p.ListPrice,

t.ActualCost, t.Quantity

from Production.Product p cross join Production.TransactionHistory t

where

p.class is not null and 

t.ActualCost <> 0.00 and 

p.StandardCost <> 0.00 and

p.ListPrice <> 0.00 and

p.Name like '%Road%' and

p.ProductNumber like '%7'

-- will try with group by 

-- from the sql query execution cross join performance takes too long time compared with inner join

-- so prefer only inner join, full join , right / left join sql query operations

select 

Top 100

p.ProductID, AVG(p.StandardCost) as AvgStdCost, AVG(p.ListPrice) as AvgListPrice,

Avg(t.ActualCost) as AvgActualCost, Count(t.Quantity) as TotalQuantity,

Row_number() over (order by AVG(p.StandardCost) desc  ) as StandardCostRank,

Rank() over (order by AVG(p.StandardCost) desc  ) as DistinctStandardCostRank,

Dense_Rank() over (order by AVG(p.StandardCost) desc  ) as DistinctNextSeqStandardCostRank

from Production.Product p inner join Production.TransactionHistory t

on

p.ProductID = t.ProductID 

where

p.class is not null and 

t.ActualCost <> 0.00 and 

p.StandardCost <> 0.00 and

p.ListPrice <> 0.00 and

p.Name like '%Road%'

--p.ProductNumber like '%7'

group by

p.ProductID

Order by

AvgStdCost Desc

-- now will do more sql query operations with more than two tables

-- will check in db schema

-- from db schema simple flow will join Sales person with product 

-- Sales person Business EntityId connecting with salesperson id foreignkey SalesOderHeader table

-- finding outliers from max min sales deviations totally 14 records has min max deviations in salesdata

-- thanks for watching

select 

p.ProductID, AVG(sp.Bonus) as AverageBonus, Avg(sp.SalesQuota)  as AverageSalesQuota,

Avg(sp.SalesYTD)  as AverageSalesYTD,Avg(sp.SalesLastYear) as AverageSalesLY,

Avg(sod.UnitPrice)  as AverageUP, Avg(sod.UnitPriceDiscount)  as AverageUPD, 

Avg(sod.LineTotal)  as AverageLineTotal,

(Max(sp.SalesYTD) - Avg(sp.SalesYTD)) as MaxSalesDeviation,

(Avg(sp.SalesYTD) - Min(sp.SalesYTD)) as MinSalesDeviation

from 

Sales.SalesPerson sp

inner join 

Sales.SalesOrderHeader soh

on

sp.BusinessEntityID = soh.SalesPersonID

inner join 

Sales.SalesOrderDetail sod -- salesorderid

on

soh.SalesOrderID = sod.SalesOrderID

inner join

Sales.SpecialOfferProduct sop 

on

sop.SpecialOfferID = sod.SalesOrderDetailID

inner join

Production.Product p

on

p.ProductID = sop.ProductID

group by

p.ProductID

having

(Max(sp.SalesYTD) - Avg(sp.SalesYTD)) <> 0.00 and

(Avg(sp.SalesYTD) - Min(sp.SalesYTD)) <> 0.00

order by

p.ProductID