Important questions for PG:
1. Explain SQL Command.
2. Explain GRANT and REVOKE command.
3. Deffrentiate SQL V/s. SQL *Plus
4. What is snapshot. Explain in detail.
5. Explain function of SQL.
6. Explain Operator.
7. What is normalization. Explain in detail.
8.What is cluster ? Explain in details.
9. Explain Group by and Having clause.
10.Explain transaction control command.
11. Explain Redo log file in detail.
12. What is table space? Explain its types.
13. What is backup and recovery?
14. What is trigger? Explain.
15. What is cursor? Explain its types.
16. Explain Instance Architecture.
17. What is stored procedure? Explain with example.
18. Explain import and export.
19. Write down Dr. E. F. Ted Codd Rule.
20. Justify Oracle as RDBMS.

Create following table
Table name : cust_master
Fields: cust_no (p), name, address, city

Table name : product_master
Fields: pro_no (p), p_name, qty, saleprice, costprice

Table name : purchase
Fields: cust_no (f), pro_no (f), p_qty, amount

and solve following queries:

(A) List the name of all customer who purchase "computer"
(B) List the p_name, p_qty and amount of all product purchase by "yash"
(C) List the non-moving product_name
(D) List the cust_name who purchase product of more thatn Rs. 1000

Solution:
create table cust_master
(cust_no varchar2(6) primary key,
name varchar2(25),
adderss varchar2(50),
city varchar2(15));

create table product_master
(pro_no varchar2(6) primary key,
p_name varchar2(15),
qty number(4),
saleprice number,
costprice number);

create table purchase
(cust_no varchar2(6) references cust_master(cust_no),
pro_no varchar2(6) references product_master(pro_no),
p_qty number(4),
amount number);


(A) List the name of all customer who purchase "computer"
      select name from cust_master where cust_no in (select cust_no from purchase where 

      pro_no in (select pro_no from product_master where     p_name="computer"))

(B) List the p_name, p_qty and amount of all product purchase by "yash"
      select d.p_name, p.p_qty, p.amount from product_master d, purchase p where

      p.pro_no=d.pro_no and p.cust_no in (select cust_no from cust_master where name="yash")

(C) List the non-moving product_name
      select p_name from product_master where pro_no not in (select distinct pro_no from 

      purchase)

(D) List the cust_name who purchase product of more thatn Rs. 1000
       select name from cust_master where cust_no in (select cust_no from purchase 

       where amount>1000)

Write an update trigger on client_master table. The system keep records are being updated. The old values of updated records should be added in the temp table.

Table : temp
Fields: Client_no, name, operation, o_date, userid

SOLUTION:

CREATE OR REPLACE TRIGGER trg_client_master
BEFORE UPDATE ON client_master
FOR EACH ROW

BEGIN
   INSERT INTO TEMP VALUES(:old.Client_no, :old.name, 'UPDATE', sysdate, UID);
END;
/

Write a PL/SQL block to display following series

1 1 2 3 5 8 ..... N

DECLARE
term1 number;
term2 number;
term3 number;
n number;
BEGIN
  n:=&n;
  term1:=1;
  term2:=0;
  term3:=1;
  FOR i in 1..n
  LOOP
    dbms_output.put_line(term3||' ');
    term1:=term2;
    term2:=term3;
    term3:=term1+term2;
  END LOOP;
END;

ORACLE questions paper

Example of trigger:

There are three tables: studinfo, result and update_studinfo. If you insert data into studinfo then that data will be inserted into result with calculation automatically. If you update any data then new data will be inserted into result and old data will be inserted into update_studinfo. If you delete any data then old data will be inserted into update_studinfo table.

You can do that in following way:

create table studinfo
(studid varchar2(3),
name varchar2(15),
php number(3),
oracle number(3),
vb number(3));


create table result
(studid varchar2(3),
name varchar2(15),
php number(3),
oracle number(3),
vb number(3),
total number(3),
per number(6,2),
operation varchar2(7),
op_date date);


create table update_studinfo
(studid varchar2(3),
name varchar2(15),
php number(3),
oracle number(3),
vb number(3),
operation varchar2(7),
op_date date);


create or replace trigger trgstudinfo
before insert or update or delete
on studinfo
for each row
declare
total number;
per number(6,2);
begin
  if inserting then
    total:=:new.php+:new.oracle+:new.vb;
    per:=total/3;
    insert into result values(:new.studid,:new.name,:new.php,:new.oracle,:new.vb,total,per,'INSERT',sysdate);
  end if;

  if updating then
    total:=:new.php+:new.oracle+:new.vb;
    per:=total/3;
    insert into result values(:new.studid,:new.name,:new.php,:new.oracle,:new.vb,total,per,'UPDATE',sysdate);
    insert into update_studinfo values(:old.studid,:old.name,:old.php,:old.oracle,:old.vb,'UPDATE',sysdate);
  end if;

  if deleting then
     insert into update_studinfo values(:old.studid,:old.name,:old.php,:old.oracle,:old.vb,'DELETE',sysdate);   
  end if;

end;
/