Slip no-1
Q3. Consider the following entities and their relationships. [40]
Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many Constraint:
Primary key, prem_amt and maturity_amt should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will return total maturity amount of policies of a particular client.
Soluation:-
1)Create table client(cnoint primary key not null,cname char(27),addr char(28),birthdate date);
2)Create table policyinfo(pnoint primary key not null,des char(28),maturity_amtint,
prematurity_amt int,date1 date);
3)Create table client_policy(cnoint references client(cno),pnoint references policyinfo(pno));
SQL>desc client;
Name Null? Type
----------------------------------------- -------- ---------------------------
CNO NOT NULL NUMBER(38)
CNAME CHAR(23)
ADDR CHAR(28)
BIRTHDATE DATE
SQL>descpolicyinfo
Name Null? Type
----------------------------------------- -------- ---------------------------
PNO NOT NULL NUMBER(38)
DES CHAR(26)
MATURITY_AMT NUMBER(38)
PREMATURITY_AMT NUMBER(38)
DATE1 DATE
SQL>descclient_policy
Name Null? Type
----------------------------------------- -------- ---------------------------
CNO NUMBER(38)
PNO NUMBER(38)
SQL> select * from client;
CNO CNAME ADDR BIRTHDATE
---------- ----------------------- ---------------------------- ---------
1 raghavpune 09-JAN-98
2 aaravmumbai 09-FEB-99
3 shamalwagholi 04-MAR-97
SQL> select * from policyinfo;
PNO DES MATURITY_AMT PREMATURITY_AMT DATE1
---------- -------------------------- ------------ --------------- ------------------------------------------
11 bba 1200 2800 09-FEB-98
12 ba 1500 4450 04-MAR-88
13 bca 1900 4000 04-OCT-90
23 bba 1200 2300 01-JAN-20
24 ba 1900 4300 01-JAN-20
SQL> select * from client_policy;
CNO PNO
---------- ----------
1 11
2 12
3 13
SQL> create or replace function fr(xz in char)
2 return number as
3 sd number;
4 begin
5 select sum(policyinfo.maturity_amt) into sd
6 from client,policyinfo,client_policy
7 where client.cno=client_policy.cno
8 and policyinfo.pno=client_policy.pno
9 and cname=xz;
10 return sd;
11 end;
12 /
Function created.
Function calling
SQL> begin
2 dbms_output.put_line('output'||fr('shamal'));
3 end;
4 /
Output:-
total maturity amt1900
PL/SQL procedure successfully completed.
Write a cursor which will display policy date wise client details.
Soluation:-
SQL> declare
2 cursor q is select client.cno,cname,addr,birthdate,date1
3 fromclient,policyinfo,client_policy
4 whereclient.cno=client_policy.cno
5 andpolicyinfo.pno=client_policy.pno
6 order by date1;
7 q1q%rowtype;
8 begin
9 open q;
10 loop
11 fetch q into q1;
12 exit when q%notfound;
13 dbms_output.put_line('output:'||q1.date1||''||q1.cno||''||q1.cname||''||q1.
addr||''||q1.birthdate);
14 end loop;
15 close q;
16 end;
17 /
Output:-
output:04-MAR-88 2aarav mumbai 09-FEB-99
output:04-OCT-90 3shamal wagholi 04-MAR-97
output:09-FEB-98 1raghav pune 09-JAN-98
PL/SQL procedure successfully completed.
Slip no-2
Q3. Consider the following Item_Supplier database [40]
Item (itemno, itemname )
Supplier (supplier_No ,supplier_name, address, city )
Relationship between Item and Supplier is many-to-many with descriptive attribute
rate and quantity Constraints: itemno ,supplier_No primary key Create a RDB in 3NF
and write PL/SQL blocks in Oracle for the following:
Write function to print the total number of suppliers of a particular item
Soluation:-
SQL> create table item1(inoint primary key,iname char(27));
Table created.
SQL> create table supplier1(snoint primary key,sname char(28),addr char(28),cit
y char(29));
Table created.
SQL> create table its(inoint references item1(ino),snoint references supplier1
(sno));
Table created.
SQL>desc item;
Name Null? Type
----------------------------------------- -------- ----------------------------
INO NOT NULL NUMBER(38)
INAME CHAR(28)
SQL>desc supplier;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NOT NULL NUMBER(38)
SNAME CHAR(29)
ADDR CHAR(29)
CITY CHAR(29)
SQL>descits;
Name Null? Type
----------------------------------------- -------- ----------------------------
INO NUMBER(38)
SNO NUMBER(38)
SQL> insert into item1 values(1,'keybord');
1 row created.
SQL> insert into item1 values(2,'harddisk');
1 row created.
SQL> insert into item1 values(3,'keybord');
1 row created.
SQL> insert into item1 values(4,'moniter');
1 row created.
SQL> select * from item1;
INO INAME
------- ---------------------------
1 keybord
2 harddisk
3 keybord
4 moniter
SQL> insert into supplier1 values(11,'raghav','pune','pune');
1 row created.
SQL> insert into supplier1 values(12,'aarav','wagholi','pune');
1 row created.
SQL> insert into supplier1 values(13,'aarav','bd','mumbai');
1 row created.
SQL> insert into supplier1 values(14,'aarav','bd','mumbai');
1 row created.
SQL> insert into item1 values(5,'harddisk');
1 row created.
SQL> insert into supplier1 values(15,'patil','thane','mumbai');
1 row created.
SQL> select * from supplier1;
SNO SNAME ADDRCITY
11 raghavpunepune
12 aaravwagholipune
14 aaravbdmumbai
15 patil thanemumbai
SQL> insert into its values(1,11);
1 row created.
SQL> insert into its values(2,12);
1 row created.
SQL> insert into its values(3,13);
1 row created.
SQL> insert into its values(4,14);
1 row created.
SQL> insert into its values(5,15);
1 row created.
SQL> select * from its;
INO SNO
---------- ----------
1 11
2 12
3 13
4 14
5 15
create or replace function df(kl in char)
return number as
s number;
begin
select count(sname) into s from item1,supplier1,its
where item1.ino=its.ino
and supplier1.sno=its.sno
andiname=kl;
return s;
end;
/
Function created.
Function calling:-
declare
mn number(4);
begin
mn:= df('keybord');
dbms_output.put_line('total number of suppliers:' ||mn);
end;
/
Output:-
total number of suppliers:3
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on rate and
quantity less than or equal to zero. (Raise user defined exception and
give appropriate message)
Soluation:-
SQL> create or replace trigger fg
2 before insert or update
3 on its
4 for each row
5 begin
6 if(:new.rate<=0 or :new.quantity<=0) then
8 raise_application_error(-20007,'invalid');
9 end if;
10 end;
11 /
Trigger created.
Output:-
SQL> insert into its values(1,11,7,3);
1 row created.
SQL> insert into its values(2,12,-45,3);
insert into its values(2,12,-45,3)
*
ERROR at line 1:
ORA-20007: invalid
ORA-06512: at "SYSTEM.FG", line 4
ORA-04088: error during execution of trigger 'SYSTEM.FG'
SQL> insert into its values(3,13,45,-3);
insert into its values(3,13,45,-3)
*
ERROR at line 1:
ORA-20007: invalid
ORA-06512: at "SYSTEM.FG", line 4
ORA-04088: error during execution of trigger 'SYSTEM.FG'
SQL> insert into its values(4,14,45,3);
1 row created.
Slip no-3
Q3. Consider the following entities and their relationship. [40]
Newspaper (name,language , publisher , cost )
Cities (pincode , city, state)
Relationship between Newspaper and Cities is many-to-many with descriptive attribute
daily required Constraints: name and pincode primary key Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
Write a trigger which will fire before insert on the cities table which check
that the pincode must be of 6 digit.
(Raise user defined exception and give appropriate message).
Soluation:-
1)Create table newspaper(name char(28) primary key,lan char(28),publisher char(29),cost int);
2)Create table cities(pincodeint primary key,city char(29),state char(28));
3)Create table nc(name char(28) references newspaper(name),pincodeint references
cities(pincode));
SQL>desc newspaper
Name Null? Type
----------------------------------------- -------- -------------------------
NAME NOT NULL CHAR(29)
LAN CHAR(28)
PUBLISHER CHAR(29)
COST NUMBER(38)
SQL>desc cities
Name Null? Type
----------------------------------------- -------- -------------------------
PINCODE NOT NULL NUMBER(38)
CITY CHAR(28)
STATE CHAR(28)
SQL>descnc
Name Null? Type
----------------------------------------- -------- -------------------------
NAME CHAR(29)
PINCODE NUMBER(38)
SQL> select * from newspaper;
NAME LANPUBLISHER COST
----------------------------- --------------------------------------------------------------
sakal Marathi asd 3
timesof india English jfg 5
india express English hu 4
> select * from cities;
PINCODE CITY STATE
---------- ---------------------------- ----------------------------
413501 osmanabadmaharashtra
411014 punemaharashtra
400132 mumbaimaharashtra
124364 punemaharashtra
122365 punemaharashtra
764557 punemaharashtra
564534 punemaharashtra
7 rows selected.
SQL> select * from nc ;
NAME PINCODE
----------------------------- ----------
sakal 413501
times of india 411014
india express 400132
SQL> create or replace trigger t4
2 before insert or update
3 on cities for each row
4 begin
5 if(length(:new.pincode)<6 or length(:new.pincode)>6) then
6 raise_application_error(-20001,'ERROR::Pincode should be 6 digits');
7 end if;
8 end;
9 /
Trigger created.
Output:-
SQL> insert into cities values(124364,'pune','maharashtra');
1 row created.
SQL> insert into cities values(45565,'pune','maharashtra');
insert into cities values(45565,'pune','maharashtra')
*
ERROR at line 1:
ORA-20001: ERROR::Pincode should be 6 digits
ORA-06512: at "SYSTEM.T4", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T4'
SQL> insert into cities values(8976543,'pune','maharashtra');
insert into cities values(8976543,'pune','maharashtra')
*
ERROR at line 1:
ORA-20001: ERROR::Pincode should be 6 digits
ORA-06512: at "SYSTEM.T4", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T4'
Write a procedure to calculate city wise total cost of each newspaper
Soluation:-
SQL> create or replace procedure getcity as
2 cursor c1 is select cities.city,cost,newspaper.name from newspaper,cities,nc
3 where newspaper.name=nc.name
4 andcities.pincode=nc.pincode
5 order by city;
6 res c1%rowtype;
7 begin
8 open c1;
9 loop
10 fetch c1 into res;
11 exit when c1%notfound;
12 dbms_output.put_line(res.city||''||res.cost||''||res.name);
13 end loop;
14 close c1;
15 end;
16 /
Procedure created.
Output:-
SQL> execute getcity();
mumbai 4 india express
osmanabad 3 sakal
pune 5 times of india
PL/SQL procedure successfully completed.
Slip no-4
Q3 Consider the following entities and their relationships. [40]
Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many Constraint:
Primary key, prem_amt and maturity_amt should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will display all policy details having premium
amount less than 5000.
Soluation:-
SQL> create table client(cnoint primary key,cname char(23),addr char(28),birthd
ate date);
Table created.
SQL>desc client;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(38)
CNAME CHAR(23)
ADDR CHAR(28)
BIRTHDATE DATE
SQL> create table policyinfo(pnoint primary key,des char(26),maturity_amtint c
heck(maturity_amt>0),prematurity_amtint check(prematurity_amt>0),date1 date);
Table created.
SQL>descpolicyinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
DES CHAR(26)
MATURITY_AMT NUMBER(38)
PREMATURITY_AMT NUMBER(38)
DATE1 DATE
SQL> create table client_policy(cnoint references client(cno),pnointreferenc
espolicyinfo(pno));
Table created.
SQL>descclient_policy;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NUMBER(38)
PNO NUMBER(38)
SQL> insert into client values(1,'raghav','pune','09/jan/98');
1 row created.
SQL> insert into client values(2,'aarav','mumbai','09/feb/99');
1 row created.
SQL> insert into client values(3,'shamal','wagholi','04/mar/97');
1 row created.
SQL> select * from client;
CNO CNAME ADDR BIRTHDATE
---------- ----------------------- ---------------------------- ---------
1 raghavpune 09-JAN-98
2 aaravmumbai 09-FEB-99
3 shamalwagholi 04-MAR-97
SQL> insert into policyinfovalues(11,'bba',1200,2800,'09/feb/98');
1 row created.
SQL> insert into policyinfovalues(12,'ba',1500,4450,'04/mar/88');
1 row created.
SQL> insert into policyinfovalues(13,'bca',1900,4000,'04/oct/90');
1 row created.
SQL> select * from policyinfo;
PNO DES MATURITY_AMT PREMATURITY_AMT DATE1
---------- -------------------------- ------------ --------------- ---------
11 bba 1200 2800 09-FEB-98
12 ba 1500 4450 04-MAR-88
13 bca 1900 4000 04-OCT-90
SQL> insert into client_policyvalues(1,11);
1 row created.
SQL> insert into client_policyvalues(2,12);
1 row created.
SQL> insert into client_policyvalues(3,13);
1 row created.
SQL> select * from client_policy;
CNO PNO
---------- ----------
1 11
2 12
3 13
SQL> create or replace procedure getdet as
2 respolicyinfo%ROWTYPE;
3 cursor c2 is select * from policyinfo
4 where prematurity_amt<=5000;
5 begin
6 open c2;
7 loop
8 fetch c2 into res;
9 exit when c2%NOTFOUND;
10 dbms_output.put_line(res.pno||' '||res.des||' '||res.maturity_amt||' '||re
s.prematurity_amt||' '||res.date1);
11 end loop;
12 close c2;
13 end;
14 /
Procedure created.
Output:-
SQL> execute getdet();
11 bba 1200 2800 09-FEB-98
12 ba 1500 4450 04-MAR-88
13 bca 1900 4000 04-OCT-90
23 bba 1200 2300 01-JAN-20
24 ba 1900 4300 01-JAN-20
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on policy_info
having maturity amount less than premium amount. (Raise user defined exception
and give appropriate message)
Soluation:-
SQL> create or replace trigger t2
2 before insert or update
3 onpolicyinfo for each row
4 begin
5 if(:new.maturity_amt<:new.prematurity_amt) then
6 raise_application_error(-20003,'ERROR:m_amt should be greater than p_amt');
7 end if;
8 end;
9 /
Trigger created.
Output:-
SQL> insert into policyinfovalues(33,'dsw',1200,100,'09/feb/09');
1 row created.
SQL> insert into policyinfovalues(34,'dsw',1200,1400,'09/feb/09');
insert into policyinfo values(34,'dsw',1200,1400,'09/feb/09')
*
ERROR at line 1:
ORA-20003: ERROR:m_amt should be greater than p_amt
ORA-06512: at "SYSTEM.T2", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T2'
Slip no-5
Q3 Consider the following entities and their relationships. [40]
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many. Constraint:
Primary key, Price should not be null. Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
Write a function which will accept publication name from user and
display total price of books of that publication.
Soluation:-
SQL> select * from book;
BID BNAME ANAME PRICE PUBLICATION LNO
---------- ----------------------------- -----------------------------------------------------------------------------
20 dreams adds 340 sucesses 1
10 nature hgsds 360 sucesses 1
59 nature hgsds 360 sucesses 1
SQL> select * from library;
LNO LNAME LOCATIONLIBRARIAN NOB
----------------------------- ----------
1 dcmipuneraghav 12000
12 dnyandip mumbai aarav 22000
13 divy wagholi shamal 29000
SQL> create or replace function sf(j in char)
2 return number as
3 sd number;
4 begin
5 select sum(price) into sd from book,library
6 wherelibrary.lno=book.lno;
7 returnsd;
8 end;
9 /
Function created.
Fuction calling:-
SQL> begin
2 dbms_output.put_line('output:'||sf('sucesses'));
3 end;
4 /
Output:-
output:1060
PL/SQL procedure successfully completed.
2) Write a cursor which will display library wise book details.
(Use Parameterized Cursor)
Soluation:-
SQL> declare
2 cursor q is select library.lname,book.bid,bname,aname,price,publication from book,library
3 wherelibrary.lno=book.lno
4 order by lname;
5 q1q%rowtype;
6 begin
7 open q;
8 loop
9 fetch q into q1;
10 exit when q%notfound;
11 dbms_output.put_line('output:'||q1.lname||''||q1.bid||''||q1.bname||''||q1.
aname||''||q1.price||''||q1.publication);
12 end loop;
13 close q;
14 end;
15 /
Output:-
output:dcmi 20 dreams adds 340 sucesses
output:dcmi 59nature hgsds 360 sucesses
output:dcmi 10nature hgsds 360 sucesses
PL/SQL procedure successfully completed.
Slip no-6
Q3 Consider the following entities and their relationships. [40]
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many. Constraint:
Primary key, inv_amount should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will display details of employees invested
amount in “Mutual Fund”
Soluation:
1)Create table employee(eidint primary key,ename char(29),addr char(28));
2)Create table investment(inoint primary key,iname char(29),idatedate,iamtint,eidint,
constraintfk_employeeinvestmentforeign key(eid)references employee(eid));
SQL>desc employee
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL NUMBER(38)
ENMAE CHAR(25)
ADDR CHAR(27)
SQL>desc investment
Name Null? Type
----------------------------------------- -------- ----------------------------
INO NOT NULL NUMBER(38)
INAME CHAR(28)
IDATE DATE
IAMT NUMBER(38)
EID NUMBER(38)
SQL> select * from employee;
EID ENMAE ADDR
---------- ------------------------- ---------------------------
101 raghavpune
103 aaravwagholi
102 vijaymumbai
1 raghavpune
SQL> select * from investment;
INO INAME IDATE IAMT EID
---------- ---------------------------- --------- ---------- ----------
1 rahul 09-JAN-02 1200000 101
2 archana 02-MAR-05 1000000 102
3 pooja 04-MAR-09 9000000 103
6 xyz 26-NOV-16 27000 102
8 xyz 26-NOV-08 27000 102
78 xyz 26-NOV-22 27000 102
7 xyz 03-NOV-23 27000 102
89 xyz 09-NOV-23 27000 102
34 mutual fund 17-JAN-09 120000 1
33 mutual fund 17-JAN-09 1200000 101
10 rows selected.
SQL> create or replace procedure le as
2 cursor d is select employee.eid,enmae,addr,iname from employee,investment
3 whereemployee.eid=investment.eid
4 andinvestment.iname='mutual fund';
5 d1d%rowtype;
6 begin
7 open d;
8 loop
9 fetch d into d1;
10 exit when d%notfound;
dbms_output.put_line('output:'||d1.eid||''||d1.enmae||''||d1.add);
12 end loop;
13 close d;
14 end;
15 /
Procedure created.
Output:-
SQL> execute le();
output:1raghav pune
output:101raghav pune
PL/SQL procedure successfully completed.
Write a cursor which will display date wise investment details.
Soluation:-
SQL> declare
2 cursor y is select investment.ino,iname,idate,iamt,employee.eid
3 frominvestment,employee
4 whereemployee.eid=investment.eid
5 order by idate;
6 y1y%rowtype;
7 begin
8 open y;
9 loop
10 fetch y into y1;
11 exit when y%notfound;
12 dbms_output.put_line('output:'||y1.idate||''||y1.ino||''||y1.iname||''||y1.
iamt||''||y1.eid);
13 end loop;
14 close y;
15 end;
16 /
Output:-
output:26-NOV-088 xyz 27000 102
output:17-JAN-0933 mutual fund 1200000 101
output:17-JAN-0934mutual fund 120000 1
output:26-NOV-166xyz 27000 102
output:26-NOV-2278xyz 27000 102
output:03-NOV-237xyz 27000 102
output:09-NOV-2389xyz 27000 102
PL/SQL procedure successfully completed.
Slip no-7
Q3 Consider the following entities and their relationships. [40]
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price) T
he relationship between Bill and Menu is Many to Many with quantity as descriptive
attribute. Constraint: Primary key, price should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display menu details having price between 200 to 500
which were order on ‘Saturday’ .
Soluation:-
SQL> create table bill(bnoint primary key,dayvarchar(27),tnoint,totalint);
Table created.
SQL>desc bill
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NOT NULL NUMBER(38)
DAY VARCHAR2(27)
TNO NUMBER(38)
TOTAL NUMBER(38)
SQL> insert into bill values(1,'monday',23,123);
1 row created.
SQL> insert into bill values(2,'saturday',23,234);
1 row created.
SQL> insert into bill values(3,'saturday',21,45);
1 row created.
SQL> select * from bill;
BNO DAY TNO TOTAL
---------- --------------------------- ---------- ----------
1 monday 23 123
2 saturday 23 234
3 saturday 21 45
SQL> create table menu(dnoint primary key,d_descvarchar(29),price int);
Table created.
SQL>desc menu
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
D_DESC VARCHAR2(29)
PRICE NUMBER(38)
SQL> insert into menu values(11,'asd',234);
1 row created.
SQL> insert into menu values(12,'fsd',659);
1 row created.
SQL> insert into menu values(13,'jho',467);
1 row created.
SQL> select * from menu;
DNO D_DESC PRICE
---------- ----------------------------- ----------
11 asd 234
12 fsd 659
13 jho 467
SQL> create table bm(bnoint references bill(bno),dnoint references menu(dno));
Table created.
SQL>descbm
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NUMBER(38)
DNO NUMBER(38)
SQL> insert into bmvalues(1,11);
1 row created.
SQL> insert into bmvalues(1,12);
1 row created.
SQL> insert into bmvalues(2,13);
1 row created.
SQL> insert into bmvalues(2,12);
1 row created.
SQL> insert into bmvalues(3,12);
1 row created.
SQL> insert into bmvalues(3,13);
1 row created.
SQL> select * from bm;
BNO DNO
---------- ----------
1 11
1 12
2 13
2 12
3 12
3 13
6 rows selected.
SQL> create or replace procedure yu as
2 cursor f is select menu.dno,d_desc,price from bill,menu
3 where price between 200 and 500
4 and day='saturday'
5 andbill.bno=bm.bno
6 andmenu.dno=bm.dno;
7 f1f%rowtype;
8 begin
9 open f;
10 loop
11 fetch f into f1;
12 exit when f%notfound;
13 dbms_output.put_line('output:'||f1.dno||''||f1.d_desc||
14 end loop;
15 close f;
16 end;
17 /
Procedure created.
Output:-
SQL> execute yu();
output:13jho467
output:13jho467
PL/SQL procedure successfully completed.
2) Write a trigger which will fire before insert or update on Menu having price
less than or equal to zero. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger nj
2 before insert or update
3 on menu
4 for each row
5 begin
6 if(:new.price<=0) then
7 raise_application_error(-20004,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
Output:-
SQL> insert into menu values(23,'dde',23);
1 row created.
SQL> insert into menu values(87,'dde',-0);
insert into menu values(87,'dde',-0)
*
ERROR at line 1:
ORA-20004: enter more than 0
ORA-06512: at "SYSTEM.NJ", line 3
ORA-04088: error during execution of trigger 'SYSTEM.NJ'
Slip no-8
Q3 Consider the following entities and their relationships. [40]
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many.
Constraint: Primary key, fix_amt should be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will accept plan number from user and
display all the details of the selected plan
Soluatiion:-
SQL> create table plan11(pnoint primary key,pname varchar2(24),nooffreecallsint,
freecalltimetimestamp,famtint check(famt>0));
Table created.
SQL> create table cust11(cnoint primary key,cname varchar2(28),mnoint,pnoint,
constraint fk_plan11cust11 foreign key(pno)references plan11(pno));
Table created.
SQL> insert into plan11values(11,'ddsd',12,'09/jan/07 12:09:09',1200);
1 row created.
SQL> insert into plan11values(12,'ytti',22,'02/feb/03
11:05:07',1300);
1 row created.
SQL> insert into plan11 values(13,'kuio',23,'01/mar/0211:02:03',1400);
1 row created.
SQL> select * from plan11;
PNO PNAME NOOFFREECALLSFREECALLTIMEFAMT
11 ddsd 1209-JAN-07 12.09.09.000000 Pm 1200
12 ytti 2202-FEB-03 11.05.07.000000 AM 1300
13 kuio 2301-MAR-02 11.02.03.000000 Am 1400
SQL> insert into cust11 values(1,'priti',1223223232,11);
1 row created.
SQL> insert into cust11 values(2,'shamal',567576687,12);
1 row created.
SQL> insert into cust11 values(3,'raghav',576786878,13);
1 row created.
SQL> select * from cust11;
CNO CNAME MNO PNO
---------- ---------------------------- ---------- ----------
1 priti 1223223232 11
2 shamal 567576687 12
3 raghav 576786878 13
SQL> create or replace function yt(n in number)
2 return varchar2 as
3 res varchar2(29);
4 begin
5 select pname into res from plan0 where pno=n;
6 return res;
7 end;
8 /
Function created.
Function calling
SQL> begin
2 dbms_output.put_line('output:'||yt(11));
3 end;
4 /
Output:-
output:ddsd
PL/SQL procedure successfully completed.
2) Write a cursor which will display customer wise plan details.(Use Parameterized Cursor)
Soluation:-
SQL> declare
2 n number;
3 cursor c1(n number) is select plan11.pno,pname,famt from plan11, cust11
4 where plan11.pno=cust11.pno
5 and cust11.cno=n;
6 c c1%ROWTYPE;
7 begin
8 n:=&n;
9 open c1(n);
11 fetch c1 into c;
10 loop
12 exit when c1%NOTFOUND;
13 dbms_output.put_line(c.pno||' '||c.pname||' '||c.famt);
14 end loop;
15 close c1;
16 end;
17 /
Output:-
Enter value for n: 1
old 8: n:=&n;
new 8: n:=1;
11 ddsd 1200
PL/SQL procedure successfully completed.
Slip no-9
Q3 Consider the following entities and their relationships. [40]
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One. Constraint:
Primary key. Project Status Constraints: C – Completed, P - Progressive, I – Incomplete
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which accept department name and display total
number of projects whose status is “p”(progressive).
create table project(pnoint primary key,pname char(29),sdatedate,dudgetint,status
char(28)check(status in('c','i','p')));
SQL>desc project
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
PNAME CHAR(29)
SDATE DATE
DUDGET NUMBER(38)
STATUS CHAR(28)
SQL> select * from project;
PNO PNAME SDATE DUDGET STATUS
---------- ----------------------------- --------- ---------
1 abc 09-JAN-20 200000 c
2 ass 09-MAR-20 50000 i
3 hhs 04-JAN-20 300000 p
12 xyz 09-JAN-09 12000 p
create table department(dnoint primary key,dname char(24),hod char(28),loc char(29),
pnoint,constraintfk_projectdepartment foreign key(pno)references project(pno));
SQL>desc department;
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
DNAME CHAR(24)
HOD CHAR(28)
LOC CHAR(29)
PNO NUMBER(38)
SQL> select * from department;
DNO DNAME HOD LOC PNO
---------- ------------------------ ------------- --------------
11 computer science mane pune 1
12 commercedespandepune 2
13 computer science kadampune 3
23 slbdrpune 12
SQL> create or replace function fg(d in char)
2 return number as
3 bs number(10);
4 begin
5 select count(pname) into bs from project,department
6 where project.pno=department.pno
7 and dname=d
8 and status='p';
9 return bs;
10 end;
11 /
Function created.
function calling:-
SQL> begin
2 dbms_output.put_line('output:'||fg('slb'));
3 end;
4 /
Output:-
output:3
PL/SQL procedure successfully completed.
Write a cursor which will display status wise project details of each department.
Soluation:-
SQL> declare
2 cursor s1 is
3 select pno,pname,sdate,dudget,status from project
4 where sdate='09/jan/2020';
5 s s1%rowtype;
6 begin
7 open s1;
8 loop
9 fetch s1 into s;
10 exit when s1%notfound;
11 dbms_output.put_line(s.pno||''||s.pname||''||s.sdate||''||s.dudget||''|
|s.status);
12 end loop;
13 close s1;
14 end;
15 /
Output:-
1abc 09-JAN-20200000c
PL/SQL procedure successfully completed.
Slip no-10
Q3 Consider the following entities and their relationships. [40]
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many. Constraint: Primary Key,
charges must be greater than 0. Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
Write a function which will accept member id and scheme from user and
display charges paid by that member.
Soluation:-
1.create table gym23(name varchar2(29) primary key,city varchar2(28),charges int,
scheme varchar2(29));
SQL>desc gym23
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(29)
CITY VARCHAR2(28)
CHARGES NUMBER(38)
SCHEME VARCHAR2(29)
insert into gym23 values('abc','pune',34000,'hty');
1 row created.
insert into gym23 values('pqr','pune',30000,'yhj');
1 row created.
insert into gym23 values('xyz','pune',90000,'yuhs');
1 row created.
SQL> select * from gym23;
NAME CITY CHARGES SCHEME
abcpune 34000 hty
pqrpune 30000 yhj
xyzpune 90000 yuhs
3 row selected
2.create table member9(id int primary key,mname varchar2(29),phnoint,addr varchar2(28)
,name varchar2(29),constraint fk_gym23member9 foreign key(name)references
gym23(name));
SQL>desc member9
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
MNAME VARCHAR2(29)
PHNO NUMBER(38)
ADDR VARCHAR2(28)
NAME VARCHAR2(29)
insert into member values(11,'raghav',7875657575,'wagholi','abc' );
1 row created.
insert into member values(12,'aarav',7565456478 ,'wagholi','pqr' );
1 row created.
insert into member values(13,'shamal ',6565657668 ,'pune','xyz' );
1 row created.
SQL> select * from member9;
ID MNAME PHNO ADDR NAME
--------------------------------------------------------------
11 raghav 7875657575 wagholiabc
12 aarav 7565456478 wagholipqr
13 shamal 6565657668 pune xyz
SQL> create or replace function getprise (n IN number)
2 return number as
3 res number(10);
4 begin
5 select charges into res
6 from gym23,member9
7 where id=n
8 and gym23.name=member9.name;
9 return res;
10 end;
11 /
Function created.
Function calling:-
SQL> begin
2 dbms_output.put_line('output:'||getprise(11));
3 end;
4 /
Output:-
output:34000
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on Gym having charges
less than 1000. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger t11
2 before insert or update
3 on gym23
4 for each row
5 begin
6 if(:new.charges<1000) then
7 raise_application_error(-20002,'ERROR::Charges should be greater than 1000');
8 end if;
9 end;
10 /
Trigger created.
Output:-
SQL> insert into gym23 values('sd','pune',2300,'wee');
1 row created.
SQL> insert into gym23 values('yw','pune',200,'wee');
insert into gym23 values('yw','pune',200,'wee')
*
ERROR at line 1:
ORA-20002: ERROR::Charges should be greater than 1000
ORA-06512: at "SYSTEM.T11", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T11'
Slip no-11
Q3 Consider the following entities and their relationships. [40]
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity
should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
Write a function which will accept Lab number from user and display total number
of student allocated in that lab.
Soluation:-
SQL> Create table student2(rnoint primary key,sname char(29),class int,timetabl
eint);
Table created.
SQL> Create table lab2(lnoint primary key,lname char(29),capacity int,equ char(
23),rnoint,constraint fk_student2lab2 foreign key(rno)references student2(rno));
Table created.
SQL>desc student2;
Name Null? Type
----------------------------------------- -------- ----------------------------
RNO NOT NULL NUMBER(38)
SNAME CHAR(29)
CLASS NUMBER(38)
TIMETABLE NUMBER(38)
SQL> insert into student2 values(1,'raghav','12',10);
1 row created.
SQL> insert into student2 values(2,'shamal','11',10);
1 row created.
SQL> insert into student2 values(3,'aarav','13',12);
1 row created.
SQL> select * from student2;
RNO SNAME CLASS TIMETABLE
---------- ----------------------------- ---------- ----------
1 raghav 12 10
2 shamal 11 10
3 aarav 13 12
SQL>desc lab2
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL NUMBER(38)
LNAME CHAR(29)
CAPACITY NUMBER(38)
EQU CHAR(23)
RNO NUMBER(38)
SQL> insert into lab2 values(11,'slb',56,'computer',1);
1 row created.
SQL> insert into lab2 values(12,'vbb',79,'computer',2);
1 row created.
SQL> insert into lab2 values(13,'rlb',79,'computer',2);
1 row created.
SQL> select * from lab2;
LNO LNAME CAPACITY EQU RNO
------------ ---------------- ---------------- --------- ----------
11 slb 56 computer 1
12 vbb 79 computer 2
13 rlb 79 computer 2
SQL> create or replace function qw(v in number)
2 return number as
3 sd number;
4 begin
5 select capacity into sd from lab2
6 wherelno=v;
7 returnsd;
8 end;
9 /
Function created.
Function calling:-
SQL> begin
2 dbms_output.put_line('output'||qw(11));
3 end;
4 /
Output:-
output56
PL/SQL procedure successfully completed.
2)Write a cursor which will display lab wise student details.
Soluation:-
SQL> declare
2 cursor g is select student2.rno,sname,class,timetable,lname
3 from student2,lab2
4 where student2.rno=lab2.rno
5 order by lname;
6 g1g%rowtype;
7 begin
8 open g;
9 loop
10 fetch g into g1;
11 exit when g%notfound;
12 dbms_output.put_line('output:'||g1.lname||''||g1.rno||''||g1.sname||''||g1.
class||''||g1.timetable);
13 end loop;
14 close g;
15 end;
16 /
Output:-
output:rlb 2shamal 1110
output:slb 1raghav 1210
output:vbb 2shamal 1110
PL/SQL procedure successfully completed.
Slip no-12
Q3 Consider the following entities and their relationships. [40]
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive
attribute. Constraint: Primary key, rate should be > 0. Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
Write a function which will accept wholesaler name from user and will display
total number of items supplied by him.
Soluation:-
SQL> create table wholesaler(wnoint primary key,wname char(29),addr char(28),city char(28));
Table created.
SQL>desc wholesaler;
Name Null? Type
----------------------------------------- -------- ----------------------------
WNO NOT NULL NUMBER(38)
WNAME CHAR(29)
ADDR CHAR(28)
CITY CHAR(28)
SQL> insert into wholesaler values(1,'raghav','wagholi','pune');
1 row created.
SQL> insert into wholesaler values(2,'aarav','thane','mumbai');
1 row created.
SQL> insert into wholesaler values(3,'vijay','thane','mumbai');
1 row created.
SQL>select * from wholesaler;
WNO WNAME ADDR CITY
1 raghavwagholipune
2 aarav thane mumbai
3 vijay thane mumbai
SQL> create table product(pnoint primary key,pname char(28),rate int);
Table created.
SQL>desc product;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
PNAME CHAR(28)
RATE NUMBER(38)
SQL> insert into product values(11,'pen',12);
1 row created.
SQL> insert into product values(12,'pencil',11);
row created.
SQL> insert into product values(13,'notebook',23);
1 row created.
SQL> select * from product;
PNO PNAME RATE
---------- ---------------------------- ----------
11 pen 12
12 pencil 11
13 notebook 23
SQL> create table wp( quantity int,wnoint references wholesaler(wno),pnoint re
ferences product(pno));
Table created.
SQL>descwp;
Name Null? Type
----------------------------------------- -------- ----------------------------
QUANTITY NUMBER(38)
WNO NUMBER(38)
PNO NUMBER(38)
SQL> insert into wpvalues(56,1,11);
1 row created.
SQL> insert into wpvalues(52,2,12);
1 row created.
SQL> insert into wpvalues(22,3,13);
1 row created.
SQL> select * from wp;
QUANTITY WNO PNO
---------- ---------- ----------
56 1 11
52 2 12
22 3 13
SQL> create or replace function ni(s in number)
2 return number as
3 gh number;
4 begin
5 select count(wp.pno) into gh from product,wholesaler,wp
6 wherewholesaler.wno=wp.wno
7 andproduct.pno=wp.pno
8 andwholesaler.wno=s;
9 returngh;
10 end;
11 /
Function created.
Function calling:-
SQL> begin
2 dbms_output.put_line('output:'||ni(1));
3 end;
4 /
Output:-
output:1
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on product having rate less
than or equal to zero (Raise user defined exception and give appropriate
message)
Soluation:-
SQL> create or replace trigger ee
2 before insert or update
3 on product
4 for each row
5 begin
6 if(:new.rate<=0) then
7 raise_application_error(-20008,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
Output:-
SQL> insert into product values(101,'pen',123);
1 row created.
SQL> insert into product values(102,'pen',-123);
insert into product values(102,'pen',-123)
*
ERROR at line 1:
ORA-20008: enter more than 0
ORA-06512: at "SYSTEM.EE", line 3
ORA-04088: error during execution of trigger 'SYSTEM.EE'
Slip no-13
Q3 Consider the following entities and their relationships. [40]
Country (CId, CName ,no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many. Constraint:
Primary key, area should not be null. Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
Write a function which will display name of the country having minimum population
Soluation:-
SQL> Create table country2(cidint primary key,cname char(29),nosint,area char
(29),loc char(29),population int);
Table created.
SQL> Create table citizen3(id int primary key,name char(29),mt char(29),snamech
ar(29),cidint,constraint fk_country2citizen3 foreign key(cid)references country2(cid));
Table created.
SQL>desc country2
Name Null? Type
---------------------------------------- -------- -------------------------
CID NOT NULL NUMBER(38)
CNAME CHAR(29)
NOS NUMBER(38)
AREA CHAR(29)
LOC CHAR(29)
POPULATION NUMBER(38)
SQL> insert into country2 values(101,'india',34,'ert','gsuy',1230000);
1 row created.
SQL> insert into country2 values(102,'us',34,'ert','gsuy',2120000);
1 row created.
SQL> insert into country2 values(103,'china',23,'ert','gsuy',200000);
1 row created.
SQL> select* from country2;
CID CNAME NOSAREA LOC POPULATION
----------------------------- ----------------------------- ---------------------------------------------------------
101 india 34 ertgsuy 1230000
102 us 34ertgsuy 2120000
103 china 23ertgsuy 200000
SQL>desc citizen3
Name Null? Type
----------------------------------------- -------- -------------------------
ID NOT NULL NUMBER(38)
NAME CHAR(29)
MT CHAR(29)
SNAME CHAR(29)
CID NUMBER(38)
SQL> insert into citizen3 values(1,'raghav','marathi','maharashtra',101);
1 row created.
SQL> insert into citizen3 values(2,'shamal','marathi','maharashtra',102);
1 row created.
SQL> insert into citizen3 values(3,'aarav','marathi','maharashtra',102);
1 row created.
SQL> select * from citizen3;
ID NAME MTSNAME CID
----------------------------- ----------
1 raghavmarathimaharashtra 101
2 shamal Marathi maharashtra 102
3 aaravmarathimaharashtra 102
SQL> create or replace function er
2 return char as
3 uy char(29);
4 begin
5 selectcname into uy from country2
6 where population=(select min(population) from country2);
7 returnuy;
8 end;
9 /
Function created.
Function calling:-
SQL> begin
2 dbms_output.put_line('output:'||er());
3 end;
4 /
Output:-
output:china
PL/SQL procedure successfully completed.
Write a cursor which will display county wise citizen details.
Soluation:-
SQL> declare
2 cursor k is select cname,id,name,mt,sname
3 from country2,citizen3
4 where country2.cid=citizen3.cid
5 order by cname;
6 k1k%rowtype;
7 begin
8 open k;
9 loop
10 fetch k into k1;
11 exit when k%notfound;
12 dbms_output.put_line('output:'||k1.cname||''||k1.id||''||k1.name||''||k1.mt
||''||k1.sname);
13 end loop;
14 close k;
15 end;
16 /
Output:-
output:india 1raghav marathimaharashtra
output:us 3aarav marathimaharashtra
output:us 2shamal marathimaharashtra
PL/SQL procedure successfully completed.
Slip no-14
Q3 Consider the following entities and their relationships. [40]
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will accept teacher name from user and display his/her college details.
Soluation:-
Create table college(code int primary key,cname char(29),addr char(29));
Table created
Create table teacher1(tidint primary key,tname char(29),qualification char(29),specialisation char(29),desg char(29),code int,constraint fk_collegeteacher1 foreign key(code)references college(code));
Table created
SQL>desc college
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NOT NULL NUMBER(38)
CNAME CHAR(29)
ADDR CHAR(29)
SQL>desc teacher1
Name Null? Type
----------------------------------------- -------- ----------------------------
TID NOT NULL NUMBER(38)
TNAME CHAR(20)
QUALIFICATION CHAR(20)
SPECIALISATION CHAR(28)
SALARY NUMBER(38)
DESG CHAR(29)
CODE NUMBER(38)
SQL> select * from college;
CODE CNAME ADDR
---------- ----------------------------- ----------------------------
1 bjswagholi
2 wadiyapune
3 dpmmumbai
SQL> select * from teacher1;
TID TNAME QUALIFICATION SPECIALISATION SALARY DESG CODE
---------------------------- ---------- ----------------------------- -----------------------------------------------------
21 mane mcomhwe 30000 teaher 1
22 jadhav m ghy 40000 teach 2
23 deshamukhbahs 90000 teach 3
SQL> create or replace procedure vs(b in char) as
2 cursor f is select college.code,cname,addr from college,teacher1
3 wherecollege.code=teacher1.code
4 andtname=b;
5 f1f%rowtype;
6 dint;
7 vn char(29);
8 gh char(29);
9 begin
10 open f;
11 loop
12 fetch f into f1;
13 exit when f%notfound;
14 d:=f1.code;
15 vn:=f1.cname;
16 gh:=f1.addr;
17 dbms_output.put_line('output:'||d||''||vn||''||gh);
18 end loop;
19 close f;
20 end;
21 /
Procedure created.
SQL> execute vs('mane');
OUTPUT:-
output:1bjs wagholi
PL/SQL procedure successfully completed.
1)Write a trigger which will fire before insert or update on Teacher having salary less than or equal to zero (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger rt
2 before insert or update
3 on teacher1
4 for each row
5 begin
6 if(:new.salary<=0) then
7 raise_application_error(-20003,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into teacher1 values(78,'bhagat','mca','bm',40000,'teach',3);
1 row created.
SQL> insert into teacher1 values(76,'kadam','mca','bm',-40000,'teach',3);
insert into teacher1 values(76,'kadam','mca','bm',-40000,'teach',3)
*
ERROR at line 1:
ORA-20003: enter more than 0
ORA-06512: at "SYSTEM.RT", line 3
ORA-04088: error during execution of trigger 'SYSTEM.RT'
Slip no-15
Q3 Consider the following entities andtheirrelationships. [40]
Driver (driver_id, driver_name,
address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute.
Constraint: Primary key, driver_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will display the total number of person who are using “Swift”car
Soluation:-
SQL> create table driver2(did int primary key,dname char(29),addr char(29));
Table created.
SQL> create table car(lnoint primary key,model char(29),year int);
Table created.
SQL> create table dc1( pdate date,did int references driver2(did),lnoint references car(lno));
Table created.
SQL>desc driver
Name Null? Type
----------------------------------------- -------- ----------------------------
D_NO NOT NULL NUMBER(38)
D_NAME NOT NULL VARCHAR2(10)
LICENCE_NO NUMBER(10)
ADDR VARCHAR2(10)
D_AGE NUMBER(10)
SALARY NUMBER(10)
SQL>desc driver2
Name Null? Type
---------------------------------------- -------- ----------------------------
DID NOT NULL NUMBER(38)
DNAME CHAR(29)
ADDR CHAR(29)
SQL> insert into driver2 values(1,'raghav','pune');
1 row created.
SQL> insert into driver2 values(2,'aarav','mumbai');
1 row created.
SQL> insert into driver2 values(3,'rohan','mumbai');
1 row created.
SQL> select * from driver2;
DID DNAME ADDR
---------- ----------------------------- -----------------------------
1 raghavpune
2 aaravmumbai
3 rohanmumbai
SQL>desc car
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL NUMBER(38)
MODEL CHAR(29)
YEAR NUMBER(38)
SQL> insert into car values(101,'swift',2001);
1 row created.
SQL> insert into car values(102,'swift',2003);
1 row created.
SQL> insert into car values(103,'seho',2003);
1 row created.
SQL> select * from car;
LNO MODEL YEAR
---------- ----------------------------- ----------
101 swift 2001
102 swift 2003
103 seho 2003
SQL>desc dc
Name Null? Type
----------------------------------------- -------- ----------------------------
DID NUMBER(38)
LNO NUMBER(38)
SQL> insert into dc values(1,101);
1 row created.
SQL> insert into dc values(2,101);
1 row created.
SQL> insert into dc values(3,102);
1 row created.
SQL> insert into dc values(3,103);
1 row created.
SQL> select * from dc;
DID LNO
---------- ----------
1 101
2 101
3 102
3 103
SQL> create or replace function gh
2 return number as
3 sd number;
4 begin
5 select count(model) into sd
6 from car
7 where model='swift';
8 returnsd;
9 end;
10 /
Function created.
FUNCTIN CALLING:
SQL> begin
2 dbms_output.put_line('outpout:'||gh());
3 end;
4 /
OUTPUT:-
outpout:2
PL/SQL procedure successfully completed.
2)Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriatemessage)
Slip no-16
Q3 Consider the following entities and their relationships. [40]
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will display games details having number of players more than 5
Soluation:-
SQL> create table game2(gname char(29) primary key,nop int,cname char(29));
Table created.
SQL> create table player2(pid int primary key,pname char(29),addr char(29),cname
char(27));
Table created.
SQL> create table gp2(gname char(29) references game2(gname),pid int references
player2(pid));
Table created.
SQL> desc game2
Name Null? Type
----------------------------------------- -------- ----------------------------
GNAME NOT NULL CHAR(29)
NOP NUMBER(38)
CNAME CHAR(29)
SQL> insert into game2 values('cricket',12,'eui');
1 row created.
SQL> insert into game2 values('kabddi',7,'uye');
1 row created.
SQL> insert into game2 values('kho-kho',7,'jude');
1 row created.
SQL> select * from game2;
GNAME NOP CNAME
----------------------------- ---------- ----------------------------
cricket 12 eui
kabddi 7 uye
kho-kho 7 jude
SQL> desc player2
Name Null? Type
----------------------------------------- -------- ----------------------------
PID NOT NULL NUMBER(38)
PNAME CHAR(29)
ADDR CHAR(29)
CNAME CHAR(27)
SQL> insert into player2 values(101,'rohit sharma','mumbai','bgg');
1 row created.
SQL> insert into player2 values(102,'pravin narval','patana','iur');
1 row created.
SQL> insert into player2 values(103,'huins','bihar','ius');
1 row created.
SQL> select * from player2;
PID PNAME ADDRCNAME
------------------------------------------------------------------------------
101 rohit sharma mumbaibgg
102 pravin narval patina iur
103 huins biharius
SQL> desc gp2
Name Null? Type
----------------------------------------- -------- ----------------------------
GNAME CHAR(29)
PID NUMBER(38)
SQL> insert into gp2 values('cricket',101);
1 row created.
SQL> insert into gp2 values('kabddi',102);
1 row created.
SQL> insert into gp2 values('kho-kho',103);
1 row created.
SQL> select * from gp2;
GNAME PID
----------------------------- ----------
cricket 101
kabddi 102
kho-kho 103
SQL> create or replace procedure io as
2 cursor d is select gname,nop,cname
3 from game2
4 where nop>5;
5 g char(29);
6 n int;
7 c char(29);
8 d1 d%rowtype;
9 begin
10 open d;
11 loop
12 fetch d into d1;
13 exit when d%notfound;
14 g:=d1.gname;
15 n:=d1.nop;
16 c:=d1.cname;
17 dbms_output.put_line('output:'||g||''||n||''||c);
18 end loop;
19 close d;
20 end;
21 /
Procedure created.
SQL> execute io();
OUTPUT:-
output:cricket 12eui
output:kabddi 7uye
output:kho-kho 7jude
procedure successfully completed.
SQL> create or replace PROCEDURE ge as
2 cursor c1 is select * from game2 where nop>5;
3 c c1%ROWTYPE;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1%NOTFOUND;
9 dbms_output.put_line(c.gname||' '||c.nop||' '||c.cname);
10 end loop;
11 close c1;
12 end;
13 /
Procedure created.
SQL> execute ge();
cricket 12 eui
kabddi 7 uye
kho-kho 7 jude
PL/SQL procedure successfully completed.
2) Write a trigger which will fire before insert or update on Game having no_of_players less than or equal to zero. (Raise user defined exception and give appropriate message)
SQL> create or replace trigger hu
2 before insert or update
3 on game2
4 for each row
5 begin
6 if(:new.nop<=0) then
7 raise_application_error(-20008,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into game2 values('tenis',1,'jn');
1 row created.
SQL> insert into game2 values('basketball',-1,'jn');
insert into game2 values('basketball',-1,'jn')
*
ERROR at line 1:
ORA-20008: enter more than 0
ORA-06512: at "SYSTEM.HU", line 3
ORA-04088: error during execution of trigger 'SYSTEM.HU'
Slip no-17
Q3. Consider the following Item_Supplier database [40]
Company (name , address , city , phone , share_value)
Person (pname ,pcity )
Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares i Constraints: name,pname primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a trigger before insert or update on No_of_shares field should not be zero.(Raise user defined exception and give appropriate message)
Soluation:-
SQL> create table company(name char(29) primary key,addr char(29),city char(29),
phone int,svalue int);
Table created.
SQL> create table person(pname char(29) primary key,pcity char(29));
Table created.
SQL> create table pc(name char(29) references company(name),pname char(29) refer
ences person(pname));
Table created.
SQL> desc company
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL CHAR(29)
ADDR CHAR(29)
CITY CHAR(29)
PHONE NUMBER(38)
SVALUE NUMBER(38)
SQL> insert into company values('tata','pune','pune',45457658,12100000);
1 row created.
SQL> insert into company values('bjaj','thane','mumbai',69870972,1200000);
1 row created.
SQL> insert into company values('finix','banglor','pune',68764497,7800000);
1 row created.
SQL> select * from company;
NAME ADDRCITY PHONE SVALUE
----------------------------- ---------- ----------
tata punepune 45457658 12100000
bjaj thane mumbai 69870972 1200000
finix banglorpune 68764497 7800000
SQL> desc person;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNAME NOT NULL CHAR(29)
PCITY CHAR(29)
SQL> insert into person values('raghav','pune');
1 row created.
SQL> insert into person values('aarav','mumbai');
1 row created.
SQL> insert into person values('shamal','osmanabad');
1 row created.
SQL> select * from person;
PNAME PCITY
----------------------------- -----------------------------
raghav pune
aarav mumbai
shamal osmanabad
SQL> desc pc;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME CHAR(29)
PNAME CHAR(29)
SQL> insert into pc values('tata','raghav');
1 row created.
SQL> insert into pc values('bjaj','aarav');
1 row created.
SQL> insert into pc values('bjaj','shamal');
1 row created.
SQL> insert into pc values('finix','shamal');
1 row created.
SQL> select * from pc;
NAME PNAME
----------------------------- -----------------------------
tata raghav
bjaj aarav
bjaj shamal
finix shamal
SQL> create or replace trigger te
2 before insert or update
3 on pc
4 for each row
5 begin
6 if(:new.nos<=0) then
7 raise_application_error(-20001,'Shares must be greater than zero');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into pc values('tata','raghav',65);
1 row created.
SQL> insert into pc values('bjaj','shamal',0);
insert into pc values('bjaj','shamal',0)
*
ERROR at line 1:
ORA-20001: Shares must be greater than zero
ORA-06512: at "SYSTEM.TE", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TE'
Write a function to display total no_of_shares of a specific person.
Soluation:-
SQL> create or replace function bl
2 return number as
3 res number;
4 begin
5 select count(nos) into res from pc
6 where pname='shamal';
7 return res;
8 end;
9 /
Function created.
FUNCTIN CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||bl());
3 end;
4 /
OUTPUT:-
output:2
slip no-18
Q3. Consider the following entities and their relationship. [40]
Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)
Relationship between Student and Competition is many-to-many with descriptive attribute rank and year. Constraints: primary key, foreign key, primary key for third table(s_reg_no, comp_no, year),s_name and comp_name should not be null,comp_type can be sports or academic. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.
Soluation:-
SQL> create table student9(srno int primary key,sname char(29),sclass int);
Table created.
SQL> create table competition(cno int primary key,cname char(29),ctype char(29));
Table created.
SQL> create table sc(srno int references student9(srno),cno int references competition(cno));
Table created.
SQL> desc student9
Name Null? Type
----------------------------------------- -------- ----------------------------
SRNO NOT NULL NUMBER(38)
SNAME CHAR(29)
SCLASS NUMBER(38)
SQL> insert into student9 values(101,'shamal',12);
1 row created.
SQL> insert into student9 values(102,'raghav',13);
1 row created.
SQL> insert into student9 values(103,'aarav',14);
1 row created.
SQL> select * from student9;
SRNO SNAME SCLASS
---------- ----------------------------- ----------
101 shamal 12
102 raghav 13
103 aarav 14
SQL> desc competition
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(38)
CNAME CHAR(29)
CTYPE CHAR(29)
SQL> insert into competition values(1,'queis','ty');
1 row created.
SQL> insert into competition values(2,'speech','he');
1 row created.
SQL> insert into competition values(3,'running','t8');
1 row created.
SQL> select * from competition;
CNO CNAME CTYPE
---------- ----------------------------- -----------------------------
1 queis ty
2 speech he
3 running t8
SQL> desc sc
Name Null? Type
----------------------------------------- -------- ----------------------------
SRNO NUMBER(38)
CNO NUMBER(38)
SQL> insert into sc values(101,1);
1 row created.
SQL> insert into sc values(101,2);
1 row created.
SQL> insert into sc values(102,2);
1 row created.
SQL> insert into sc values(103,3);
1 row created.
SQL> select * from sc;
SRNO CNO
---------- ----------
101 1
101 2
102 2
103 3
SQL> create or replace function kl(n in number)
2 return number as
3 res number;
4 begin
5 select count(cno) into res from sc where srno=n;
6 return res;
7 end;
8 /
Function created.
FUNCTION CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||kl(101));
3 end;
4 /
OUTPUT:-
output:2
PL/SQL procedure successfully completed.
Write a cursor which will display year wise details of competitions. (Use parameterized cursor)
Soluation:-
SQL> declare
2 cursor c1(n int) is select year,competition.cno,cname,ctype
3 from student9,competition,sc
4 where student9.srno=sc.srno
5 and competition.cno=sc.cno
6 and year=n
7 order by year;
8 c c1%ROWTYPE;
9 begin
10 open c1(&n);
11 loop
12 fetch c1 into c;
13 exit when c1%notfound;
14 dbms_output.put_line(c.year||' '||c.cno||' '||c.cname||' '||c.ctype);
15 end loop;
16 close c1;
17 end;
18 /
OUTPUT:-
Enter value for n: 2018
old 10: open c1(&n);
new 10: open c1(2018);
2018 2 speech he
2018 3 running t8
PL/SQL procedure successfully completed.
Slip no-19
Q3 Consider the following entities and their relationships. [40]
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name) Relation between Game and Player is Many to Many. Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will return total number of football players of “Sports Club”.
Soluation:-
SQL> create table game5(gname char(29) primary key,nop int,name char(29));
Table created.
SQL> create table player5(pid int primary key,pname char(29),addr char(29),cname char(27));
Table created.
SQL> create table gp5(gname char(29) references game5(gname),pid int references player5(pid));
Table created.
SQL> desc game5
Name Null? Type
----------------------------------------- -------- ----------------------------
GNAME NOT NULL CHAR(29)
NOP NUMBER(38)
NAME CHAR(29)
SQL> insert into game5 values('cricket',12,'ass');
1 row created.
SQL> insert into game5 values('fotball',7,'aj');
1 row created.
SQL> insert into game5 values('football',7,'aj');
1 row created.
SQL> insert into game5 values('kho-kho',7,'jo');
1 row created.
SQL> select * from game5;
GNAME NOP NAME
----------------------------- ---------- -----------------------------
cricket 12 ass
fotball 7 aj
football 7 aj
kho-kho 7 jo
SQL> desc player5;
Name Null? Type
----------------------------------------- -------- ----------------------------
PID NOT NULL NUMBER(38)
PNAME CHAR(29)
ADDR CHAR(29)
CNAME CHAR(27)
SQL> insert into player5 values(101,'rohit sharma','mumbai','sports club');
row created.
SQL> insert into player5 values(102,'virat','pune','game club');
1 row created.
SQL> insert into player5 values(103,'hardik','pune','ghf club');
1 row created.
SQL> select * from player5;
PID PNAME ADDRCNAME
---------------------------
101 rohit sharma mumbaisports club
102 virat punegame club
103 hardik puneghf club
SQL> desc gp5;
Name Null? Type
----------------------------------------- -------- ----------------------------
GNAME CHAR(29)
PID NUMBER(38)
SQL> insert into gp5 values('football',101);
1 row created.
SQL> insert into gp5 values('cricket',102);
1 row created.
SQL> select * from gp5;
GNAME PID
----------------------------- ----------
football 101
cricket 102
SQL> create or replace FUNCTION getnum
2 return number as
3 res number(10);
4 begin
5 select sum(nop) into res from game5,player5,gp5
6 where player5.pid=gp5.pid
7 and game5.gname=gp5.gname
8 and gp5.gname='football'
9 and cname='sports club';
10 return res;
11 end;
12
13 /
Function created.
FUNCTIN CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||getnum());
3 end;
4 /
OUTPUT:-
output:7
PL/SQL procedure successfully completed.
Write a cursor which will display club wise details of players.
Soluation:-
SQL> declare
2 cursor t is select cname,pid,pname,addr from player5
3 order by cname;
4 t1 t%rowtype;
5 begin
6 open t;
7 loop
8 fetch t into t1;
9 exit when t%notfound;
10 dbms_output.put_line('output:'||t1.cname||''||t1.pid||''||t1.pname||''||t1.
addr);
11 end loop;
12 close t;
13 end;
14 /
OUTPUT:-
output:game club 102 virat pune
output:ghf club 103 hardik pune
output:sports club 101 rohit sharma mumbai
PL/SQL procedure successfully completed.
Slip no-20
Consider the following entities and their relationships. [40]
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display car details used on specific day.
Soluation:-
SQL> create table driver2(did int primary key,dname char(29),addr char(29));
Table created.
create table car(lno int primary key,model char(29),year int);
Table created.
SQL> desc driver2;
Name Null? Type
----------------------------------------- -------- -----------------
DID NOT NULL NUMBER(38)
DNAME CHAR(29)
ADDR CHAR(29)
SQL> insert into driver2 values(1,'raghav','pune');
1 row created.
SQL> insert into driver2 values(2,'aarav','mumbai');
1 row created.
SQL> insert into driver2 values(3,'rohan','mumbai');
1 row created.
SQL> select * from driver2;
DID DNAME ADDR
---------- ----------------------------- -----------------------------
1 raghav pune
2 aarav mumbai
3 rohan mumbai
SQL> desc car;
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL NUMBER(38)
MODEL CHAR(29)
YEAR NUMBER(38)
SQL> insert into car values(101,'swift',2001);
1 row created.
SQL> insert into car values(102,'swift',2018);
1 row created.
SQL> insert into car values(103,'seho',2003);
1 row created.
SQL> select * from car;
LNO MODEL YEAR
---------- ----------------------------- ----------
101 swift 2001
102 swift 2018
103 seho 2003
SQL> create table dc1( pdate date,did int references driver2(did),lno int refere
nces car(lno));
SQL> desc dc1;
Name Null? Type
----------------------------------------- -------- ----------------------------
PDATE DATE
DID NUMBER(38)
LNO NUMBER(38)
SQL> insert into dc1 values('09/jan/20',1,101);
1 row created.
SQL> insert into dc1 values('08/mar/20',3,102);
1 row created.
SQL> insert into dc1 values('02/mar/20',3,103);
1 row created.
SQL> select * from dc1;
PDATE DID LNO
--------- ---------- ----------
09-JAN-20 1 101
08-MAR-20 3 102
02-MAR-20 3 103
SQL> create or replace PROCEDURE js as
2 cursor c1 is select car.lno,model,year from car,dc1
3 where car.lno=dc1.lno
4 and pdate='08-mar-2020';
5 d c1%ROWTYPE;
6 begin
7 open c1;
8 loop
9 fetch c1 into d;
10 exit when c1%NOTFOUND;
11 dbms_output.put_line(d.lno||' '||d.model||' '||d.year);
12 end loop;
13 close c1;
14 end;
15
16 /
Procedure created.
OUTPUT:-
SQL> execute js();
102 swift 2018
PL/SQL procedure successfully completed.
Write a cursor which will display driver wise car details in the year 2018.
Soluation:-
SQL> declare
2 cursor d is select dname,car.lno,model,year from driver2,car,dc1
3 where driver2.did=dc1.did
4 and car.lno=dc1.lno
5 and year=2018
6 order by dname;
7 d1 d%rowtype;
8 begin
9 open d;
10 loop
11 fetch d into d1;
12 exit when d%notfound;
13 dbms_output.put_line('output:'||d1.dname||''||d1.lno||''||d1.model||''||d1.
year);
14 end loop;
15 close d;
16 end;
17 /
OUTPUT:-
output:rohan 102swift 2018
PL/SQL procedure successfully completed.
Slip no-21
Q3 Consider the following entities and their relationships. [40]
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers.
Soluation:-
SQL> Create table college1(code int primary key,cname char(29),addr char(29));
Table created.
SQL> Create table teacher10(tid int primary key,tname char(29),qualification char(29),specialisation char(29),desg char(29),code int,constraint fk_college1teacher10 foreign key(code)references college1(code));
Table created.
SQL> desc college1;
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NOT NULL NUMBER(38)
CNAME CHAR(29)
ADDR CHAR(29)
SQL> insert into college1 values(101,'bjs','wagholi');
1 row created.
SQL> insert into college1 values(102,'modern','pune');
1 row created.
SQL> insert into college1 values(103,'wadiya','pune');
1 row created.
SQL> select * from college1;
CODE CNAME ADDR
---------- ----------------------------- -----------------------------
101 bjs wagholi
102 modern pune
103 wadiya pune
SQL> desc teacher10;
Name Null? Type
----------------------------------------- -------- ----------------------------
TID NOT NULL NUMBER(38)
TNAME CHAR(29)
QUALIFICATION CHAR(29)
SPECIALISATION CHAR(29)
DESG CHAR(29)
CODE NUMBER(38)
SQL> insert into teacher10 values(1,'mane','ph.d','bm','hod',101);
1 row created.
SQL> insert into teacher10 values(2,'patil','ph.d','ob','principal',102);
1 row created.
SQL> insert into teacher10 values(3,'kadam','mca','io','teacher',103);
1 row created.
SQL> select * from teacher10;
TID TNAME QUALIFICATIONSPECIALISATION DESG CODE
----------------------------- ----------------------------- ----------
1 mane ph.dbm hod 101
2 patil ph.dob principal 102
3 kadam mcaio teacher 103
SQL> create or replace function bf(n in char)
2 return number as
3 e number;
4 begin
5 select count(tname) into e
6 from teacher10,college1
7 where college1.code=teacher10.code
8 and qualification='ph.d'
9 and cname=n;
10 return e;
11 end;
12 /
Function created.
FUNCTIN CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||bf('bjs'));
3 end;
4 /
OUTPUT:
output:1
PL/SQL procedure successfully completed.
Write a cursor which will display college wise teacher details.
Soluation:-
SQL> declare
2 cursor v is select cname,teacher10.tid,tname,qualification,specialisation,desg
3 from college1,teacher10
4 where college1.code=teacher10.code
5 order by cname;
6 v1 v%rowtype;
7 begin
8 open v;
9 loop
10 fetch v into v1;
11 exit when v%notfound;
12 dbms_output.put_line('output:'||v1.cname||''||v1.tid||''||v1.tname||''||v1.
qualification||''||v1.specialisation||''||v1.desg);
13 end loop;
14 close v;
15 end;
16 /
OUTPUT:-
Output:bjs 1mane ph.dbm hod
output:modern 2patil ph.dob principal
output:wadiya 3kadam mcaio teacher
PL/SQL procedure successfully completed.
Slip no-22
Consider the following entities and their relationships. [40]
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many. Constraint: Primary key, area should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display name of citizens having mother toung “Marathi “ and from “India”;
Soluation:-
SQL> Create table country2(cidint primary key,cname char(29),nosint,area char
(29),loc char(29),population int);
Table created.
SQL> Create table citizen3(id int primary key,name char(29),mt char(29),snamech
ar(29),cidint,constraint fk_country2citizen3 foreign key(cid)references country
2(cid));
Table created.
SQL>desc country2
Name Null? Type
----------------------------------------- -------- -------------------------
CID NOT NULL NUMBER(38)
CNAME CHAR(29)
NOS NUMBER(38)
AREA CHAR(29)
LOC CHAR(29)
POPULATION NUMBER(38)
SQL> insert into country2 values(101,'india',34,'ert','gsuy',1230000);
1 row created.
SQL> insert into country2 values(102,'us',34,'ert','gsuy',2120000);
1 row created.
SQL> insert into country2 values(103,'china',23,'ert','gsuy',200000);
1 row created.
SQL> select* from country2;
CID CNAMENOS AREA LOC POPULATION
----------------------------- ----------------------------- ----------
101 india 34 ert gsuy 1230000
102 us 34ertgsuy 2120000
103 china 23ertgsuy 200000
SQL>desc citizen3
Name Null? Type
----------------------------------------- -------- -------------------------
ID NOT NULL NUMBER(38)
NAME CHAR(29)
MT CHAR(29)
SNAME CHAR(29)
CID NUMBER(38)
SQL> insert into citizen3 values(1,'raghav','marathi','maharashtra',101);
1 row created.
SQL> insert into citizen3 values(2,'shamal','marathi','maharashtra',102);
1 row created.
SQL> insert into citizen3 values(3,'aarav','marathi','maharashtra',102);
1 row created.
SQL> select * from citizen3;
ID NAME MTSNAME CID
----------------------------- ----------
1 raghavmarathi maharashtra 10
2 shamalmarathi maharashtra 102
3 aaravmarathi maharashtra 102
SQL> create or replace procedure vs as
2 cursor g is select name from citizen3,country2
3 where country2.cid=citizen3.cid
4 and mt='marathi'
5 and cname='india';
6 g1 g%rowtype;
7 begin
8 open g;
9 loop
10 fetch g into g1;
11 exit when g%notfound;
12 dbms_output.put_line('output:'||g1.name);
13 end loop;
14 close g;
15 end;
16 /
Procedure created.
OUTPUT:-
SQL> execute vs();
output:raghav
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on country having no_of_state less than equal to zero. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger df
2 before insert or update
3 on country2
4 for each row
5 begin
6 if(:new.nos<=0) then
7 raise_application_error(-20002,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into country2 values(108,'us',23,'dwe','eeree',1220000);
1 row created.
SQL> insert into country2 values(109,'uk',-9,'ha','jk',19000);
insert into country2 values(109,'uk',-9,'ha','jk',19000)
*
ERROR at line 1:
ORA-20002: enter more than 0
ORA-06512: at "SYSTEM.DF", line 3
ORA-04088: error during execution of trigger 'SYSTEM.DF'
Slip no-23
Q3 Consider the following entities and their relationships. [40]
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will display details of products supplied by “Mr. Patil”
Soluation:-
SQL> create table wholesaler(wnoint primary key,wname char(29),addr char(28),city char(28));
Table created.
SQL>desc wholesaler;
Name Null? Type
----------------------------------------- -------- ----------------------------
WNO NOT NULL NUMBER(38)
WNAME CHAR(29)
ADDR CHAR(28)
CITY CHAR(28)
SQL> insert into wholesaler values(1,'raghav','wagholi','pune');
1 row created.
SQL> insert into wholesaler values(2,'aarav','thane','mumbai');
1 row created.
SQL> insert into wholesaler values(3,'vijay','thane','mumbai');
1 row created.
SQL>select * from wholesaler;
SQL> insert into wholesaler values(4,'mr.patil','pune','pune');
1 row created.
WNO WNAME ADDR CITY
1 raghav wagholi pune
2 aarav thane mumbai
3 vijay thane mumbai
4 mr.patil pune pune
SQL> create table product(pnoint primary key,pname char(28),rate int);
Table created.
SQL>desc product;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
PNAME CHAR(28)
RATE NUMBER(38)
SQL> insert into product values(11,'pen',12);
1 row created.
SQL> insert into product values(12,'pencil',11);
row created.
SQL> insert into product values(13,'notebook',23);
1 row created.
SQL> select * from product;
PNO PNAME RATE
---------- ---------------------------- ----------
11 pen 12
12 pencil 11
13 notebook 23
SQL> create table wp( quantity int,wnoint references wholesaler(wno),pnoint re
ferences product(pno));
Table created.
SQL>descwp;
Name Null? Type
----------------------------------------- -------- ----------------------------
QUANTITY NUMBER(38)
WNO NUMBER(38)
PNO NUMBER(38)
SQL> insert into wpvalues(56,1,11);
1 row created.
SQL> insert into wpvalues(52,2,12);
1 row created.
SQL> insert into wpvalues(22,3,13);
1 row created.
SQL> insert into wp values(123,4,12);
1 row created.
SQL> select * from wp;
QUANTITY WNO PNO
---------- ---------- ----------
56 1 11
52 2 12
22 3 13
123 4 12
SQL> create or replace procedure bn as
2 cursor c1 is select product.pno,pname,rate
3 from wholesaler,product,wp
4 where wholesaler.wname='mr.patil'
5 and wholesaler.wno=wp.wno
6 and product.pno=wp.pno;
7 c c1%ROWTYPE;
8 begin
9 open c1;
10 loop
11 fetch c1 into c;
12 exit when c1%NOTFOUND;
13 dbms_output.put_line(c.pno||''||c.pname||''||c.rate);
14 end loop;
15 close c1;
16 end;
17 /
Procedure created.
OUTPUT:-
SQL> execute bn();
12 pencil 11
PL/SQL procedure successfully completed.
Write a cursor which will display wholesaler wise product details
(Use Parameterized cursor)
Soluation:-
SQL> declare
2 cursor c1(n in char) is select wname,product.pno,pname,rate from wholesaler,product,wp
3 where wholesaler.wno=wp.wno
4 and product.pno=wp.pno
5 and wholesaler.wname=n
6 order by wname;
7 c c1%ROWTYPE;
8 begin
9 open c1(&n);
10 loop
11 fetch c1 into c;
12 exit when c1%NOTFOUND;
13 dbms_output.put_line(c.wname||' '||c.pno||' '||c.pname||' '||c.rate);
14 end loop;
15 close c1;
16 end;
17 /
OUTPUT:-
Enter value for n: 'vijay'
old 9: open c1(&n);
new 9: open c1('vijay');
vijay 13 notebook 23
PL/SQL procedure successfully completed.
Slip no-24
Q3 Consider the following entities and their relationships. [40]
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display details of students which perform practical sessions in a given Lab.
Soluation:-
SQL> Create table student2(rnoint primary key,sname char(29),class int,timetabl
eint);
Table created.
SQL> Create table lab2(lnoint primary key,lname char(29),capacity int,equ char(
23),rnoint,constraint fk_student2lab2 foreign key(rno)references student2(rno));
Table created.
SQL>desc student2;
Name Null? Type
----------------------------------------- -------- ----------------------------
RNO NOT NULL NUMBER(38)
SNAME CHAR(29)
CLASS NUMBER(38)
TIMETABLE NUMBER(38)
SQL> insert into student2 values(1,'raghav','12',10);
1 row created.
SQL> insert into student2 values(2,'shamal','11',10);
1 row created.
SQL> insert into student2 values(3,'aarav','13',12);
1 row created.
SQL> select * from student2;
RNO SNAME CLASS TIMETABLE
---------- ----------------------------- ---------- ----------
1 raghav 12 10
2 shamal 11 10
3 aarav 13 12
SQL>desc lab2
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL NUMBER(38)
LNAME CHAR(29)
CAPACITY NUMBER(38)
EQU CHAR(23)
RNO NUMBER(38)
SQL> insert into lab2 values(11,'slb',56,'computer',1);
1 row created.
SQL> insert into lab2 values(12,'vbb',79,'computer',2);
1 row created.
SQL> insert into lab2 values(13,'rlb',79,'computer',2);
1 row created.
SQL> select * from lab2;
LNO LNAME CAPACITY EQU RNO
------------ ---------------- ---------------- --------- ----------
11 slb 56 computer 1
12 vbb 79 computer 2
13 rlb 79 computer 2
SQL> create or replace procedure kj(n IN number) as
2 cursor c1 is select student2.rno,sname,class,timetable
3 from lab2,student2
4 where student2.rno=lab2.rno
5 and lab2.lno=n;
6 c c1%rowtype;
7 begin
8 open c1;
9 loop
10 fetch c1 into c;
11 exit when c1%NOTFOUND;
12 dbms_output.put_line('output:'||c.rno||' '||c.sname||' '||c.class||''||c.t
imetable);
13 end loop;
14 close c1;
15 end;
16 /
Procedure created.
SQL> execute kj(11);
OUTPUT:-
output:1 raghav 1210
PL/SQL procedure successfully completed.
2)Write a trigger which will fire before delete on Lab (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger kd
2 before delete
3 on lab2
4 for each row
5 declare
6 del_lab exception;
7 begin
8 raise del_lab;
9 exception
10 when del_lab then
11 raise_application_error(-20001,'Record can not be deleted');
12 end;
13 /
Trigger created.
Output:
SQL> DELETE FROM lab2 WHERE equ='computer';
DELETE FROM lab2 WHERE equ='computer'
*
ERROR at line 1:
ORA-20001: Record can not be deleted
ORA-06512: at "SYSTEM.KD", line 7
ORA-04088: error during execution of trigger 'SYSTEM.KD'
Slip no-25
Q3 Consider the following entities and their relationships. [40]
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display member details of gym located at “Pimpri’”
Soluation:-
1.create table gym23(name varchar2(29) primary key,city varchar2(28),charges int,scheme varchar2(29));
SQL>desc gym23
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(29)
CITY VARCHAR2(28)
CHARGES NUMBER(38)
SCHEME VARCHAR2(29)
insert into gym23 values('abc','pune',34000,'hty');
1 row created.
insert into gym23 values('pqr','pune',30000,'yhj');
1 row created.
insert into gym23 values('xyz','pune',90000,'yuhs');
1 row created.
SQL> select * from gym23;
NAME CITY CHARGES SCHEME
abcpune 34000 hty
pqrpune 30000 yhj
xyzpune 90000 yuhs
3 row selected
2.create table member9(id int primary key,mname varchar2(29),phnoint,addr varchar2(28),name varchar2(29),constraint fk_gym23member9 foreign key(name)references gym23(name));
SQL>desc member9
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
MNAME VARCHAR2(29)
PHNO NUMBER(38)
ADDR VARCHAR2(28)
NAME VARCHAR2(29)
insert into member values(11,'raghav',7875657575,'wagholi','abc' );
1 row created.
insert into member values(12,'aarav',7565456478 ,'wagholi','pqr' );
1 row created.
insert into member values(13,'shamal ',6565657668 ,'pune','xyz' );
1 row created.
SQL> insert into member9 values(21,'shamal',7768886542,'pimpari','xyz');
1 row created.
SQL> select * from member9;
ID MNAME PHNO ADDR NAME
--------------------------------------------------------------
11 raghav 7875657575 wagholiabc
12 aarav 7565456478 wagholipqr
13 shamal 6565657668 pune xyz
21 shamal 7768886542 pimpari xyz
SQL> create or replace procedure hj as
2 cursor c1 is select id,mname,phno,addr from gym23,member9
3 where gym23.name=member9.name
4 and city='PIMPRI';
5 res c1%ROWTYPE;
6 begin
7 open c1;
8 loop
9 fetch c1 into res;
10 exit when c1%NOTFOUND;
11 dbms_output.put_line(res.id||' '||res.mname||' '||res.phno||' '||res.addr);
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> execute hj();
OUTPUT:-
32 ANIKET 8605471492 WAGHOLI
PL/SQL procedure successfully completed.
Write a cursor which will display gym wise member details.(Use Parametrized Cursor)
Soluation:-
SQL> declare
2 g_var gym23%rowtype;
3 m_var member9%rowtype;
4 cursor g (name char) is
5 select * from gym23;
6 cursor m is
7 select * from member9;
8 begin
9 open g('abc');
10 loop
11 fetch g into g_var;
12 exit when g%notfound;
13 dbms_output.put_line(g_var.name);
14 open m;
15 loop
16 fetch m into m_var;
17 exit when m%notfound;
18 dbms_output.put_line(m_var.id||''||m_var.mname||''||m_var.phno||''||m_var.a
ddr);
19 end loop;
20 close m;
21 end loop;
22 close g;
23 end;
24 /
OUTPUT:-
abc
11raghav7875657575wagholi
12aarav7565456478wagholi
13shamal6565657668pune
pqr
11raghav7875657575wagholi
12aarav7565456478wagholi
13shamal6565657668pune
xyz
11raghav7875657575wagholi
12aarav7565456478wagholi
13shamal6565657668pune
PL/SQL procedure successfully completed.
Slip no-26
Q3 Consider the following entities and their relationships. [40]
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One. Constraint: Primary key. Project Status Constraints: C – Completed, P - Progressive, I – Incomplete Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display the name of HOD who has completed maximum project.
Soluation:-
create table project(pnoint primary key,pname char(29),sdatedate,dudgetint,status char(28)check(status in('c','i','p')));
SQL>desc project
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
PNAME CHAR(29)
SDATE DATE
DUDGET NUMBER(38)
STATUS CHAR(28)
SQL> select * from project;
PNO PNAME SDATE DUDGET STATUS
---------- ----------------------------- --------- ---------
1 abc 09-JAN-20 200000 c
2 ass 09-MAR-20 50000 i
3 hhs 04-JAN-20 300000 p
12 xyz 09-JAN-09 12000 p
create table department(dnoint primary key,dname char(24),hod char(28),loc char(29),pnoint,constraintfk_projectdepartment foreign key(pno)references project(pno));
SQL>desc department;
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
DNAME CHAR(24)
HOD CHAR(28)
LOC CHAR(29)
PNO NUMBER(38)
SQL> select * from department;
DNO DNAME HOD LOC PNO
---------- ------------------------ ------------- --------------
11 computer science mane pune 1
12 commercedespande pune 2
13 computer science kadam pune 3
23 slbdr pune 12
SQL> create or replace procedure ef as
2 cursor m is select department.hod from department,project
3 where project.pno=department.pno
4 and pname=(select max(pname) from project);
5 m1 m%rowtype;
6 begin
7 open m;
8 loop
9 fetch m into m1;
10 exit when m%notfound;
11 dbms_output.put_line('ouput:'||m1.hod);
12 end loop;
13 close m;
14 end;
15 /
Procedure created.
SQL> execute ef();
OUTPUT:-
ouput:dr
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger dx
2 before insert or update
3 on project
4 for each row
5 begin
6 if(:new.dudget<=0) then
7 raise_application_error(-20006,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into project values(78,'ds','09/jan/20',1290000,'p');
row created.
SQL> insert into project values(77,'hgo','09/jan/20',-43500,'i');
insert into project values(77,'hgo','09/jan/20',-43500,'i')
*
ERROR at line 1:
ORA-20006: enter more than 0
ORA-06512: at "SYSTEM.DX", line 3
ORA-04088: error during execution of trigger 'SYSTEM.DX'
Slip no-27
Q3 Consider the following entities and their relationships. [40]
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display the plan having minimum response.
Soluation:-
SQL> create table plan11(pnoint primary key,pname varchar2(24),nooffreecallsint,freecalltimetimestamp,famtint check(famt>0));
Table created.
SQL> create table cust11(cnoint primary key,cname varchar2(28),mnoint,pnoint,constraint fk_plan11cust11 foreign key(pno)references plan11(pno));
Table created.
SQL> insert into plan11values(11,'ddsd',12,'09/jan/07 12:09:09',1200);
1 row created.
SQL> insert into plan11values(12,'ytti',22,'02/feb/03
11:05:07',1300);
1 row created.
SQL> insert into plan11 values(13,'kuio',23,'01/mar/0211:02:03',1400);
1 row created.
SQL> select * from plan11;
PNO PNAME NOOFFREECALLSFREECALLTIME FAMT
-------------------------------------------------------------------------------------------------------
11 ddsd 1209-JAN-07 12.09.09.000000 PM 1200
12 ytti 2202-FEB-03 11.05.07.000000 AM1300
13 kuio 2301-MAR-02 11.02.03.000000 AM1400
SQL> insert into cust11 values(1,'priti',1223223232,11);
1 row created.
SQL> insert into cust11 values(2,'shamal',567576687,12);
1 row created.
SQL> insert into cust11 values(3,'raghav',576786878,13);
1 row created.
SQL> select * from cust11;
CNO CNAME MNO PNO
---------- ---------------------------- ---------- ----------
1 priti 1223223232 11
2 shamal 567576687 12
3 raghav 576786878 13
SQL> create or replace procedure yh as
2 cursor k is select pname from plan11
3 where nooffreecalls=(select min(nooffreecalls) from plan11);
4 k1 k%rowtype;
5 begin
6 open k;
7 loop
8 fetch k into k1;
9 exit when k%notfound;
10 dbms_output.put_line('output:'||k1.pname);
11 end loop;
12 close k;
13 end;
14 /
Procedure created.
OUTPUT:-
SQL> execute yh();
output:ddsd
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on mobile number having length less than or greater than10. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger mn
2 before insert or update
3 on cust11
4 for each row
5 begin
6 if(length(:new.mno)<10 or length(:new.mno)>10) then
7 raise_application_error(-20007,'enter must 10 number');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into cust11 values(89,'shamal',7768886542,11);
1 row created.
SQL> insert into cust11 values(90,'shamal',776888654,12);
insert into cust11 values(90,'shamal',776888654,12)
*
ERROR at line 1:
ORA-20007: enter must 10 number
ORA-06512: at "SYSTEM.MN", line 3
ORA-04088: error during execution of trigger 'SYSTEM.MN'
SQL> insert into cust11 values(91,'shamal',77688865423,13);
insert into cust11 values(91,'shamal',77688865423,13)
*
ERROR at line 1:
ORA-20007: enter must 10 number
ORA-06512: at "SYSTEM.MN", line 3
ORA-04088: error during execution of trigger 'SYSTEM.MN'
Slip no-28
Q3 Consider the following entities and their relationships. [40]
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which accept a table number and display total amount of bill for a specific table
Soluation:-
SQL> create table bill(bnoint primary key,dayvarchar(27),tnoint,totalint);
Table created.
SQL>desc bill
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NOT NULL NUMBER(38)
DAY VARCHAR2(27)
TNO NUMBER(38)
TOTAL NUMBER(38)
SQL> insert into bill values(1,'monday',23,123);
1 row created.
SQL> insert into bill values(2,'saturday',23,234);
1 row created.
SQL> insert into bill values(3,'saturday',21,45);
1 row created.
SQL> select * from bill ;
BNO DAY TNO TOTAL
---------- --------------------------- ---------- -----------------------------------
1 monday 23 123
2 saturday 23 234
3 saturday 21 45
SQL> create table menu(dnoint primary key,d_descvarchar(29),price int);
Table created.
SQL>desc menu
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
D_DESC VARCHAR2(29)
PRICE NUMBER(38)
SQL> insert into menu values(11,'asd',234);
1 row created.
SQL> insert into menu values(12,'fsd',659);
1 row created.
SQL> insert into menu values(13,'jho',467);
1 row created.
SQL> select * from menu;
DNO D_DESC PRICE
---------- ----------------------------- ------------------
11 asd 234
12 fsd 659
13 jho 467
SQL> create table bm(bno int references bill(bno),dno int references menu(dno));
Table created.
SQL>desc bm
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NUMBER(38)
DNO NUMBER(38)
SQL> insert into bm values(1,11);
1 row created.
SQL> insert into bm values(1,12);
1 row created.
SQL> insert into bm values(2,13);
1 row created.
SQL> insert into bmvalues(2,12);
1 row created.
SQL> insert into bm values(3,12);
1 row created.
SQL> insert into bm values(3,13);
1 row created.
SQL> select * from bm;
BNO DNO
---------- ----------
1 11
1 12
2 13
2 12
3 12
3 13
6 rows selected.
SQL> create or replace function df(n IN number)
2 return number as
3 res number(10);
4 begin
5 select total into res from bill
6 where tno=n;
7 return res;
8 end;
9 /
Function created.
FUNCTIN CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||df(21));
3 end;
4 /
output:-
output:45
PL/SQL procedure successfully completed.
Write a cursor which will display table wise menu details.
Soluation:-
SQL> declare
2 cursor z is select bill.tno,menu.dno,d_desc,price
3 from bill,menu,bm
4 where bill.bno=bm.bno
5 and menu.dno=bm.dno
6 order by tno;
7 z1 z%rowtype;
8 begin
9 open z;
10 loop
11 fetch z into z1;
12 exit when z%notfound;
13 dbms_output.put_line(z1.tno||''||z1.dno||''||z1.d_desc||''||z1.price);
14 end loop;
15 close z;
16 end;
17 /
output:
21 12 fsd 659
21 13 jho 467
23 12 fsd 659
23 12 fsd 659
23 11 asd 234
23 13 jho 467
PL/SQL procedure successfully completed.
Slip no-29
Q3 Consider the following entities and their relationships. [40]
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many. Constraint: Primary key, inv_amount should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will return total investment amount of a particular client.
Soluation:
1)Create table employee(eidint primary key,ename char(29),addr char(28));
2)Create table investment1(inoint primary key,iname char(29),idatedate,iamtint,eidint,constraintfk_employeeinvestment1 foreign key(eid)references employee(eid));
SQL>desc employee
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL NUMBER(38)
ENMAE CHAR(25)
ADDR CHAR(27)
SQL>desc investment
Name Null? Type
----------------------------------------- -------- ----------------------------
INO NOT NULL NUMBER(38)
INAME CHAR(28)
IDATE DATE
IAMT NUMBER(38)
EID NUMBER(38)
SQL> select * from employee;
EID ENMAE ADDR
---------- ------------------------- --------------------------
101 raghavpune
103 aaravwagholi
102vijaymumbai
1raghavpune
SQL> select * from investment;
INO INAME IDATE IAMT EID
---------- ---------------------------- --------- ---------- ----------
1 rahul 09-JAN-02 1200000 101
2 archana 02-MAR-05 1000000 102
3 pooja 04-MAR-09 9000000 103
6 xyz 26-NOV-16 27000 102
8 xyz 26-NOV-08 27000 102
78 xyz 26-NOV-22 27000 102
7 xyz 03-NOV-23 27000 102
89 xyz 09-NOV-23 27000 102
34 mutual fund 17-JAN-09 120000 1
33 mutual fund 17-JAN-09 1200000 101
10 rows selected.
SQL> create or replace function fr(xz in char)
2 return number as
3 sd number;
4 begin
5 select sum(investment.iamt) into sd
6 from employee,investment
7 where employee.eid=investment.eid
8 and enmae=xz;
9 return sd;
10 end;
11 /
Function created.
FUNCTION CALLING:-
SQL> begin
2 dbms_output.put_line('output:'||vc('raghav'));
3 end;
4 /
output:7020000
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on Investment having investment amount less than 50000. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger cx
2 before insert or update
3 on investment
4 for each row
5 begin
6 if(:new.iamt<50000) then
7 raise_application_error(-20005,'enter more than 50000');
8 end if;
9 end;
10 /
Trigger created.
OUTPUT:-
SQL> insert into investment values(19,'hj','09/jan/09',12000000,101);
1 row created.
SQL> insert into investment values(167,'hj','09/jan/09',12000,102);
insert into investment values(167,'hj','09/jan/09',12000,102)
*
ERROR at line 1:
ORA-20005: enter more than 50000
ORA-06512: at "SYSTEM.CX", line 3
ORA-04088: error during execution of trigger 'SYSTEM.CX'
Slip no-30
Q3 Consider the following entities and their relationships. [40]
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.
Soluation:-
SQL> desc library0;
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL NUMBER(38)
LNAME CHAR(26)
LOCATION CHAR(27)
LIBRARIAN CHAR(25)
NOB NUMBER(38)
SQL> select * from library0;
LNO LNAME LOCATIONLIBRARIAN NOB
------------------------- -----------------------------------------------------------------------
1 dcmi punemane 1000
2 dipak mumbaijadhav 13200
3 divy wagholikadam 13000
4ganesh punebhagat 1300
5rani Mumbai patil 1800
9 dpu punehg 1200
12 dpu punehg 1200
7 rows selected.
SQL> desc book0;
Name Null? Type
----------------------------------------- -------- ----------------------------
BID NOT NULL NUMBER(38)
BNAME CHAR(25)
A_NAME CHAR(28)
PRICE NUMBER(38)
PUBLICATION CHAR(28)
LNO NUMBER(38)
SQL> select * from book0;
BID BNAME A_NAME PRICEPUBLICATION LNO
---------------------------- ----------------------------------------------------------------------------------------------
21 aai patil 120ghu 1
2 karwhar pati 120jds 2
23 gh s.b.rathod 230raghav1
34 life patil 239nirali 9
67 dreams patil 239nirali 12
45 hgjh hsgwi 12whw 1
6 rows selected.
SQL> create or replace procedure sn as
2 cursor i is select bname from book0,library0
3 where library0.lno=book0.lno
4 and a_name='patil'
5 and lname='dpu';
6 i1 i%rowtype;
7 begin
8 open i;
9 loop
10 fetch i into i1;
11 exit when i%notfound;
12 dbms_output.put_line('output:'||i1.bname);
13 end loop;
14 close i;
15 end;
16 /
Procedure created.
SQL> execute sn();
OUTPUT:-
output:life
output:dreams
PL/SQL procedure successfully completed.
Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)
Soluation:-
SQL> create or replace trigger mk
2 before insert or update
3 on book0
4 for each row
5 begin
6 if(:new.price<=0) then
7 raise_application_error(-20009,'enter more than 0');
8 end if;
9 end;
10 /
Trigger created
OUTPUT:-
SQL> insert into book0 values(78,'fk','bhuj',470,'foyp',4);
1 row created.
SQL> insert into book0 values(56,'fk','bhuj',0,'fgf',5);
insert into book0 values(56,'fk','bhuj',0,'fgf',5)
*
ERROR at line 1:
ORA-20009: enter more than 0
ORA-06512: at "SYSTEM.MK", line 3
ORA-04088: error during execution of trigger 'SYSTEM.MK'
Write a procedure which will display games details having number of players more than 5
Write a trigger before insert or update on No_of_shares field should not be zero.(Raise user defined exception and give appropriate message)
Write a function to display total no_of_shares of a specific person.
Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.
Write a cursor which will display year wise details of competitions. (Use parameterized cursor)
Write a function which will return total number of football players of “Sports Club”.
Write a cursor which will display club wise details of players.
Write a procedure to display car details used on specific day.
Write a cursor which will display driver wise car details in the year 2018.
Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers.
Write a cursor which will display college wise teacher details.
Write a procedure to display name of citizens having mother toung “Marathi “ and from “India”;
Write a trigger which will fire before insert or update on country having no_of_state less than equal to zero. (Raise user defined exception and give appropriate message)
Write a procedure which will display details of products supplied by “Mr. Patil”
Write a cursor which will display wholesaler wise product details
Write a procedure to display details of students which perform practical sessions in a given Lab.
Write a procedure to display member details of gym located at “Pimpri’”
Write a cursor which will display gym wise member details.(Use Parametrized Cursor)
Write a procedure to display the name of HOD who has completed maximum project.
Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)
Write a procedure to display the plan having minimum response.
Write a trigger which will fire before insert or update on mobile number having length less than or greater than10. (Raise user defined exception and give appropriate message)
Write a function which accept a table number and display total amount of bill for a specific table
Write a cursor which will display table wise menu details.
Write a function which will return total investment amount of a particular client.
Write a trigger which will fire before insert or update on Investment having investment amount less than 50000. (Raise user defined exception and give appropriate message)
Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.
Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)
Check Boney Kapoor net worth
ReplyDeletevery nice information. for bca practical slips solutions visit to
ReplyDeletehttps://bcaslips2020.blogspot.com