Savitribai Phule Pune University

F. Y. B. Sc. (Computer Science)

(2019 Pattern)
Semester-II

Work Book

For

CS-123 Lab Course – I & II

Name_______________________________________________________________________

College_________________________________________________________________________________________________

Roll No._____________________________ Division________________________

Academic Year________________

1
Introduction
1. About the workbook

This workbook is intended to be used by F.Y.B.Sc (Computer Science) students for the
Computer Science laboratory courses in their curriculum. In Computer Science, hands-on
laboratory experience is critical to the understanding of theoretical concepts studied in the
theory courses. This workbook provides the requisite background material as well as
numerous computing problems covering all difficulty levels.

The objectives of this book are

1) Defining clearly the scope of the course
2) Bringing uniformity in the way the course is conducted across different colleges
3) Continuous assessment of the course
4) Bring in variation and variety in the experiments carried out by different students in
a batch
5) Providing ready reference for students while working in the lab
6) Catering to the need of slow paced as well as fast paced learners

2. How to use this workbook

This workbook is mandatory for the completion of the laboratory course. It is a measure
of the performance of the student in the laboratory for the entire duration of the course.

2.1 Instructions to the students

Please read the following instructions carefully and follow them
1) You are expected to carry this book every time you come to the lab for computer
science practicals.
2) Students should prepare oneself beforehand for the Assignment by reading the relevant
material.
3) Instructor will specify which problems to solve in the lab during the allotted slot and
student should complete them and get verified by the instructor. However student should
spend additional hours in Lab and at home to cover as many problems as possible given in
this book.
4) You will be assessed for each exercise on a scale of5
i) Not done 0
ii) Incomplete 1
iii) Late Complete 2
iv) Needs improvement 3
v) Complete 4
vi) Well Done 5

2.2. Instructions to the Instructors

1) Explain the assignments and related concepts in around ten minutes using whiteboard if
required or by demonstrating the software.
2) Fill in the blanks with different values for each student.
3) Choose appropriate problems to be solved by student by ticking
Hint :For ‘C’ Language make Set A compulsory, minimum two from Set B and

2
 minimum one from Set C.
For RDBMS: Solve any 3 Sets out of Set A, B, C, D, E
4) Make sure that students follow the instruction as given above.
5) After a student completes an assignment, the instructor has to verify the outputs and
sign in the provided space after the activity.
6) Youshouldevaluateeachassignmentcarriedoutbyastudentonascaleof5asspecifiedabove
by ticking appropriate box.
7) The value should also be entered on assignment completion page of the respective Lab
course.

2.3. Instructions to the Lab administrator

You have to ensure appropriate hardware and software is made available to each student.
The operating system and software requirements on server side and also client side are as
given below
1) Server Side and client side
a. Operating System
• Linux
b. Software’s to be installed
• C
• PostgreSQL

2) Editors : Any linux based editor like vi, gedit etc.

___________________________________________________________________________

Prepared by:

Ms. Dipita Dhande H.P.T.Arts and R.Y.K. Science College, Nashik

Ms. Varsha Joshi H.P.T. Arts and R.Y.K. Science College, Nashik.
Ms. Chitra Alavani Kaveri College of Arts, Science and Commerce, Pune
Ms. Akhila Kalmad Kaveri College of Arts, Science and Commerce, Pune
Mr. Kudale Gautam V. P. Arts Science and Commerce College, Baramati
Ms. Kadam T.T V. P. Arts Science and Commerce College, Baramati
Mr. Ahire U.B V. P. Arts Science and Commerce College, Baramati

Editors:
Dr. Manisha Bharambe MES Abasaheb Garware College, Pune

Dr. Poonam Ponde Nowrosjee Wadia College, Pune

3
Assignment Completion Sheet

Section I : Advanced ‘C’

Sr. Assignment Name Marks Signature
No (out of 5)
1. Use of Simple Pointers
2. Dynamic Memory Allocation
3. String Handling Using Standard Library Functions.
4. String Handling Using User defined Functions.
5. Structure and Unions.
6. File Handling (Text Files).
7. Command Line Arguments and Pre-processor Directives.
Section II :RDBMS
1 Stored Procedure
2 Stored Function
3 Cursors
4 Exception Handling
5 Triggers
Total Marks ( out of 60 )

Total Marks (out of 15)

This is to certify that Mr / Ms _______________________________________________ having

Seat Number: _____________ has successfully completed the Laboratory course work on
Advanced ‘C’ Programming and Relational Database Management Systems.

Instructor Head

Internal Examiner External Examiner

4
 Lab Course
Section I

Advanced ‘C’ Programming

5
Assignment1: Simple Pointers in C.
A Pointer is a variable that stores the memory address of another variable

Action syntax Example

involvingPointers
Declaration of data_type * pointer_name int *p1,*p2;
pointers float *temp1;
Initialization of pointer =&variable int a, *p= &a;
pointers p1=&n;
Pointer Arithmetic The C language allow arithmetic an integer pointer
operations to be performed on increments by sizeof(int).
pointers: Increment, Decrement,
Addition, Subtraction
When a pointer is incremented
(or decremented) by 1, it
increments by sizeof(datatype).

Pointers and We can pass the address of a

Functions variable to a function. The
function can accept this address
in a pointer and use the pointer to
access the
variable’s value.
Arrays And Pointers An array name is a pointer to the int n[10];
first element in the array. It holds *n , *(n + 0 ) represents
the base address of the array. 0th element
Every array expression is
converted to pointer For eg: n[ j ], *(n+ j ),* (j + n) ,
a) a[i] is same as*(a+i) j[n]: represent the value
b) a[i][j] is same as *(*a+i)+j) of the jth element of
c) &a[i] is same as a+i arrayn
d) &a[i][j] is same as a[i]+j

Sampleprogram 1)
The following program illustrate how to handle integer values using pointers
main()
{
int a=10, b=20;
int *pa, *pb;
pa=&a;
pb=&b;
*pb=*pa+5;
printf(“a=%d\tb=%d\n”,a,b);
printf(“*pa=%d \t *pb=%d\n”,*pa,*pb);
}
6
Sample Program 2)
The following program illustrate call by value and call by reference methods of
passing the values.
main()
{
int a = 10, b = 20;
void swap1( int x, int y);
void swap2( int *ptr1, int *ptr2);

printf(“\nBefore swapping : a=%d, b=%d”, a,b);

swap1(a, b);
printf(“\nAfter swapping by swap1 : a=%d, b=%d”, a,b);
swap2(&a, &b);
printf(“\nAfter swapping by swap2 : a=%d, b=%d”, a,b);
}
void swap1( int x, int y)
{
int temp;
temp = x; x =y;y = temp;
}
void swap2( int *ptr1, int *ptr2)
{
int temp;
temp =*ptr1;*ptr1 =*ptr2;*ptr2 =temp;
}

Sample Program 3)
The following program illustrate displaying array using pointers
main()
{
int a[]={10, 5, 18, 30, 25};

for(i=0; i<5;i++)
printf(”%d\t”,*(a+i));
}

Sample Program 4)
The following program illustrate displayingmatrix using pointers
main()
{
int a[2][2]={{10, 5}, {18, 25}};

for(i=0; i<2;i++)
{
for(j=0; j<2; j++)
printf(“%d\n”, *(*(a+i)+j));
}
}
7
Set A: Write C programs for the following problems.
1. Write a program to read two integers using pointers and perform all arithmetic
operations on them.
2. Write a program to accept an integer using pointer and check whether it is even or odd.
3. Write a program to find maximum from two integers using pointers.
4. Write a program to display the elements of an array containing n integers in the reverse
order using a pointer to the array.

Set B. Write C programs for the following problems.

1. Write a program to read N integers in an array using pointers and display minimum from
them.
2. Write a function that takes radius of as parameter and two variables. Set first variable to
area of circle and second to perimeter of circle. Accept radius in main and also display
area and perimeter in main using the above function.
(Hint: Pass the addresses of the variables to the function to get area and perimeter)
3. Write a function which takes distance in kilometer, centimeter and millimeter as
parameters as an integer d and increments the distance by d millimeters. Accept distance
and d in main and Display the new distance in main using the above function.
4. Accept n integers in array A in main. Write a function which takes this array as
parameter and find minimum and maximum from it. Display these values in main.

Set C. Write programs to solve the following problems

1. Accept date (dd, mm yy). Write a function to add no of days to the date and display the
new date. Pass dd, mm and yy to the function using pointers.
2. Write a function which accepts a number and three flags as parameters. If the number is
even,
setthefirstflagto1.Ifthenumberisprime,setthesecondflagto1.Ifthenumberisdivisibleby3 or
7, set the third flag to 1. In main, accept an integer and use this function to check if it is
even, prime and divisible by 3 or 7. (Hint : pass the addresses of flags to the function)

8
Assignment 2:Dynamicmemory allocation in C.

Actions syntax Example

involvingPointers
Pointer To Pointer datatype **pointer_to_pointer; int a; int * p; int **q;
p = &a; q = *p ;
Pointer to array datatype (*var_name)[size] int (*p)[5];

Array of Pointers Datatype *array_name[size]; int *p[5];

To allocate memory The functions used are : malloc, int * p,*p1;

Dynamically calloc, realloc p = (int *) malloc(10 *
sizeof(int));
a. ptr = ( cast-type * ) malloc ( p1 = (int *) calloc(10,
byte-size) ; sizeof(int));
Allocates a block of p1=realloc(p1,20*
contiguous bytes. If the space sizeof(int));
in heap is notsufficient to
satisfy request,allocation fails,
returns NULL.

b. ptr1 = ( cast-type * ) calloc (

byte-size);
Similar to malloc, but
initializes the memory block
allocated to 0.
c. ptr = realloc( ptr, new size
);To increase / decrease
memory size.

Sample program 1)
The following program illustrate how to accept N integers and store them dynamically
and access them.
main()
{
int *p, n,i;
printf(“How many elements :”);
scanf(“%d”,&n);
p = (int *)malloc(n*sizeof(int));
/* Accepting data */
for(i=0; i<n;i++)
scanf(”%d”,p+i);
/* Displaying data */
for(i=0; i<n;i++)
printf(”%d\t”,*(p+i));
}

9
Sample program 2)
#include<stdio.h>
#include<stdlib.h>
main()
{
int *a[10],r,c,i,j;
printf("Enter the order of matrix\n");
scanf("%d%d",&r,&c);
printf("Enter matrix elements\n");
for(i=0;i<r;i++)
{
/**** dynamically allocate memory for every row ****/
a[i]=(int *)malloc(c*sizeof(int));
for(j=0;j<c;j++)
{
scanf("%d",a[i]+j);
}
}
/****** Display Matrix ******/
printf("The matrix is as below\n");
for(i=0;i<r;i++)
{
for(j=0;j<c;j++)
{
printf("%d\t",*(*(a+i)+j));
}
printf("\n");
}
}

Set A. Write C programs for the following problems.

1. Type the above sample program 1 and execute it.
2. Type the above sample program 2 and execute it.
3. Write a program to accept N integers and store them dynamically display them in
reverse order.
4. Write a program to allocate memory dynamically for n integers such that the memory is
initializedto0. And display it.

Set B. Write C programs for the following problems.

1. Accept N integers in an array using dynamic memory allocation. Find maximum from them
and display.
2. Accept n integers in an array. Copy only the non-zero elements to another array
(allocated using dynamic memory allocation). Calculate the sum and average of non-
zeroelements.
3. Accept a matrix of order mXn (Use dynamic memory allocation and array of pointers
concept). Display trace of the matrix.
4. Accept a matrix of order mXn (Use dynamic memory allocation and array of pointers
10
 concept). Display Column wise sum of elements.
Set C. Write programs to solve the following problems
1. There are 5 students numbered 1 to 5. Each student appears for different number of
subjects in an exam. Accept the number of subjects for each student and then accept the
marks for each
subject.Foreachstudent,calculatethepercentageanddisplay.(Hint:Usearrayof5pointersand
use dynamic memoryallocation)
2. Accept a matrix of order m X n using dynamic memory allocation. Construct new
matrix of order m X (n+1)(Use realloc function)such that (n+1)th column contains the
sum of all elements of the corresponding row. And display new matrix.
Example:
2 3 7  2 3 7 12
If input matrix is   then output matrix should be  
5 1 4 5 1 4 10

11
Assignment 3: String handling in C and standard library functions for
strings
A string is an array of characters terminated by a special character called NULL
character(\0). Each character is stored in 1 byte as its ASCII code.
An array of strings is a two dimensional array of characters. It can be treated as a 1-D array
such that each array element is a string.
Actions Explanation Example
Involving
strings
Declaring char string_name[size]; char str[80];
Strings
Initializing char str[]= { ’G’, ’o’, ’o’,
Strings d’,’\0’ };
char str [ ] = “Hello”;
Accepting scanf and gets can be used to accept strings char name[20], address[50];
Strings printf(“\n Enter your
name”);
scanf(“%s”,name);
printf(“\n Enter your
address”);
gets(address);
Displaying printf and puts can be used to display strings. Printf(“\n The name is %s:”,
Strings name);
printf(“\n The address is :”);
puts(address);
Declaring char array[size1][size2]; char cities[4][10]
String array

Initializing char cities[4][10] = { “Pune”,

String array “Mumbai”,
“Delhi”, “Chennai”} ;

All string operations are performed using functions in “string.h”. Some of the most
commonly used functions are
a. strlen(str) – Returns the number of characters in the string (excluding \0)
b. strcpy(deststr, srcstr) – Copies one string to another
c. strcmp(str1,str2) – Compares two strings. Returns 0 (equal), +ve (first string >
second), -ve (first string <second ). It is casesensitive.
d. strcmpi(str1,str2) – Same as strcmp but ignores case
e. strcat(str1,str2) – Concatenates the second string to the first. Returns the concatenated
string.
f. strchr(str1, ch) – truncate the string before first occurrence of character in the string.
g. strrchr(str1,ch) –truncate the string before last occurrence of character in the string.

12
 Sample program 1)
#include <string.h>
main( )
{
char str1[30],str2[30],str3[60];
printf(“\nEnter the first string:”);
gets(str1);
printf(“\n Enter the second string string:”);
gets(str2);
if (strlen(str1) == strlen(str2))
{ strcpy(str3,strrev(str1));
strcat(str3,str2);
puts(str3);
}
else
{ strcat(str1,str2);
puts(str1);
}
}

Sample Program 2)
#include <stdio.h>
main( )
{
charlist[10][20]; /*list is an array of 10 strings*/
charname[20];
int i,n;
printf(“\n How many names ?:”);
scanf(“%d”, &n);
/* Accepting n names */
for (i=0;i<n; i++)
{
printf(“\n Enter name %d,”i);
gets(list[i]);
}

printf(“\n Enter the name to be searched “);

gets(name);
for (i=0; i<n; i++)
{
if(strcmp(list[i],name)==0)
break;
}
if(i==n)
printf(“Name is in list\n”);
else
printf(“Name is not in list\n”);
}
13
Set A. Write C programs for the following problems.
1. Write a program to read a string and copy it to another sting and display copied string.
Also the length of copied string. (Use built in functions)
2. Write a program to read two strings. If first string is greater than second then
concatenate second to first and display concatenated string, If first string is smaller than
second then concatenate first to second, other-wise display length of string. (use strcmp)
3. Write a program to read a string and one character. Check whether given character is
present in the given string or not? (Hint: use strchr or strrchr)

Set B. Write C programs for the following problems.

1. 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 ?.
2. Write a program which accepts a sentence from the user. Find and display reverse of it.
(Don’t use any function).
3. Write a program that accepts n words and outputs them in dictionary order.
4. Define two constant arrays of strings, one containing country names (ex: India, France
etc) and
theothercontainingtheircapitals.(ex:Delhi,Parisetc).Notethatcountrynamesandcapitalnam
es have a one-one correspondence. Accept a country name from the user and display its
capital. Example: Input: India , Output: Delhi.

Set C. Write programs to solve the following problems

1. Write a program that accepts a string and displays it in the shape of a kite. Example:
“abc” will be displayed as:
aa
abab
abcabc
abab
aa
2. Write a program that accepts a string and generates all its permutations. For example:
ABC, ACB, BAC, BCA, CAB,CBA
3. Write a menu driven program to perform the following operations on strings using
standard library functions:
1.Length 2. Copy 3.Concatenation 4. Compare

14
Assignment 4: String handling using user defined function and
pointers.
Sample program 1)
The following program illustrate user defined function for converting string to
lower case without using pointers
#include<stdio.h>
void string_lower(char t[20], char s[20])
{
int i=0;
while(s[i] != ’\0’)
{
if (‘A’ <= s[i] && s[i] <=’Z’)
t[i]=s[i]-32;
else
t[i]=s[i];
}
t[i]=’\0’; /* terminate target string */
}
main()
{
char str1[30],str2[30];
printf(“Enter string: ”);
gets(str1);
string_lower(str2,str1);
printf(“Converted string is : %s\n”,str2);
}

Sample program 2)
The following program illustrate function for string copy using pointers
#include<stdio.h>
void string_copy(char *t, char *s)
{
while(*s != ’\0’)
{
*t=*s;
s++;
t++;
}
*t=’\0’; /* terminate target string */
}
main()
{ char str1[30],str2[30];
printf(“Enter string: ”);
gets(str1);
string_copy(str2,str1);
printf(“Copied string is : %s\n”,str2);
}
15
Set A . Write C programs for the following problems.
1. Write a program to accept a string and find its length using user defined function.
(Don’t use pointers)
2. Write a function that takes a string as parameterand returns the same string in upper
case(use pointes). Accept this string in main and display converted string in main only.
3. Write a function to find reverse of the string and use it in main.

Set B. Write C programs for the following problems.

1. Write a function to compare two strings. Write another function to reverse the string.
In main function a string and check whether it is palindrome or not using above
functions. (Hint: A palindrome string is a string which reads same in forward as well as
backward direction for example: madam, nitin, etc.)
2. Write a program that will accept a string and character to search. The program will call a
function, which will search for the occurrence of the character in the string and return its
position. Function should return –1 if the character is not found in thestring. Display this
position in main() function.
3. Write a program that will accept a string and character to search. The program will call a
function, which will find occurrence of given character in the string. Display this count
in main() function.

Set C. Write programs to solve the following problems

1. Write a Menu driven program to perform following operations on strings till user selects
exit.
1. Copy 2. Concatenate 3. Length 4. Reverse
2. Write a function to find occurrence of every character in the string. Use it in main.
For. example if input string is “This is Monitor” then output should be
Character t occurs 2 times
Character h occurs 1 times
Character i occurs 3 times
Character s occurs 2 times
Character m occurs 1 times
Character o occurs 2 times
Character n occurs 1 times
Character r occurs 1 times
3. Write a function, which displays a given number in words. Use it in main() function.
For Example: 129 One Hundred Twenty Nine
2019 Two Thousand Nineteen

16
Assignment 5: Structures and union in C
Structure: A structure is a composition of variables possibly of different data types, grouped
together under a single name. Each variable within the structure is called a ‘member’.

Nested structures: The individual members of a structure can be other structures as well.
This is called nesting of structures.

Union :A union is a variable that contains multiple members ofpossibly different data types
grouped together under asingle name. However, only one of the members can be used at a
time. They occupy thesamememory area.
Operations Syntax / Description Example
performed
Declaring a structure struct structure-name struct student
{ {
type member-1 ; char name[20];
type member-2; int rollno;
….. int marks;
type member-n ; };
};
Creating structure struct structurename variable; struct student stud1;
variables
Accessing structure variable.member stud1.name
members stud1.rollno
stud1.marks
initializing a structure the initialization values have to be struct student stud1 =
variable given in {} and in order {“ABCD”,10,95};
Pointer to a structure struct structure-name *pointer- struct student *ptr;
name; ptr = &stud1;
Accessing members pointer-name-> member-name; ptr->name;
using Pointer ptr->rollno;
Array of structures struct structure-name array- struct student stud[10];
name[size];
passing Structures to return-type function-name void display(struct student s);
Functions (struct structure-name variable);
pass an array of return-type function-name ( struct void display(struct student
structures to a function structure-name array[size]); stud[10]);
Creating a nested struct structure1 struct student
structure { {
Method 1 ... int rollno;
struct structure2 char name[20];
{ struct date
... {
} variable; int dd, mm, yy;
... } admdate;
}; };

17
Creating a nested struct structure2 struct date
structure { {
Method 2 ... int dd, mm, yy;
}; };

struct structure1 struct student

{ {
... int rollno;
struct structure2 char name[20];
variable; struct date admdate;
... };
};
Accessing nested nested structure members stud1.bdate.dd, stud1.bdate.mm
structure members can be accessed using the
(.)operator repeatedly.
self-referential A structure containing a struct node
structure pointer to the same {
structure int info;
struct node *next;
};
Unions union union-name union u
{ {
type member-1 ; char a; int b;
type member-2; };
…..
type member-n ;
};

Sample Program 1)
The following program illustrate array of structures.
#include<stdio.h>
struct student
{ int rollno;
char name[20];
int marks[3];
floatperc;
};
void main( )
{
int i, sum j;
struct student s[10];
printf(“\n Enter the details of the 10 students \n”);
for (i=0;i<10;i++)
{
printf(“\n Enter the name and roll number \n”);
scanf(“%s%d”,s[i].name, &s[i].rollno);
18
 printf(“\n Enter marks for three subjects:”);
sum = 0 ;
for { j=0;j<3;j++)
{
scanf(“%d”,&s[i].marks[j]);
sum = sum + s[i].marks[j];
}
s[i].perc=(float)sum/3;
}
/* Display details of students */
printf(“\n\n Name \t Roll no\t Percentage”);
printf(“\n================================\n”);
for(i=0;i<10;i++)
{
printf(“\n%s\t%d\t%f”,s[i].name,s[i].rollno,s[i].perc);
}
}

Sample Program 2)
The following program illustrate union.
structure is for a library book with the following details : id, title, publisher, code ( 1 – Text
book, 2 – Magazine, 3 – Reference book). If the code is 1, store no-of-copies. If code = 2,
store the issue month name. If code = 3, store edition number. Also store the cost.

struct library_book
{
int id;
char title[80],publisher[20] ;
int code;
union u
{
int no_of_copies; char month[10]; int edition;
}info;
intcost;
};
void main( )
{
struct library_book book1;
printf(“\n Enter the details of the book \n”);

printf(“\n Enter the id, title and publisher \n”);

scanf(“%d%s%s”,&book1.id, book1.title, book1.publisher);
printf(“\nEnterthecode:1-TextBook,2-Magazine,3-Reference”);
scanf(“%d”,book1.code);

19
 switch(book1.code)
{
case 1: printf(“Enter the number of copies :”);
scanf(“%d”,&book1.info.no_of_copies); break;
case2: printf(“Enter the issue month name:”);
scanf(“%s”,book1.info.month); break;
case3: printf(“Enter the editionnumber:”);
scanf(“%d”,&book1.info.edition); break;
}
printf(“Enter the cost :”);
scanf(“%d”,&book1.cost);
/* Display details of book */
printf(“\n id = %d”, book1.id);
printf(“\n Title = %s”, book1.title);
printf(“\n Publisher = %s”, book1.publisher);
switch(book1.code)
{
case1:printf(“Copies = %d:”,book1.info.no_of_copies);
break;
case2: printf(“Issue month name =%s”,book1.info.month);
break;
case3:printf(“Edition number=%d:”,book1.info.edition);
break;
}
printf(“\n Cost = %d”, book1.cost);
}

Set A . Write C programs for the following problems.

1. Create a structure employee (id, name, salary). Accept details of n employees and
display display it in summary format.
2. Type sample program 2 and execute it.

Set B . Write C programs for the following problems.

1. Create a structure item(item number, item name, rate, qty, total). Accept details of n
items (calculate total as qty * rate). And display Bill in the following format

Sr. No. Item Name Rate Qty Total

1 Pen 10 3 30.00
2 Eraser 5 1 05.00
Grand Total : 35.00

2. Create a structure car (car number, model name, colour, cost). Accept details of n cars
and write amenu driven program to perform the following operations. Write separate
functions for the different options.
i) Search byname
ii) Displayall

20
3. Create a structure movie (name, release year, duration). Accept details of n movies.
Write a function to sort them according to release year. Display them in main() function.
( Hint: Use dynamic memoryallocation.)

Set C. Write programs to solve the following problems

1. Create a structure Fraction (numerator, denominator). Accept details of n fractions and

write a menu driven program to perform the following operations. Write separate
functions for the different options. Use dynamic memoryallocation.
i) Display the largestfraction
ii) Display the smallestfraction
iii) Sortfractions
iv) Displayall
Note: While accepting fractions, store the fractions in the reduced form.

2. Modify the sample program 2 above to accept details for n books and write a menu
driven program for the following:
i) Display all referencebooks
ii) Search magazine according for specificmonth
iii) Find the total cost of all books (Hint: Useno_of_copies).

3. Create a structure employee(id, name, joiningdate(dd, mm, yyyy)). Accept the details for
n employees. Accept month and display the details of employee having birthdate in that
month. (Hint : Use Nestedstructure)

21
Assignment 6: File Handling (Text Files)

Operations Syntax Example

performed
Declaring File FILE * pointer; FILE *fp;
pointer
Opening a File fopen(“filename”,mode); fp=fopen(“a.txt”, “r”);
where mode = “r”, “w”,
“a”, “r+”, “w+”, “a+”
Checking for if (pointer==NULL) if(fp==NULL)
successful open exit(0);
Checking for end feof(pointer) if(feof(fp))
of file printf(“File has ended”);
Closing a File fclose(pointer); fclose(fp);
fcloseall();
Character I/O fgetc, fscanffputc, fprintf ch=fgetc(fp);
fscanf(fp, ”%c”,&ch);
fputc(fp,ch);
String I/O fgets, fscanffputs, fprintf fgets(fp,str,80); fscanf(fp, ”%s”,str);

Reading and fscanffprintf fscanf(fp, ”%d%s”,&num,str);

writing formatted fprintf(fp, “%d\t%s\n”, num, str);
data
Random access to ftell, fseek, rewind fseek(fp,0,SEEK_END);
files /* end of file*/
long int size = ftell(fp);
Sample program 1)
This progam illustrate to read the contents of a file and display it.
#include <stdio.h>
void main()
{
FILE * fp;
char ch,fname[20];
printf(“Enter the filename : ”);
gets(fname);
fp = fopen(fname, “r”);
if(fp==NULL)
{
printf(“File openingerror”);
exit(0);
}
while(!feof(fp))
{
ch = fgetc(fp);
printf(“%c”,ch);
}
fclose(fp);
}
22
Sample Program 2)
#include <stdio.h>
void main()
{
FILE * fp;
char str[20];
int num;
fp = fopen(“student.txt”, w+”);
if(fp==NULL)
{
printf(“File opening error”);
exit(0);
}
fprintf(fp,“%s\t%d\n”, “ABC”,1000);
fprintf(fp,“%s\t%d\n”, “DEF”,2000);
fprintf(fp,“%s\t%d\n”, “XYZ”,3000);

rewind(fp);
while( !feof(fp))
{
fscanf(fp,“%s%d”, str, &num);
printf(“%s\t%d\n”, str, num);
}
fclose(fp);
}

Set A. Write C programs for the following problems.

1. Write a program to accept a file name and a single character. Display the count
indicating total number of times character occurs in thefile
2. Write a program to accept two filenames. Copy the contents of the first file to the second
such that the case of all alphabets isreversed.
3. Write a program to accept a filename and count the number of words, lines and
characters in thefile.

Set B. Write C programs for the following problems.

1. Write a program which accepts a filename and an integer as command line arguments
and encrypts the file using the key. (Use any encryptionalgorithm).
2. Write a program to read integers from a file and write even and odd numbers in separate
files.
3. Write a program to compare two files character by character and check whether they are
same or not?
4. Write a program to accept details of n students (roll number, name, percentage) and
write it to a file named “student.txt”. Accept roll number from the user and search the
student in the file.

23
Set C . Write C programs for the following problems.

1. Write a menu driven program for a simple text editor to perform the following
operations on afile, which contains lines oftext.
i. Display thefile
ii. Copy m lines from position n top
iii. Delete m lines from positionp
iv. Modify the nthline
v. Add nlines

2. Write a program two read text files and perform the following actions till user selects
exit.
i. Concatenate two files and save in third file.
ii. Merge the files.
iii. exit

24
Assignment 7: Command line arguments and preprocessor
directives.

Actions Syntax Example

Declaring They begin with a #

Preprocessor which must be the first
directives non-spacecharacter on
theline.
They do not end with a
semicolon.
Defining Macro # define MACRO- # define PI 3.142
Substitution NAME value
Directive
Defining # define # define SQR(x)x*x
Argumentedmacro MACRO(argument) value #define LARGER(x,y)
((x)>(y)?(x):(y))
Defining Nested one macro using another #define CUBE(x) (SQUARE(x)*(x))
macro
Defining File #include<filename> #include <stdio.h>
Inclusion #include“filename”
directive
Using Conditional # if, # else, # elif, # endif #ifdef PI #undef PI
Compilation #ifdef #endif
directive
Using Command int argc - argument void main(int argc, char *argv[])
Line Arguments counter char *argv[]- {
argument vector printf(“There are %d arguments in
all”, argc);
for (i=0; i<argc; i++)
{
printf(”Argument %d=%s”,
i,argv[i]);
}
}
To run a program Compile the program Example: a.out ABC 20
using command using cc Execute the Here, ABC and 20 are the two
line arguments program using a.out command line arguments which are
followed by command stored in the form of strings.
line arguments To use 20 as an integer, use function
atoi .
Example: int num = atoi(argv[2]);

25
Sample Program 1)
#define INRANGE(m) ( m>= 1 && m<=12)
#define NEGATIVE(m)(m<0)
#define ISLOWER(c) (c>=’a’&&c<=’z’)
#define ISUPPER(c) (c>=’A’&&c<=’Z’)
#define ISALPHA(c) (ISUPPER(c)||ISLOWER(c))
#define ISDIGIT(c)(c>=’0’&&c<=’9’)

void main()
{
int m; char c;
printf(“Enter an integer corresponding to the month”);
scanf(“%d”,&m);
if(NEGATIVE(m))
printf(“Enter a positive number”);
else
if(INRANGE(m))
printf(“You Entered a valid month”);

printf(“Enter a character:”);
c=getchar();
if(ISAPLHA(c))
printf(“You entered an alphabet”);
else
if(ISDIGIT(c))
printf(“You Entered a digit”);
}

Sample Program 2)
#include <stdio.h>
void main(int argc, char *argv[])
{
FILE * fp; long
intsize;
fp = fopen(argv[1], “r”);
if(fp==NULL)
{
printf(“File opening error”);
exit(0);
}
fseek(fp, 0, SEEK_END); /* move pointer to end of file */
size = ftell(fp);
printf(“The file size = %ld bytes”, size);
fclose(fp);
}

26
Set A Write C programs for the following problems.

1. Write a program to define value of pi and use it to find area and perimeter of a circle.
2. Write a program to display all command line arguments passed to main in the reverse
order.
3. Write a program to accept three integers as command line arguments and find the
minimum, maximum and average of the three. Display error message if invalid number
of arguments are entered.
4. Define a macro MIN which gives the minimum of two numbers. Use this macro to find
the minimum of nnumbers.

Set B . Write C programs for the following problems.

1. Write a program which accepts a string and two characters as command line arguments and
replace all occurrences of the first character by thesecond.
2. Write a program which accepts a two integersas command line arguments. If second
number is 1 then display factorial of first number and if second number is 2 the check
whether first number is even or odd. If the user enters invalid number of arguments,
display appropriate message.
3. Write a program to accept two file names as command line argument and copy the contents of
first file to second file.
4. Write a program to accept two file names using command line argument and copy the contents
of first file to second after deleting all blank lines.

Set C . Write C programs for the following problems.

1. Create a header file “mymacros.h” which defines the followingmacros.

i. SQR(x)
ii. CUBE(x) - nested
iii. GREATER2(x,y)
iv. GREATER3 (x,y,z) –nested
v. FLAG ( value=1) (which may or may not bedefined)
Include this file in your program. Write a menu driven program to use macros SQR,
CUBE, GREATER2 and GREATER3. Your program should run the first two macros if the
macro called FLAG has been defined. If it is not defined, execute the other two macros.
Run the program twice – with FLAG defined and with FLAG not defined.

2. Write aprogram to read three file names using command line arguments. Write all even lines to
first file and odd lines to second file. And display both new files.

27
Lab Course

Section II

RDBMS

28
Assignment 1: Procedures

( Postgresql 11 and above support CREATE PROCEDURE command.

Versions bellow 11 should solve assignment 1 using functions.)

A subprogram is a program unit/module that performs a particular task. These subprograms are
combined to form larger programs. This is basically called the 'Modular design'. A subprogram
can be invoked by another subprogram or program which is called the calling program.
A subprogram can be created at the schema level, Inside a package, Inside a PL/SQL block.
At the schema level, subprogram is a standalone subprogram. It is created with the CREATE
PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be
deleted with the DROP PROCEDURE or DROP FUNCTION statement.
A subprogram created inside a package is a packaged subprogram. It is stored in the database
and can be deleted only when the package is deleted with the DROP PACKAGE statement. We
will discuss packages in the chapter 'PL/SQL - Packages'.
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.
PL/SQL provides two kinds of subprograms −
• Functions − These subprograms return a single value; mainly used to compute and return
a value.
• Procedures − These subprograms do not return a value directly; mainly used to perform
an action.
Each PL/SQL subprogram has a name, and may also have a parameter list.

S.No Parts & Description

1 Declarative Part
It is an optional part. However, the declarative part for a subprogram does
not start with the DECLARE keyword. It contains declarations of types,
cursors, constants, variables, exceptions, and nested subprograms. These
items are local to the subprogram and cease to exist when the subprogram
completes execution.
2
Executable Part
This is a mandatory part and contains statements that perform the designated
action.
3 Exception-handling
This is again an optional part. It contains the code that handles run-time
errors.

29
PostgreSQL 11 introduced stored procedures that support transactions.
Create Procedure Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $$
stored_procedure_body;
$$;

Example 1:
CREATE OR REPLACE FUNCTION sum_n_product3(IN x int,IN y int, OUT sum int, OUT prod int) AS $$
BEGIN
IF x < 2 THEN
RAISE WARNING 'information message %', now();
RAISE NOTICE 'information message %', now();
RAISE INFO 'information message %', now();
END IF;
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql

Solve any three from the Following:

SET A
a. Write a procedure to display sum and product of two numbers using IN and OUT.
b. Write a procedure to display division of two numbers use raise to display error messages.

SET B

Create table employee (empno,ename,dept,salary).

a. Write a procedure to insert values in employee table.
b. Write a procedure to accept dept and display all employees working in that dept.

SET C

Route(route_no, source, destination, no_of_station)

Bus (bus_no, capacity, depot_name)
Relationship between Route and Bus is one-to-many

a. Write a procedure which will display all bus details for a given route.
b. Write a procedure to update source of route no 101.

30
SET D
Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)
Relationship between Patient and Doctor is many-to-many with descriptive attribute disease and
no_of_visits.

a. Write a procedure which will display patient detail who has visited more than 3 times to
the given doctor for ‘Diabetes’.
b. Write a procedure which displays Mr.Kumars total number of Visit.

Assignment Evaluation

0: Not Done [ ] 1: Incomplete [ ] 2: Late Complete [ ]

3: Needs Improvement [ ] 4: Complete [ ] 5: Well Done [ ]

31
Assignment 2 - Stored Functions
Stored functions are user defined functions, that are created using the CREATE FUNCTION
statement. The functions, thus created, are called stored functions because they are stored as
database objects within the PostgreSQL database. The CREATE FUNCTION command names
the new function, states its arguments and return type.
Creating a stored function:
CREATE FUNCTION identifier (arguments) RETURNS type AS’
`DECLARE
Variable–declarations; [……]
BEGIN
Statement;
[……..]
END ;
‘ LANGUAGE ‘plpgsql’;

Argument Variables:
PL/pgSQL functions can accept argument variables of different types. Function arguments allow
a user to pass information into a function, that the function may need.
Each function argument that is received by a function is incrementally assigned to an identifier
that begins with the dollar ($) sign and is labeled with the argument number. Thus the identifier
$1 is used for the first argument, $2 is used for the second argument and so on an so forth.
PL/pgSQL allows us to create variable aliases. Aliases are created using the ALIAS keyword
and it gives us the ability to provide an alternate variable to use when referencing argument
variables. All aliases should be declared within the DECLARE section of a block before they are
used.
Returning variables:
PL/pgSQL functions must return a value that matches the data type specified as the return type
during the function definition. Values are returned from a function using the RETURN
statement.
Attributes:
PL/pgSQL provides variable attributes that basically assists or helps the database programmer ,
when working with database objects. These attributes are %TYPE and %ROWTYPE.
The TYPE attribute : Used to declare a variable with the data type of a referenced database
object ( a table column).
Syntax :Variable_nametable_name.column_name%TYPE ;
The %ROWTYPEattribute : Used to declare a PL/pgSQL record variable to have the same
structure as the rows in a table , that we specify in the function block.
Syntax :Variable_nametable_name%ROWTYPE;
Controlling Program Flow:
Most programming languages provides different ways of controlling the flow of execution,
within a program. PL/pgSQL also provides various statements using which a programmer can
control the way actions will be executed within a PL/pgSQL function code. PL/pgSQL provides

32
conditional statements and control loops for the same.

Satement Syntax
1 The IF/THEN statement If < Condition> Then
statement;
[..]
Else
Statement;
[..]
End if;
Nested If-else if If Then
Statement;
[..]
Else if Then
Statement;
[….]
Else if Then
Statement;
[……]
Else
Statement;
[….]
End if;
End if;
End if;
Loops LOOP
Statement;
[………]
EXIT [label] WHEN
END LOOP;
While Loop While Condition
Loop
Statement;
[…….]
End loop;
For Loop For Loop–Index In {Reverse} expression1 ..... expression2
Loop
Statement;
[……..]
End loop;
For Loop (iterate through a For {record_variable | %rowtype_variable } IN
query resultset) select_statement
LOOP
Statement;
[……….]
END LOOP

33
SET A
Using If.-Then-else,case,for,while and unconditional loops

1. Find maximum and minimum from two numbers

2. Check the number is positive, negative or zero.
3. Find maximum and minimum from three numbers
4. Find number is even or odd
5. Find sum of first 10 numbers (using unconditional loop)
6. Display all odd numbers from 1 to 50.
7. Find sum and average of first n numbers using conditional loop(while)
8. Count even numbers from given range(m to n) (for)
9. Search the given number is in given range.
10. Display a number in word (Using Case) and loop.

SET B
Bank database
Consider the following database maintained by a Bank. The Bank maintains information about
its branches, customers and their loan applications.
Following are the tables:
BRANCH (BID INTEGER, BRNAME CHAR (30), BRCITY CHAR (10))
CUSTOMER (CNO INTEGER, CNAME CHAR (20), CADDR CHAR (35), CITY CHAR(20))
LOAN_APPLICATION (LNO INTEGER, LAMTREQUIRED MONEY, LAMTAPPROVED
MONEY, L_DATE DATE)
The relationship is as follows: BRANCH, CUSTOMER, LOAN_APPLICATION are related
with ternary relationship. TERNARY (BID INTEGER, CNO INTEGER, LNO INTEGER).
a) Write a function that returns the total number of customers of a particular branch.( Accept
branch name as input parameter.)
b) Write a function to find the maximum loan amount approved.
c) Create a function which returns the total number of customers who have applied for a loan
more than Rs.100000. (Accept loan amount as input parameter)

SET C
Student- Teacher database
Consider the following database maintained by a school. The school maintains information about
students and the teachers. It also gives information of the subject taught by the teacher.
Following are the tables:
STUDENT (SNO INTEGER, S_NAME CHAR(30), S_CLASS CHAR(10), S_ADDR
CHAR(50))
TEACHER (TNO INTEGER, T_NAME CHAR (20), QUALIFICATION CHAR
(15),EXPERIENCE INTEGER)
The relationship is as follows: STUDENT-TEACHER: M-M with descriptive attribute
SUBJECT.
a) Write a function to find name of the most experienced teacher for “Mathematics”.

34
 b) Write a function to find the teacher teaching maximum number of subjects.
c) Write a function to find the number of teachers having qualification “Ph. D.”.

SET D
Project – Employee Database
Consider the following database
Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute
start_date.
Create the above database in PostGreSQL and insert sufficient records.
Execute any two of the following using PL/pgSQL
a. Write a stored function to accept project type as an input and display all project names of
that type.
b. Write a function which accepts employee name and prints the details of the project which
the employee works on.
c. Write a function to accept project name as input and returns the number of employees
working on the project.

SET E
Student - Subject Database
Consider the following database
Student (roll_no integer, name varchar(30), address varchar(50), class varchar(10))
Subject (scodevarchar(10), subject_name varchar(20))
Student-Subject are related with M-M relationship with attributes marks_scored.
Create the above database in PostGreSQL and insert sufficient records.
a.Write a function which will accept the roll no and print all thedetails of that student.
b. Write a function to accept student name as input and displays details of that student.
c. Write a stored function using cursors, to accept class from the user and display the details of
the students of that class.

Assignment Evaluation

0: Not Done [ ] 1: Incomplete [ ] 2: Late Complete [ ]

3: Needs Improvement [ ] 4: Complete [ ] 5: Well Done [ ]

35
Assignment 3: Cursors
PL/SQL Cursors provide a way to select multiple rows of data from the database and then to
process each row individually. Using a cursor, we can traverse up and down a result set and
retrieve only those rows which are explicitly requested. Cursors basically help an application to
efficiently use a static result set.
Declaring Cursor Variables
All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special
data type refcursor. We can declare a cursor variable either as a bound cursor variable or an
unbound cursor variable.
a. To create an unbound cursor variable , just declare it as a variable of type refcursor.
b. To create a bound cursor variable, use the following cursor declaration.
syntax
name CURSOR [ ( arguments ) ] FOR query;

where arguments, if specified, is a comma-separated list of pairs ‘name datatype’ that define
names to be replaced by parameter values in the given query. The actual values to substitute for
these names will be specified later, when the cursor is opened (parameterized cursors).
Opening Cursors
Before a cursor can be used to retrieve rows, it must be opened. PL/pgSQL has three forms of
the OPEN statement, two of which use unbound cursor variables while the third uses a bound
cursor variable.
Syntax for OPEN FOR query
OPEN unbound_cursorvar FOR query;
Syntax for OPEN FOR EXECUTE
OPEN unbound_cursorvar FOR EXECUTE query_string USING expression [, ... ] ];
Syntax for Opening a Bound Cursor
OPEN bound_cursorvar [ ( argument_values ) ];

Using Cursors
Once a cursor has been opened, it can be manipulated with the statements describedbelow.
FETCH
Syntax :
FETCH [ direction { FROM | IN } ] cursor INTO target;
The direction clause can be any of the following variants :
NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD,
orBACKWARD. Default is NEXT.
MOVE : MOVE repositions a cursor without retrieving any data.
Syntax :
MOVE [ direction { FROM | IN } ] cursor;
The direction clause can be any of the variants NEXT, PRIOR, FIRST, LAST,
36
ABSOLUTEcount, RELATIVE count, ALL, FORWARD [ count | ALL ], or BACKWARD [
count | ALL ].
CLOSE : CLOSE closes the portal underlying an open cursor. This can be used to
releaseresources earlier than end of transaction, or to free up the cursor variable to be
openedagain.
Syntax :
CLOSE cursor;
Example 1:
Consider a relation, Employee (eno, ename, deptno,salary). We write a function, to Definea
cursor to print the details of the employee along with commission earned for eachemployee.
Commission is 20% of salary for employees of dept no = 5; its 50% of salary foremployees of
dept no = 8; its 30% of salary for employees of dept no = 10.
Create function cursor_demo( ) returns integer as ‘
Declare
Emp–rec Employee%rowtype
C–emp cursor for Select * from employee;
Comm Number (6,2);
Begin
Loop
Fetch C–emp into emp–rec;
If emp–rec.deptno = 5 then
Comm:= emp–rec.salary * 0.2;
Else if emp–rec.deptno = 8 then
Comm := emp–rec.salary * 0.5;
Else If emp–rec.deptno = 10 then
Comm := emp–rec.salary * 0.3;
End if;
End if;
Endif;
Raise notice ‘’emp–rec.ename||emp–rec.deptno||emp–rec.salary||comm. ‘’;
Exit when not found;
End loop;
Close C–emp;
End; ‘ language ‘plpgsql’;

Example 2:
Consider the following relations, Dept(dno, dname) Employee(eno, dno, ename, salary) and the
relation between Dept and Employee is one to many(1:M). Now we write a PL/pgSQL function
to print the list of employees, department wise. Here we use 2 cursors, the 1st Cursor retrieves
the department Information for each department and the 2nd cursor, to which dept number is sent
as a parameter retrieves the employees for that department.
Create function parameterizedcursor_demo( ) returns integer as ‘
Declare
37
 d–Info cursor for Select * from dept;
E–Info cursor (dnumdept.dno%type) for
Select * From Emplyee Where dno = dnum;
X number := 0;
Begin
For drecIn d–Info
Loop
Raise notice ‘’ drec.dno || drec.dname’’;
For erecIn E–Info (drec.dno)
Loop
Raise notice ‘’erec.eno || erec.ename || erec.salary’’;
X := X + 1;
End loop;
Raise notice “Total employees for:||drec.dname||‘is %’|| X’’;
End loop;
Return (X);
End; ‘ language ‘plpgsql’;

SET A
Bus – Route Database
Consider the following database
Bus (bus_noint , capacity int , depot_name varchar(20))
Route (route_no int, source varchar(20), destination varchar(20), No_of_stations int)
Bus and Route are related with many to many relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function using cursor, which will give details of all routes on which bus no
110 is running.
b. Write a stored function using cursor, which will give details of all buses on route from
“Station” to “Airport”.

SET B
Student –Teacher database
Consider the following database
Teacher( t_no int, t_name varchar(20), age int, yr_experience int)
Subject (s_no int, s_namevarchar(15))
Teacher and Subject are related with many to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function using cursor which will accept the subject name and print the
names of all teachers teaching that subject.
b. Write a cursor to accept the subject's name from the user as an input and display names
of all teachers teaching that student.

SET C
Person - Area Database
38
Person (pno int, name varchar (20), birthdate date, income money)
Area (aid int, aname varchar (20), area_type varchar (5) )
The person and area related to many to one relationship. The attribute ‘area_type’ can have
values either ‘urban’ or ‘rural’.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a cursor to accept a month as an input parameter from the user and display the
names of persons whose birthday falls in that particular month.
b. Write a cursor to display the names of persons living in urban area.
c. Write a cursor to print names of all persons having income between50000 and 100000.

SET D
Student – Competition Database
Consider the following entities and their relationship.
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.
a) Write a cursor which will display year wise details of competitions held. (Use
parameterized cursor)
b) Write a cursor which will display student wise total count of competition participated.

SET E
Car – Driver Database
Consider the following database:
Car (c_no int, owner varchar(20), model varchar(10), color varchar(10)
Driver (driver_no int, driver_namevarchar(20), license_no int, d_age int, salary float)
Car and Driver are related with many to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function with cursor which accepts the color and prints the names of all
owners who own a car of that color.
b. Write a cursor which accepts the driver name and prints thedetails of all cars that this
driver has driven, if the drivername is invalid, print an appropriate message.

Assignment Evaluation

0: Not Done [ ] 1: Incomplete [ ] 2: Late Complete [ ]

3: Needs Improvement [ ] 4: Complete [ ] 5: Well Done [ ]

39
40
Assignment 4: Handling errors and Exceptions
The RAISE statements raise errors and exceptions during a PL/pgSQL function’sexecution..A
Raise statement is also given the level of error it should raise and the stringerror message it
should send to postgreSQL. The string can also be embedded withvariables and expressions, that
one needs to list along with the error message. Thepercent (%) sign is used as the place holder
for the variables that are inserted into thestring.
The syntax of the RAISE statement is as follows :
RAISE level ‘’message string ‘’ [, identifier [….]];
The three possible values for the RAISE statement’s level are as follows:
Value Explanation
DEBUG Debug level statements send the specified text as a debug message
to the PostgreSQL log.
NOTICE Notice level statements send the specified text as a Notice;
EXCEPTION Exception level statements send the specified text as an ERROR.
The exception level also causes the current transaction to be
aborted.

Practice examples :
Example 1
In this example , the first raise statement gives a debug level message. The second andthird raise
statements send a notice to the user. The fourth raise statement displays anerror and throws an
exception, causing the function to end and the transaction to beaborted.
Create function raise_demo( ) returns integer as ‘
Declare
Int_var integer: =1;
Begin
-- raise a debug level message
Raise debug ‘’the raise demo function began’’;
Int_var := int_var+1;
--raise a notice stating the change in value of variable
Raise notice ‘’ variable int_var‘s value is now % .’’,int_var;
--raise an exception
Raise exception ‘’variable % changed. Transaction aborted.’’,int_var;
Return 1;
End;
‘language ‘plpgsql’;

SET A
Project-Employee database
Consider the following database:
Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute
start_date.
41
Create the above database in PostGreSQL and insert sufficient records.

a. Write a stored function to accept project name as input and print the names ofemployees
working on the project. Also print the total number of employees working onthat project.
Raise an exception for an invalid project name.

b. Write a stored function to accept empno as an input parameter from the user and count
the number of projects of a given employee. Raise an exception if the employee number
is invalid.

SET B
Person – Area database
Person (pno int, name varchar (20), birthdate date, income money)
Area (aid int, aname varchar (20), area_type varchar (5) )
The person and area related to many to one relationship. The attribute ‘area_type’ can have
values either ‘urban’ or ‘rural’.
Create the above database in PostGreSQL and insert sufficient records.

a. Write a stored function that accepts the area name as an input parameter from the user
and displays the details of persons living in that area. Raise an exception if area name is
invalid.

SET C
Wholesaler – Product database
Consider the following entities and their relationships.
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.
Create the above database in PostGreSQL and insert sufficient records.

a. Write a function to accept quantity from user. Quantity must be within range 50-200. If user
enters the quantity out of range then raise an user defined exception “quantity_out_of _range”
otherwise enter the record in table.
b. Write a function which accept rate from user. If user enters rate less than or equal to zero then
raise an user defined exception “Invalid_Rate_Value” otherwise display message “Correct
Input”.
c. Write a function to accept product name as parameter. If entered product name is not valid
then raise an user defined exception”Invalid_Product_Name” otherwise display product details
of specified product.

Assignment Evaluation

0: Not Done [ ] 1: Incomplete [ ] 2: Late Complete [ ]

42
3: Needs Improvement [ ] 4: Complete [ ] 5: Well Done [ ]

Assignment 5 : Triggers.
A trigger defines a function which occurs before or after, an action on a table. A trigger
isimplemented through PL/pgSQL, C or any other functional language that PostgreSQL canuse
to define a function.
A trigger is a PL/pgSQL block that is associated with a table, stored in a database andexecuted in
response to a specific data manipulation event. Triggers can be executed orfired in response to
the following events.
a. A row is inserted into table
b. A row in a table is updated.
c. A row in a table is deleted.
Syntax for defining a database trigger
Create Trigger trigger–name
{ Before | After} {event [ or event …]} ON table–name
for each { Row | statement}
execute procedure fucntionname( arguments) ;
A trigger procedure is created with the CREATE FUNCTION command, declaring it as
afunction with no arguments and a return type of trigger.
Special variables created automatically, on call to a trigger function, are as follows :
Variable Name Purpose and contents
NEW Holds the new database row for INSERT/UPDATE operations in
row-level triggers
OLD Holds the old database row for UPDATE/DELETE operations in
row-level triggers
TG_NAME Contains the name of the trigger actually fired.
TG_WHEN Specifies the trigger timing. Contains a string of BEFORE or
AFTER,
TG_LEVEL Specifies the trigger type. Contains a string of either ROW or
STATEMENT
TG_OP Specifies the trigger operation. Contains a string of INSERT,
UPDATE, or DELETE.
TG_RELID Contains the object ID of the table that caused trigger invocation.
TG_TABLE_NAME Contains the name of the table that caused the trigger invocation.
TG_TABLE_SCHEMA Contains the name of the schema of the table that
caused trigeer invocation
TG_NARGS Number of arguments given to the trigger procedure
TG_ARGV[ ] Contains the arguments for the trigger statement.

Example 1
This trigger ensures that any time a row is inserted or updated in the table, the currentuser name
43
and time are stamped into the row. And it checks that an employee's name isgiven and that the
salary is a positive value.

CREATE TABLE employee (ename text, esalary integer, last_date timestamp, last_usertext );

CREATE FUNCTION emp_timestamp() RETURNS trigger AS ‘

BEGIN
-- Check that empname and salary are given
IF NEW.ename IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.esalary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.ename;
END IF;
IF NEW.esalary< 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.ename;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
‘ LANGUAGE ‘plpgsql’;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

SET A
Movie – Actor Database
Consider the following database
Movie (m_name varchar (25), release_year integer, budget money)
Actor (a_namevarchar(30), role varchar(30), charges money, a_address varchar(30) )
Movie and Actor are related with many to many relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger which will be executed whenever an actor is deleted from the actor table,
display appropriate message.
b. Write a trigger which will be executed whenever a movie is deleted from the movie table,
display appropriate message.
c. Write a trigger which will be executed whenever insertion is made to the movie table. If
the budget is less than 1,00,000 do not allow the insertion. Give appropriate message.

SET B
Doctor – Hospital Database
Consider the following database
44
Doctor (d_no int, d_namevarchar(30), specialization varchar(35), charges int)
Hospital (h_no int, h_namevarchar(20), city varchar(10))
Doctor and Hospital are related with many to one relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger before insert/update on Doctor table. Raise exception if charges are <0.
b. Write a trigger that restricts insertion of charges value greater than 500.

SET C
Student – Subject database
Consider the following database :
Student (rollno integer, name varchar(30),city varchar(50),class varchar(10))
Subject(Scodevarchar(10),subject name varchar(20))
Student and subject are related with M-M relationship with attributes marks_scored.
Create the above database in PostGreSQL and insert sufficient records
a. Write a trigger before insert/update the marks_scored. Raise exception if Marks are
negative.
b. Write a trigger which is executed when insertion is made in the student-subject table. If
marks_scored is less than 0, give appropriate message and do not allow the insertion.
c. Write a trigger which will prevent deleting students from ‘Pune’ city.

SET D
Customer – Account database
Consider the following database
Customer (cno integer, cnamevarchar(20), city varchar(20))
Account (a_no int, a_typevarchar(10), opening_date date, balance money)
Customer and Account are related with one to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger which is executed whenever update is made tothe account table. If the
balance becomes less than 1000, print anerror message that balance cannot be less than
1000.
b. Write a trigger before deleting an account record from Accounttable. Raise a notice and
display the message “Account record isbeing deleted.”
c. Write a trigger before inserting an account record in Accounttable and raise exception if
balance is <500.

Assignment Evaluation

0: Not Done [ ] 1: Incomplete [ ] 2: Late Complete [ ]

3: Needs Improvement [ ] 4: Complete [ ] 5: Well Done [ ]

45
46