How to reuse complex queries with CREATE VIEW | Essential SQL

Опубликовано: 15 Октябрь 2024
на канале: Essential SQL (EssentialSQL)

1,088

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/Essential...
Corresponding Article: https://www.essentialsql.com/sql-coal...

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;