Answer to Question #273024 in Databases | SQL | Oracle | MS Access for Aliyah

Question

Identify the errors and re‐write the correct code: (10 MARKS)

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

					\\ "declare" error
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	    \\ sign ";" error

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;
                        \\ "end" error

correct code:

declare

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;

end;