DBMS LAB ASSIGNMENT 2

LAB EXERCISES

LAB 2:

1. List the employees name and salary increased by 20% and expressed as whole numbers of dollars.

2. Display ename and job with an alias EMPLOYEE_AND_JOB.

3. Try this command and find the difference with Q.2.

SELECT RPAD(ENAME,10) || LPAD(JOB,10) EMPLOYEE_AND_JOB FROM EMP;

4. Try this command.

SELECT ENAME||’(‘|| INITCAP(JOB) || ‘ )’ EMPLOYEE_AND_JOB FROM EMP;

5. Search for the list of employees with a job that the user enters.

NOTE: The search should be case insensitive.

6. Change the designation of salesman with sales person in department 20 as there are women salesperson in the organisation.

7. Display name, hiredate from Emp Table . Hire_date should be in the displayed in the form of Thirteen June 1998.

8. Display the date of completing probation of an employee. Add 12 months to the hire_date and then order by the hire_date.

Now order by ADD_MONTH(HIRE_DATE,12) . Note the difference.

9. Display the salary of an employee. If salary is greater 1500 then the salary is to be

displayed. If 1500 then display Ón target’,if less then display below 1500.

Hint: Use DECODE Function.

10. Display the day of the week of today. Use the sysdate and the format of DD.MM.YY.

11. Write a query to calculate the length of service an employee has put in the company. Use DEFINE.

12. Given a string of the format ‘nn/nn’, Verify that the first and last 2 characters are numbers and that the middle character is a ‘/. Print the expression ÝES’if valid else Print ‘no’. Use the values for testing :’12/34/,’01/1a’,’99/88’.

13. Employees hired on or before the 15th of any month are paid on the last Friday of that month.

14. Find the minimum salary of all employees.

15. Find the minimum, maximum and average salaries of all employees.

16. Use the minimum and maximum salary for each job type.

17. Find out how many managers there are without listing them.

18. Find the average salary and average total remuneration for each job type. Remember salesman earn commission.

19. Find out the difference between highest and lowest salaries.

20. Find all departments which have more than 3 employees

21. Check whether all employee numbers are indeed unique.

22. Use lowest paid employees working for each manager. Exclude any groups where the minimum salary is less than 1000. Sort the output by salary.