AdventureWorks Database: Returns all rows and a subset of the columns

SQL Query - AdventureWorks: Exercise-3 with Solution

3. From the following table write a query in SQL to return all rows and a subset of the columns (FirstName, LastName, businessentityid) from the person table in the AdventureWorks database. The third column heading is renamed to Employee_id. Arranged the output in ascending order by lastname.

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 specific columns (firstname, lastname, and businessentityid with an alias 'Employee_id') from the person table
SELECT firstname, lastname, businessentityid as Employee_id  
-- Specifying 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.
FROM person.person AS e  
-- Ordering the result set by the LastName column in ascending order
ORDER BY lastname;

Explanation:

• The SELECT firstname, lastname, businessentityid as Employee_id statement specifies the columns to be retrieved from the "person" table. The alias 'Employee_id' is assigned to the "businessentityid" column for easier reference in the result set.
• 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:

firstname               |lastname              |employee_id|
------------------------+----------------------+-----------+
Syed                    |Abbas                 |        285|
Catherine               |Abel                  |        293|
Kim                     |Abercrombie           |         38|
Kim                     |Abercrombie           |        295|
Kim                     |Abercrombie           |       2170|
Sam                     |Abolrous              |       2357|
Hazem                   |Abolrous              |        211|
Humberto                |Acevedo               |        297|
Gustavo                 |Achong                |        291|
Pilar                   |Ackerman              |        299|
Pilar                   |Ackerman              |        121|
Luke                    |Adams                 |      16884|
Adam                    |Adams                 |      16901|
Natalie                 |Adams                 |      10262|
Isabella                |Adams                 |      10261|
Morgan                  |Adams                 |      10259|
Kaitlyn                 |Adams                 |      10258|
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Retrieve all rows, columns using table aliasing.
Next: Retrieve only the rows for product.