SQL query

1

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, and country . You want to retrieve the first 3 letters of each country name. You decide to use the SUBSTR function to retrieve the first 3 letters of each country name, and use the AS command to store the result in a new column called new_country .

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3 letters of each country name and store the result in a new column as new_country .

NOTE: The three dots (...) indicate where to add the statement.

I just don't seem to get after watching all the videos. Thank you. in advance for the help

1 Like
2

Hi Webbyreach,

If you read the question it's pretty simple. First step is to know where the data is comming from.

SELECT country FROM the database table that contains customer data. Let's assume the table is dbo.customer then it will be

SELECT country
FROM dbo.customer;

If that is working you can use the SUBSTR function to select only the first 3 letters for the country and use AS new_country as a new column name.

SELECT SUBSTR(country) AS new_country
FROM dbo.customer;

This will give an error but you can use your favourite searchengine and search for SQL function SUBSTR to find the correct answer :wink:

2 Likes
4

I suspect my favourite searchengine is generating a lot of traffic to this question so I'll give the correct answer right away as some people are already using the searchengine:

SELECT SUBSTR(country,3) AS new_country
FROM dbo.customer;

1 Like
5

Even this was not the correct answer but since you did not see the full question I cannot blame you. This is part of the test and honestly I only answered it because I select the option which seemed most correct and if it was wrong I took the test again.
The thing is, after you wrote your query, you should also respond to the question "What customer ID number appears in row 2 of your query result? "
No matter how I looked at the assignment the answer was always 2, but you could only select options 3,28,55 and 47.
So I've selected each option and at the end, when I selected 55, it showed as a correct and it also showed the correct SQL querry (having a correct SQL querry was not a condition to pass), which is:

SELECT customer_id, SUBSTR(country, 1, 3) AS new_country FROM customer ORDER BY country

I guess they forgot to mention to order countries, or maybe as beginners we don't know it's common

6

As a bit of a sidebar... Ya just gotta love some of these "training courses". "AS" is NOT a command. It's only a "keyword" to assign a "column alias name" to the expression whether the expression is another column name, literal, or formula.

Just so folks don't take the following link as spam... it's totally free and I have zero affiliation with the site. I just like how they teach beginners in SQL the "ropes".

1 Like
7

The substring function follows the following structure:


SUBSTRING ( expression, start, length )

The start parameter is indexed from 1

So the answer is:

SELECT SUBSTR(country,1, 3) AS new_country
FROM tbl_customer