AdventureWorks Database: Retrieve all rows and columns from a table
SQL Query - AdventureWorks: Exercise-1 with Solution
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/
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: AdventureWorks Exercises Home.
Next: Retrieve all rows, columns using table aliasing.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-1.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics