AdventureWorks Database: Retrieve all rows, columns using table aliasing
SQL Query - AdventureWorks: Exercise-2 with Solution
2. From the following table write a query in SQL to retrieve all rows and columns from the employee table using table aliasing in the Adventureworks database. Sort the output in ascending order on lastname.
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 all columns from the person table with an alias 'e'
SELECT e.*
-- Ordering the result set by the LastName column in ascending order
FROM person.person AS e
ORDER BY LastName;
Explanation:
- The SELECT e.* statement retrieves all columns from the person table using the alias 'e'.
- FROM person.person AS e specifies the table from which the data will be retrieved, with "person" being the schema name and "person" being the table name. The alias 'e' is assigned to the table for easier reference.
- The ORDER BY LastName clause sorts the result set based on the LastName column in ascending order.
Sample Output:
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
285|SP |false |Mr. |Syed |E |Abbas | | 0| |[XML] |ff284881-01c2-4c77-95a7-4db96f59bb70|2013-03-07 00:00:00.000|
293|SC |false |Ms. |Catherine |R. |Abel | | 1|[XML] |[XML] |d54e0552-c226-4c22-af3b-762ca854cdd3|2015-04-15 16:33:33.077|
2170|GC |false | |Kim | |Abercrombie | | 2| |[XML] |24f01b54-7a67-4b48-9ecc-72545d36c0ac|2009-11-29 00:00:00.000|
295|SC |false |Ms. |Kim | |Abercrombie | | 0|[XML] |[XML] |f7cbdb48-0b44-470e-9f37-7060446fbfb9|2015-04-15 16:33:33.077|
38|EM |false | |Kim |B |Abercrombie | | 2| |[XML] |9a2163b3-2f4d-4f9a-91bd-07d326140f9c|2010-01-09 00:00:00.000|
2357|GC |false | |Sam | |Abolrous | | 1| |[XML] |78b8f41e-ed14-4e96-9531-ce9630e79e10|2009-02-21 00:00:00.000|
211|EM |false | |Hazem |E |Abolrous | | 0| |[XML] |c2637051-25a5-4461-b06a-523119259430|2009-02-21 00:00:00.000|
297|SC |false |Sr. |Humberto | |Acevedo | | 2|[XML] |[XML] |5a41d336-84cf-44d7-b12b-83b64b511f7e|2015-04-15 16:33:33.090|
291|SC |false |Mr. |Gustavo | |Achong | | 2|[XML] |[XML] |d4c132d3-fcb5-4231-9dd5-888a54bec693|2015-04-15 16:33:33.060|
299|SC |false |Sra. |Pilar | |Ackerman | | 0|[XML] |[XML] |df1fb8ab-2323-4330-9ab8-54e13ce6d8f9|2015-04-15 16:33:33.090|
121|EM |false | |Pilar |G |Ackerman | | 0| |[XML] |81f50324-d0b5-4ea5-8b20-f99d46572c76|2008-12-26 00:00:00.000|
4970|IN |false | |Devin | |Adams | | 2| |[XML] |ee57a0e0-d49e-4ca6-9c94-64e8eb0c05d6|2014-05-06 00:00:00.000|
4429|IN |false | |Xavier |C |Adams | | 1| |[XML] |1578432b-b398-4359-b432-bf868c00a3c6|2013-10-19 00:00:00.000|
4891|IN |false | |Charles |R |Adams | | 2| |[XML] |faa29129-5894-4f86-979d-015ee4733364|2013-07-07 00:00:00.000|
4350|IN |false | |Seth |L |Adams | | 0| |[XML] |2b17fa8a-9643-4dcf-a61b-38fb9c3b0d3e|2014-03-06 00:00:00.000|
3889|IN |false | |Fernando |S |Adams | | 0| |[XML] |33fa7ddb-6372-48d7-92e4-75ef504a3746|2013-10-31 00:00:00.000|
67|EM |false | |Jay |G |Adams | | 0| |[XML] |2fe289a7-ce57-49e5-be61-3e6580d22ea6|2009-02-26 00:00:00.000|
1770|IN |false |Mr. |Ben | |Adams | | 0| |[XML] |ad322f5c-d052-4dde-84bc-d4b3b2682348|2011-10-24 00:00:00.000|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Retrieve all rows and columns from a table.
Next: Returns all rows and a subset of the columns.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics