Reduce the computation time of the queries above using indexes

[mesajflaviu]

Hello. I have few SQL queries. Something like this:

number of patients born on Januray 1st, 2000
number of patients born before Januray 1st, 2000
number of patients having firstname "JOHN"
distribution patients per birth year
distribution of firstnames

I have to create these queries if data structure is like this:

P00003542,JOHN^SMITH,19960124,M
P00002888,BILLY^STANLEY,20050903,U
P00006888,HRO^ELLEN,20040314,U

What query should I do on last requests (distribution patients per birth year) ? Grouping data by birth year ?

And I have another task then:

Reduce the computation time of the queries above using indexes

What should I do in this case on queries from above ?

[Arjay]

Unless this is a homework assignment, I would import the records into a local database and let its database engine execute the queries.

[mesajflaviu]

I create an index for that table:

CREATE INDEX id_index ON patients(patient_id);

and removing index:

DROP INDEX id_index;