C Progrmming Slip 9

BCA PRACTICAL SLIPS SOLUTION

Slip 9
Q1. Write a C program to accept a character from the user and check whether the character is a
vowel or consonant. [15 Marks]
#include <stdio.h>
int main()
{
 char c;
 int isLowercaseVowel, isUppercaseVowel;
 printf("Enter an alphabet: ");
 scanf("%c",&c);
 isLowercaseVowel = (c == 'a' || c == 'e' || c == 'i' || c == 'o' || c == 'u');
 isUppercaseVowel = (c == 'A' || c == 'E' || c == 'I' || c == 'O' || c == 'U');
 if (isLowercaseVowel || isUppercaseVowel)
 printf("%c is a vowel.", c);
 else
 printf("%c is a consonant.", c);
 return 0;
}

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

Q2. Write a program to accept two numbers as range and display multiplication table
of all numbers within that range. [25 Marks]
#include <stdio.h>
int main()
{
 int i,j;
 for(j=1; j<=5; j++)
 {
 for(i=1; i<=10; i++)
 {
 //printf("\t ");
 printf(" \n %d * %d = %d ",j, i, j*i);
 }
 printf("\n");
 }
 return 0;
}

C Progrmming Slip 8

BCA PRACTICAL SLIPS SOLUTION

Slip8

Q1. A cashier has currency notes of denomination 1, 5 and 10. Write a C program to accept
the withdrawal amount from the user and display the total number of currency notes of
each denomination the cashier will have to give. [15 Marks]
#include<stdio.h>
int main()
{
int w,x,y,z;
printf("enter withdrow amount : ");
scanf("%d",&w);
x=w/10;
w=w%10;
y=w/5;
w=w%5;
z=w;
printf("note of 10 : %d\n",x);
printf("note of 5 : %d\n",y);
printf("note of 1 : %d\n",z);
}

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

Q2. Write a menu driven program to perform the following operation on m*n
Matrix [25 Marks]
1. Calculate sum of upper triangular matrix elements
2. Calculate sum of diagonal elements
#include <stdio.h>
void main()
{
 int fig_code;
 float side, base, length, breadth, height, area, radius;
 printf("-------------------------\n");
 printf(" 1 --> sum of upper\n");
 printf(" 2 --> sum of dignola\n");
 printf("-------------------------\n");
 int i, j, a[10][10], sum, rows, columns;
 printf("\nEnter the number of Rows : ");
 scanf("%d", &rows);
 printf("\nEnter the number of Columns : ");
 scanf("%d", &columns);
 //Accept the Elements in Matrix
for (i = 0; i < rows; i++)
 for (j = 0; j < columns; j++) {
 printf("\nEnter the Element a[%d][%d] : ", i, j);
 scanf("%d", &a[i][j]);
 }
 printf("Enter the Figure code\n");
 scanf("%d", &fig_code);
 switch(fig_code)
 {

 case 1:
 sum = 0;
 for (i = 0; i < rows; i++)
 for (j = 0; j < columns; j++) {
 // Condition for Upper Triangle
 if (i < j) {
 sum = sum + a[i][j];
 }
 }
printf("sum of upper=%d",sum);
 break;
 case 2:
sum = 0;
 for (i = 0; i < rows; i++)
 for (j = 0; j < columns; j++) {
 // Condition for Upper Triangle
 if (i == j) {
 sum = sum + a[i][j];
 }
 }
printf("sum of digonal=%d",sum);
 break;

 default:
 printf("Error in figure code\n");
 break;
 }
}

C Progrmming Slip 7

BCA PRACTICAL SLIPS SOLUTION

Slip7
Q1. Write a C program to accept the x and y coordinates of two points and compute the
distance between the two points. [15 Marks]
#include <stdio.h>
#include <stdlib.h>
#include <math.h>
int main()
{
float distance, a, b, c, d;
printf("\nEnter The Coordinates of Point A:\n");
printf("\nX - Axis Coordinate: \t");
scanf("%f", &a);
printf("\nY - Axis Coordinate: \t");
scanf("%f", &b);
printf("\nEnter The Coordinates of Point B:\n");
printf("\nx - Axis Coordinate:\t");
scanf("%f", &c);
printf("\nY - Axis Coordinate: \t");
scanf("%f", &d);
distance = sqrt((c - a) * (c - a) + (d - b) * (d- b));
printf("\nDistance between Points A and B: %f\n", distance);
return 0;
}

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

Q2. Write a program to calculate Multiplication of two matrices of order m*n.
[25 Marks]
#include <stdio.h>
int main()
{
 int a[10][10], b[10][10], result[10][10], r1, c1, r2, c2, i, j, k;
 printf("Enter rows and column for first matrix: ");
 scanf("%d %d", &r1, &c1);
 printf("Enter rows and column for second matrix: ");
 scanf("%d %d",&r2, &c2);
 // Column of first matrix should be equal to column of second matrix
and
 while (c1 != r2)
 {
 printf("Error! column of first matrix not equal to row of
second.\n\n");
 printf("Enter rows and column for first matrix: ");
 scanf("%d %d", &r1, &c1);
printf("Enter rows and column for second matrix: ");
 scanf("%d %d",&r2, &c2);
 }
 // Storing elements of first matrix.
 printf("\nEnter elements of matrix 1:\n");
 for(i=0; i<r1; ++i)
 for(j=0; j<c1; ++j)
 {
 printf("Enter elements a%d%d: ",i+1, j+1);
 scanf("%d", &a[i][j]);
 }
 // Storing elements of second matrix.
 printf("\nEnter elements of matrix 2:\n");
 for(i=0; i<r2; ++i)
 for(j=0; j<c2; ++j)
 {
 printf("Enter elements b%d%d: ",i+1, j+1);
 scanf("%d",&b[i][j]);
 }
 // Initializing all elements of result matrix to 0
 for(i=0; i<r1; ++i)
 for(j=0; j<c2; ++j)
 {
 result[i][j] = 0;
 }
 // Multiplying matrices a and b and
 // storing result in result matrix
 for(i=0; i<r1; ++i)
 for(j=0; j<c2; ++j)
 for(k=0; k<c1; ++k)
 {
 result[i][j]+=a[i][k]*b[k][j];
 }
 // Displaying the result
 printf("\nOutput Matrix:\n");
 for(i=0; i<r1; ++i)
 for(j=0; j<c2; ++j)
 {
 printf("%d \t ", result[i][j]);

 }
 return 0;
}

C Programming Slip 6

BCA PRACTICAL SLIPS SOLUTION

Slip6
Q1. Write a C Program to accept a character from the keyboard and display its previous and
next character in order. Ex. If character entered is ‘d’, display “The previous character
is c”, “The next character is e”. [15 Marks]
#include <stdio.h>
int main()
{
char ch;
printf("Enter character:\t");
scanf("%c", &ch);
printf("You entered: %c\n", ch);
printf("Previous character: %c\n", ch - 1);
printf("Next character: %c\n", ch + 1);
}

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

Q2. Write a program to accept a string and then count the occurrences of a specific character of
a string. [25 Marks]
#include <stdio.h>
#include <string.h>
 // Driver code
int main()
{ char str[50]= "geeksforgeeks";
 char c ;
 int res = 0;
 printf("\n enter character ");
 scanf("%c",&c);
 for (int i=0;i<strlen(str);i++)
 // checking character in string
 if (str[i] == c)
 res++;
 printf("occurence of %c=%d",c,res++);
 return 0;
}

C Progrmming Slip 5

BCA PRACTICAL SLIPS SOLUTION

Slip5
Q1. Write a C program to accept dimensions length (l), breadth(b) and height(h) of a
cuboids and display surface area and volume (Hint : surface area=2(lb+lh+bh ),
volume=lbh ) [15 Marks]
#include <stdio.h>
int main()
{
float length, width, height;
float SA, Volume;
printf("\nPlease Enter Length, Width and Height of a Cuboid\n");
scanf("%f %f %f",&length, &width, &height);
SA = 2 * (length * width + length * height + width * height);
Volume = length * width * height;
printf("\n The Surface Area of a Cuboid = %.2f\n",SA);
printf("\n The Volume of a Cuboid = %.2f\n",Volume);
}

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

Q2.Write a program which accepts a sentence from the user and alters it as follows: Every space
is replaced by *, case of all alphabets is reversed, digits are replaced by ? [25 Marks]
# include<stdio.h>
 /* Function to get sum of digits */
int main()
{
 int c = 0;
 char ch, s[100];
 printf("Input a string\n");
 gets(s);
 for(c=0;s[c] != '\0';c++) {
 ch = s[c];
 if (ch >= 'A' && ch <= 'Z')
s[c] = s[c] + 32;
 else if (ch >= 'a' && ch <= 'z')
 s[c] = s[c] - 32;
 else if(s[c]==' ')
 s[c] = '*';
 else if(s[c]>0 || s[c]<9)
 s[c] = '?';
 }
 puts(s);
 return 0;
}

C Progrmming Slip 4

BCA PRACTICAL SLIPS SOLUTION

Slip4
Q1. Write a C program to accept two numbers and print arithmetic and harmonic mean of
the two numbers (Hint: AM= (a+b)/2 ,HM = ab/(a+b) ) [15 Marks]
#include <stdio.h>
int main()
{
int a,b;
float arithmetic_mean,harmonic_mean;
printf("enter two no. A and B :-");
scanf("%d%d",&a,&b);
arithmetic_mean = (a+b) /2;
harmonic_mean = (a*b) / (a+b);
printf("arithmetic mean = %f and harmonic mean =
%f",arithmetic_mean,harmonic_mean);
}

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

Q2. Create a structure Student (id, name, marks). Accept details of n students and write a menu
driven program to perform the following operations. [25 Marks]
a) Search student by id
b) Display all students
#include<stdio.h>
#include<stdlib.h>
struct details
{
 char name[30];
 int eid;
 int salary;
}emp[5];
void emp_search(int r)
{
 int id,i;
 printf("\nEnter Employee-Id to be Searched : ");
scanf("%d",&id);
 printf("----------------------------------------\n");
 for(i=0;i<r;i++)
 {
 if(emp[i].eid==id)
 {
 printf("Employee Id : %d",emp[i].eid);
 printf("\nName : %s",emp[i].name);
 printf("\nSalary : %d\n",emp[i].salary);
 }
 }
}
void display(int r)
{
 int i;
 printf("\nList of All Employees:\n");
 printf("-------------------------------\n");
 printf("Emp-Id\tEmp-Name Salary\n");
 printf("--------------------------------\n");
 for(i=0;i<r;i++)
 {
 printf("%d\t%s\t %d\n",emp[i].eid,emp[i].name,emp[i].salary);
 }
}
int main()
{
 int n,i,ch;
printf("/*How Many Employee Record You Want to Add*/\n\nEnter Limit : ");
 scanf("\n %d",&n);
 for(i=0;i<n;i++)
 {
 printf("-----------------------------------------");
 printf("\n\tEnter Details of Employee-%d",i+1);
 printf("\n-----------------------------------------");
 printf("\nName of Employee : ");
 scanf("%s",emp[i].name);
 printf("Employee-Id : ");
 scanf("%d",&emp[i].eid);
 printf("Salary : ");
 scanf("%d",&emp[i].salary);
 }
 while(1)
 {
 printf("-----------------------------------------\n");
 printf("\t\tMenu\n");
 printf("-----------------------------------------");
 printf("\n 1:Search Employee by E-ID");
 printf("\n 2:List of All Employee");
 printf("\n 3:Exit");
 printf("\n----------------------------------------\n");
 printf("Enter Your Choice : ");
 scanf("\n %d",&ch);
 switch(ch)
 {
case 1: emp_search(n);
 break;
 case 2: display(n);
 break;
 case 3: exit(0);
 }
 }
 return 0;
}

C Progrmming slip 3

BCA PRACTICAL SLIPS SOLUTION

Slip 3
Q1. Write a C program to accept temperatures in Fahrenheit (F) and display it in Celsius(C)
and Kelvin (K) (Hint: C=5.0/9(F-32), K = C + 273.15) [15 Marks]
#include<stdio.h>
#include<conio.h>
int main()
{
float cel,fer,kel;
printf("Enter Temperature in Fahrenheit :");
scanf("%f",&fer);
cel= (fer-32)/1.8 ;
printf("Celsius = %f \n",cel);
kel = (fer-32)/1.8 + 273.15 ;
printf("Kelvin = %f \n",kel);
return (0) ;
}

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

Q2. Write a menu driven program to perform the following operations on strings using standard
library functions: [25 Marks]
Length of String 2. Copy String 3. Connect Two Strings 4. Compare two strings
#include<stdio.h>
#include<conio.h>
#include<string.h>
 void main()
{
 char str[20],str1[20];
 int ch,i,j;
 clrscr();
 do
{
 printf("\n****MENU****");
 printf("\n1:Find Length");
 printf("\n2:Copy the Strings");
 printf("\n3:Compare the Strings");
 printf("\n4:Concatenate the Strings");
 printf("\n5:Exit");
 printf("\nEnter your choice: ");
 scanf("%d",&ch);
 switch(ch)
{

case 1:
 printf("\nEnter the string: ");
 scanf("%s",&str);
 i=strlen(str);
 printf("\nThe Length of given string is: %d",i);
 break;
 case 2:
 printf("\nEnter the first string: ");
 scanf("%s",&str);
 printf("\nEnter the second string: ");
 scanf("%s",&str1);
 strcpy(str,str1);
 printf("\nThe Copied string is: %s",str);
 break;
 case 3:
 printf("\nEnter the first string: ");
 scanf("%s",&str);
 printf("\nEnter the second string: ");
 scanf("%s",&str1);
 j=strcmp(str,str1);
 if(j==0)
 {
 printf("\nThe string is same");
 }
 else
 {
 printf("\nThe string is not same");
 }
 break;
 case 4:
 printf("\nEnter the first string: ");
 scanf("%s",&str);
 printf("\nEnter the second string: ");
 scanf("%s",&str1);
 strcat(str,str1);
 printf("\nThe Concatenated string is: %s",str);
 break;
 case 5:
 exit(0);
 break;
}
}
 while(ch!=5);
 getch();
}

C Progrmming slip 2

BCA PRACTICAL SLIPS SOLUTION

Slip2
Q1. Write a C program to accept radius of a circle and display the area and circumference
of a circle. [15 Marks]
#include<stdio.h>
int main() {
 int rad;
 float PI = 3.14, area, ci;
 printf("\nEnter radius of circle: ");
 scanf("%d", &rad);
 area = PI * rad * rad;
 printf("\nArea of circle : %f ", area);
 ci = 2 * PI * rad;
 printf("\nCircumference : %f ", ci);
 return (0);
}

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

Q2. Write a program to calculate sum of following series up to n terms. [25 Marks]
Sum=X+X2/2!+X3/3!+……
(Note: Write separate user defined function to calculate power and factorial)
#include<math.h>
#include<stdio.h>
 int fact(int index)
 {
int f = 1, i;
for(i = 1; i <= index; i ++)
{
 f = f*i;
}
return f;
 }
// Driver Code
void main()
{
 int x = 1;
 int n = 2;
 double sum = 0, m;
 // Sum of n-1 terms starting from 2nd term
 int i;
 for (i = 1; i < =n; i++) {
m = pow(x, i) / fact(i);
sum = sum + m;
 }
 printf("\n%.4f", sum);
 getch();
}

C Progrmming slip 1

BCA PRACTICAL SLIPS SOLUTION

Slip1
Q1. Write a C program to accept dimensions of a cylinder and display the surface area and
 volume of cylinder. [15 Marks]
#include <stdio.h>
int main()
{
float radius, height;
float surface_area, volume;
printf("Enter value for radius and height of a cylinder : \n");
scanf("%f%f", &radius, &height);
surface_area = 2 * (22 / 7) * radius * (radius + height);
volume = (22 / 7) * radius * radius * height;
printf("Surface area of cylinder is: %f", surface_area);
printf("\n Volume of cylinder is : %f", volume);
}

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

Q2. Create a structure employee (id, name, salary). Accept details of n employees and write a
 menu driven program to perform the following operations. [25 Marks]
a) Search employee by id
b) Display all employees
#include<stdio.h>
struct details
{
 char name[30];
 int eid;
 int salary;
}emp[5];
void emp_search(int r)
{
 int id,i;
 printf("\nEnter Employee-Id to be Searched : ");
 scanf("%d",&id);
 printf("----------------------------------------\n");
 for(i=0;i<r;i++)
 {
 if(emp[i].eid==id)
 {
 printf("Employee Id : %d",emp[i].eid);
 printf("\nName : %s",emp[i].name);
 printf("\nSalary : %d\n",emp[i].salary);
}
}
}
void display(int r)
{
 int i;
 printf("\nList of All Employees:\n");
 printf("-------------------------------\n");
 printf("Emp-Id\tEmp-Name Salary\n");
 printf("--------------------------------\n");
 for(i=0;i<r;i++)
 {
 printf("%d\t%s\t %d\n",emp[i].eid,emp[i].name,emp[i].salary);
 }
}
int main()
{
 int n,i,ch;
 printf("/*How Many Employee Record You Want to Add*/\n\nEnter
Limit : ");
 scanf("\n %d",&n);
 for(i=0;i<n;i++)
 {
 printf("-----------------------------------------");
 printf("\n\tEnter Details of Employee-%d",i+1);
 printf("\n-----------------------------------------");
 printf("\nName of Employee : ");
 scanf("%s",emp[i].name);
 printf("Employee-Id : ");
 scanf("%d",&emp[i].eid);
 printf("Salary : ");
 scanf("%d",&emp[i].salary);
 }
 while(1)
 {
 printf("-----------------------------------------\n");
 printf("\t\tMenu\n");
 printf("-----------------------------------------");
 printf("\n 1:Search Employee by E-ID");
 printf("\n 2:List of All Employee");
 printf("\n 3:Exit");
 printf("\n----------------------------------------\n");
 printf("Enter Your Choice : ");
scanf("\n %d",&ch);
 switch(ch)
 {
 case 1: emp_search(n);
 break;
 case 2: display(n);
 break;
 case 3: exit(0);
 }
 }
 return 0;
}

RDBMS_Slips

RDBMS BCA PRACTICAL SLIPS SOLUTIONS

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)

IS

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

1) Write a procedure which will display details of employees invested amount in “Mutual Fund”

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

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

1) Write a procedure to display menu details having price between 200 to 500 which

were order on ‘Saturday’ .

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)

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

1) Write a function which will accept plan number from user and display all the details of the selected plan

2) Write a cursor which will display customer wise plan details.(Use Parameterized

Cursor)

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

1) Write a function which accept department name and display total number of projects

whose status is “p”(progressive).

2) Write a cursor which will display status wise project details of each department.

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

1) Write a function which will accept member id and scheme from user and display charges paid

by that member.

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

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

1) Write a function which will accept Lab number from user and display total number of student

allocated in that lab.

2) Write a cursor which will display lab wise student details.

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

1) Write a function which will accept wholesaler name from user and will display total

number of items supplied by him.

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

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

1) Write a function which will display name of the country having minimum population.

2) Write a cursor which will display county wise citizen details.

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

1) Write a procedure which will accept teacher name from user and display his/her college details.

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

Slip 15

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

1) Write a function which will display the total number of person who are using “Swift” car

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 appropriate message)

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

1) Write a procedure which will display games details having number of players more than

5.

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)

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

1) Write a trigger before insert or update on No_of_shares field should not be

zero.(Raise user defined exception and give appropriate message)

2) Write a function to display total no_of_shares of a specific person.

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

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

2) Write a cursor which will display year wise details of competitions.

(Use parameterized cursor)

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

1) Write a function which will return total number of football players of “Sports Club”.

2) Write a cursor which will display club wise details of players.

Slip 20

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

1) Write a procedure to display car details used on specific day.

2) Write a cursor which will display driver wise car details in the year 2018.

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

1) Write a function which will accept college name from user and display total number of “Ph.D”

qualified teachers.

2) Write a cursor which will display college wise teacher details.

Slip 22

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:

1) Write a procedure to display name of citizens having mother toung “Marathi “ and from

“India”;

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

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

1) Write a procedure which will display details of products supplied by “Mr. Patil”

2) Write a cursor which will display wholesaler wise product details.(Use Parameterized cursor)

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

1) Write a procedure to display details of students which perform practical sessions in a given Lab.

2) Write a trigger which will fire before delete on Lab (Raise user defined exception and give

appropriate message)

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

1) Write a procedure to display member details of gym located at “Pimpri’”

2) Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

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

1) Write a procedure to display the name of HOD who has completed maximum project.

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

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

1) Write a procedure to display the plan having minimum response.

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

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

1) Write a function which accept a table number and display total amount of bill for a

specific table

2) Write a cursor which will display table wise menu details.

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

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

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

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

1) Write a procedure to display names of book written by “Mr. Patil” and are from “DPU

Library”.

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