AdventureWorks Database: Fetch rows using a derived table

SQL Query - AdventureWorks: Exercise-43 with Solution

43. Write a SQL query to return the businessentityid, firstname and lastname columns of all persons in the person table (derived table) with persontype is 'IN' and the last name is 'Adams'. Sort the result set in ascending order on firstname. A SELECT statement after the FROM clause is a derived table.

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from a derived table
SELECT businessentityid, firstname,lastname  

-- Creating a derived table by selecting all columns from the Person table
-- Filtering the result to include only rows where persontype is 'IN'
FROM  
   (SELECT * FROM person.person  
    WHERE persontype = 'IN') AS personDerivedTable 

-- Filtering the results to include only rows where lastname is 'Adams'
WHERE lastname = 'Adams'  

-- Ordering the results by firstname
ORDER BY firstname;

Explanation:

• The SQL query retrieves data from a derived table created by selecting rows from the Person table.
• It selects three columns: businessentityid, firstname, and lastname.
• The derived table is created by selecting all columns from the Person table and filtering rows where persontype is 'IN'.
• The outer query then filters the results further to include only rows where lastname is 'Adams'.
• The result set will contain the businessentityid, firstname, and lastname of individuals whose last name is 'Adams' and their persontype is 'IN'.
• Results are ordered by firstname.

Sample Output:

businessentityid|firstname|lastname|
----------------+---------+--------+
           16867|Aaron    |Adams   |
           16901|Adam     |Adams   |
           16724|Alex     |Adams   |
           10263|Alexandra|Adams   |
           10312|Allison  |Adams   |
           10274|Amanda   |Adams   |
           10292|Amber    |Adams   |
           10314|Andrea   |Adams   |
           16699|Angel    |Adams   |
           10299|Bailey   |Adams   |
            1770|Ben      |Adams   |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Retrieve name and city of the employees.
Next: Retrieve individuals using multiple conditions.