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

SQL Query - AdventureWorks: Exercise-190 with Solution

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

Click to view Full table

Sample Solution:

SELECT salesorderid, orderdate, creditcardapprovalcode  
FROM sales.salesorderheader 
WHERE creditcardapprovalcode LIKE '1_6%'  
ORDER by orderdate;

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          |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day


SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv