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.



Follow us on Facebook and Twitter for latest update.