CRDMS Lab Guide (Part 2) PDF

CRDMS LAB GUIDE(PART 2) FYBCA B & C (Sem 2) Concept of RDBMS (CRDMS) PL/SQL Lab Exercises (2024-2025)-Part 2 Exercise : 16 Write a PL/SQL block to calculate the square and cube of the given...

CRDMS LAB GUIDE(PART 2) FYBCA B & C (Sem 2) Concept of RDBMS (CRDMS) PL/SQL Lab Exercises (2024-2025)-Part 2 Exercise : 16 Write a PL/SQL block to calculate the square and cube of the given number. Exercise : 17 Write a PL/SQL block to calculate the area of circle. Exercise : 18 Write a PL/SQL block to calculate the square and cube of the given number. Exercise : 19 Write a PL/SQL block to accept 2 numbers from the user and interchange the values of these 2 numbers. Exercise : 20 Write a PL/SQL block that display whether or not entered number is EVEN or ODD. Exercise : 21 Write a PL/SQL block that print 1 to 25 numbers using FOR loop. Exercise : 22 Write a PL/SQL block that print 1 to 10 numbers using LOOP command. Exercise : 23 Write a PL/SQL block that calculates the factorial by using WHILE LOOP. Exercise : 24 Write a PL/SQL block that displays the information of given employee number from employee table. Consider structure of table employee as emp(empno varchar2(5), empname varchar2(20), sal number(7,2)). Use %type to declare memory variable. DR. DARSHNA RAJPUT 1  CRDMS LAB GUIDE(PART 2) Exercise : 25 Write a PL/SQL block to calculate tax value for given employee number from employee table. Consider structure of table employee as emp(empno varchar2(5), empname varchar2(20), sal number(7,2)). Use %rowtype variable. If salary is less than 3000, 10% tax other wise 25% tax. Exercise : 26 Write a PL/SQL block to find out whether or not the given employee is eligible for bonus or not according to following condition. The bonus is granted if the salary is more than the average salary of employee otherwise the bonus will be not granted. Consider structure of table employee as emp(empno varchar2(5), empname varchar2(20), sal number(7,2)). Exercise : 27 Write a PL/SQL block of code that will read marks of three different subjects from Student table and calculate result i.e. Total, Percentage. Class and update Student table. Student (mo, name, marks 1, marks2, mark3, total, percent, class) Exercise : 28 Write a PL/SQL block of code that will read balance from Account table and update table with fine if balance is less than Rs. 500. Account(acc_no,acc_name,balance,fine) Exercise : 29 Consider the following table. Student Rallno number (S), Name Varchar2(15), Join date date, Address1 Varchar2(10), Address2 Varchar2(10), City Varchar2(10) Write a PL/SQL code to display students who have joined college after the year 2011. Exercise : 30 [A] Create following tables with proper constraints, primary key. Insert at least 5 records in each table. Course(course_id, course_name) Student(sno, sname, city, mobile, birth_data, course_id) DR. DARSHNA RAJPUT 2  CRDMS LAB GUIDE(PART 2) [B] Write a PL/SQL block to display the student detail of given sno. if student belongs to “Surat” city. Exercise : 31 [A] Create following tables with proper constraints, primary key. Insert at least 5 records in each table. Work (worker_id, name, salary, department) Bonus(worker_id, bonus_amount) [B] Write a PL/SQL block to increase salary of worker in department of IT by 10% of current salary. Exercise : 32 [A] Create following tables with proper constraints, primary key. Insert at least 5 records in each table. Stud_Member (Rollno, firstname, middlename, lastname, deptid, semester, contactno, gender) department (deptid, deptname) [B] Write a PL/SQL block to display total number of female student in each department. Exercise : 33 [A] Create following tables with proper constraints, primary key. Insert at least 5 records in each table. CustMast (cust_id, cname, city, phoneno) BillMast(billno, cust_id, bill_date, bill_amt) [B] Write a PL/SQL block to give 20% discount on bill amount more than Rs. 3200. Else give 5% discount and display the detail. Exercise : 34 [A] Create following tables with proper constraints, primary key. Insert at least 5 records in each table. DoctorMaster (doc_id, name, address, specialization) PatientMaster (pat_id, pat_name, dob, address, phoneno, doc_id) [B] Write a PL/SQL block to display patient information if available. If patient information is not available then print appropriate message. Exercise : 35 Consider the following tables. DR. DARSHNA RAJPUT 3  CRDMS LAB GUIDE(PART 2) Job (jid, johname, location, status { F - finish. P - pending. A - active )) Worker (wid, wname, skill_Il [ E- expert, A" average, P= poor]) Job_assign ( jid, wid, no_of_day) Write a PL/SQL Block that will take as an input the skill level and display the job wise number of employees with same skill level had worked on each job Exercise : 36 Book (Bookid, BookName, Author, Publisher) Student (Studentld, StudentName) BockIssue (BookEssueld, Bookld, Student, IssueDate, ReturnDate, BookStatus) Write a PL/SQL code block: 1. Accept student name and display all books issued by student. 2. Display all the students' name who don't return book on return date. 3. Display all the students who don't issue book since last 3 months 4. Display all the students who issue 3 books. Exercise : 37 Write a PL/SQL code block that explains the use of TOO_MANY_ROWS exception. Exercise : 38 Write a PL/SQL code block that accepts the employee number from the user then it checks whether it is exist or not. If employee number is found then enter the corresponding record for that employee into the NEWEMP table. Also handle the error situations like the user entering a number, which does not exist in EMP table. Exercise : 39 Write a PL/SQL code block that explains the use of exception trapping functions SQLCODE and SQLERRM. Exercise : 40 Write a PL/SQL code block that explains the use of USER DEFINED exception. DR. DARSHNA RAJPUT 4