2 Replies Latest reply: Jan 24, 2012 11:20 AM by Frank Kulash RSS

    Problem figuring out the difference between SQL:1999 and the Traditional


      I'm currently working on a lab assignment for class.

      Assignment is located here:

      The assignment states to write 3 queries in both the SQL:1999 version and the SQL Traditional (older version)

      I didn't read that part, and so I created 3 queries and I don't know which ones are 1999 and which ones are traditional.

      I would really appreciate it if someone helps me sort this problem out

      So overall I need to have 6 queries in total. 3 for the SQL 1999 version and 3 for the SQL traditional (older) version


      SELECT e.last_name, e.job_id, e.department_id, d.department_name
      FROM employees e JOIN departments d
      ON (e.department_id = d.department_id)
      JOIN locations l
      ON (d.location_id = l.location_id)
      WHERE LOWER(l.city) = 'toronto';


      SELECT e.last_name, d.department_name
      FROM employees e, departments d
      WHERE e.department_id = d.department_id (+) AND last_name LIKE 'G%';


      SELECT e.last_name "Employee", e.employee_id "Emp#", m.last_name "Manager", m.employee_id "Mgr#"
      FROM employees e INNER JOIN employees m
      ON e.manager_id = m.employee_id
      AND UPPER(e.last_name) LIKE 'G%';


      Edited by: 909427 on Jan 20, 2012 7:40 PM
        • 1. Re: Problem figuring out the difference between SQL:1999 and the Traditional
          Explain what you need with other words.

          By SQL:1999 do you mean ANSI? the first and the third queries you listed were ANSI using the ANSI join syntax. The second query used the traditional method of performing the join in the WHERE clause using the Oracle-specific "(+)" to indicate an outer join.

          Do you merely want to have 2 versions of each SQL, ANSI and not ANSI? Simply recode the query to use the other join syntax if that is the case.
          • 2. Re: Problem figuring out the difference between SQL:1999 and the Traditional
            Frank Kulash
            Welcome to the forum!

            I'm pretty sure that, by "SQL:1999" the instructions mean ANSI notation (that is, using the keywords JOIN and ON in the FROM clause), and by "traditional" they mean the older notation, where the FROM clause only contains a comma-separated list of table names (perhaps with aliases), and all the join conditions are in the WHERE clause. As Riedelme said, Queries 1) and 3) as you posted them are already written the "SQL:1999" way, and 2) is already the "traditional" way.

            Be careful to follow all the instructions, including those about upper- (ABC...) and lower-case (abc ...) letters.

            It looks like this assignment is based on the hr.employees table that Oracle provides for exercises like this. Note that not every employee in that table has a manager, similar to the way that not every employee has a department.

            This question concerns the SQL language, not the SQL*Plus front end. In the future, you'll get better response if you post questions like this on the SQL and PL/SQL Forum:
            The same account you created to post this question will work for either forum.