w3resource

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

Click to view Full table

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-2.php