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

ID: 3809354 • Letter: E

Question

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

DEPT (DEPTNO, DNAME, LOC)

Write the Relational Algebra Expressions for 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 names along with their manager’s name.

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

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

1) SELECT ENAME

FROM EMPLOYEE ,DEPT

WHERE JOB = 'CLERK' AND DNAME = 'ACCOUNTING;

2) SELECT ENAME,LOC

FROM EMPLOYEE ,DEPT

ORDER BY COMM DESC;

3) SELECT ENAME,(SAL+((SAL*COMM)/100)) AS "TOTAL_SALARY

FROM EMPLOYEE,DEPT

WHERE DNAME = 'SALES';

4) SELECT ENAME,DNAME,LOC

FROM EMPLOYEE,DEPT

WHERE DNAME = 'SALES';

5) select distinct e.ENAME as Employee, m.MGR as reports_to, m.ENAME as Manager
from EMPLOYEE e
inner join EMPLOYEE m on e.MGR = m.EMPNO;

6) SELECT DNAME,AVG(SAL)

FROM EMPLOYEE,DEPT

GROUP BY DNAME;

7) SELECT DNAME,COUNT(DNAME)

FROM EMPLOYEE,DEPT

WHERE DNAME IN

(

SELECT DNAME

FROM EMPLOYEE

GROUP BY DNAME

HAVING COUNT(*) >5

)

GROUP BY DNAME;

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