AdventureWorks Database: Get mailing addresses for companies in cities begin with PA outside US
SQL Query - AdventureWorks: Exercise-162 with Solution
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.
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-162.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics