Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Fabio Campanini
PLUS
Fabio Campanini
Courses Plus Student 1,544 Points

Hi, I'm not able to overcome task 2/6 of the set operations challenges

This is the code I wrote: select name from fruit where name between "a%" and "k%" union select name from vegetable where name between "a%" and "k%";

Where is the error?

I am assuming you wanted all fruits and vegetables with names starting with (a, b,...k) and Fruits can not also be Vegetables (mutually exclusive events). Without seeing more code or what the return values produced were, could be a couple problems, but here's a few possible solutions, most likely presented first.

1) Assuming you have 1 table with all fruits and vegetables with various names, I believe you misused AND or forgot parenthesis. You want the union of fruits and vegetables, but union is an OR operation, not AND. Mathematically, this should read similarly to (condition1*condition2)+(condition3*condition4), which is fruits(a+b+...+k)+vegetables(a+b+...+k). You can use the expansion+distribution properties, because they are all on one table and instead say (condition1 OR condition3)*condition2. The parenthesis that contain "fruit OR vegetable" are super important because AND is otherwise considered before OR. Try (fruit OR vegetable) AND Between ("a%", "k%").

2) If fruits and vegetables are on different tables, your problem is bigger. Union is not the same as join. Join combines two tables (or sets) and should only be used if fruits and vegetables are on different tables. Assuming you wanted all (fruits OR vegetables) a-k returned, you screwed up when you tried to join. Fruits are, by definition, not vegetables so if you used AND (instead of OR), your code returns nothing (there will be no overlap). You should review how join is performed if fruits and vegetables are on different tables.

3) Back to 1 table solutions, you could be coding between "a...forever and k...forever". In essence, (A+inf)-(K+inf). The limiting K set is supposed to represent the end of A set, but the % symbol screws up k. "k%" also goes on forever. This mathematically is still acceptable (because A set goes on forever so you would get A through K), but systemically would take a very long time to calculate (forever after A-forever after K). Try "k" or even "%k", though "%k" could end early on something like 'aardvark', depending on language.

4) It could be a syntax issue of the software. Instead of using "a%" and "k%", try using "a_" and "k_".

Steven Parker
Steven Parker
221,902 Points

Curious suggestions — have you actually tried doing these things in SQLite? I don't think most of them actually work.

Fabio Campanini
Fabio Campanini
Courses Plus Student 1,544 Points

Sorry, I didn't expose my problem clearly: my bad. The query I have to write is in SQL, and this is the request of the challenge: "There are two tables Fruit and Vegetable table. The Fruit table has a FruitID and a Name column and the Vegetable table has a VegetableID and Name column. Create a list of all fruits and vegetables starting with the letters A through K . In other words all fruit and vegetables that don't start with the letter L to Z."

Steven Parker
Steven Parker
221,902 Points

I understood that from your original question. I just added an example to my answer that might be helpful.

To facilitate the most complete and accurate answers, always provide a link to the course page you are working with.

4 Answers

Steven Parker
Steven Parker
221,902 Points

You can only use wildcards with "LIKE" (not "BETWEEN").

For this challenge, you'll probably need to use a comparison operator and take advantage of the fact that things that come earlier in alphabetical order are considered "less than" things that come later.

So you might do something like this:

... WHERE name < "L"

I don't know if my assumptions are correct, but yes, I have tried all of my suggestions (SQLite, MySQL, Access) before to answer various query requests. What they ask of Campanini isn't exactly clear here, but based on what he has posted, I posted possible solutions in order of likelihood so even if my syntax is off (because those 3 aren't exactly the same), he has a better idea of what might be the root cause.

Steven Parker
Steven Parker
221,902 Points

Really? I suppose I'll have to go try it myself now, but I will certainly be shocked if syntax like Between ("a%", "k%") actually works.

Thank you for the SQLite detail. I will remember that in the future. How about you try the other 2 of the three I listed? "any" is a rather tilted assumption even if you want to disregard MySql and Access. "Which" makes me laugh. Calm down Mr. Authority on all dbms everywhere. You're about to become a meme.

Steven Parker
Steven Parker
221,902 Points

I'm happy to be able provide amusement along with information :smile:, but the question was sincere and made no assumptions. Do you know of any software which uses that syntax?

Maybe it is "AND name BETWEEN ('a%','k%')". Maybe it is "AND (name>='a%' and name<='k%')". He didn't mention what software he was using so arguing over syntax is a moot point.

Steven Parker
Steven Parker
221,902 Points

Does the syntax "BETWEEN ('a%','k%')" work in any DBMS software? Which one(s)?

And all of the challenges here use SQLite.

Fredric von Stange
Fredric von Stange
3,867 Points

So what is the final answer for this? I'm also stuck on this challenge.

Steven Parker
Steven Parker
221,902 Points

Did you see my answer above?

Fredric von Stange
Fredric von Stange
3,867 Points

I did, but I didn't realize I needed to include the WHERE statement in both SELECT statements. I figured it out, thanks!