EMPLOYEE TABLE (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) DEPT (DEPTN

Question

EMPLOYEE TABLE (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Solve the following DML queries for the above two tables.         

1- Display the name of all the clerks who belong to the ‘Accounting’ department.

2- Display the name and location of the departments in which the employees earn commission in the descending order of the commission.

3- Calculate the total salary of all the managers of the ‘Sales’ department.

4- Display the names of salesmen in the ‘Sales’ department along with the department name and location.

5- Display the employee name in title case and job in lower case.

6- Display the employee name in upper case and his salary in 10 digits with preceding zeroes.

7- Display the employee names along with their manager’s name.

8- Display the employee name starting from the 4th character.

9- Replace the letter ‘A’ with ‘OO’ in the employee name and replace the letter ‘E’ with ‘AA’ in job.

10- Display the employee name and job along with the number of characters in them.

11- Display the department-wise average salary of the employees.

12- Display the department name and the number of employees in each department for those departments which have more than 5 employees working in them.

13- Delete the record of all the employees who work in the same department of ‘MILLER’.

14- Increment the salary of all the MANAGERs by 5% of their salary.

Explanation / Answer

ANSWERS:

1. Display the name of all the clerks who belong to the ‘Accounting’ department.

Select E.ENAME from EMPLOYEE E, DEPT D where (E.JOB ='Clerk' and D.DNAME ='Accounting');

2. Display the name and location of the departments in which the employees earn commission in the descending order of the commission.

Select D.DNAME, D.LOC from DEPT D, EMPLOYEE E order by E.COMM desc;

3.Calculate the total salary of all the managers of the ‘Sales’ department.

Select Sum(E.SAL) from EMPLOYEE E, DEPT D where (E.JOB = 'MANAGER' and D.DNAME = 'SALES');

4. Display the names of salesmen in the ‘Sales’ department along with the department name and location.

Select E.ENAME, D.DNAME, D.LOC from EMPLOYEE E, DEPT D where (E.JOB ='Salesman' AND D.DNAME ='Sales');

5. Display the employee name in title case and job in lower case.

Select initcap(ENAME), lower(JOB) from EMPLOYEE;

6. Display the employee name in upper case and his salary in 10 digits with preceding zeroes.

Select upper(ENAME), LPAD(SAL,10,'0') from EMPLOYEE;

7. Display the employee names along with their manager’s name.

Select ENAME, MGR from EMPLOYEE;

8. Display the employee name starting from the 4th character.

Insufficient information

9. Replace the letter ‘A’ with ‘OO’ in the employee name and replace the letter ‘E’ with ‘AA’ in job.

Select replace(ENAME,'A','OO')ENAME, replace(ENAME,'E','AA')ENAME from EMPLOYEE;

10- Display the employee name and job along with the number of characters in them.

Select LEN(ENAME), JOB from EMPLOYEE;

11- Display the department-wise average salary of the employees.

Select avg(E.SAL),D.DNAME from EMPLOYEE E, DEPT D group by D.DNAME;

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.