Between ___ and ___


#1

BETWEEN 'A' AND 'J' filters results beginning with A but not including J. Whereas with the years query BETWEEN 1990 AND 2000, it filters 1990 and including 2000.

Is that just something you have to remember in SQL that the range for integers and text are different in that sense? Or is there a way to not include and include the end range for both data types?


#2

Did you test a name that was just J? If you did, then it would have been selected.

What won't be selected is everything higher than J, like Ja or Je. This is just like 2000 would be selected but not 2001.

Do you see how it works now?


Why for last element BETWEEN is inclusive if it's an integer but not inclusive if it's a string?
#3

I do get what you are saying, but the exact wording used in the exercise is...

This statement filters the result set to only include movies with names that begin with letters "A" up to but not including "J".

So it may be how you say it is, that it would include "J" if there were a movie titled "J" in the table, but it's not worded like that.


#4

It's a little bit sloppy on the wording because is isn't taking into account the edge case of a movie called "J".

I don't know if you bothered testing yet, but it is a good way to see how things work.

INSERT into movies (id, name)
    VALUES (998, 'J');
INSERT into movies (id, name)
    VALUES (999, 'Ja');
SELECT * FROM movies
    WHERE name BETWEEN 'Iron' AND 'J';

When you get to dates, you'll find there are some behaviour there that you might find unexpected too:


#5

I actually did go in and try to add a movie titled "J" and perform this task after.

Maybe you can also answer this question while I'm here about SQL so far (I'm now up to aggregate functions). All of the lessons have placed emphasis on using capitalization for clauses and labeling data types. But I noticed in here that I was able to pass the lessons and make the code work by using all lower case.

I know in other coding, I always use a capital letter in variable making like myAge or myFavoriteSport, etc. Is the capitalization for SQL purely to help make your code look neater and be able to discern what is doing what?


#6

I'm glad to hear that you did the testing :thumbsup:

As usual I will resort to SO answers, they have so many good one already.

With SQL case is just for readability. I like seeing the CAPS on the keywords but not on everything. You will see lots of variations out there. There might well be standards set by the company you end up coding for.

Threads about keywords:

Example of a LOT of CAPS:
http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm