Other Master Degree-Master of Computer Management (M.C.M) 2nd Sem ADVANCED RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS) USING ORACLE(University of Pune, Pune-2013)
[4377]-2003
M. C. M. (Semester - II) Examination - 2013
ADVANCED RELATIONAL DATABASE MANAGEMENT
SYSTEM (RDBMS) USING ORACLE
(2012 Pattern)
Time : 3 Hours] [Max. Marks : 70
Instructions :
(1) Question Nos. 1 and 7 are compulsory.
(2) Attempt any three from the remaining.
(3) Figure to the right indicate full marks.
(4) Draw neat diagrams wherever necessary.
Consider the following tables to solve queries and plsql given below :
Employees :
(employee_id, last_name, salary, commission_pct, hire_date, manager_id, job_title, department_id)
Departments :
(department : id, department_name, location_id)
Locations :
(location_id, city, street, address)
Q.1) Solve any ten queries given below : [20]
(1) Create Employees Table with constraints.
(2) Count unique department number from employees table.
(3) Display the last name of employees whose third letter should contains the character 'a'.
(4) Add column e-mail in employees table.
(5) Calculate average commission percentage of all employees.
(6) Display the last name and salary of employees whose salary is not in range of $5000 to $12000.
(7) Display the last name and job title of all employees who do not have a manager.
(8) Display the last name, years completed of employees from the date of joining the Company.
(9) Write a query to display the last name, department number and department name for all employees.
(10) Display the employees whose job title is same as that of employee 141.
(11) Display the last name and employee number along with their manager's last name and manager number.
(12) Delete the records of employees whose salary is less than $12000.
Q.2) Write a cursor to displaying top 5 salaried employees. (Display only three column : employee_id, last_name, salary). [10]
Q.3) Write a procedure display_info to accept the employee_id and display its following details of that employee : [10]
(employee_id, last_name, department_id, department_name)
Q.4) Write a function tax_cal to calculate the annual income tax of all employees and return annual income tax. (consider 10% tax per annum) [10]
Q.5) Write a package specification and body which contains followings : [10]
(a) A procedure which accept the number and calculate the factorial of given number.
(b) A function which accept the number and return if it is odd or even.
Q.6) Write a trigger that will allow user to update record in 'Employees' only between Monday to Friday. [10]
[4377]-2003 2Contd.
Q.7) Write a short notes : (Any Four) [20]
(a) Exception Handling
(b) Constraints
(c) Stages of Database Startup and Shutdown Options
(d) Types of Backup and Steps to take Backup without Shutting Down Database
(e) Sequence
(f) Data Types in Oracle
| Earning: ₹ 7.05/- |
