AdventureWorks Database: Fetch rows using a derived table
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
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample Solution:
-- Selecting specific columns from a derived table
SELECT businessentityid, firstname,lastname
-- Creating a derived table by selecting all columns from the Person table
-- Filtering the result to include only rows where persontype is 'IN'
FROM
(SELECT * FROM person.person
WHERE persontype = 'IN') AS personDerivedTable
-- Filtering the results to include only rows where lastname is 'Adams'
WHERE lastname = 'Adams'
-- Ordering the results by firstname
ORDER BY firstname;
Explanation:
- The SQL query retrieves data from a derived table created by selecting rows from the Person table.
- It selects three columns: businessentityid, firstname, and lastname.
- The derived table is created by selecting all columns from the Person table and filtering rows where persontype is 'IN'.
- The outer query then filters the results further to include only rows where lastname is 'Adams'.
- The result set will contain the businessentityid, firstname, and lastname of individuals whose last name is 'Adams' and their persontype is 'IN'.
- Results are ordered 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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.