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,982 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
203,443 Points

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

7 Answers

Steven Parker
Steven Parker
203,443 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
203,443 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,665 Points

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

Marc Hensley
Marc Hensley
11,016 Points

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!

Mohammad Bazarbay
Mohammad Bazarbay
1,967 Points

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.

elomaye
elomaye
5,225 Points

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

kevin hudson
PLUS
kevin hudson
Courses Plus Student 9,652 Points

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
203,443 Points

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

Miguel Richardson
Miguel Richardson
3,336 Points

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.