Forum Stats

  • 3,784,122 Users
  • 2,254,894 Discussions
  • 7,880,697 Comments

Discussions

Query complex in Oracle SQL

8886da1b-b96f-46a0-ab95-a450d345a825
edited May 13, 2019 11:02AM in SQL & PL/SQL

I have the following tables and their fields

mas mas mas mas.png

They ask me for a query that seems to me quite complex, I have been going around for two days and trying things, it says:

It is desired to obtain the average age of female athletes, medal winners (gold, silver or bronze), for the different modalities of 'Artistic Gymnastics'. Analyze the possible contents of the result field in order to return only the expected values, even when there is no data of any specific value for the set of records displayed by the query. Specifically, we want to show the gender indicator of the athletes, the medal obtained, and the average age of these athletes. The age will be calculated by subtracting from the system date (SYSDATE), the date of birth of the athlete, dividing said value by 365. In order to avoid showing decimals, truncate (TRUNC) the result of the calculation of age. Order the results by the average age of the athletes.

Well right now I have this:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> person</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">gender</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">score<br/> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> person</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">athlete</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">competition</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">sport <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> person</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idperson </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> athlete</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idathlete    <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> athlete</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idathlete</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">  score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idathlete  <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> competition</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idsport </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> sport</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idsport <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> person</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">gender</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'F'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> competition</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">idsport</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">18<br/></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">score </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">in</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'Gold'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'Silver'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'Bronze'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)<br/></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">group</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">by</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> person</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">gender</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">score</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

And I got this out

stackoverflow ingles.png

By adding the person.birthdate field instead of leaving 18 records of the 18 people who have a medal, I'm going to many more records.

Apart from that, I still have to draw the average age with SYSDATE and TRUNC that I try in many ways but I do not get it.

mas overglow ingles.png

On the other hand I can not get the average of the ages but not with a simple query like this

select

trunc( avg( sysdate - to_date( person.birthdate, 'DD/MM/YYYY') ) / 365 ) trunc_diff_years

from person

give me this error

ORA-01839: date not valid for month specified

01839. 00000 -  "date not valid for month specified"

Something light?

8886da1b-b96f-46a0-ab95-a450d345a825
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 11, 2019 9:09AM

    It is not clear if BIRTHDATE is MM/DD.YYYY or DD/MM/YYYY; which is why it is Worst Practice to store any date in STRING

    8886da1b-b96f-46a0-ab95-a450d345a825
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,026 Red Diamond
    edited May 11, 2019 8:31AM

    You completely misunderstand dates. Column person.birthdate data type is DATE. TO_DATE first parameter is string. So when you issue:

    to_date( person.birthdate, 'DD/MM/YYYY')

    Oracle is implicitly converting person.birthdate to string using session NLS_DATE_FORMAT and then converts resulting string to date using format DD/MM/YYYY. Therefore, if session NLS_DATE_FORMAT is not DD/MM/YYYY you will get error or even worse wrong result. Anyway, use:

    select  trunc(avg(sysdate - person.birthdate) / 365 ) trunc_diff_years

      from  person

    /

    SY.

    8886da1b-b96f-46a0-ab95-a450d345a825
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,026 Red Diamond
    edited May 11, 2019 8:37AM

    Ah, I missed column is CHAR(10) - very bad design. Then check column and I bet some strings don't follow DD/MM/YYYY format or most likely are bad date strings like '31/04/2019':

    SQL> select to_date('31/04/2019','dd/mm/yyyy') from dual;

    select to_date('31/04/2019','dd/mm/yyyy') from dual

                   *

    ERROR at line 1:

    ORA-01839: date not valid for month specified

    SQL>

    SY.

    8886da1b-b96f-46a0-ab95-a450d345a825
  • 8886da1b-b96f-46a0-ab95-a450d345a825
    edited May 11, 2019 9:17AM

    The brithdate field in the database is a char (10 byte) and is stored as DD / MM / YYY, how can I get the average of the ages?

    And the rest of the query? They see something that is wrong?

    The design I did not do, it's an exercise at the university, maybe that design makes me give myself so many problems.

  • 8886da1b-b96f-46a0-ab95-a450d345a825
    edited May 11, 2019 9:17AM

    The brithdate field in the database is a char (10 byte) and is stored as DD / MM / YYY, how can I get the average of the ages?

    And the rest of the query? They see something that is wrong?

    @The design I did not do, it's an exercise at the university, maybe that design makes me give myself so many problems.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 11, 2019 10:04AM
    8886da1b-b96f-46a0-ab95-a450d345a825 wrote:The brithdate field in the database is a char (10 byte) and is stored as DD / MM / YYY, how can I get the average of the ages?

    And the rest of the query? They see something that is wrong?

    The design I did not do, it's an exercise at the university, maybe that design makes me give myself so many problems.

    post COPY & PASTE results running SQL below to validate contents of BIRTHDATE column

    SELECT COUNT(*) FROM PERSONS WHERE TO_DATE('1066-01-01','YYYY-MM-DD') < TO_DATE(BIRTHDATE,'DD/MM/YYYY');

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    edited May 11, 2019 10:35AM

    Hi,

    As others have pointed out, there is some invalid data in the person.birthdate column.  That's to be expected when dates are stored in a CHAR column, or any data type except DATE (or TIMESTAMP).  Given that you are stuck with the CHAR data type, you have to decide how to deal with invalid data, e.g., you might decide to ignore persons who have invalid birthdates.  How to determine if a string can be converted to a DATE or not depends on your Oracle version.  (A whole bunch of things depend on your Oracle version, so always say what version you're using, e.g. 12.2.0.1.0, whenever you post a question.)  Starting in Oracle 12.2, you can use the "DEFAULT … ON CONVERSION ERROR" option in TO_DATE.  This thread:

    shows a couple of other ways that work in earlier versions as well.

    Besides that, I see another issue.

    Your instructions say to compute an average age "for the different modalities of 'Artistic Gymnastics'".  I interpret that to mean the output should have one row of output for each modality, whatever a "modality" is.  (Maybe "ball", "clubs" and "freehand" are different modalities.  My guess is that there is something in the competition table. perhaps competition_id, that determines the modality.)  At any rate, to get one row per modality, you want to

    GROUP BY  modality

    (substitute the right expression for modality), but

    GROUP BY  person.gender, person.birthdate, score.score

    produces a row of output for each distinct combination of gender, birthdate and score, as you posted in your first message.

    You don't need to get a separate average for each gender; you're specifying that all the rows will have gender='F'.  (However, including gender in the GROUP BY clause won't change the results.)

    You don't want to get a separate average age for each birthdate.  Every person with the same birthdate will necessarily have the same age,  It would be silly to compute the average of things that you know are all equal.

  • 8886da1b-b96f-46a0-ab95-a450d345a825
    edited May 11, 2019 11:43AM

    I attached the image with the result

    ORA-01839: date not valid for month specified

    01839. 00000 -  "date not valid for month specified"

    *Cause:   

    *Action:

    foro oraclae.png

    I also attach another image with the data of the person table and the birthdate field

    mas oracle 2.png

  • 8886da1b-b96f-46a0-ab95-a450d345a825
    edited May 11, 2019 11:49AM

    The version I use is the Oracle Database 11g Express Edition Release 11.2.0.2.0.

    With regard to artistic gymnastics, that type of sport I get with the idsport when idsport = 18 is that it is a type of artistic gymnastics.
    I have to get 3 columns in the query:
    A column with the women who have participated and won a medal in the competitions with idspor = 18 and then you see a third column with the average age of the girls that meet that condition.
    One of the problems I have is not being able to use join and having to use equalities ...
    I think I'm close to the solution but I do not get it.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 11, 2019 12:00PM

    post COPY & PASTE results running SQL below to inspect contents of BIRTHDATE column

    SELECT DUMP(BIRTHDATE) FROM PERSONS ;