AdventureWorks Database: Find credit cards that starting with 1 and the third digit is 6

Last update on October 13 2025 12:39:15 (UTC/GMT +8 hours)

190. From the following table write a query in SQL to find all creditcardapprovalcodes starting with 1 and the third digit is 6. Sort the result set in ascending order on orderdate.

Sample table: sales.salesorderheader

salesorderid|revisionnumber|orderdate              |duedate                |shipdate               |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal   |taxamt    |freight  |totaldue   |comment|rowguid                             |modifieddate           |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
       43659|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO522145787        |10-4020-000676|     29825|          279|          5|            985|            985|           5|       16281|105041Vi84182         |              | 20565.6206| 1971.5149| 616.0984| 23153.2339|       |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
       43660|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18850127500      |10-4020-000117|     29672|          279|          5|            921|            921|           5|        5618|115213Vi29411         |              |  1294.2529|  124.2483|  38.8276|  1457.3288|       |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
       43661|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18473189620      |10-4020-000442|     29734|          282|          6|            517|            517|           5|        1346|85274Vi6854           |             4| 32726.4786| 3153.7696|  985.553| 36865.8012|       |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
       43662|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18444174044      |10-4020-000227|     29994|          282|          6|            482|            482|           5|       10456|125295Vi53935         |             4| 28832.5289| 2775.1646| 867.2389| 32474.9324|       |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
       43663|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18009186470      |10-4020-000510|     29565|          276|          4|           1073|           1073|           5|        4322|45303Vi22691          |              |   419.4589|   40.2681|  12.5838|   472.3108|       |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
       43664|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16617121983      |10-4020-000397|     29898|          280|          1|            876|            876|           5|         806|95555Vi4081           |              | 24432.6088| 2344.9921|   732.81| 27510.4109|       |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
       43665|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16588191572      |10-4020-000146|     29580|          283|          1|            849|            849|           5|       15232|35568Vi78804          |              | 14352.7713| 1375.9427| 429.9821| 16158.6961|       |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:


-- Selecting columns salesorderid, orderdate, and creditcardapprovalcode from the sales.salesorderheader table
SELECT salesorderid, orderdate, creditcardapprovalcode  
-- From the sales.salesorderheader table
FROM sales.salesorderheader 
-- Filtering records where creditcardapprovalcode starts with '1' followed by any character and then '6'
WHERE creditcardapprovalcode LIKE '1_6%'  
-- Ordering the result set by orderdate in ascending order
ORDER by orderdate;

Explanation:

This SQL query retrieves specific columns from the salesorderheader table in the Sales schema.
Comments are added to explain each part of the query for better understanding and maintenance.
Here's a breakdown of what the query does:

It selects the salesorderid, orderdate, and creditcardapprovalcode columns from the salesorderheader table.
The WHERE clause filters records where the creditcardapprovalcode starts with '1', followed by any single character, and then '6'.
The % sign in the LIKE operator acts as a wildcard, matching any sequence of characters.
The ORDER BY clause arranges the result set in ascending order based on the orderdate column.

Sample Output:

salesorderid|orderdate              |creditcardapprovalcode|
------------+-----------------------+----------------------+
       43675|2011-05-31 00:00:00.000|126121Vi61402         |
       43679|2011-05-31 00:00:00.000|126365Vi28482         |
       43680|2011-05-31 00:00:00.000|126370Vi34642         |
       43681|2011-05-31 00:00:00.000|16608Vi27707          |
       43875|2011-07-01 00:00:00.000|116188Vi1051          |
       43880|2011-07-01 00:00:00.000|126355Vi95120         |
       43887|2011-07-01 00:00:00.000|106673Vi48861         |
       43889|2011-07-01 00:00:00.000|126756Vi87636         |
       43893|2011-07-01 00:00:00.000|116815Vi96000         |
       43916|2011-07-01 00:00:00.000|18665Vi32553          |
...

Go to:

PREV : Return rows except two values in the two tables match.
NEXT : Retrieve all rows, columns using table aliasing.

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.