Derived Tables

Following are the signs those qualify a query to be a derived table:

1. These are defined in FROM clause of an outer query.
2.  It is surrounded by parenthesis followed by AS clause to define the derived table name.

A derived table is an example of a Sub Query that is used in the FROM clause of a SELECT statement to retrieve a set of records. A derived table is a virtual table that is created within the scope of a query.

Case Scenario:

Create a table for trainers:

CREATE TABLE CodePicksTrainer(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
JoiningDate DATE
)

Insert trainer details:

INSERT INTO CodePicksTrainer(FirstName, LastName, DateOfBirth, JoiningDate)
SELECT 'Vivek', 'Grover', '1989-09-01', '2017-10-01'
UNION ALL
SELECT 'Ujjwal', 'Grover', '1994-11-27', '2017-09-10'
UNION ALL
SELECT 'Sahil', 'Arora', '1988-09-28', '2016-05-01'

Create a table for batches taken by trainers:

CREATE TABLE CodePickBatches(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
StartDate DATE,
TrainerID INT FOREIGN KEY REFERENCES CodePicksTrainer(ID)
)

Insert Batches Detail:

INSERT INTO CodePickBatches (Name, StartDate, TrainerID)
SELECT 'SQL Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Java Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Corel Draw', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Ujjwal' AND LastName = 'Grover')
UNION ALL
SELECT 'ASP.Net', '2016-06-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'SQL Programming', '2016-07-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'R Programming', '2017-04-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')

Requirement – One of your client demands a report with detail in following format containing Trainer Name, Joining date, Date of Birth, Total number of batches being run by trainer.

Code Build: Our result relies firstly on CodePickBatches table from where we can do aggregation to find number of batches being run my each Trainer. Once, we find the number of batches being run my each trainer, then we can make a join with CodePickTrainer table to find the detail of each trainer as shown in Requirement section. So, it requires the use of a derived table or on the fly table having aggregated data.

 SELECT
T.LastName + ', '+ T.FirstName AS TrainerName,
T.DateOfBirth,
T.JoiningDate,
TS.TotalRunningBatches
FROM (
SELECT
COUNT(1) AS TotalRunningBatches, B.TrainerID
FROM CodePickBatches B
GROUP BY B.TrainerID
) AS TS
INNER JOIN CodePicksTrainer T ON T.ID = TS.TrainerID
ORDER BY TotalRunningBatches DESC

In above code, a derived table named TS is created which makes its join with CodePicksTrainer table to find expected data. Total running batches are calculated in derived table (TS) itself. (It is surrounded by round brackets and followed by AS clause).