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
renhe
10,282 PointsAlternative 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;
Steven Parker
229,644 PointsNo, that wouldn't work. See my answer below about restrictions on wildcards.
7 Answers
Steven Parker
229,644 PointsThe 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.
renhe
10,282 PointsAh 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
229,644 PointsThat'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
18,672 PointsSELECT *FROM students WHERE last_name < 'N' ORDER BY last_name ASC;
Marc Hensley
11,384 PointsThis solution also works (thanks Andrew Chalkley) using a less than operator:
SELECT * FROM STUDENTS WHERE LAST_NAME < "N" ORDER BY LAST_NAME;
Mark Chesney
11,747 PointsAndrew Chalkley taught me everything I know in SQL!
Mohammad Bazarbay
1,967 PointsI did something similar. Here is what I did:
SELECT * FROM STUDENTS WHERE LAST_NAME BETWEEN 'A' AND 'N' ORDER BY LAST_NAME ASC;
horus93
4,333 PointsYea, 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.
elomaur
5,276 PointsThis worked for me
SELECT * FROM STUDENTS
WHERE LAST_NAME BETWEEN "A" AND "N"
ORDER BY LAST_NAME ASC;
kevin hudson
Courses Plus Student 11,987 PointsI 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
229,644 PointsJust like Diana and Marc, you found the definitive solution.
Miguel Richardson
3,650 PointsSELECT 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.
Kareem Jeiroudi
14,984 PointsKareem Jeiroudi
14,984 PointsYeah, this way it should work too, but be careful not to include N, therefore you should type
BETWEEN 'A%' AND 'M%'.