How to reuse complex queries with CREATE VIEW | Essential SQL

Learn how to create a SQL view so you can reuse your complex queries in your SQL Server, PostgeSQL, and MySQL databases. More importantly watch until the end if you want to know how do a case insensitive replacement in PostgreSQL. One Last Thing... This video is part of my Intermediate Learning series. If you're interested in learning SQL subscribe to @Essential SQL and then check out our Intermediate Learner Playlist. Of course, I also encourage you to visit https://www.essentialsql.com to learn even more! Important links: Sample PizzaDB: https://github.com/kwenzel1/EssentialSQLSamples/tree/master/PizzaDB Corresponding Article: https://www.essentialsql.com/sql-coalesce-function-and-null/ Source Code: --First Query select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, Sum(i.Quantity * p.Price) OrderPrice, cast(Sum(i.Quantity * p.Price) * isnull(cp.PercentDiscount ,0) / 100.0 as numeric(14,2)) DiscountAmount, cast(Sum(i.Quantity * p.Price) * (1.00 - (isnull(cp.PercentDiscount ,0) / 100.0)) as numeric(14,2)) FinalOrderPrice from CustomerOrder o inner join Customer c on o.CustomerID = c.CustomerID inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID inner join Product p on i.ProductID = p.ProductID left join Coupon cp on o.CouponID = cp.CouponID group by o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount --SQL Server create view CustomerOrderSummary as select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, Sum(i.Quantity * p.Price) OrderPrice, cast(Sum(i.Quantity * p.Price) * isnull(cp.PercentDiscount ,0) / 100.0 as numeric(14,2)) DiscountAmount, cast(Sum(i.Quantity * p.Price) * (1.00 - (isnull(cp.PercentDiscount ,0) / 100.0)) as numeric(14,2)) FinalOrderPrice from CustomerOrder o inner join Customer c on o.CustomerID = c.CustomerID inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID inner join Product p on i.ProductID = p.ProductID left join Coupon cp on o.CouponID = cp.CouponID group by o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount --PostgesSQL create view public.CustomerOrderSummary as select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, Sum(i.Quantity * p.Price) OrderPrice, cast(Sum(i.Quantity * p.Price) * coalesce(cp.PercentDiscount ,0) / 100.0 as decimal(14,2)) DiscountAmount, cast(Sum(i.Quantity * p.Price) * (1.00 - (coalesce(cp.PercentDiscount ,0) / 100.0)) as decimal(14,2)) FinalOrderPrice from CustomerOrder o inner join Customer c on o.CustomerID = c.CustomerID inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID inner join Product p on i.ProductID = p.ProductID left join Coupon cp on o.CouponID = cp.CouponID group by o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount; --MySQL create view CustomerOrderSummary as select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, Sum(i.Quantity * p.Price) OrderPrice, cast(Sum(i.Quantity * p.Price) * coalesce(cp.PercentDiscount ,0) / 100.0 as decimal(14,2)) DiscountAmount, cast(Sum(i.Quantity * p.Price) * (1.00 - (coalesce(cp.PercentDiscount ,0) / 100.0)) as decimal(14,2)) FinalOrderPrice from CustomerOrder o inner join Customer c on o.CustomerID = c.CustomerID inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID inner join Product p on i.ProductID = p.ProductID left join Coupon cp on o.CouponID = cp.CouponID group by o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount;