Databases | SQL | Oracle | MS Access Answers

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)

Discuss the various PL/ SQL scalar data types available to store specific kinds of data with the use of examples. (10 marks)

Write a program that will ask for a price. If the price is greater than 1000, compute a 10%

discount from the original price. Display the computed discount.

1.     Write a PL/SQL code to print all tables up to a number fetched from a table using implicit cursors.

9.

Find the number of videos published between the years 2010 & 2016.

Note:

• Sort the output in the ascending order of the year
• Keep the year in the integer format

Expected Output Format:

yearno_of_videos......

 Consider the transactions:

T₁: R₁(A), W₁(B)

and

T₂: R₂(A), W₂(A), W₂(B)

Suppose we insert a pair of lock and unlock commands for each element of the database that the transactions can access.

• Explain how many concurrent execution schedules, of the two transactions, follow the 2PL protocol.
• Explain whether there are any serializable schedules of concurrent execution of the two transactions that do not follow the 2PL protocol.

Check whether the given schedules are conflict serializable or not

 S1 : R2(B)W2(A)R1(A)R3(A)W1(B)W2(B)W3(B)

S2:R3(Y)W3(Z)R1(X)W1(X)W3(Y)W3(Z)R2(Z)R1(Y)W1(Y)R2(Y)W2(Y)

Create a package which contains function to ask the user to enter the student’s marks and determine the GRADE according to the following criteria.

>90 A

>80AND <90 B

>70AND <80 C

>60 AND<70 D

<60 F