AdventureWorks Database: Provide the connections in Australia and the length of FirstName
SQL Query - AdventureWorks: Exercise-64 with Solution
64. From the following tables write a query in SQL to return the number of characters in the column FirstName and the first and last name of contacts located in Australia.
Sample table: Sales.vstorewithcontacts
businessentityid|name |contacttype |title|firstname |middlename|lastname |suffix|phonenumber |phonenumbertype|emailaddress |emailpromotion|
----------------+-----------------------------------------+------------------+-----+------------------------+----------+----------------------+------+-------------------+---------------+-------------------------------------------+--------------+
292|Next-Door Bike Store |Owner |Mr. |Gustavo | |Achong | |398-555-0132 |Cell |[email protected] | 2|
294|Professional Sales and Service |Owner |Ms. |Catherine |R. |Abel | |747-555-0171 |Cell |[email protected] | 1|
296|Riders Company |Owner |Ms. |Kim | |Abercrombie | |334-555-0137 |Work |[email protected] | 0|
298|The Bike Mechanics |Owner |Sr. |Humberto | |Acevedo | |599-555-0127 |Cell |[email protected] | 2|
300|Nationwide Supply |Owner |Sra. |Pilar | |Ackerman | |1 (11) 500 555-0132|Cell |[email protected] | 0|
302|Area Bike Accessories |Owner |Ms. |Frances |B. |Adams | |991-555-0183 |Work |[email protected] | 1|
304|Bicycle Accessories and Kits |Owner |Ms. |Margaret |J. |Smith | |959-555-0151 |Cell |[email protected] | 0|
306|Clamps & Brackets Co. |Owner |Ms. |Carla |J. |Adams | |107-555-0138 |Cell |[email protected] | 0|
308|Valley Bicycle Specialists |Purchasing Agent |Mr. |Jay | |Adams | |158-555-0142 |Cell |[email protected] | 1|
310|New Bikes Company |Purchasing Agent |Mr. |Ronald |L. |Adina | |453-555-0165 |Cell |[email protected] | 0|
- more -
Sample table: Sales.vstorewithaddresses
businessentityid|name |addresstype|addressline1 |addressline2 |city |stateprovincename |postalcode|countryregionname|
----------------+-----------------------------------------+-----------+---------------------------------------+----------------------------+--------------------+-------------------+----------+-----------------+
292|Next-Door Bike Store |Main Office|Mall Of Memphis | |Memphis |Tennessee |38103 |United States |
294|Professional Sales and Service |Main Office|57251 Serene Blvd | |Van Nuys |California |91411 |United States |
296|Riders Company |Main Office|Tanger Factory | |Branch |Minnesota |55056 |United States |
298|The Bike Mechanics |Main Office|Johnny Appleseed Shop.center | |Mansfield |Ohio |44903 |United States |
300|Nationwide Supply |Main Office|4250 Concord Road | |Rhodes |New South Wales |2138 |Australia |
302|Area Bike Accessories |Main Office|6900 Sisk Road | |Modesto |California |95354 |United States |
304|Bicycle Accessories and Kits |Main Office|Lewiston Mall | |Lewiston |Idaho |83501 |United States |
306|Clamps & Brackets Co. |Main Office|Leesburg Premium Outlet Centre | |Leesburg |Virginia |20176 |United States |
308|Valley Bicycle Specialists |Main Office|Blue Ridge Mall | |Kansas City |Missouri |64106 |United States |
310|New Bikes Company |Main Office|Hilton Head Factory Outlets No. 25 | |Bluffton |South Carolina |29910 |United States |
- more -
Sample Solution:
-- Selecting distinct lengths of first names, along with first and last names
SELECT DISTINCT LENgth(FirstName) AS FNameLength, FirstName, LastName
-- From the Sales.vstorewithcontacts view aliased as 'e'
FROM Sales.vstorewithcontacts AS e
-- Joining with the Sales.vstorewithaddresses view aliased as 'g'
INNER JOIN Sales.vstorewithaddresses AS g
-- Join condition based on businessentityid columns from both views
ON e.businessentityid = g.businessentityid
-- Filtering the results to include only records from Australia
WHERE CountryRegionName = 'Australia';
Explanation:
- SELECT DISTINCT LENgth(FirstName) AS FNameLength, FirstName, LastName: This line selects distinct lengths of first names along with the corresponding first and last names. The DISTINCT keyword ensures that only unique combinations of length and first name are returned. The LENgth() function is used to calculate the length of the first name. The AS keyword is used to alias the calculated length as FNameLength.
- FROM Sales.vstorewithcontacts AS e: This line specifies the source of the data as the 'Sales.vstorewithcontacts' view, aliased as 'e'. The AS keyword is used to give the view an alias, which can be referenced in subsequent parts of the query.
- INNER JOIN Sales.vstorewithaddresses AS g ON e.businessentityid = g.businessentityid: This line performs an inner join with the 'Sales.vstorewithaddresses' view, aliased as 'g'. It joins the 'e' and 'g' views based on the 'businessentityid' columns from both views. This means that only rows with matching 'businessentityid' values will be included in the result set.
- WHERE CountryRegionName = 'Australia': This line filters the results to include only records where the 'CountryRegionName' column is equal to 'Australia'. This condition is applied after the join, so it filters the combined result set of the joined views.
Sample Output:
fnamelength|firstname|lastname |
-----------+---------+------------+
3|Ann |Wilson |
3|Don |Funk |
3|Jay |Fluegel |
3|Mae |Anderson |
3|Ole |Weldon |
4|Eric |Coleman |
4|Jeff |Hay |
4|Jeff |Henshaw |
4|John |Fredericksen|
4|Jose |Lugo |
4|Judy |Storjohann |
4|Lynn |Gonzales |
4|Neal |Hasty |
4|Neil |Charney |
4|Scot |Bent |
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the number of characters in the FirstName column.
Next: Find products between $1000 and $1220 in lower, upper, and lowerupper.
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