Answer to Question #273026 in Databases | SQL | Oracle | MS Access for rosebud21

Question

Assist John in understanding the different explicit cursors he can use.Using the scenario in Q2, answer the following questions.(20Marks)

Your colleague John has been struggling with a PL/SQL query that should display the CustomerID and the House Number, including the Materials used in the construction of the townhouses.Identify the errors and re‐write the correct code: set serveroutput on c_id CUSTOMER.cust_id%Type; t TOWNHOUSE.house_num%Type; m_name MATERIALS.material_name%Type;cursor info is select cu.cust_id,t.house_num, m.material_name from CUSTOMER cu, TOWNHOUSE t, MATERIALS m, PURCHASE_AGREEMENT pa where cu.cust_id = pa.cust_id and t.house_num = pa.house_num and m.material_id = pa.material_id begin for rec in info  c_id:=rec.cust_id;  t:=rec.house_num;  m_name:=rec.material_name;  dbms_output_put_line('CUSTOMER ID:   ' || c_id || ', ' || chr(13) ||'HOUSE NUM:     ' || t || chr(13) || 'MATERIALS: ' || m_name);  dbms_output.put_line('‐‐‐‐‐‐‐'); end loop;

Answer 1

There is a problem with the SELECT statement.

The SELECT statement can be:

select cu.cust_id, t1.house_num, m.material_name

from CUSTOMER cus, TOWNHOUSE t1, MATERIALS m, PURCHASE_AGREEMENT pa

where cus.cust_id = pa.cust_id

and t1.house_num = pa.house_num

and m.material_id = pa.material_id