Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases SQL Reporting by Example Day 2: Advanced Selecting Students A to M

Alternative Soln: Generate a list of students with last names from A to M

I found that the following also works. Not sure how performant it is though...

SELECT id, first_name, last_name FROM students WHERE last_name BETWEEN 'A%' AND 'N%' ORDER BY last_name;

Kareem Jeiroudi
Kareem Jeiroudi
14,984 Points

Yeah, this way it should work too, but be careful not to include N, therefore you should type BETWEEN 'A%' AND 'M%'.

Steven Parker
Steven Parker
230,024 Points

No, that wouldn't work. See my answer below about restrictions on wildcards.

7 Answers

Steven Parker
Steven Parker
230,024 Points

The wildcard function of "%" only applies when used with LIKE, but here it makes no significant difference because it comes before any letter by value. So the filter is essentially the same as BETWEEN "A" AND "N", and the only risk would be if someone actually had a last name of "N" they would be included in the result set.

Ah so "%" is not really doing anything in this case? When I ran the query using BETWEEN 'A%' AND 'M%', people with last names starting with 'M' were not included.

Steven Parker
Steven Parker
230,024 Points

That's right, because it would stop at "M" (or more precisely at "M%") and not include anything with other letters. If you had used "Mzzz" instead it would have worked.

Happy coding!

Diana Ci
Diana Ci
18,672 Points

SELECT *FROM students WHERE last_name < 'N' ORDER BY last_name ASC;

This solution also works (thanks Andrew Chalkley) using a less than operator:

SELECT * FROM STUDENTS WHERE LAST_NAME < "N" ORDER BY LAST_NAME;

Andrew Chalkley taught me everything I know in SQL!

I did something similar. Here is what I did:

SELECT * FROM STUDENTS WHERE LAST_NAME BETWEEN 'A' AND 'N' ORDER BY LAST_NAME ASC;

horus93
horus93
4,333 Points

Yea, considering the stuff we went over in the class that's exactly the solution i'd expect most of us would of arrived at, possibly the <= >=, but I was surprised he didn't mention BETWEEN.

This worked for me SELECT * FROM STUDENTS WHERE LAST_NAME BETWEEN "A" AND "N" ORDER BY LAST_NAME ASC;

I immediately thought of < operator here was my go at it:

-- Generate a list of students with last names from A to M.
SELECT * FROM STUDENTS WHERE LAST_NAME < 'N' ORDER BY LAST_NAME;
Steven Parker
Steven Parker
230,024 Points

Just like Diana and Marc, you found the definitive solution. :+1:

SELECT LAST_NAME || ', ' || FIRST_NAME AS 'STUDENTS' FROM STUDENTS WHERE LAST_NAME > 'A' AND LAST_NAME < 'N' ORDER BY LAST_NAME ASC

I used a concatenate function to produce an a single column output.