Execute the following script in Query Editor to demonstrate the use of derived table (SELECT FROM SELECT) in a query. Derived tables are frequently used to JOIN back GROUP BY results to base tables:

USE AdventureWorks2008;

SELECT DepartmentID, Department=[Name]

      FROM (

             SELECT *

               FROM HumanResources.Department

               WHERE GroupName = 'Sales and Marketing'

             ) AS DepartmentDerivedTable

      ORDER BY Department