AdventureWorks Database: Address for city outside US starts with Pa
SQL Query - AdventureWorks: Exercise-49 with Solution
49. Write a SQL query to retrieve the mailing address for any company that is outside the United States (US) and in a city whose name starts with Pa. Return Addressline1, Addressline2, city, postalcode, countryregioncode columns.
Sample table: Person.Address|addressid|addressline1 |addressline2|city |stateprovinceid|postalcode|spatiallocation |rowguid |modifieddate | |---------|--------------------------|------------|-------------|---------------|----------|--------------------------------------------|------------------------------------|-----------------------| |1 |1970 Napa Ct. | |Bothell |79 |98011 |E6100000010CAE8BFC28BCE4474067A89189898A5EC0|9aadcb0d-36cf-483f-84d8-585c2d4ec6e9|2007-12-04 00:00:00.000| |2 |9833 Mt. Dias Blv. | |Bothell |79 |98011 |E6100000010CD6FA851AE6D74740BC262A0A03905EC0|32a54b9e-e034-4bfb-b573-a71cde60d8c0|2008-11-30 00:00:00.000| |3 |7484 Roundtree Drive | |Bothell |79 |98011 |E6100000010C18E304C4ADE14740DA930C7893915EC0|4c506923-6d1b-452c-a07c-baa6f5b142a4|2013-03-07 00:00:00.000| |4 |9539 Glenside Dr | |Bothell |79 |98011 |E6100000010C813A0D5F9FDE474011A5C28A7C955EC0|e5946c78-4bcc-477f-9fa1-cc09de16a880|2009-02-03 00:00:00.000| |5 |1226 Shoe St. | |Bothell |79 |98011 |E6100000010C61C64D8ABBD94740C460EA3FD8855EC0|fbaff937-4a97-4af0-81fd-b849900e9bb0|2008-12-19 00:00:00.000| |6 |1399 Firestone Drive | |Bothell |79 |98011 |E6100000010CE0B4E50458DA47402F12A5F80C975EC0|febf8191-9804-44c8-877a-33fde94f0075|2009-02-13 00:00:00.000| |7 |5672 Hale Dr. | |Bothell |79 |98011 |E6100000010C18E304C4ADE1474011A5C28A7C955EC0|0175a174-6c34-4d41-b3c1-4419cd6a0446|2009-12-11 00:00:00.000| |8 |6387 Scenic Avenue | |Bothell |79 |98011 |E6100000010C0029A5D93BDF4740E248962FD5975EC0|3715e813-4dca-49e0-8f1c-31857d21f269|2008-12-17 00:00:00.000| |9 |8713 Yosemite Ct. | |Bothell |79 |98011 |E6100000010C6A80AD742DDC4740851574F7198C5EC0|268af621-76d7-4c78-9441-144fd139821a|2012-05-30 00:00:00.000| |10 |250 Race Court | |Bothell |79 |98011 |E6100000010C219D64AE1FE4474040862564B7825EC0|0b6b739d-8eb6-4378-8d55-fe196af34c04|2008-12-02 00:00:00.000| -- more --Sample table: Person.StateProvince
stateprovinceid|stateprovincecode|countryregioncode|isonlystateprovinceflag|name |territoryid|rowguid |modifieddate |
---------------+-----------------+-----------------+-----------------------+------------------------+-----------+------------------------------------+-----------------------+
1|AB |CA |false |Alberta | 6|298c2880-ab1c-4982-a5ad-a36eb4ba0d34|2014-02-08 10:17:21.587|
2|AK |US |false |Alaska | 1|5b7b8462-a888-4e0b-a3e1-7278f8af107e|2014-02-08 10:17:21.587|
3|AL |US |false |Alabama | 5|41b328be-21ae-45d0-841d-6f8dd71ce626|2014-02-08 10:17:21.587|
4|AR |US |false |Arkansas | 3|54656a80-06f2-4c70-ba10-247179fc246e|2014-02-08 10:17:21.587|
5|AS |AS |true |American Samoa | 1|255d15e1-9f6e-4cf8-9e5f-6b3858ad9b6a|2014-02-08 10:17:21.587|
6|AZ |US |false |Arizona | 4|fb8be18e-f441-44f0-a4a9-1d0f204cb701|2014-02-08 10:17:21.587|
7|BC |CA |false |British Columbia | 6|d27fcc6e-bb99-438b-ba86-285ceeb2fa53|2014-02-08 10:17:21.587|
8|BY |DE |false |Bayern | 8|d54e5000-a0da-46d1-86b0-b8fe16c9f781|2014-02-08 10:17:21.587|
9|CA |US |false |California | 4|3b2ff23c-1c75-40ae-9093-f4eb42263f4e|2014-02-08 10:17:21.587|
10|CO |US |false |Colorado | 3|292df595-7d3c-41fb-a040-7c184d379fce|2014-02-08 10:17:21.587|
11|CT |US |false |Connecticut | 2|1e7bb47a-e16b-4968-86fa-45af0211fa84|2014-02-08 10:17:21.587|
12|DC |US |false |District of Columbia | 2|a1f3c57e-85b3-41e3-88e8-07244cf087dd|2014-02-08 10:17:21.587|
-- more --
Sample Output:
addressline1 |addressline2 |city |postalcode|countryregioncode| --------------------------+--------------+-----------------+----------+-----------------+ 21105, rue de Varenne | |Paris |75013 |FR | 22, rue du Départ | |Pantin |93500 |FR | 36, avenue de la Gare | |Paris |75019 |FR | 39, route de Marseille | |Paris |75016 |FR | 98, rue Montcalm | |Paris |75019 |FR | 39, avenue des Laurentides| |Paris La Defense |92081 |FR | 3101, avenue de Malakoff | |Paris |75003 |FR | 9005, rue des Bouchers | |Paris |75005 |FR | ...
Sample Solution:
-- Selecting specific columns from the Address and StateProvince tables
SELECT AddressLine1, AddressLine2, City, PostalCode, CountryRegionCode
-- From the Person schema's Address table, aliasing it as 'a'
FROM Person.Address AS a
-- Joining with the StateProvince table based on StateProvinceID
JOIN Person.StateProvince AS s ON a.StateProvinceID = s.StateProvinceID
-- Filtering the results to include only rows where CountryRegionCode is not 'US'
-- AND City starts with 'Pa'
WHERE CountryRegionCode NOT IN ('US')
AND City LIKE 'Pa%' ;
Explanation:
- The SQL query retrieves data from the Address table in the Person schema and the StateProvince table in the same schema.
- It selects five columns: AddressLine1, AddressLine2, City, PostalCode, and CountryRegionCode.
- The JOIN clause is used to combine rows from both tables based on matching StateProvinceID.
- The WHERE clause filters the results to include only rows where:
- CountryRegionCode is not 'US', meaning the country is not the United States.
- City starts with 'Pa', using the LIKE operator with the pattern 'Pa%' to match any city that starts with 'Pa'.
- The result set will contain addresses with corresponding state/province information for countries other than the United States and cities starting with 'Pa'.
- Comments are provided for each code line to explain the purpose and functionality of the code.
Sample Output:
addressline1 |addressline2 |city |postalcode |countryregioncode| --------------------------+--------------+-------------------------------------+--------------------+-----------------+ 21105, rue de Varenne | |Paris |75013 |FR | 22, rue du Départ | |Pantin |93500 |FR | 36, avenue de la Gare | |Paris |75019 |FR | 39, route de Marseille | |Paris |75016 |FR | 98, rue Montcalm | |Paris |75019 |FR | 39, avenue des Laurentides| |Paris La Defense |92081|FR | 3101, avenue de Malakoff | |Paris |75003 |FR | 9005, rue des Bouchers | |Paris |75005 |FR | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Retrieve records contain green_ in a field.
Next: Fetch first twenty rows from the table.
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://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-49.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics