Saturday, April 15, 2006

SECTION 3 LESSON 4
1. 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 *
from f_customers,f_orders
where f_customers.id=f_orders.cust_id(+);

2. 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 d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id=d.department_id(+);

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

----select d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id(+)=d.department_id;

4. 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 (+);
b. SELECT e.first_name,e.last_name, d.department_name, e.department_id

FROM employees e, departments d
WHERE e.department_id = d.department_id;
c. WHERE e.position >= p.highest and<=p.lowest;
d. SELECT e.employee_id, e. last_name, d. location_id

FROM employees e, departments d
WHERE e.department_id = d.department_id;

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

----select dc.cd_number,dc.title,dt.cd_number
from d_cds dc,d_track_listings dt
where dc.cd_number(+)=dt.cd_number;