BBA CA and BCA RDBMS SEM 2 Practical slips solution

BBA CA and BCA Practical slips solution

Pune University BBA(CA)/BCA Software Practical Slips Solution C Programming, Rdbms , DBMS, WebTech subjects in syllabus

RDBMS_slip

RDBMS Practical Slips Solution

Slip 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 >

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_dt)

cp_info(c_no,p_no)

1) Write a function which will return total maturity amount of policies of a particular client.

create or replace function f1(cno IN number)
return number is
tot number(15);
begin
select sum(policy_info maturity_amt) into tot
from policy_info,cp_info,client
where policy_info.p_no=cp_info.p_no and client.c_no= cp_info.c_no and client.c_no=cno ;
return tot;
end;

calling function

begin

dbms_output.put_line(‘total maturity amt of policies of given

client’||f1(2));

end;

2) Write a cursor which will display policy date wise client details.

Declare
cursor c1 (dt IN varchar) is
select c_no, c_name, c_addr, birth_date
from client,policy,clientp
where client.c_no=clientp.c_no
and policy.p_no=clientp.p_no
and policy_dt=dt;
r1 c1%rowtype;
Begin
open c1(‘&dt’);
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.c_name||’ ’||r1.c_addr||’ ’||r1.birth_date);
end loop;
close c1;
end;

-------------------------------------------------------------------------------------------

Slip 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:

1) Write function to print the total number of suppliers of a particular item

create or replace function f1(itno IN number) return number as xyz number;

begin

select count(supplier.sno) into xyz from item,supplier,is1 where item.ino=is1.ino and supplier.sno=is1.sno and item.ino=itno;

return (xyz);

end f1;

begin

dbms_output.put_line (' total no of supplier for item=' ||f1(1));

end;

2) 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)

create or replace trigger t3

before insert or update on is1

for each row

Begin

if(:new.rate<=0 or:new.quantity<=0) then

raise_application_error(-20001,'rate and qunatity should be > zero');

End if;

End;

calling trigger

insert into is1 values(1,101,0,1);

-----------------------------------------------------------------------------------------

Slip 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 table newspaper (name char(20) primary key,language char(20) , publisher varchar(20) , cost number(5) );

create table cities (pincode number(20) primary key , city char(20), state char(20));

create table nc (name char(20) references newspaper(name), pincode number(20) references cities(pincode), dailyr number(20) );

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) 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).

create or replace trigger t8
before insert on cities
for each row
Begin
if(length(:new.pincode) !=6) then
raise_application_error(-20001,'pincode length should be 6');
End if;
End;

calling trigger

insert into cities values(1125,'pune','mh');

2) Write a procedure to calculate city wise total cost of each newspaper

Write a procedure to calculate city wise total cost of each newspaper

CREATE OR REPLACE Procedure tc(ecity IN char)

tot number;

BEGIN

select sum(cost) into tot from newspaper,cities,nc where newspaper.name=nc.name and cities.pincode=nc.pincode and city=ecity;

dbms_output.put_line('citiwise total cost of newspapaper='||tot);

END;

Calling procedure

begin

tc('pune');

end;

-----------------------------------------------------------------------------------------

Slip4

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)

create table client(cno int primary key,cname varchar(10),addr varchar(15),bdate varchar(15));

create table policy(pno int primary key,disc varchar(10),mamt int,pamt int,pdate varchar(15));

create table cp(cno int references client(cno),pno int references policy(pno));

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:

1) Write a procedure which will display all policy details having premium amount less than 5000.

create or replace procedure p4 as cursor c4 is select pamt,pdate from client,policy,cp where client.cno=cp.cno and policy.pno=cp.pno and pamt< 5000;

c c4 %rowtype;

begin

open c4;

loop

fetch c4 into c;

exit when c4%notfound;

dbms_output.put_line(c.pamt||' '||c.pdate);

end loop;

close c4;

end;

Calling procedure

begin

p4();

end;

2) 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)

create or replace trigger t5

before insert or update on policy

for each row

Begin

if(:new.mamt < :new.pamt) then

raise_application_error(-20001,'mamt should > pamt');

End if;

End;

calling trigger

insert into policy values(2,'lic','20000','30000','10/5/2020');

-----------------------------------------------------------------------------------------------------------------------

Slip 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 table library(l_no number(3) primary key,lname varchar(20),location varchar(20),librarian varchar(20),no_of_book number(3) );

create table books(b_id number(3) primary key,b_name varchar(20),author_name varchar(20),price number(3),publication varchar(20),l_no number(3) references library(l_no));

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept publication name from user and display total price of

books of that publication.

create or replace function funl(pn in varchar) return number as pm number;

begin

select sum(books.price) into pm from library,books where library.l_no=books.l_no and publication='vision';

if sql %found then return(pm);

else return null;

end if;

end;

calling function

begin

dbms_output.put_line('price='||funl('vision'));

end;

2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)

Declare

cursor c1 (lib IN varchar) is

select b_name ,author_name ,price ,publication

from library,books

where library.l_no=books.l_no

and lname=lib;

r1 c1%rowtype;

Begin

open c1(:lib);

loop

fetch c1 into r1;

exit when c1%notfound;

dbms_output.put_line(r1.b_name||' '||r1.author_name ||' '||r1.price ||' '||r1.publication );

end loop;

close c1;

end;

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”

Solution:

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.

Solution:-

SQL> declare

2 cursor y is select investment.ino,iname,idate,iamt,employee.eid

3 from investment,employee

4 where employee.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)

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 procedure to display menu details having price
between 200 to 500 which were order on ‘Saturday’ .

Solution:-

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

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)

Solution:-

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)

Solution:-

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.

Solution:-

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.

Solution:-

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)

Solution:-

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.

Solution:-

SQL> Create table student2(rnoint primary key,sname char(29),class int,

timetableint);

Table created.

SQL> Create table lab2(lnoint primary key,lname char(29),capacity int,equ

cha(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.

Solution:-

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.

Solution:-

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)

Solution:-

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

Solution:-

SQL> Create table country2(cid int primary key,cname char(29),nos int,

area char(29),loc char(29),population int);

Table created.

SQL> Create table citizen3(id int primary key,name char(29),mt char(29),

snamechar(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.

Solution:-

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.
Solution:-
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)
Solution:-
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 asdescriptive 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
Solution:-

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
Solution:-

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)
Solution:-
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.
Solution:-
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.
Solution:-
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)
Solution:-
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”.
Solution:-
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.
Solution:-
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.
Solution:-
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.
Solution:-
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.
Solution:-
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.
Solution:-
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”;

Solution:-
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)
Solution:-
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”
Solution:-
   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)
Solution:-
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.
Solution:-
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)
Solution:-
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’”
Solution:-

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)
Solution:-
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.
Solution:-
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)
Solution:-

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.
Solution:-
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)
Solution:-
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
Solution:-
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.
Solution:-
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.

Solution:
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)
Solution:-
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”.
Solution:-
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)
Solution:-
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'