AdventureWorks Database: Retrieve records contain green_ in a field

Last update on March 27 2024 13:00:25 (UTC/GMT +8 hours)

SQL Query - AdventureWorks: Exercise-48 with Solution

48. Write a SQL query on the following table to retrieve records with the characters green_ in the LargePhotoFileName field. The following table's columns must all be returned.

Sample table: Production.ProductPhoto

productphotoid|thumbnailphoto                                                                                                                                                                                                                                                 |thumbnailphotofilename             |largephoto                                                                                                                                                                                                                                                     |largephotofilename                 |modifieddate           |
--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------------------+
             1|47494638396150003100F70000000000800000008000808000000080800080008080808080C0C0C0FF000000FF00FFFF000000FFFF00FF00FFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|no_image_available_small.gif       |474946383961F0009500F70000000000800000008000808000000080800080008080808080C0C0C0FF000000FF00FFFF000000FFFF00FF00FFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|no_image_available_large.gif       |2008-04-30 00:00:00.000|
            69|47494638396150003100F70000E3E3FCA6ACB3F5F6FE303D47F8FAFDEDEEFE989DA2F6F8FD6C86B5999B9DD3D3D5E2E5EACCD0D79AA2A8BDBEC2868B93ACB2B8BCBCBE5A6674526A913A4046F9FAFADBDCE9AAABAD656C76BFC0C7E9E9FDDCE1EA27272BEFEFFCDCDEE2C5CCE3CACBCCDADADC94A8C5B1B3B5FEFEFF5B76A86|racer02_black_f_small.gif          |474946383961F0009500F70000D3D3FEE2E3FE86878ADBDCFED7D8E7929BA8545659C9CAD8B7C4CD97999B030405767A8436424F8AAACCF9FAFA96A3AF719BCD7677793A3C40535963EBF3F8656569FAFBFDEBEBECC7C7E4BABCC5F7F9FCDBDCDE738CAEA8ADB327608BCCCCFDE2E3E4778591D2D3D5262629B5D0E25A636ED|racer02_black_f_large.gif          |2008-04-30 00:00:00.000|
            70|47494638396150003100F70000E3E3FCEBECF3F5F6FE999B9DEDEDFEDCE3EB374249B2BCCFABADB2F9FAFDD3D3D4373C44BBBCBEE2E5EA868B93F9FAFAACB0B6C9CBCCAAABADDCDDEADADADC5068907E848A26272AE9E9FD676D7598A2A9CDCFD7B1B3B5DBDDE1C5CCE3C2C3C5FEFEFEBEC0C194ACC7A2A3A65B75A60506066|racer02_black_small.gif            |474946383961F0009500F7000086878AECEDFED7D8E7D3D3FEE3E3FE909AA8DCDDFE545559C7C8D997999BB8C4CD96A2AF757679749ECE767A8289ABCAF9FAFA3A3C4038424C656569535962FAFBFDEDF0FBEBEBEDC8C8E6708FAEBABCC4DBDCDEF7F9FC77858F262629E2E3E4A9ADB25A636FCCCCFDD2D3D52E588ECACCCEB|racer02_black_large.gif            |2012-10-19 09:56:38.273|
            72|47494638396150003100F70000F1F2FEE3E3FCA3ACB6EBEBF3F5F6FEEDEEFE66676BF9FAFEE2E5EAB7BAC65656593B47539A9B9D989DA2D3D3D4787D835C6672D9E2E9DBDDE2868B93EFF0FDBBBCBEF9FAFAAAABAD3B3C42C8CBE6DBDCE9B6C8D5E9E9FDACBBC8D9DADD676C74CED0D37A858CFEFEFEC4D8E7748699206289E|racer02_blue_f_small.gif           |474946383961F0009500F700008EAFC785868991A5B2B5C7D1F2F3F4ECEDFE97989AD7D8E7D3D3FEE2E3FEDCDDFEC7C9D9757B8354555976777A76858FEBF2FAF9FAFA6991B2656669FAFBFD555A61EAEBED39414A3A3C40C7C7E6ACD3E8F7F9FCB9BCC5DBDCDEA8ADB2CCCCFDD2D3D5E2E3E477A9D150728E989CA2DDE1E3C|racer02_blue_f_large.gif           |2012-10-19 09:56:38.273|
			-- more --

Click to view Full table

Sample Output:

productphotoid|thumbnailphoto                                                                                                                                                                                                                                                 |thumbnailphotofilename             |largephoto                                                                                                                                                                                                                                                     |largephotofilename                 |modifieddate           |
--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------------------+
            76|47494638396150003100F700007689B3B1D5A475777BECEBF234353A85A376FAFCFE45474BF1F2FEE2E2FC6B919D4D5A6CF8FAFEF4F6FE65676BDAE7ECEDEDFE787C805B994CBEBDC3BACFB65A6674AAABB0F6F8FC56565A478C349A9DA1E3E4EBDCDDE1CDD1D9244356D1D1D3586F8EDBDDEA78CA6ABBBCBE68AB58F9FAFA3|racer02_green_f_small.gif          |474946383961F0009500F70000CACBF8D6E4EFA3D19EECEDFEF3F4F4D3D3FEE2E3FEDCDDFED7D7E7979AA6B4C7CEA5A7AAC7C8DA85868983B67633C90754555A2C930797999A719CCD96A5B0F9FAFA777A8287ABCEFAFBFDEEF0F876767977858A3B3C41656669575A604DAE2FC7C6ECEAEBED47CA233A4149BBBCC3F7F9FCA|racer02_green_f_large.gif          |2012-10-19 09:56:38.273|
            77|47494638396150003100F70000B8D3C4E2E3F397A5A945596B6B86B767AB56F5F8FC89BC7134353A45464AFAFCFEEBEBF3E1E2E3F1F2FEE3E3FB75767A797C816CC751F5F6FE65676BF8FAFD499434BEBDC26D9399EDEDFEA6AAB2ECF9E856565AE3E5E884D3713E47509A9DA15A9A4AADB2B6DEE3EADCDDE3CFD1D8D2D2D3D|racer02_green_small.gif            |474946383961F0009500F70000444549309407CACAF8D6E4EE34C90849CE24D8D8E8ECEDFEF2F3F3D3D3FEE2E3FEDCDDFE9699A7A4A6AAB9C4CD858689C6C8DA54555A719DCD96A5B098999A767A8350A731F9FAFAFAFBFD76767A88ABCDEEF1F4D4EDCF69A9563B3C41646569575A60EAEBED75868D6ADB49C6C6EC3C4148B|racer02_green_large.gif            |2012-10-19 09:56:38.273|
            91|47494638396150003100F7000000000000000800080800081008081008100808101008101810101810181810182118182118212118212921212921292921293129293131294229313129313931313939314A3139392152082B472E393942394242424A42425231426B395A6B4239394A39424A42424A424A4A4A4A4A524A4A4|julianax_r_02_f_green_small.gif    |474946383961F0009500F70000000205000810040C10101808081414101018101818101021101821181021181818181821182110211010291010261B1B182118102121182121212121292121182129212129182921182929212929292929212931292931213110213129214A18313121312129293131312D35294B0E293E202|julianax_r_02_f_green_large.gif    |2012-10-19 09:56:38.273|
            95|47494638396150003100F70000000000000008000808000810080810001000081010081018101018101818101821181821182121182129212129212929212931292931312939292942293131293139313139313939215A002D4E1C394A393139423939423942424A63424A6B4231294A39394A39424A42424A424A4A4A4A4A4|julianax_r_02_green_small.gif      |474946383961F0009500F70000000202000808000010000810020D0D081010000818081808081418101010101018101810101818180818181808211018181818211818101821181821082108212110182118212118102121212918213110294218264C10315218295A0C2E5D121821211821291829212118212121212121292|julianax_r_02_green_large.gif      |2012-10-19 09:56:38.273|
           105|47494638396150003100F700000200050D0512151012181818220E14211C252621262929293100002D292D3129313131313929313931313131393931392139103142210852001C5A08316B103939394231394239394242393939424239424242424A39424A42424A424A4A4A4A52424252424A524A4A5A4A4A6F10106D2E2E6|roadster_green_f_small.gif         |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101014100818081421080C2D040A1010101014141810101810181818181818212113162E1512182118182121252114271D2225272B2F252D1F490A20670144101144272B3131353F372B3931393939393|roadster_green_f_large.gif         |2012-10-19 09:56:38.273|
           106|47494638396150003100F70000020005080810100018120D1029040818151B211821231E262B1618292929312929292931312931313131313139392D31213910314221085200205A08316B103931393939394231394239393939424239424242424A39424A42424A424A4A4A4A632020573F41731C1C7B3C3C524A4A524A525|roadster_green_small.gif           |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101010100818081422080A3104081012121810101810181818182112161A1C1C211C1C2B191824241D2925292B272D3129311D51041C6300186B00296502451C2033312E35373045352D3F373D3C423C4|roadster_green_large.gif           |2012-10-19 09:56:38.273|
           123|47494638396150003100F7000010081018101821102921103118181821181821182121182921212129212929213131213929292929293131293129293939294A31313139313131313939313929314239393139393942393939394242394242394A4242464A42424A4E35577029314A634A31524A424A4A4A4A4E464E524A4E4|superlight_metalicgreen_f_small.gif|474946383961F0009500F70000150E111B1221221E262921292525312B273331292931293139392131312931313131393136293633333639393142393118314A312D4631424A3929423931394231393931423B3B3F4239424242394242424A3D4242394A42424A4A394A4A424A39473E29425C52424A5242524A4A4E524A4A3|superlight_metalicgreen_f_large.gif|2012-10-19 09:56:38.273|
           124|47494638396150003100F700001008101810181818182118182118212121212921292929292514312D2535292939312931332B3939313131313939313929314239393139393942393939394242394242394A4242424A6B2142424A4A424A4A4A4A4F474C524A52524A5A4A5A4A5D7B2652525263845252525A5A525A5A5A5A6|superlight_metalicgreen_small.gif  |474946383961F0009500F70000150E131C1821221D25292129291E3326292E3129293129312D2D3531312939293139293931313139313131313939462918314A21425A3929422E3C4A3931393939393F363C393F4C4239424A3942423F414444414A394A4A424A52424A524252414C3C394B504E4A4A4A524A525A39524A525|superlight_metalicgreen_large.gif  |2012-10-19 09:56:38.273|

Click to view Full table

Sample Solution:

-- Selecting all columns from the ProductPhoto table
SELECT *   

-- From the Production schema's ProductPhoto table
FROM Production.ProductPhoto  

-- Filtering the results to include only rows where LargePhotoFileName contains '%greena_%' pattern
-- The '%' wildcard matches any sequence of characters
-- The '_a' pattern matches any character followed by 'a'
-- The ESCAPE clause specifies 'a' as the escape character
-- This means that the '%' and '_' characters in the pattern will be treated as literals rather than wildcards
-- So, '%greena_%' will match any value where 'green' is followed by 'a' and then followed by any sequence of characters
WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;

Explanation:

clause filters the results to include only rows where the The SQL query retrieves data from the ProductPhoto table within the Production schema.
It selects all columns using *.
The WHERE LargePhotoFileName column matches the specified pattern.
The pattern %greena_% includes two wildcards:

%: Matches any sequence of characters.
_: Matches any single character.

The pattern greena represents the literal string 'green' followed by any character.
The ESCAPE 'a' clause is used to specify that the character 'a' will be treated as an escape character.
This means that the % and _ characters in the pattern will be treated as literals rather than wildcards.
So, the pattern %greena_% will match any value where 'green' is followed by 'a' and then followed by any sequence of characters.

Sample Output:

productphotoid|thumbnailphoto                                                                                                                                                                                                                                                 |thumbnailphotofilename             |largephoto                                                                                                                                                                                                                                                     |largephotofilename                 |modifieddate           |
--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------------------+
            76|47494638396150003100F700007689B3B1D5A475777BECEBF234353A85A376FAFCFE45474BF1F2FEE2E2FC6B919D4D5A6CF8FAFEF4F6FE65676BDAE7ECEDEDFE787C805B994CBEBDC3BACFB65A6674AAABB0F6F8FC56565A478C349A9DA1E3E4EBDCDDE1CDD1D9244356D1D1D3586F8EDBDDEA78CA6ABBBCBE68AB58F9FAFA3|racer02_green_f_small.gif          |474946383961F0009500F70000CACBF8D6E4EFA3D19EECEDFEF3F4F4D3D3FEE2E3FEDCDDFED7D7E7979AA6B4C7CEA5A7AAC7C8DA85868983B67633C90754555A2C930797999A719CCD96A5B0F9FAFA777A8287ABCEFAFBFDEEF0F876767977858A3B3C41656669575A604DAE2FC7C6ECEAEBED47CA233A4149BBBCC3F7F9FCA|racer02_green_f_large.gif          |2012-10-19 09:56:38.273|
            77|47494638396150003100F70000B8D3C4E2E3F397A5A945596B6B86B767AB56F5F8FC89BC7134353A45464AFAFCFEEBEBF3E1E2E3F1F2FEE3E3FB75767A797C816CC751F5F6FE65676BF8FAFD499434BEBDC26D9399EDEDFEA6AAB2ECF9E856565AE3E5E884D3713E47509A9DA15A9A4AADB2B6DEE3EADCDDE3CFD1D8D2D2D3D|racer02_green_small.gif            |474946383961F0009500F70000444549309407CACAF8D6E4EE34C90849CE24D8D8E8ECEDFEF2F3F3D3D3FEE2E3FEDCDDFE9699A7A4A6AAB9C4CD858689C6C8DA54555A719DCD96A5B098999A767A8350A731F9FAFAFAFBFD76767A88ABCDEEF1F4D4EDCF69A9563B3C41646569575A60EAEBED75868D6ADB49C6C6EC3C4148B|racer02_green_large.gif            |2012-10-19 09:56:38.273|
            91|47494638396150003100F7000000000000000800080800081008081008100808101008101810101810181810182118182118212118212921212921292921293129293131294229313129313931313939314A3139392152082B472E393942394242424A42425231426B395A6B4239394A39424A42424A424A4A4A4A4A524A4A4|julianax_r_02_f_green_small.gif    |474946383961F0009500F70000000205000810040C10101808081414101018101818101021101821181021181818181821182110211010291010261B1B182118102121182121212121292121182129212129182921182929212929292929212931292931213110213129214A18313121312129293131312D35294B0E293E202|julianax_r_02_f_green_large.gif    |2012-10-19 09:56:38.273|
            95|47494638396150003100F70000000000000008000808000810080810001000081010081018101018101818101821181821182121182129212129212929212931292931312939292942293131293139313139313939215A002D4E1C394A393139423939423942424A63424A6B4231294A39394A39424A42424A424A4A4A4A4A4|julianax_r_02_green_small.gif      |474946383961F0009500F70000000202000808000010000810020D0D081010000818081808081418101010101018101810101818180818181808211018181818211818101821181821082108212110182118212118102121212918213110294218264C10315218295A0C2E5D121821211821291829212118212121212121292|julianax_r_02_green_large.gif      |2012-10-19 09:56:38.273|
           105|47494638396150003100F700000200050D0512151012181818220E14211C252621262929293100002D292D3129313131313929313931313131393931392139103142210852001C5A08316B103939394231394239394242393939424239424242424A39424A42424A424A4A4A4A52424252424A524A4A5A4A4A6F10106D2E2E6|roadster_green_f_small.gif         |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101014100818081421080C2D040A1010101014141810101810181818181818212113162E1512182118182121252114271D2225272B2F252D1F490A20670144101144272B3131353F372B3931393939393|roadster_green_f_large.gif         |2012-10-19 09:56:38.273|
           106|47494638396150003100F70000020005080810100018120D1029040818151B211821231E262B1618292929312929292931312931313131313139392D31213910314221085200205A08316B103931393939394231394239393939424239424242424A39424A42424A424A4A4A4A632020573F41731C1C7B3C3C524A4A524A525|roadster_green_small.gif           |474946383961F0009500F7000000000000080008000000000800080808000C08080808081010020810081008100808101010100818081422080A3104081012121810101810181818182112161A1C1C211C1C2B191824241D2925292B272D3129311D51041C6300186B00296502451C2033312E35373045352D3F373D3C423C4|roadster_green_large.gif           |2012-10-19 09:56:38.273|
           123|47494638396150003100F7000010081018101821102921103118181821181821182121182921212129212929213131213929292929293131293129293939294A31313139313131313939313929314239393139393942393939394242394242394A4242464A42424A4E35577029314A634A31524A424A4A4A4A4E464E524A4E4|superlight_metalicgreen_f_small.gif|474946383961F0009500F70000150E111B1221221E262921292525312B273331292931293139392131312931313131393136293633333639393142393118314A312D4631424A3929423931394231393931423B3B3F4239424242394242424A3D4242394A42424A4A394A4A424A39473E29425C52424A5242524A4A4E524A4A3|superlight_metalicgreen_f_large.gif|2012-10-19 09:56:38.273|
           124|47494638396150003100F700001008101810181818182118182118212121212921292929292514312D2535292939312931332B3939313131313939313929314239393139393942393939394242394242394A4242424A6B2142424A4A424A4A4A4A4F474C524A52524A5A4A5A4A5D7B2652525263845252525A5A525A5A5A5A6|superlight_metalicgreen_small.gif  |474946383961F0009500F70000150E131C1821221D25292129291E3326292E3129293129312D2D3531312939293139293931313139313131313939462918314A21425A3929422E3C4A3931393939393F363C393F4C4239424A3942423F414444414A394A4A424A52424A524252414C3C394B504E4A4A4A524A525A39524A525|superlight_metalicgreen_large.gif  |2012-10-19 09:56:38.273|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Average sales orders per sales agent.
Next: Address for city outside US starts with Pa.