Databases | SQL | Oracle | MS Access Answers

Which conditional statement, IF‐THEN‐ELSIF or CASE statement is more useful in creating

conditional programs? Defend your choice

Which conditional statement, IF‐THEN‐ELSIF or CASE statement is more useful in creating conditional programs? Defend your choice.

Explain the role and purpose of the two codes provided below.

Create or Replace Trigger Purchase_Entry

After Insert or Update of Purchase_Amt on PURCHASE_AGREEMENT

For Each Row

Begin

  if(:New.Purchase_Amt < 0) Then

   RAISE_APPLICATION_ERROR(‐20100,  

     'Cannot enter a purchase agreement amount less than zero.');

  end if;

End;

insert into PURCHASE_AGREEMENT values (559, '27 May 2020', ‐1, 12350,'C122',1)

Your colleague, John, has been struggling with a PL/SQL query

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;

Explain the role and purpose of the two codes provided below. (5 marks)

Create or Replace Trigger Purchase_Entry

After Insert or Update of Purchase_Amt on PURCHASE_AGREEMENT

For Each Row

Begin

   if(:New.Purchase_Amt < 0) Then

      RAISE_APPLICATION_ERROR(‐20100,

            'Cannot enter a purchase agreement amount less than zero.'); 

   end if;

End;

insert into PURCHASE_AGREEMENT values (559, '27 May 2020', ‐1, 12350,'C122',1)

continuous....Write the code solution using an explicit cursor with a simple Loop. Make sure to use your own variables. (10 marks)

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;

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;

Define exception handling in your own words then justify, using at least four examples of pre‐ defined system exceptions. (10 marks)

To accomplish automated memory management, Oracle uses two initialisation parameters. Explain the two initialisation parameters used to accomplish memory management then provide an advantage of using automatic memory management over manual memory management (5 marks)