AdventureWorks Database: Address for city outside US starts with Pa
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics