1.
(i) Write a query to the list of all students who were fail in the particular subject and also
find max marks in sub1 and average of sub 2 from total 5 subjects.(3.1)
(ii) Write PL/SQL queries to create Triggers.(6.3)
2. (i) create a Student Database with the following tables.
STUDENT_PERSONAL_INFO with fields (NAME,ROLL_NO,ADDRESS,PHONE_NO)
STUDENT_CLASS_INFO with NAME, ROLL_NO, SECTION.
Use subqueries to display NAME, PHONE_NO of the Student Database whose section is ‘A”.
(3.2)
(ii) Use appropriate Visual programming tools like oracle forms and reports, visual basic etc to
create user interface screens and generate reports for library management.(8)
3.(i) Create Employee Database with Employee_Personal (ID, NAME,
DOJ,AGE,ADDRESS,DEPARTMENT,DESIGNATION) and
Employee_Salary(ID,NAME,SALARY,BP,DA,HRA,CCA,GROSS_PAY,NET_PAY).
Use sql sub queries to updates the SALARY by 10 times in the EMPLOYEE_Salary table for
all employee whose AGE is greater than or equal to 29.(3.3)
(ii) Write a PL/SQL program to demonstrate Functions.(11)
4.(i)Use appropriate Visual programming tools like oracle forms and reports, visual basic etc
to create user interface screens and generate reports for student mark sheet processing.(9)
(ii) Write PL/SQL queries to create Triggers.(6.3)
5.(i) Write a PL/SQL program to demonstrate Packages.(7)
(ii) Use appropriate Visual programming tools like oracle forms and reports, visual basic etc to
create user interface screens and generate reports for Pay roll processing.(10)
6.(i) Write a PL/SQL program to demonstrate Cursors.(5.4)
(ii) Use VB as the front-end tool and any RDBMS as the back-end tool. Create database and
performing the operations Insertion, Deletion, Modification for Payroll processing.
DA as 30% of Basic pay, HRA as 20% of Basic pay, PF as 18 % of Basic pay.
Gross_pay as Basic Salary + HRA + DA.
Net_Pay as Gross_Pay – PF.
Calculate Gross pay and Net pay using given formula and also generate a simple report.(10)
7. (i). Create a table called Employee with the following structure.
Empno (Number), Ename( Varchar2(20)), Job (Varchar2(20) ), Sal (Number)
a). Add a column DoJ (DATE) with the Employee table.
b. Insert any five records into the table.
c. Update the column details of job
d. Rename the column Sal as Emp_Salary of Employ table using alter command.
e. Delete the employee whose empno is 101.(2.1)
(ii) Write a PL/SQL program to demonstrate Exceptions.(4.1/4.2/4.3/4.4)
8. (i). Create department table with the following structure.
Deptno (Number), Deptname (Varchar2(20)), location (Varchar2(20))
a. Add column designation to the department table.
b. Insert values into the table.
c. List the records of emp table grouped by deptno.
d. Update the record where deptno is 5.
e. Delete any column data from the table(2.2)
(ii) Write PL/SQL queries to create Triggers. .(6.3)
9. (i) Write a program to create database and perform the operations given below.
a) Insertion
b) Deletion
c) Generate a simple report for the students admitted between 2010 to 2020.(9)
(ii) Write a PL/SQL program to demonstrate Exceptions. .(4.1/4.2/4.3/4.4)
10.(i) Write a PL/SQL program to demonstrate Procedure and Functions.(11,12)
(ii) Consider the details of student database with [Link], Name, Marks in 3 subjects.
Generate a result report if marks in any subject is <50 then the student fails in the subject.(9)
11. (i) Write an application program to perform the basic DML operations with constraints
related to “Employee Database”.(10)
(ii) Write a PL/SQL program to demonstrate Procedure and Functions. .(11,12)
12.(i) Write an application program to perform the basic DDL operations with constraints
related to “Library Management”.(8)
(ii) Write a PL/SQL program to demonstrate Packages.(7)
13.(i) Write PL/SQL queries to create Triggers.(6.3)
(ii) Create a table for “Library Management System” and do the insertion, modification and
deletion and prepare simple reports.(8)
14.(i) Write PL/SQL Programs in Cursors using Loops.(5.4)
(ii) Develop “Employee” details table.
a. Count the number of employees in department 20
b. Find the minimum salary earned by clerk.
c. Find minimum, maximum, average salary of all employees.
d. List the minimum and maximum salaries for each job type.
e. List the employee names in descending order.
f. List the employee id, names in ascending order by empid.(3.4)
15.(i) Create a table called Branch
table. Name Type
Branch_name Varchar2(20)
Branch_city Varchar2(20)
Asserts Number
a. Increase the size of data type for Asserts to the branch.
b. Add and drop a column to the Branch table.
c. Insert values to the table.
d. Update the Branch_name column
e. Delete any two columns from the table(2.3)
(ii) Write Pl./SQL code in Function for Factorial number and Prime number.(11)
16.(i) Create a table called Stock table
Name Type
Sid Number
Sname Varchar2(20)
Rating Varchar2(20)
a. Add column age to the Stock table.
b. Insert values into the Stock table.
c. Delete the row with Rating >8.
d. Update the column details of Stock.
e. Insert null values into the table.(2.4)
(ii) Write a PL/SQL block to handle the following BUILT-IN EXCEPTIONS.(4.1/4.2/4.3/4.4)
17.(i) Create a table called Customer table
Name Type
Cust_name Varchar2(20)
Cust_street Varchar2(20)
Cust_city Varchar2(20)
a. Insert records into the table.
b. Add salary column to the table.
c. Alter the table column domain.
d. Drop salary column of the customer table.
e. Delete the rows of customer table whose cust_city is ‟New York”.(1.2)
(ii) Write pl/sql code using Trigger to salary with more than old salary.(6.3)
1. DDL – Ex 2
2. DML – Ex1,2,3,4
3. SUBQUERIES – Ex 1,2,3,4
4. EXCEPTION HANDLING – Any one
5. CURSOR – Ex 4
6. TRIGGER – Ex 3
7. PACKAGE
8. LIBRARY MANAGEMENT
9. STUDENT MARK SHEET
10. PAYROLL PROCESSING
11. FUNCTION – Factorial, Prime number
12. PROCEDURE