w3resource

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   |
...
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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to get the top 10 values in PostgreSQL?

select *
from scores
order by score desc
limit 10

Database: PostgreSQL

Ref : https://bit.ly/3VxP1yR