. (i) Using Employee Database with two tables.
Employee_Personal_details as parent table with NOT
1
NULL and UNIQUE constraints for Employee_ID and NOT NULL for age with Employee ID as primary
key. Also create another table Employee_Salary_details as child table with reference of Employee_ID as
foreign key. Use DDL commands create and alter. Also create
Also perform the following queries
a) Determine the names of employee, who earn more than 20000.
b) Determine the names of employees, who take highest salary in their departments.
c) Determine the employees, who are located at the same place.
d) Determine the employees, whose total salary is like the minimum salary of any department.
e) Determine the department which does not contain any employees.
(ii) Write a PL/SQL program to demonstrate Exceptions.
. (i) create a table for payroll processing and perform do the insertion, modification, deletion and
2
aggregate functions (count, max and min).
(ii) Write PL/SQL queries to create Procedures.
. (i) Write a query to the list of all students who were fail in the particular subject and also find max
3
marks in sub 1 and average of sub 2 from total 5 subjects.
(ii) Write PL/SQL queries to create Triggers
. (i) Create Employee Database with Employee Personal (ID,NAME,DOJ,AGE,
4
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
(ii) Write a PL/SQL program to demonstrate Functions.
.(i)Use appropriate Visual programming tools like oracle forms and reports, visual basic etc to create
5
user interface screens and generate reports for student mark sheet processing.
(ii) Write PL/SQL queries to create Triggers
. (i) Create an employee data base and divided into certain the database into certain departments and
6
each department consists of employees. The following two tables describes the schemes.
Dept_table (deptno, dname, loc)
Emp_table (empno, ename, job, mgr, hiredate, sal, comm, deptno)
a) Update the employee salary by 15%, whose experience is greater than 10 years.
b) Delete the employees, who completed 30 years of service.
c) Display the manager who is having maximum number of employees working under him?
d) Create a view, which contain employee names and their manager.
Generate a report of employees who gets more than 20000 salary.
(ii) Write PL/SQL queries to create Triggers.
7.(i) Write a PL/SQL program to demonstrate Cursors.
( 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.
8.(i) As a designer identify the PL/SQL procedures necessary and create them using cursors.
( ii) The student mark sheet database containing student's Name, Roll No, Exam ID, Subject marks
saymml,m2,m3,m4,m5,total,average and grade. Display the details using sql queries for the following.
a)avg>80 means "Outstanding"
b)avg>75 means "First class"
c) avg between 50 and 60 "Second class"
d) avg <50 means "Fail".9. (i) Consider the following tables namely "DEPARTMENTS" and
"EMPLOYEES" Their schemas are as follows.
.(i)Consider the following tables namely “DEPARTMENTS" and “EMPLOYEES”.Their schemas are as
9
follows.
Departments (dept_no, dept_name, dept_location);
Employees (emp_id, emp_name, emp_salary,dept_no);
a) Develop a query to use all DDL commands and constraints.
b) Develop a query to use all DML commands and constraints.
Generate a report of employees who joins after 1-jan-2020.
(ii) Write a PL/SQL program to demonstrate Procedure and Functions
0. (i). Create a table called Employee with the following structure.
1
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.
(ii) Write a PL/SQL program to demonstrate Exceptions.
1. (i). Create department table with the following structure.
1
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
(ii) Write PL/SQL queries to create Triggers.
2. (i) Write a program to create database and perform the operations given below.
1
a) Insertion
b) Deletion
c) Generate a simple report for the students admitted between 2010 to 2020.
(ii) Write a PL/SQL program to demonstrate Exceptions.
13.(i) Write a PL/SQL program to demonstrate Procedure and Functions.
( ii) Consider the details of student database with Reg.no, Name, Marks in 3 subjects. Generate a result
report if marks in any subject is <50 then the student fails in the subject.
4. (i) Write an application program to perform the basic DML operations with constraints related to
1
"Employee Database".
(ii) Write a PL/SQL program to demonstrate Procedure and Functions.
15.(i) Write PL/SQL Programs in Cursors using Loops.
( 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 employees1
6.(i) Create a "Employee" table with minimum 6 attributes. Also do the following.
1
a. Create a user and grant all permissions to the user. b. Insert values in the department table and use
commit.
c. Add constraints like unique and not null to the department table. d. Insert repeated values and null
values into the table.
( ii) Write a PL/SQL procedure to work with Arithmetic operations.
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.
7.(i) Create a table called Branch table.
1
Name Туре
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
(ii) Write Pl./SQL code in Function for Factorial number and Prime number
8.(i) Create a table called Stock table
1
Name Туре
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.
(ii) Write a PL/SQL block to handle the following BUILT-IN EXCEPTIONS.
