Filtering
1. From zoo table, query the list of zoo_name names starting with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '^[abc].*'
2. From zoo table, query the list of zoo_name names that do not start with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '^[^abc]'
3. From zoo table, query the list of zoo_name names that do end with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '.*[^abc]$'
4. From zoo table, query the list of zoo_name names starting and ending with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '^[abc].*[abc]$';
5. From zoo table, query the list of zoo_name names that either do not start or end with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '^[^abc]|[^abc]$';
-- Condition allows for cities that start or end with a vowel, as long as they don't both start and end with a vowel.
6. From zoo table, query the list of zoo_name names that do not start and end with a,b,c. Your result cannot contain duplicates.
SELECT DISTINCT zoo_name
FROM zoo
WHERE zoo_name REGEXP '^[^abc].*[^abc]$'
7. From zoo table, query the list where the zoo_name is ‘wild haven’ and the animal_name can either be ‘lion’ or ‘tiger’.
SELECT * FROM zoo
WHERE zoo_name ='wild haven' AND (animal_name='lion' OR animal_name='tiger');
8. From zoo table, return all the rows that have animal_name starts with the letter ‘a’.
SELECT * FROM zoo
WHERE animal_name LIKE 'a%'
9. From zoo table, return all the rows that have animal_name starts with the letter ‘a’, and make the match case insensitive.
SELECT * FROM zoo
WHERE animal_name ILIKE 'a%'
10. From zoo table, return all the rows that have animal_name ends with the letter ‘er’.
SELECT * FROM zoo
WHERE animal_name LIKE '%er'
11. From zoo table, return all the rows that have animal_name starting with either “b”, “s”, or “p”.
SELECT * FROM zoo
WHERE animal_name LIKE '[bsp]%';
12. From zoo table, return all the rows that have animal_name starting with either “a”, “b”, “c” or “d”.
SELECT * FROM zoo
WHERE animal_name LIKE '[a-d]%';
13. From zoo table, return all the rows that have animal_name start “a” and are at least 4 characters in length.
SELECT * FROM zoo
WHERE animal_name LIKE 'a___%'; # 3 underscores.
14. From zoo table, return all the rows that have animal_name starts with any character, followed by “ion”.
SELECT * FROM zoo
WHERE animal_name LIKE '_ion'