SUBJECT: DATABASE MANAGEMENT SYSTEM NOTE: All these tasks must be done in Oracle Database 10g Enterprise Edition Display the Employee name and designation in one column. Display all records from table...

SUBJECT: DATABASE MANAGEMENT SYSTEM

NOTE: All these tasks must be done in Oracle Database 10g Enterprise Edition

1. Display the Employee name and designation in one column.


2. Display all records from table whose name is Smith using all uppercases in where clause.


3. Display all records from table whose name is Smith using first letter captializaed in where clause.


4. Display all records from table whose name is Smith using all lowercases in where clause.


5. Display all records from table whose name is Smith using lower and upper cases both. i.e. SMITH, smith both should be acceptable.


6. Show the result of those employees whose name length exceeds 8.


7. Display ename, salary and concatenated result of deptno and empno from table EMP where salary is less than 2500 or job is equal to MANAGER.


8. Display first 3 characters of ename, deptno and job from table EMP where second character of ename is ‘A’ and deptno is 30 or job is SALESMAN.


9. Right Append dollar sign in Employee table to make salary 8 digit value.


10. Calculate annual salary and rename it as PER_ANNUM_SALARY of the employee whose ename is KING and salary is greater than 1500 from table EMP.


11. Round 345.6665 upto 2 digits from table DUAL.


12. Members of the HR department want to have more flexibility with the queries that you are writing. They would like a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt


13. The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. You can test the data with the following values:
    manager ID = 103, sorted by employee last name:
    manager ID = 201, sorted by salary:


14. Write a query that displays the last name (with the first letter uppercase and all other letters lowercase) and the length of the last name for all employees whose name starts with the letters J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.


15. Write a query to display the current date. Label the column Date


16. The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary.


17. The HR department wants to find the length of employment for each employee. For each employee, display the last name and calculate the number of months between today and the date on which the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.


18. Write a query that produces the following for each employee:
    earns monthly but wants <3 times="" salary="">. Label the column Dream Salary