4 Replies Latest reply: Apr 2, 2013 6:45 AM by Frank Kulash RSS

    Relational Division Problem

    1000105
      d

      Edited by: 997102 on Apr 2, 2013 4:40 AM
        • 1. Re: Relational Division Problem
          Frank Kulash
          Hi,

          Welcome to the forum!

          Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Simplify the problem if possible. For example, if you're real problem involves 7 tables, but you can show what you don't understand with just 3 tables, then post a problem involving only those 3 or 4 tables. Explain that you really have 7 tables, so people will post a solution that can easily be adapted to 7 tables.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Relational Division Problem
            1000105
            x

            Edited by: 997102 on Apr 2, 2013 4:40 AM
            • 3. Re: Relational Division Problem
              1000105
              "Find all employees whose job history includes sales rep and sales manager.


              SELECT * FROM Employees WHERE Employee_ID IN (
              SELECT Employee_ID
              FROM Job_History JH1
              WHERE NOT EXISTS
              (SELECT *
              FROM Jobs
              WHERE job_id in ('SA_REP', 'SA_MAN') AND
              NOT EXISTS
              (SELECT *
              FROM Job_History JH2
              WHERE JH1.Employee_ID = JH2.Employee_ID
              AND JH2.Job_ID = Jobs.Job_ID))); "

              i found this example but it doesnt work with my table because job_id is numbers.

              any help would be great.
              Thanks
              • 4. Re: Relational Division Problem
                Frank Kulash
                Hi,

                That's the idea for the INSERT statements. Don't forget to post
                <ul><li>CREATE TABLE statements
                <li>Results you want from the given data
                <li>Explanation of how you get those results from that data
                <li>Database version</ul>
                997102 wrote:
                INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
                VALUES (108, 'WILL','SMITH, 'WILL.SMITH@GMAIL.COM','075356215672', '19-MAY-06',207, 36500, '', 87, 27)
                >
                Test (and, if necessary, correct) your statements before you post them. Make sure, for example, that quotes are balanced.

                Do not use a VARCHAR2 value, such as '19-MAY-06' to represent a date. Use a DATE column, and insert DATE values, such as the value returned by TO_DATE.

                It's better if you can simplify the number of columns. For example, you may really need to include all the columns from the employees table in the output, but do they all really play some role in this problem? It looks like only employee_id, and maybe department_id, are really necessary to show what you need to do. It might be helpful to include one other column, such as first_name, just to show how the extra columns need to be included.
                If you already have INSERT statements like this handy, then we can work around the irrelevant columns. Just make sure the INSERT statements you post actually work.
                "Find all employees whose job history includes sales rep and sales manager.

                SELECT * FROM Employees WHERE Employee_ID IN (
                SELECT Employee_ID
                FROM Job_History JH1
                WHERE NOT EXISTS
                (SELECT *
                FROM Jobs
                WHERE job_id in ('SA_REP', 'SA_MAN') AND
                NOT EXISTS
                (SELECT *
                FROM Job_History JH2
                WHERE JH1.Employee_ID = JH2.Employee_ID
                AND JH2.Job_ID = Jobs.Job_ID))); "
                Never write, let alone post, unformattted code. Indent the code to show the sub-queries, and the major clauses of each. For example:
                SELECT  * 
                FROM      employees 
                WHERE      employee_id IN 
                     (
                         SELECT  employee_id 
                         FROM    job_history     jh1   ...
                Use \
                 tags, as explained in the forum FAQ {message:id=9360002}, when posting any formatted text.
                
                i found this example but it doesnt work with my table because job_id is numbers.
                Just use numbers where the example above uses strings.  For example:
                WHERE job_id IN (200, 201)
                any help would be great.
                Thanks