This discussion is archived
2 Replies Latest reply: Jan 24, 2012 9:20 AM by Frank Kulash RSS

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

912430 Newbie
Currently Being Moderated
Hey,

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

Assignment is located here:
http://hal.cs.camosun.bc.ca/~weston/comp155/tyo/Lab3.html

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

1.)

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';

2.)

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

3.)

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%';


Thanks

Edited by: 909427 on Jan 20, 2012 7:40 PM
  • 1. Re: Problem figuring out the difference between SQL:1999 and the Traditional
    riedelme Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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:
    SQL and PL/SQL
    The same account you created to post this question will work for either forum.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points