AdventureWorks Database: Retrieve individuals using multiple conditions
44. Create a SQL query to retrieve individuals from the following table with a businessentityid inside 1500, a lastname starting with 'Al', and a firstname starting with 'M'.
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
SELECT businessentityid, firstname, LastName
-- From the Person schema's Person table
FROM person.person
-- Filtering the results to include only rows where businessentityid is less than or equal to 1500
-- AND the last name contains 'Al' (case-insensitive)
-- AND the first name contains 'M' (case-insensitive)
WHERE businessentityid <= 1500 AND LastName LIKE '%Al%' AND FirstName LIKE '%M%';
Explanation:
- The SQL query retrieves data from the Person table within the Person schema.
- It selects three columns: businessentityid, firstname, and LastName.
- The WHERE clause filters the results based on three conditions:
- businessentityid is less than or equal to 1500.
- LastName contains 'Al' (using the % wildcard to match any characters before and after 'Al').
- FirstName contains 'M' (using the % wildcard to match any characters before and after 'M').
- The LIKE operator is used for pattern matching with wildcards:
- % represents zero or more characters.
- This query will return rows where the businessentityid is less than or equal to 1500, the last name contains 'Al', and the first name contains 'M'.
Sample Output:
businessentityid|firstname|lastname |
----------------+---------+---------+
327|Milton |Albury |
335|Michelle |Alexander|
341|Marvin |Allen |
343|Michael |Allen |
1149|Mary |Alexander|
Go to:
PREV : Retrieve individuals using multiple conditions.
NEXT : Using a derived table with multiple values.
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.