So I have a table with the birth dates and I need to average the people's age. How do I do that? I know I have to use months_between(). Thank you in advance!
Why do you think you need
months_between? You don't (unless you have a very specific and unusual definition of "average age").
Over a long enough period (like 40+ years, say), a person's age in years can be calculated (within a narrow approximation window) as the age in days, divided by 365.25. The age in days is simply a difference between two dates,
BORN. The first one is provided by the system and the second is in your table. Assuming, that is, that you want age as of today; otherwise change SYSDATE to whatever "as-of" (fixed) date you want to use.
So, something like
select [some columns here], AVG(SYSDATE - BORN)/365.25 as avg_age from your_table
Not clear why you would select
dual; surely you didn't call your table
dual? Nor did you change the standard
dual table to add your own data to it?
When people ask you what datatype you use for
born in your tables, what you see on the screen when you query for it is not sufficient; the screen will show a string (it's the only thing a screen shows) and doesn't necessarily reflect what's in the database. To get the proper answer, run
DESCRIBE table_name; that will show all the columns in
table_name and their datatype. Note that
DESCRIBE table_name is a SQL*Plus command (understood by Toad and SQL Developer - whatever you use to communicate with the database), so it doesn't need a
; or a
/ at the end. Just type it at the prompt and hit