Write a SQL query to show all the orderNumber whose customer Name start with letter ‘N’

Question

Given image, I want to find the order number of all the customers details whose name starts with 'N'.

Answer 1

Something like this should get you pretty close:

SELECT o.orderNumber
FROM orders o
LEFT JOIN customers c
ON c.customerNumber = o.customerNumber
WHERE c.customerName LIKE 'N%'

Answer 2

This looks like homework, so I am going to give you a lesson in business logic as if it was homework, because clearly, this a simple join that has some implications that you might not consider if you just implemented Neil's solution.

The first thing is that: this needs to go back to the business for "clarification".

• What's the code being used for? (Reporting, just a one off [it rarely is a one off], sense checking)
• How clean is the customerName field?
• First Name or Last Name?
• NEVER USE LIKE IF YOU CAN HELP IT!

These are important considerations.

If you were to put this into an SSRS report for a business, you would want to know exactly what field you are looking in, the assumption is that you would use customerName field but the business might decide to use either of ContactNames, as customerName might contain something like: "Mr Norman Smith" or "Smith,Norman" or "Norman Smith", a mix of the lot, it is not uncommon in businesses to have dirty data, especially if they do not place value in data-stewardship. You should ALWAYS CHECK THE FIELD and CONSULT THE BUSINESS BEFORE PROCEEDING.

SELECT DISTINCT customerName FROM customers

It is also the case that 'N' is not going to be a fixed field and should be a parameter in your query that the user can enter, because, it is clear at least from my industry experience that this is going to end up as a report that a customer will have access to.

If this is going to be data placed into an SSRS report, or even pushed out, knowing your declarations is going to be useful. In an SSRS report, you can comment out your parameter SET parts, and even your DECLARES, (you wouldn't ever delete them in case you needed somebody to evaluate your code) and the SSRS Report Builder would pluck those parameters out and you can DECLARE them in the Builder, as well as assign text values to numerical data for labels.

Here is an example code I would write for this if a business gave me that spec with 0 clarification and no comm's.

/*Declares your variables*/
DECLARE @Initial varchar(1) --Declaring our Initial: Note this is why SQL is self documenting
DECLARE @FieldChoice = int --Declaring our field choice
SET @Initial = 'N' --Initial to search
SET @FieldChoice = 1 -- {1,2,3} = {customerName,contactFirstName,contactLastName}

/*Your query*/
SELECT O.orderNumber
FROM orders O
INNER JOIN customers C
ON C.customerNumber = O.customerNumber 
WHERE (LEFT(C.customerName,1) IN (@Initial) AND @FieldChoice = 1)
OR (LEFT(C.contactFirstName,1) IN (@Initial) AND @FieldChoice = 2)
OR (LEFT(C.contactLastName,1) IN (@Initial) AND @FieldChoice = 3)  

This hasn't been tested, so you should test it yourself against your data.

In an SSRS report, for example: You would parse the Numbers for the field headers, but give them RichText labels that reflect the field header. This allows the business to decide which header to check.

If you consult the business and they say, "Actually the LastName field is what we want, we don't care about first names", you would end up with this code. This is why it is important to seek clarification.

/*Declares your variables*/
DECLARE @Initial varchar(1) --Declaring our Initial
SET @Initial = 'N' --Initial to search

/*Your query*/
SELECT O.orderNumber
FROM orders O
INNER JOIN customers C
ON C.customerNumber = O.customerNumber 
AND (LEFT(C.contactLastName,1) IN (@Initial) 

Note in this code, I have opted to put 'AND' in place of the `WHERE, because I am leveraging the INNER JOIN's capabilities to filter as an example: But you should always check your EXECUTION PLAN because that can make a difference to your query time. The assumption is that this is homework and you are learning.

You should also consider, if this is homework, the potential for putting this into a Stored Procedure or Function, depending on the business use case, but bare in mind, Stored Procedures do not like the IN clause when it comes to pushing out to reporting. It's not that they don't function, they just cannot handle arrays of values very well, because the stored proc' parameters only accepts strings. https://mitchelsellers.com/blog/article/using-the-in-clause-with-stored-procedures

But highlighting the fact that businesses do not always have clean data and that the question is unclear from a requirements standpoint is the difference between a Distinction and a Pass.

You have to consider "What is the business trying to achieve" when you are report writing.

Why not use LIKE? Well, it is fine and performs better in some cases, but you need to be aware of your back end to front end methodology if you do use LIKE. If you are parsing credentials through from a web page, you just opened your business up to SQL injection attacks, because folks can parse stuff into LIKE and the database is going to allow reads, so goodbye customer data. You have just removed one layer of protection by using the LIKE clause so all that is left to protect you from a table drop is your security settings and limitations on characters within your parameter box. If you are the Data Designer and NOT the Dev on the webpage and you aren't aware of injection risks, this is how dire mistakes can happen.

What Neil T put is a perfectly acceptable answer, it get's a pass, that's all it gets.