...

View Full Version : Help with Oracle SQL*Plus Query assignment



tridentspk
01-21-2012, 03:37 AM
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

Old Pedant
01-21-2012, 05:06 AM
You posted in the MySQL forum, so expect your post to be moved to the "other databases" forum soon.

But in any case, there aren't many Oracle people who hang out around here.

SQL 1999 seems ancient, to me. I would think the *IT* would be the "traditional" SQL. Sounds like Oracle is still a bit out of date compared to most products,

*IF* I were guessing, I'd say that the use of the keyword JOIN means the newer version and the older version is just done using WHERE clauses.

I know that many many many years ago we used to use *= for a LEFT JOIN.

That is


SELECT ...
FROM a *= b WHERE a.x = b.x

was really old notation for


SELECT ...
FROM a LEFT JOIN b ON a.x = b.x

But that's more like 1985 to 1990 or so, in the SQL Server world. Much older than SQL 1999.

You will pardon me, I hope, for saying this seems like a useless exercise. Nobody in their right mind would use a SQL dialect older than 1999 for any real world work today.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum