20 Oracle Nonequijoins and Outer Joins

1.  Create a join based on the cost of the event between the DJs on Demand tables D_EVENTS and D_PACKAGES. Show the name of the event and the code for each event.

Nonequi join only:

SELECT  d_events.name, d_packages.code

FROM d_events, d_packages

WHERE(d_events.cost BETWEEN d_packages.low_range AND d_packages.high_range);

Above is OK if all the events have cost covered in available ranges.

Nonequi along with left outer equivalent in oracle:

SELECT  d_events.name, d_packages.code

FROM d_events, d_packages

WHERE  d_events.cost >= d_packages.low_range (+) AND d_events.cost <= d_packages.high_range(+);

In this case, if I change cost out of available range, even then event is not skipped.

2.  Using the Oracle database, create a query that returns the employee last name, salary, and job-grade level based on the salary. Select the salary between the lowest and highest salaries.

I am assuming that ranges in job_grades cover all employees, I only need simple nonequi join here.

SELECT employees.last_name, employees.salary, job_grades.grade_level

FROM employees,job_grades

WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

3.  What condition requires the creation of a nonequijoin?

When there is no exact match (=) between columns of two tables, but still there is a relation hidden.

4.  Rewrite the following nonequijoin statement using the logical condition operators (AND, OR, NOT): WHERE a.ranking BETWEEN g.lowest_rank AND g.highest_rank

WHERE  a.ranking >= g.lowest_rank AND a.ranking <= g. highest_rank

5. How do you know when to use a table alias and when not to use a table alias?

·         Alias for table becomes must when query targets recursive relationships. -Must

·         If two columns have similar named columns and I want to identify them separately, even though I can use table full name, but alias is helpful. Alias helps to identify table with smaller word.- good to do

·         But once alias is used for table, it must have to be used throughout.--must take care of

6.  What kind of join would you use if you wanted to find data between a range of numbers?

Nonequi join.

7.  You need to produce a report for Global Fast Foods showing customers and orders. A customer must be included on the report even if the customer has had no orders.

SELECT f_customers.first_name ||' '|| f_customers.last_name "Customer Name", f_orders.order_number, f_orders.order_total, f_orders.order_date

FROM f_customers, f_orders

WHERE  f_customers.id = f_orders.cust_id(+);

This is oracle proprietary equivalent of LEFT OUTER JOIN

8.  Create a query of the Oracle database that shows employee last names, department IDs, and department names. Include all employees even if they are not assigned to a department.

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id(+);

This is oracle proprietary equivalent of LEFT OUTER JOIN

9.  Modify the query in problem 2 to return all the department IDs even if no employees are assigned to them.

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id(+) = departments.department_id;

This is oracle proprietary equivalent of RIGHT OUTER JOIN

10. There are one or more errors in each of the following statements. Describe the errors and correct them.

a.  WHERE e.department_id(+) = d.department_id (+);

ORA-01468: a predicate may reference only one outer-joined table

There is no direct oracle equivalent for FULL OUTER JOIN.

FIX:

WHERE e.department_id  = d.department_id (+);

WHERE e.department_id(+) = d.department_id;

If I do

WHERE employees.department_id = departments.department_id(+) OR

employees.department_id(+) = departments.department_id;

I will still get

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Fix2 A set operator:

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id(+)

UNION

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id(+) = departments.department_id ;

b.  SELECT e.employee id, e. last name, d. location id

FROM employees, departments

WHERE e.department_id = d.department_id(+);

·         Column names are wrong,

·         Table alias is used but not assigned to tables.

FIX:

SELECT e.employee_id, e.last_name, d.location_id

FROM employees e , departments d

WHERE e.department_id = d.department_id(+);

11. Create a query that will show all CD titles and song IDs in the DJs on Demand database even if there is no CD number in the track-listings table.

SELECT d_cds.title , d_track_listings.song_id

FROM  d_cds, d_track_listings

WHERE d_cds.cd_number = d_track_listings.cd_number(+) ;

12. How many times has someone asked you: “What do you want to be when you grow up?” For most of us, the first thing that comes to mind is something like business manager, engineer, teacher, game designer, doctor, scientist, computer programmer, or accountant -- all pretty much traditional career choices. Have you ever thought about working in an odd job or nontraditional career? There are people who are professional shoppers for busy executives, directors of zoos, recipe designers, insecticide chemists, golf-course designers, and turf managers. Picture yourself in a dream job or nontraditional career doing something that you think would be interesting, life fulfilling, and profitable.

Use Internet resources to explore your idea. Write a brief description of the job to share with the class.

This is out of scope for current course at virtual college.