procedure
[Link] a program in MySQL which takes two number from user and print the max one.(Min_Max)
[Link] a procedure which accept number of records from the user and print the records.
(procedure_IN)
[Link] a procedure which accept the name of job and display the number of employees which is
involved in that job.(procedure_INOUT)
[Link] a procedure which print the maximum salary in employee table.(procedure_OUT)
[Link] a program in MySQL which accept a number and check whether the number is a palindrome
number or not.(palindrom)
[Link] a program which accept a number from user and check whether it is prime or not.(prime)
[Link] a procedure which accept number of Fibonacci series and prints the Fibonacci series up to that
count.(fibonacci)
[Link] a prgram in MySQL which accept a number and display its factorial.(factorial)
[Link] a procedure which prints first 20 even numbers.(even_series)
[Link] a procedure which accept a number from user and print sum of the even numbers between 1
to that number.(even_sum)
11. Write a procedure which accept the radius and pritns the area and circumference of the circle.
[Link] a procedure to print the maximum and minimum from the given three numbers
[Link] a program in MySQL which accept a number and display its factorial
[Link] a MySQL procedure which accept a number and prints its table number.
15. Write a PL/SQL to take two numbers as user input into two variables, calculate difference, product,
and division, and display them.
16. Write a PL/SQL to take user input into a number variable, increment it by ten and display it?
[Link] a table Student(ID number, marks number) and
insert two rows (100, 75) and (101,76). Write a PL/SQL program to take student ID as user input into a
variable , query a student table to fetch that student's marks, and display it.
18. Enter marks obtained by a student and store it into a variable.
Check if marks is greater than or equal to 35, then print 'passed, enjoy' or else print 'failed, work hard'.
19. in the above question, check if the mark is >= 60, then print 'Secured first division'. If less than 60 but
>= 45, then print ' second division', else print 'not qualified , try for next'.
20. Write a program to create a variable Grade of character type and take its value from the user. Use
case statement for below conditions.
a. If Grade is A , Print 'Excellent'
b. if Grade is B, print 'Very good'
c. If Grade is C , print 'Well done'
d. if Grade is D, print 'Try Again'
e. Else print 'Invalid Grade, please check'
21. Write a program to print even numbers between 1 and 100 using for loop.
22. Write a program to print square values of numbers from 1 to 10 using while loop.
23. Write a PL/SQL program that takes employee ID as input, it will fetch the salary of the employee,
increment it by 10%, and then return it to calling programs that will print message ' Incremented Salary
after 10% hike is ........'
24. Create a table Student_marks(id number, marks1 number,marks2 number,marks3 number),
and insert some records. Write a procedure that will take ID as input and calculate the total marks of
the student by adding all the marks, then calculate its percentage, and return percent of marks to the
calling program that will print percent of marks obtained by that student.
25. Write a PLSQL procedure that will take an integer as input, calculate its factorial, and return it to the
calling program.
26. Write a procedure which accept input as number and print the exact inverse of that number
[Link] a procedure which accept input as number and print the exact reverse of that number
28. Armstrong Number
29. Perfect number
30. Reverse Number
31 Prime number
------------------
if exists
[Link] the empno from emp table display his job, sal, deptno,deptname, location.
[Link] the empno and dispaly his total salary(sal+comm ) using out mode
[Link] job and display total count of employees working in that job profile(use count).
[Link] the manager and display the employees reporting to him. (use self join).
--------------------
function
36) Write a function to return the employee name having minimum salary from ‘HR’ department.
37) Write a function to find area of circle
38) Write a function to find area of rectangle
few program from above procedure list can also be written using function
------------------------
While/Repeat...until/loop...end loop
[Link] table of 18 using loop ,end loop
[Link] the reverse of a given number using while loop
41. Write a trigger on the product table which will insert deleted records into
product_backup table when any record from product table is deleted
product(pid,pname,price,qty)
42. Write a trigger after insert or delete or update on the product table which will update the
product_backup table table if inser/update/delete action happen on product table.
[Link] a procedure using cursor to read the product table and add first 5 records in product_temp
table and print it in the following format
product-1 costs 300 inr
product-2 costs 150 inr
.
.
product-5 costs 200 inr
[Link] a function to accept the values for three sides of a triangle and display which type of triangle it
is
([Link] angle triangle x*x=y*y+z*z or y*y=x*x+z*z or z*z=x*x+y*y)
([Link] triangle: All three sides are equal)
([Link] Triangles: An isosceles triangle has two sides of equal length.)
A) Student-Teacher Database Consider the following Entities and their Relationships
for Student-Teacher database. Student (s_no int, s_name varchar (20), s_class
varchar (10), s_addr varchar (30)) Teacher (t_no int, t_name varchar (20),
qualification varchar (15), experience int) Relationship between Student and
Teacher is many to many with descriptive attribute subject
1. Write a trigger before insert the record of Student. If the sno is less than or equal to
zero give the message “Invalid Number”.
2. Write a trigger before update a student’s s_class from student table. Display
appropriate message
[Link] a stored function to count the number of the teachers who are teaching to a
given student. Accept student name as input parameter.
4. Write a stored function to find the details of minimum experienced teacher.
[Link] the names of students of class ‘FYBCA’.
2. List the names of the students to whom ‘_______’ is teaching.
3. List the details of all teachers whose names start with the alphabet ‘S’.
4. List the names of teachers teaching subject ‘ARDBMS’.
5. Find the number of teachers having qualification as ‘Ph. D.’.
6. Find the number of students living in ‘Deccan’.
7. Find the details of maximum experienced teacher.
8. Find the names of students of class ‘SYBCA’ and living in ‘Sangvi’.
9. List the names of all teachers with their subjects along with the total number of
students they are teaching.
10. List the names of students who are taught by most experienced teacher
B) Consider the database maintained by a company which stores the details of the
projects assigned to the employees. Following are the tables:
PROJECT (PNO INTEGER, P_NAME CHAR(30), PTYPE CHAR(20),DURATION
INTEGER)
EMPLOYEE (ENO INTEGER, E_NAME CHAR (20), QUALIFICATION CHAR (15),
JOINDATE DATE)
The relationship is as follows:
PROJECT - EMPLOYEE: M-M Relationship , with descriptive attributes as start_date
(date), no_of_hours_worked (integer).
a)Write a stored function to accept project name as input and print the names of
employees working on the project. Also print the total number of employees working on
that project. Raise an exception for an invalid project name.
b)Write a stored function to decrease the Hours_worked by 2 hours, for all projects in
which employees from department no 2 is working. Raise an exception , in case the
hours_worked becomes = 0 , after updation.
c)Write a function to accept project name as input and returns the number of employees
working on the project.
d)Write a function to find the number of employees whose date of joining is before
‘03/10/2010’
C) Item(itemno integer,Itemname varchar(20),quantity integer)
Supplier(SupplierNo,Supplier name,address,city)
Item_sup(item_no integer ,Supplier_no integer,Rate Money)
Item and supplier are related with many to many relationship .Rate is descriptive attribute.
a. Write a trigger before update on rate field, If the difference in the old rate and new
rate to be entered is more than Rs 2000/ . Raise an exception and display the
corresponding message
b. Write a trigger before insert or update on rate field, If the rate to be entered is zero
then. Raise an exception and display the message “Zero rate not allowed”.
D) 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).
Solve the Queries
1. Find the names of the customers for the “Aundh” branch.
2. List the names of the customers who have received loan less than their requirement.
3. Find the maximum loan amount approved.
4. Find out the total loan amount sanctioned by “Deccan “branch.
5. Count the number of loan applications received by “[Link]” branch.
6. List the names of the customer along with the branch names who have applied for loan
in the month of September.
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) Write a stored function to print the total number of customers of a particular branch.
( Accept branch name as input parameter.) In case the branch name is invalid, raise an
exception for the same.
d) Write a stored function to increase the loan approved amount for all loans by 20%. In
case the initial loan approved amount was less than Rs 10000, then print a notice to the
user, before updating the amount .
E) Student –Marks database
Student (rollno integer,name varchar(30),address varchar(50),class varchar(10))
Subject(Scode varchar(10),subject name varchar(20))
student and subject are related with M-M relationship with attributes marks scored.
a) Write a trigger before deleting a student record from the student table. Raise a notice
and display the message “student record is being deleted”
b) Write a trigger to ensure that the marks entered for a student, with respect to a subject
is never < 10 and greater than 100.
F) News paper database
Newspaper(name varchar(20), language varchar(20),Publisher varchar(20),cost money)
Cities(pincode varchar(6), city varchar(20), state varchar(20))
Newspaper & Cities M to M relationship with descriptive attribute daily_required integer
a) Calculate the length of pincode. Write a trigger which will fire before insert on the
cities table which check that the pincode must be of 6 digit. If it is more or less then
it display the appropriate message.
b) Write a trigger which will prevent deleting cities from Maharashtra state.