SQL Tutorial - How to Join to Derived Tables скачать в хорошем качестве

SQL Tutorial - How to Join to Derived Tables

In this SQL Tutorial learn to how to join to derived tables. I talk you through the benefits of derived tables, show you to create a derived table then how to join to the derived table. In the video we work through practical examples, creating the same query with a join between two tables then grouping and creating a derived table then joining to give the same results. If you are not familiar with derived tables then you can check out this video on my channel:    • How to create Derived Tables in SQL Server  . #sqltutorials #sqlserver #dataengineering #data If you would like to follow along with the video you can use the code examples below: CREATE TABLE dbo.Customers ( CustomerKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Customers_CustomerKey PRIMARY KEY (CustomerKey), FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL, BusinessName VARCHAR(50) NULL, CustomerType CHAR(1) ); INSERT INTO dbo.Customers (FirstName, LastName, BusinessName, CustomerType) VALUES ('Albert', 'Gunner', NULL, 'P'), (NULL, NULL, 'Beach Store', 'B'), ('Catherine', 'Smith', NULL, 'P'), (NULL, NULL, 'Duncan''s Hair', 'B'), ('Erin', 'Fairclough', NULL, 'P'), (NULL, NULL, 'Gaming Zone', 'B'), ('Henry', 'Long', NULL, 'P'); CREATE TABLE dbo.Orders ( OrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Orders_OrderKey PRIMARY KEY (OrderKey), CustomerKey INT NULL, OrderDate DATE NULL, OrderAmount DECIMAL(8, 2) ); INSERT INTO dbo.Orders (CustomerKey, OrderDate, OrderAmount) VALUES (1, '20220501', 1000.00), (1, '20220602', 9500.00), (2, '20220501', 3000.00), (2, '20220602', 3000.00), (3, '20220501', 12000.00), (3, '20220602', 6000.00), (3, '20220501', 4000.00), (4, '20220602', 7000.00), (4, '20220501', 9000.00), (4, '20220602', 10000.00), (4, '20220501', 6000.00), (5, '20220602', 8000.00), (5, '20220501', 8000.00), (6, '20220602', 22000.00), (7, '20220501', 3000.00), (7, '20220602', 2000.00); -- create query using GROUP BY SELECT Cust.CustomerKey, Cust.FirstName, Cust.LastName, Cust.BusinessName, Cust.CustomerType, COUNT(OrderKey) AS NoOfOrders, SUM(OrderAmount) AS TotalAmount FROM dbo.Customers AS Cust INNER JOIN dbo.Orders AS Ord ON Cust.CustomerKey = Ord.CustomerKey GROUP BY Cust.CustomerKey, Cust.FirstName, Cust.LastName, Cust.BusinessName, Cust.CustomerType; -- create query using DERIVED TABLE SELECT Cust.CustomerKey, Cust.FirstName, Cust.LastName, Cust.BusinessName, Cust.CustomerType, Ord.NoOfOrders, Ord.TotalAmount FROM dbo.Customers AS Cust INNER JOIN ( SELECT CustomerKey, COUNT(OrderKey) AS NoOfOrders, SUM(OrderAmount) AS TotalAmount FROM dbo.Orders GROUP BY CustomerKey ) AS Ord ON Cust.CustomerKey = Ord.CustomerKey;