Databases Reporting with SQL Working with Text Getting the Length of a String

Rhea Khanna
Rhea Khanna
2,838 Points

DESC, LIMIT and Alias

In the library database there's a books table with the columns id, title, author, genre and first_published.

Find the book with the longest title. Show the title and then the length. Alias the result of the length calculation to be longest_length. Only retrieve the longest book.

My answer: SELECT LENGTH title FROM books AS "longest_length" DESC LIMIT 1;

Can someone explain where I have gone wrong

1 Answer

Zachary Miles
Zachary Miles
5,187 Points

There are several things that are missing or are in the incorrect place in your query. 1) The challenge asks you to "show the title and then the length" for the longest title. When you want to show specific columns, you must place them in your SELECT statement. So, to show the title column, as the challenge requests, you must include “title” in your select statement. So, you would need to start the query by writing “SELECT title” 2) In addition to the title, the challenge asks you to also show the length of the longest title. This requires the use of an SQL function. If you review previous videos, you will see the syntax is FUNCTION_NAME(column_name). So, to get the length of the title column, you would use the function LENGTH with the name of the desired column in parenthesis. IFor this challenge it would be LENGTH(title), and you can now add this to your query. So, our current query would be “SELECT title, LENGTH(title)” 3) The challenge asks that you alias the result of the length calculation to be “longest_length”. In order to alias a column, you can use the AS keyword after the column you want to alias. For example, <column_name> AS <column_alias>, and if the column name does not include a space, as is the case in longest_length, the quotes are not required. This issue with you query in regards to aliasing is that in your query you placed the AS after the FROM clause (so you are aliasing the whole query) which is not what the challenge is asking you to do. Therefore, in order to alias the length calculation to be “longest_length”, you could say “LENGTH(title) AS longest_length”. After updating our query, we now have “SELECT title, LENGTH(title) AS longest_length”. 4) The “FROM books” part of your query is correct, so we will add it to our current query. So, we now have “SELECT title, LENGTH(title) AS longest_length FROM books”. If we ran this query in this form, a table would be returned with two columns (the first column being the “title” column with the title of each book in the books table, and the second column named “longest_length” with the length of each title). However, the challenge wants us to “only retrieve the longest book”. So, this requires us to sort the results and only return the top result. To accomplish the sorting requirement we can use the SQL “ORDER BY” keyword. As you may remember, the ORDER BY keyword is used to sort the result set in ascending or descending order. The syntax for the ORDER BY keyword is “ORDER BY <column1_name>, <column2_name>, … ACS|DSC”. In your query you were correct in saying “DESC” because we want the title of the longest book to appear first, but you forgot to include the “ORDER BY” keyword and you did not provide a column in which you wanted the results to be ordered by. So, if we add the correct keyword and provide the column we want to order by, our query would now be “SELECT title, LENGTH(title) AS longest_length FROM books ORDER BY longest_length DESC”.

Lastly, since you only want one result returned, you were correct in ending the query with “LIMIT 1;”. So, our final query would be “SELECT title, LENGTH(title) AS longest_length FROM books ORDER BY longest_length DESC LIMIT 1;