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.

Sample table: Person.Person

Click to view Full table

Sample Solution:

SELECT businessentityid, firstname,lastname  
FROM  
   (SELECT * FROM person.person  
    WHERE persontype = 'IN') AS personDerivedTable 
WHERE lastname = 'Adams'  
ORDER 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.