AdventureWorks Database: Retrieve all rows and columns from a table
1. From the following table write a query in SQL to retrieve all rows and columns from the employee table in the Adventureworks database. Sort the result set in ascending order on jobtitle.
Sample table: HumanResources.Employee
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- more --
Sample Solution:
-- Selecting all columns from the employee table in the humanresources schema
SELECT *
-- Ordering the result set by the job title column in ascending order
FROM humanresources.employee
ORDER BY jobtitle;
Explanation:
- The SELECT * statement retrieves all columns from the employee table.
- FROM humanresources.employee specifies the table from which the data will be retrieved, with "humanresources" being the schema name and "employee" being the table name.
- The ORDER BY jobtitle clause sorts the result set based on the jobtitle column in ascending order.
Sample Output:
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnod
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+---------------
245|363910111 |adventure-works\barbara1 |Accountant |1976-01-04|M |F |2009-02-18|true | 58| 49|true |3ffba84e-8e97-4649-a5e1-859649d83aae|2014-06-30 00:00:00.000|/4/2/4/
248|480951955 |adventure-works\mike0 |Accountant |1979-07-01|S |M |2009-03-08|true | 59| 49|true |ac35337d-7c75-4dee-bb11-6564f257fe18|2014-06-30 00:00:00.000|/4/2/7/
241|30845 |adventure-works\david6 |Accounts Manager |1983-07-08|M |M |2009-01-30|true | 57| 48|true |2dc9d534-f5d1-4a14-8282-0a2a0eb6fd4d|2014-06-30 00:00:00.000|/4/2/
246|663843431 |adventure-works\dragan0 |Accounts Payable Specialist |1977-02-14|M |M |2009-02-11|false | 63| 51|true |51c54d34-064b-44f7-a6b1-7702bd491980|2014-06-30 00:00:00.000|/4/2/5/
247|519756660 |adventure-works\janet0 |Accounts Payable Specialist |1979-03-09|M |F |2009-03-01|false | 64| 52|true |0c641d77-8675-493f-9947-8c65136559cd|2014-06-30 00:00:00.000|/4/2/6/
243|60517918 |adventure-works\candy0 |Accounts Receivable Specialist |1976-02-23|S |F |2009-01-06|false | 61| 50|true |9e9f713b-707c-4f7e-9504-de188052a045|2014-06-30 00:00:00.000|/4/2/2/
244|931190412 |adventure-works\bryan1 |Accounts Receivable Specialist |1984-09-20|S |M |2009-01-24|false | 62| 51|true |cb3e71ec-a381-4716-87df-d3841ab9795a|2014-06-30 00:00:00.000|/4/2/3/
242|363923697 |adventure-works\deborah0 |Accounts Receivable Specialist |1976-03-06|M |F |2008-12-18|false | 60| 50|true |09f75454-028c-46ca-bc08-0147bd0220d7|2014-06-30 00:00:00.000|/4/2/1/
272|525932996 |adventure-works\janaina0 |Application Specialist |1985-01-30|M |F |2008-12-23|true | 71| 55|true |241535c7-7a31-4a6a-9e0d-a83c30c2edda|2014-06-30 00:00:00.000|/5/7/
268|314747499 |adventure-works\ramesh0 |Application Specialist |1988-03-13|S |M |2009-02-03|true | 73| 56|true |be190269-4003-4d7f-809e-7b3fdc235da8|2014-06-30 00:00:00.000|/5/3/
...
Go to:
PREV : AdventureWorks Exercises Home.
NEXT : Retrieve all rows, columns using table aliasing.
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.