Results 1 to 3 of 3

Thread: SQL for query

  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: SQL for query

    Hitting a snag.
    For each insured with the last name Obrien or Johnson, arranged by insured_ID, list Insured_ID, first and last names (may be concatenated), date of birth and social security number.
    Should look something like this:


    Code:
    SQL> SELECT INSURED_ID, UPPER(MORTAL.LAST_NAME), UPPER(MORTAL.FIRST_NAME),
      2  MORTAL.DOB, MORTAL.SSN
      3  FROM INSURED
      4  JOIN MORTAL ON INSURED.MORTAL_ID = MORTAL.MORTAL_ID
      5  WHERE MORTAL.LAST_NAME LIKE 'O%'
      6  OR MORTAL.LAST_NAME LIKE 'J%'
      7  ORDER BY INSURED_ID;
    
    INSURED_ID UPPER(MORTAL.LAST_NAME)             UPPER(MORTAL.FIRST_NAME)  DOB       SSN
    ---------- ----------------------------------- ------------------------- --------- ---------
           605 JOHNSON                             TAMARA                    31-JAN-72 781910484
           606 JOHNSON                             CHARLES                   22-FEB-95 810385495
           607 JOHNSON                             RANDY                     22-JUN-72 381780655
           616 OBRIEN                              BRUCE                     11-FEB-69 354331243
           617 OBRIEN                              MEGAN                     01-OCT-95 215879038
           618 OBRIEN                              MELINDA                   16-DEC-70 521908316
    what Ive tried:
    Code:
    SQL> SELECT FIRST_NAME, LAST_NAME, INSURED.INSURED_ID
      2  FROM MORTAL
      3  JOIN INSURED ON MORTAL.MORTAL_ID = INSURED.MORTAL_ID
      4  WHERE LAST_NAME LIKE 'O%'
      5  OR LAST_NAME LIKE 'J%'
      6  ORDER BY INSURED_ID;
    But this teacher seems to abhor the 'LIKE operator. So it must be an 'UPPER' right?
    Code:
    SQL> SELECT INSURED_ID, UPPER(MORTAL.LAST_NAME), UPPER(MORTAL.FIRST_NAME),
      2  MORTAL.DOB, MORTAL.SSN
      3  FROM INSURED
      4  JOIN MORTAL ON INSURED.MORTAL_ID = MORTAL.MORTAL_ID
      5  WHERE MORTAL.LAST_NAME = 'JOHNSON'
      6  OR MORTAL.LAST_NAME = 'OBRIEN'
      7  ORDER BY INSURED_ID;
    
    INSURED_ID UPPER(MORTAL.LAST_NAME)             UPPER(MORTAL.FIRST_NAME)  DOB       SSN
    ---------- ----------------------------------- ------------------------- --------- ---------
           606 JOHNSON                             CHARLES                   22-FEB-95 810385495
           617 OBRIEN                              MEGAN                     01-OCT-95 215879038
    so not in primary declaration nor in WHERE clause:

    Code:
    SQL> SELECT INSURED_ID,MORTAL.LAST_NAME, MORTAL.FIRST_NAME, MORTAL.DOB,
      2    MORTAL.SSN
      3    FROM INSURED
      4    JOIN MORTAL ON INSURED.MORTAL_ID = MORTAL.MORTAL_ID
      5    WHERE MORTAL.LAST_NAME = UPPER('JOHNSON')
      6   OR MORTAL.LAST_NAME = UPPER('OBRIEN')
      7   ORDER BY INSURED_ID;
    
    INSURED_ID LAST_NAME                           FIRST_NAME                DOB       SSN
    ---------- ----------------------------------- ------------------------- --------- ---------
           606 JOHNSON                             Charles                   22-FEB-95 810385495
           617 OBRIEN                              Megan                     01-OCT-95 215879038
    What am I doing wrong?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT insured_id, 
           mortal.last_name, 
           mortal.first_name, 
           mortal.dob, 
           mortal.ssn 
    FROM   insured 
           join mortal 
             ON insured.mortal_id = mortal.mortal_id 
    WHERE  Upper(mortal.last_name) = 'JOHNSON' 
            OR Upper(mortal.last_name) = 'OBRIEN' 
    ORDER  BY insured_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    SELECT insured_id, 
           mortal.last_name, 
           mortal.first_name, 
           mortal.dob, 
           mortal.ssn 
    FROM   insured 
           join mortal 
             ON insured.mortal_id = mortal.mortal_id 
    WHERE  Upper(mortal.last_name) = 'JOHNSON' 
            OR Upper(mortal.last_name) = 'OBRIEN' 
    ORDER  BY insured_id;
    Thanks, Anacedent, I can't believe I had 'UPPER' in the wrong place, gesh.....what i get for being half asleep....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •