I m a fresher in oracle plz help me in solving these queries.. plz provide tips also

[jenipriya]

Tables
------------
Employee (EmpID, EmpName,DeptID DateOfJoin, Sal, Addr)
Finance (EmpID, Sal)
Club (Clubname, EmpID, Fee, DateOfJoin)
Leave (EmpID, Date)
Department (DeptID, DeptName, NoOfEmployees)



Queries
---------------
5. display the salary deduction details for the employees in a given department for a given month. The salary deduction is sum ( Club fees for that employee) + ( Employee.Sal / 30 * no of leaves taken for that month
6. write a PL/SQL block to display the details of a given employee. Use PL/SQL variables
7. write a PL/SQL block to insert a row into Employee table. Make use of PL/SQL variables and RowType attributes.
8. write a PL/SQL block to display the no of employees in a department.
9. write a PL/SQL block to delete a row from the Leave table for the given date for an employee. Use PL/SQL variables to pass the employee id and date





1. write a PL/SQL block to display employee details. Use cursor for getting data and make use of Loop…… End Loop to fetch each row from the cursor
2. write a PL/SQL block to display the employees joined in a given club. Make use of cursor with parameters for fetching data.
3. write a PL/SQL block to update the salary of all employees by 10 %. Make use of For Update and Where Current of Clauses in cursor.
4. write a PL/SQL block to display the employee details with department name and salary deductions. Make use of cursor for loops to fetch rows.
5. write a PL/SQL block to insert a row into department table. Try to add a duplicate row. Write corresponding exception handling section.
6. Add appropriate exception handling for all the above PL/SQL block.
7. write a PL/SQL block to increase the salary of a given employee by 15 % if the years of experience of that employee is greater than 2 years else generate an error using Raise_Application_Error.
8. write a PL/SQL block to find the no of employees in each club.

Stored Procedures and Functions
----------------------------------------------------

1. Write a stored function that receives an employee number and returns the total salary deductions for that employee.
2. write a stored function that accepts a department ID and returns the number of employees working in that department. Find the number of employees working in a given department from the Employees table.
3. write a stored procedure that deletes all the rows from the finance table and insert new values into it. Make use of the above function to find the deduction in salary. Finance.Sal = Employee.Sal – deductions.
4. write a Stored procedure to update the Department tabe. Update the department table with no of employees in each department. Make use of the function, which is already created, to find the no of employees in each department.
5. write a stored function to find the employee ID who is having highest no of memberships.
6. write a PL/SQL procedure to display the details of employee who is having the highest number of club membership. Make use of the above function to find the employee ID.
7. create a package with above functions and procedures such that all functions are private and all procedures are public. Also make use of forward declerations.
8. create a package with overloaded functions.


Triggers
------------

1. write triggers for employee table such that
a. whenever a new employee is added to the employee table one row should be added in the Finance table for that employee and update Department table such that Department.NoOfEmployees = Department.NoOfEmployees + 1.
b. Whenever a row is deleted from the employee table the reverse operation should take place.
c. Whenever the salary field is updated the difference should be updated in the finance table.
(Hint: write Insert, update and Delete triggers for Employee table.)
2. write triggers for Club table such that whenever a row is inserted into the Club table the Fee amount should be deducted from Finance table for that employee if the date of join is <= 15 of that month. When ever a row is deleted from the table the fee amount should be added to the Sal of finance table if the date of removal is less than 15 of that month.

[r035198x]

Tables
------------
Employee (EmpID, EmpName,DeptID DateOfJoin, Sal, Addr)
Finance (EmpID, Sal)
Club (Clubname, EmpID, Fee, DateOfJoin)
Leave (EmpID, Date)
Department (DeptID, DeptName, NoOfEmployees)



Queries
---------------
5. display the salary deduction details for the employees in a given department for a given month. The salary deduction is sum ( Club fees for that employee) + ( Employee.Sal / 30 * no of leaves taken for that month
6. write a PL/SQL block to display the details of a given employee. Use PL/SQL variables
7. write a PL/SQL block to insert a row into Employee table. Make use of PL/SQL variables and RowType attributes.
8. write a PL/SQL block to display the no of employees in a department.
9. write a PL/SQL block to delete a row from the Leave table for the given date for an employee. Use PL/SQL variables to pass the employee id and date





1. write a PL/SQL block to display employee details. Use cursor for getting data and make use of Loop…… End Loop to fetch each row from the cursor
2. write a PL/SQL block to display the employees joined in a given club. Make use of cursor with parameters for fetching data.
3. write a PL/SQL block to update the salary of all employees by 10 %. Make use of For Update and Where Current of Clauses in cursor.
4. write a PL/SQL block to display the employee details with department name and salary deductions. Make use of cursor for loops to fetch rows.
5. write a PL/SQL block to insert a row into department table. Try to add a duplicate row. Write corresponding exception handling section.
6. Add appropriate exception handling for all the above PL/SQL block.
7. write a PL/SQL block to increase the salary of a given employee by 15 % if the years of experience of that employee is greater than 2 years else generate an error using Raise_Application_Error.
8. write a PL/SQL block to find the no of employees in each club.

Stored Procedures and Functions
----------------------------------------------------

1. Write a stored function that receives an employee number and returns the total salary deductions for that employee.
2. write a stored function that accepts a department ID and returns the number of employees working in that department. Find the number of employees working in a given department from the Employees table.
3. write a stored procedure that deletes all the rows from the finance table and insert new values into it. Make use of the above function to find the deduction in salary. Finance.Sal = Employee.Sal – deductions.
4. write a Stored procedure to update the Department tabe. Update the department table with no of employees in each department. Make use of the function, which is already created, to find the no of employees in each department.
5. write a stored function to find the employee ID who is having highest no of memberships.
6. write a PL/SQL procedure to display the details of employee who is having the highest number of club membership. Make use of the above function to find the employee ID.
7. create a package with above functions and procedures such that all functions are private and all procedures are public. Also make use of forward declerations.
8. create a package with overloaded functions.


Triggers
------------

1. write triggers for employee table such that
a. whenever a new employee is added to the employee table one row should be added in the Finance table for that employee and update Department table such that Department.NoOfEmployees = Department.NoOfEmployees + 1.
b. Whenever a row is deleted from the employee table the reverse operation should take place.
c. Whenever the salary field is updated the difference should be updated in the finance table.
(Hint: write Insert, update and Delete triggers for Employee table.)
2. write triggers for Club table such that whenever a row is inserted into the Club table the Fee amount should be deducted from Finance table for that employee if the date of join is <= 15 of that month. When ever a row is deleted from the table the fee amount should be added to the Sal of finance table if the date of removal is less than 15 of that month.

Please read the posting guidelines.

[jenipriya]

Please tell me how to insert a row in the table using %RowType attributes in PL/SQL

[r035198x]

Please tell me how to insert a row in the table using %RowType attributes in PL/SQL

Have you read any PL\SQL tutorials? You'll also find some nice articles here.