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
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join