AdventureWorks Database: Persons whose last name begins with 'L'
SQL Query - AdventureWorks: Exercise-11 with Solution
11. From the following tables write a query in SQL to find the persons whose last name starts with letter 'L'. Return BusinessEntityID, FirstName, LastName, and PhoneNumber. Sort the result on lastname and firstname.
Sample table: Person.PersonPhone
businessentityid|phonenumber |phonenumbertypeid|modifieddate |
----------------+-------------------+-----------------+-----------------------+
1|697-555-0142 | 1|2009-01-07 00:00:00.000|
2|819-555-0175 | 3|2008-01-24 00:00:00.000|
3|212-555-0187 | 1|2007-11-04 00:00:00.000|
4|612-555-0100 | 1|2007-11-28 00:00:00.000|
5|849-555-0139 | 1|2007-12-30 00:00:00.000|
6|122-555-0189 | 3|2013-12-16 00:00:00.000|
7|181-555-0156 | 3|2009-02-01 00:00:00.000|
8|815-555-0138 | 1|2008-12-22 00:00:00.000|
9|185-555-0186 | 1|2009-01-09 00:00:00.000|
10|330-555-2568 | 3|2009-04-26 00:00:00.000|
11|719-555-0181 | 1|2010-11-28 00:00:00.000|
12|168-555-0183 | 3|2007-12-04 00:00:00.000|
-- more --
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 specific columns from the 'Person' table and the 'PersonPhone' table
SELECT p.BusinessEntityID, FirstName, LastName, PhoneNumber AS Person_Phone
-- Retrieving data from the 'Person' table with an alias 'p'
FROM Person.Person AS p
-- Joining the 'Person' table with the 'PersonPhone' table based on the 'BusinessEntityID' column
JOIN Person.PersonPhone AS ph
ON p.BusinessEntityID = ph.BusinessEntityID
-- Filtering the results to include only rows where the last name starts with 'L'
WHERE LastName LIKE 'L%'
-- Ordering the results first by last name and then by first name
ORDER BY LastName, FirstName;
Explanation:
- The SELECT statement retrieves specific columns from the 'Person' table and renames the 'PhoneNumber' column to 'Person_Phone'.
- FROM Person.Person AS p specifies the table from which the data will be retrieved, with 'p' as an alias for the 'Person' table.
- The JOIN clause is used to combine data from the 'Person' table with data from the 'PersonPhone' table based on matching values in the 'BusinessEntityID' column.
- ON p.BusinessEntityID = ph.BusinessEntityID defines the condition for joining the two tables.
- The WHERE clause filters the rows to include only those where the last name starts with 'L'.
- The ORDER BY clause sorts the results first by last name in ascending order and then by first name in ascending order.
Sample Output:
businessentityid|firstname |lastname |person_phone |
----------------+-----------+---------------+-------------------+
5527|Aaron |Lal |605-555-0159 |
5268|Adam |Lal |513-555-0110 |
12539|Alejandro |Lal |1 (11) 500 555-0117|
19786|Alicia |Lal |1 (11) 500 555-0161|
12004|Alisha |Lal |1 (11) 500 555-0119|
16649|Alison |Lal |1 (11) 500 555-0177|
5005|Alvin |Lal |1 (11) 500 555-0168|
5070|Andres |Lal |1 (11) 500 555-0127|
10416|Arturo |Lal |638-555-0164 |
8951|Ashlee |Lal |1 (11) 500 555-0148|
6283|Austin |Lal |541-555-0141 |
11600|Barbara |Lal |1 (11) 500 555-0176|
6744|Benjamin |Lal |1 (11) 500 555-0148|
17275|Bethany |Lal |1 (11) 500 555-0196|
3694|Bonnie |Lal |1 (11) 500 555-0191|
9390|Brad |Lal |463-555-0111 |
20292|Bradley |Lal |1 (11) 500 555-0124|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Expression used in group by.
Next: Calculate running totals and subtotals.
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-11.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics