AdventureWorks Database: Find the number of characters in the FirstName column

Last update on December 20 2024 11:38:28 (UTC/GMT +8 hours)

63. From the following table write a query in SQL to select the number of characters and the data in FirstName for people located in Australia.

businessentityid|title|firstname|middlename|lastname    |suffix|phonenumber        |phonenumbertype|emailaddress                   |emailpromotion|addresstype|addressline1                    |addressline2        |city                 |stateprovincename  |postalcode|countryregionname|demographics|
----------------+-----+---------+----------+------------+------+-------------------+---------------+-------------------------------+--------------+-----------+--------------------------------+--------------------+---------------------+-------------------+----------+-----------------+------------+
            1699|Mr.  |David    |R.        |Robinett    |      |238-555-0100       |Home           |[email protected]    |             1|Home       |Pappelallee 6667                |                    |Solingen             |Nordrhein-Westfalen|42651     |Germany          |[XML]       |
            1700|Ms.  |Rebecca  |A.        |Robinson    |      |648-555-0100       |Cell           |[email protected]   |             0|Home       |1861 Chinquapin Ct              |                    |Seaford              |Victoria           |3198      |Australia        |[XML]       |
            1701|Ms.  |Dorothy  |B.        |Robinson    |      |423-555-0100       |Cell           |[email protected]   |             2|Home       |4693 Mills Dr.                  |                    |Geelong              |Victoria           |3220      |Australia        |[XML]       |
            1702|Ms.  |Carol Ann|F.        |Rockne      |      |439-555-0100       |Cell           |[email protected]  |             0|Home       |1312 Skycrest Drive             |                    |Lancaster            |England            |LA1 1LN   |United Kingdom   |[XML]       |
            1703|Mr.  |Scott    |M.        |Rodgers     |      |989-555-0100       |Cell           |[email protected]    |             0|Home       |9860 Brookview Drive            |                    |East Brisbane        |Queensland         |4169      |Australia        |[XML]       |
            1704|Mr.  |Jim      |          |Rodman      |      |899-555-0100       |Home           |[email protected]       |             0|Home       |2377 Joyce Dr                   |                    |Esher-Molesey        |England            |EM15      |United Kingdom   |[XML]       |
            1705|Mr.  |Eric     |          |Rothenberg  |      |326-555-0100       |Home           |[email protected]      |             0|Home       |9277 Country View Lane          |                    |Concord              |California         |94519     |United States    |[XML]       |
            1706|Mr.  |Michael  |L.        |Rothkugel   |      |358-555-0100       |Cell           |[email protected]  |             0|Home       |3552 Mildred Ln.                |                    |St. Leonards         |New South Wales    |2065      |Australia        |[XML]       |
            1707|Mr.  |Pablo    |          |Rovira Diez |      |786-555-0100       |Cell           |[email protected]     |             0|Home       |15, rue Descartes               |                    |East Brisbane        |Queensland         |4169      |Australia        |[XML]       |
            1708|Ms.  |Linda    |R.        |Rousey      |      |369-555-0100       |Home           |[email protected]    |             1|Home       |5966 Sepulveda Ct.              |                    |Seaford              |Victoria           |3198      |Australia        |[XML]       |
			- more -

Click to view Full table

Sample Solution:

-- Selecting the length of the 'FirstName' column, along with 'FirstName' and 'LastName'
SELECT LENgth(FirstName) AS Length, FirstName, LastName   

-- From the Sales schema's vIndividualCustomer view
FROM Sales.vIndividualCustomer  

-- Filtering the results to include only rows where the 'CountryRegionName' column is 'Australia'
WHERE CountryRegionName = 'Australia';

Explanation:

• The SQL query retrieves data from the vIndividualCustomer view within the Sales schema.
• It calculates the length of the FirstName column using the LENGTH() function and aliases the result as 'Length'.
• The LENGTH() function returns the number of characters in a string.
• The FirstName and LastName columns are also selected.
• The WHERE clause filters the results to include only rows where the value of the CountryRegionName column is 'Australia'.

Sample Output:

length|firstname|lastname |
------+---------+---------+
     3|Jon      |Yang     |
     6|Eugene   |Huang    |
     5|Ruben    |Torres   |
     7|Christy  |Zhu      |
     9|Elizabeth|Johnson  |
     5|Julio    |Ruiz     |
     5|Janet    |Alvarez  |
     5|Marco    |Mehta    |
     3|Rob      |Verhoff  |
     7|Shannon  |Carlson  |
     9|Jacquelyn|Suarez   |
	 ...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return the five leftmost characters of each product name.
Next: Provide the connections in Australia and the length of FirstName.