AdventureWorks Database: Persons whose last name begins with 'L'
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics