﻿ AdventureWorks: Fetch rows using a derived table

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

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.