AdventureWorks Database: Get mailing addresses for companies in cities begin with PA outside US
162. From the following tables write a query in SQL to obtain mailing addresses for companies in cities that begin with PA, outside the United States (US). Return AddressLine1, AddressLine2, City, PostalCode, CountryRegionCode.
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|
13|DE |US |false |Delaware | 2|7a11ab1d-77c0-4021-9140-8e81f105618e|2014-02-08 10:17:21.587|
14|ENG |GB |true |England | 10|3e3cb3f8-44b9-44d9-a1c3-cbfb11e0a7da|2014-02-08 10:17:21.587|
15|FL |US |false |Florida | 5|ee8ba90d-b2c3-418e-93df-20e33f095959|2014-02-08 10:17:21.587|
16|FM |FM |true |Micronesia | 9|3202da35-aed4-40e2-9ec4-27c17f420170|2014-02-08 10:17:21.587|
17|GA |US |false |Georgia | 5|a6ca20d1-31ac-4771-8994-93dbbdcce360|2014-02-08 10:17:21.587|
18|GU |US |false |Guam | 4|92b5a04e-26ec-4edb-8d14-e72e29b14411|2014-02-08 10:17:21.587|
19|HE |DE |false |Hessen | 8|834fc3df-b60d-4f94-95bd-aef8a9fb74e8|2014-02-08 10:17:21.587|
20|HH |DE |false |Hamburg | 8|1cc5a134-60d7-40c2-9269-cda494214abf|2014-02-08 10:17:21.587|
21|HI |US |false |Hawaii | 1|09cdccdc-b4b8-44ea-b04f-6ef521e3e720|2014-02-08 10:17:21.587|
22|IA |US |false |Iowa | 3|956a6c02-7d2f-4c9d-b275-8d2c0ef8fd83|2014-02-08 10:17:21.587|
23|ID |US |false |Idaho | 1|628e983a-33c7-4cb4-867f-274ef12b3597|2014-02-08 10:17:21.587|
24|IL |US |false |Illinois | 3|1f9120cf-683a-4132-a12c-98997fadeb26|2014-02-08 10:17:21.587|
25|IN |US |false |Indiana | 2|91f21ef0-c528-4310-bb29-6ba45ae75a17|2014-02-08 10:17:21.587|
...........
Sample Solution:
-- Selecting AddressLine1, AddressLine2, City, PostalCode, and CountryRegionCode columns
SELECT
AddressLine1,
AddressLine2,
City,
PostalCode,
CountryRegionCode
-- From the Address table aliased as 'a' and joining it with the StateProvince table aliased as 's' based on the StateProvinceID column
FROM
Person.Address AS a
JOIN
Person.StateProvince AS s
ON
a.StateProvinceID = s.StateProvinceID
-- Filtering records where the CountryRegionCode is not 'US' and the City starts with 'Pa'
WHERE
CountryRegionCode NOT IN ('US')
AND City LIKE 'Pa%' ;
Explanation:
- This SQL code retrieves the AddressLine1, AddressLine2, City, PostalCode, and CountryRegionCode from the Address table.
- The SELECT statement specifies the columns to be included in the result set.
- The FROM clause indicates the tables involved in the query, with the Address table aliased as 'a' and the StateProvince table aliased as 's'.
- The JOIN clause specifies how the Address and StateProvince tables are related, based on the StateProvinceID column.
- The WHERE clause filters records where the CountryRegionCode is not 'US' and the City starts with 'Pa'.
- The result set will contain the selected columns from the Address table for records meeting the specified conditions.
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 | 7551, avenue Foch | |Paris |75010 |FR | 22, rue Lafayette | |Pantin |93500 |FR | 699bis, rue des Peupliers | |Paris |75008 |FR | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the rows with green_ in the LargePhotoFileName column.
Next: A JOIN clause can join multiple values.
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