Databases Querying Relational Databases Set Operations Set Operations

Having trouble sorting columns A through K and not L to Z in SQL.

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.

Having trouble with sorting columns that start with A through K and excluding columns that start with L to Z. I'm sure that there is something I am forgetting about. The code that follows works but there has to be a lazier way to do this! One that involves less typing.

SELECT Name
FROM Fruit
WHERE Name LIKE "a%" OR Name LIKE "b%" OR Name LIKE "c%" LIKE "d%" OR Name LIKE "e%" OR Name LIKE "f%"
OR Name LIKE "g%" OR Name LIKE "H%" LIKE "i%" OR Name LIKE "j%" OR Name LIKE "k%"

UNION

SELECT Name
FROM Vegetable
WHERE Name LIKE "a%" OR Name LIKE "b%" OR Name LIKE "c%" LIKE "d%" OR Name LIKE "e%" OR Name LIKE "f%"
OR Name LIKE "g%" OR Name LIKE "H%" LIKE "i%" OR Name LIKE "j%" OR Name LIKE "k%"
ORDER BY Name ASC;

1 Answer

Steven Parker
Steven Parker
203,270 Points

You can do inequality comparisons with strings, so if you test "Name <L`", only words that come before "L" alphabetically will be selected.

Steven, Thank you.

I originally typed

 WHERE Name < "L"

but only for one query like the followings code:

 SELECT Name FROM Fruit
 UNION 
 SELECT Name FROM Vegetable 
WHERE Name < "L" 
ORDER BY Name ASC;

Instead of something as simple as adding

WHERE Name < "L" 

to each query...

 SELECT Name FROM Fruit
WHERE Name < "L"
 UNION 
 SELECT Name FROM Vegetable 
WHERE Name < "L" 
ORDER BY Name ASC;

Thanks again!