Good. Let's change the table now. We have an imaginary foundation which gives annual awards to developers with notable contributions to software development. The candidates are chosen by e-votes, and there are multiple categories:
CREATE TABLE dev_award ( id integer PRIMARY KEY, year integer, category varchar(32), first_name varchar(64), last_name varchar(64), company varchar(64), vote_count integer );
In this table, company names are written in various letter cases, so we typically use queries like the one below:
... WHERE LOWER(company) = 'vertabelo' ...
If we create an index on the column company, it won't be used in the above query because of the LOWER() function call. This time, there's no easy way to rewrite the query. In such situations, we can create an index based on the result of LOWER(company):
CREATE INDEX company_insensitive ON dev_award (LOWER(company));
We use the same syntax to create an index on an expression. Instead of providing a column name, we provide an expression (in this case, the function call LOWER(company)).
An American real estate agency has the following table for the houses they offer:
CREATE TABLE house ( id integer PRIMARY KEY, state char(2), zip_code char(5), price decimal(10, 2), bedrooms integer, built_year integer );
They now need our help. The agents frequently search for houses in a given area by taking a look at the first two characters in the zip_code column, for instance:
SELECT * FROM house WHERE LEFT(zip_code, 2) = '01';
Your task is to create an index named zip_code_first_two on the expression LEFT(zip_code, 2).
