Databases Querying Relational Databases Set Operations Review and Practice

lukej
lukej
34,220 Points

Task 2 of the Code Challenge

This query returns an error, or is not accepted by the unit testing. Is there an easier solution that I do not see?

SELECT Fruit.Name FROM Fruit WHERE Fruit.Name LIKE '[A-K]%'
  UNION
SELECT Vegetable.Name FROM Vegetable WHERE Vegetable.Name LIKE '[A-K]%';

Instructions:

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.

2 Answers

Gabriel Pierce-Lackey
Gabriel Pierce-Lackey
11,007 Points

There's a few things I would change. First, you don't need to reference the table name in the select and where clause. You're not doing a join so those queries are both kept to themselves, so there is no need to specify which table your using, as you would with a join or something. Secondly, you should between instead of like. That way it's searching for names that start with between A-K. Thirdly, you don't need a wildcard for this. It somehow knows what you mean with between. Lastly A-K will not show any that start with K, so we need to search for A-L and slap those in single quotes so it knows those are values. You might still be able to use brackets, I'm not sure. I have never seen that and I use SQL everyday. But I haven't seen it all. So I'm not sure about that part.

SELECT name FROM Fruit 
  WHERE name BETWEEN 'A' AND 'L'
UNION 
SELECT name FROM Vegetable 
  WHERE Name BETWEEN 'A' AND 'L';
Dan K
Dan K
9,884 Points

It didn't work.

Since the instructions include the very beginning of the alphabet, another solution without using between is:

SELECT Name FROM Fruit
WHERE Name < "L"
union
SELECT Name FROM Vegetable
WHERE Name < "L"