AdventureWorks Database: Find the number of characters in the FirstName column
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.
Sample table: Sales.vindividualcustomer
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 -
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.