This is, hands down, the best internet resource I’ve come across for learning more advanced SQL elements. I am learning so much.
For example, you have an index on
last_name, and you run this:
SELECT first_name, last_name, phone_number FROM employees WHERE UPPER(last_name) = UPPER('winand')
You’d think that the where clause would utilize the index, but it doesn’t.
Although there is an index on
LAST_NAME, it is unusable—because the search is not on
UPPER(LAST_NAME). From the database’s perspective, that’s something entirely different.
But!! You can create a computed field that contains that function, if you need it:
ALTER TABLE employees ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name)); CREATE INDEX emp_up_name ON employees (last_name_up);