Question

(Oracle SQL Coding)

1.Display the city (Locations Table), department_name (Departments Table), location_ID (Locations Table), and department_ID (Departments Table) for departments 10, 20, and 30 for the city of Seattle. Use tables Departments and Locations. Use a traditional (proprietary) join.

2.Recreate the query from question 1 to use a Join (ANSI) method of joining.

Answer 1

Below is the code to join tables Locations and Departments to get the result. Both tables are joined on common attributes.

Proprietary Join

SELECT l.city, d.department_name, l.location_ID
FROM Locations AS l, Departments AS d
WHERE d.department_ID IN (10,20,30);

ANSI Join

SELECT l.city, d.department_name, l.location_ID
FROM Locations AS l
INNER JOIN Departments AS d
l.location_ID = d.location_ID
WHERE d.department_ID IN (10,20,30);