ex-5 dbms lab

[raviteja bavandla]

1. Display the names of the employees who are not eligible for commission.

2. Display the name and designation of employee who does not report to anybody.

3. Display the name,salary and PF amount of all the employees

(PF is calculated as 10% of salary).

4. Display the empno, ename, sal in ascending order of salary.

5. Display the employee name and hiredate in descending order of hiredate.

6. Display the employee name, salary, job and departement No. in ascending order

of Department No and then on descending order of salary.

7. Display the no.of employees in the EMP table.

8. Display the number of jobs available in the emp table.

9. Display the total salaries payable to employees working in Department 20.

10. Display the maximum salary of employee working as a salesman.

11. Display the minimum salary from EMP table.

12. Display the average salary and number of employees working in the department 20.

13. Display the department number and number of employees in each department.

13. Display the department number and the total salary payable in each department.

14. Display the jobs and the number of employees in each job. The results should be

in descending order of the number of employees.

15. Display the average salary from each job excluding managers.

16. Display the average monthly salary for each job type within department.

17. Display the average salary for all departments employing more than five people.

18. Display the total salry, maximum and minimum salary and the average salary of

employees jobwise, for department number 20 and list only those rows having

average salary greater than 1000.

1. Display the employee numbers, names, departments numbers and

the department name.

2. Find the names of sailors who have reserved boat 103.

3. Find the names of sailors who have reserved a red boat.

4. Find the colors of boats reserved by ‘LUBBER’.

5. Find the names of sailors who have reserved a red or a green boat.

6. Find the names of sailors who have reserved at least one boat.

7. Find the names of sailors who have reserved a red and a green boat.

8. Find the names of sailors who have reserved at least two boats.

9. Find the sids of sailors with age over 20 who have not reserved a red boat.

10. Find the names of sailors who have reserved all boat.

11.Find the names of sailors who have reserved all boats called Interlake.

12.Find the sids of all sailors who have reserved red boats but not green boats.

13.Find all sids of sailors who have a rating of 10 or reserved boat 104.

14.Display list of employees working in each department. Display the department

information even if no employee belongs to that department.

15.Display employee names along with their manager name.

16.Display all employees who joined the company before their manager.

17.Display different designations in department 20 and 30.

18.Display the jobs common to department 20 and 30.

19.Display the jobs unique to department 20.

1. List the employees belonging to the department of MILLER

2. Display all employee details whose salary is greater than average salary of

employees whose hire date is before ’01-04-81’

3. Display the job with highest average salary.

4. Display the names of the employees, who earn lowest salary in each

department.

5. Display employee details who earn salary greater than the average salary of

their department.

6. Display all employees who have at least one person reporting to them.

7. Display all employee details who do not manage any one.

8. Display the employee details who have the second highest salary.

9. Display the department that has no employee.

10. Write a sql query that demonstrates simple arithmetic functions.

11. Write a sql query to demonstrate character functions.

12. Write a sql query to demonstrate DATE functions.

13. Write a sql query using Translate function

14. Write a sql query using Decode function.

15. write a sql query to increase the commission of all employees by 1000.

16. Write sql queries using To_date function.

17. Write sql queries using To_char function.