Hello, I am taking an Intro to Database Management Course and there are some que

Question

Hello,

I am taking an Intro to Database Management Course and there are some queries that I need help on for an assignment. I have posted the SQL code that the instructor wants us to work with and what he is asking us to do for the queries.

SQL Code:

Questions (queries):

a.) Show the job title with the lowest average Salary. (Hint: There are at least two ways to solve this problem. Both will require nested queries. One approach could use the ROWNUM to extract the very first row of the result. The other approach would require you to use the Having clause to only return the aggregated result where the average salary equals the minimum of the average salary values.

b.) List the departments having employees with above average salary amounts

c.) Enter data for two new employees who do not belong to any department. You can make up the data for these two records. Enter one record for a new department called Human Resources. Assign DeptNo as 50 and location as Poughkeepsie.

d.) Show employee name and department name for only those employees who have a department and those departments who have an employee. (Hint: Use Inner Join)

e.) Show employee name and department name for all employees including those who have not been assigned to any department. (Hint: Use Left Outer Join)

f.) Show employee name and department name for only those employees who have been assigned to a department but all departments including departments who do not have any employees at all. (Hint: Use Right Outer Join)

Explanation / Answer

a.) Show the job title with the lowest average Salary. I used subquery way

select job from emp e , (
select min (avg_salary) lowSal
from (select empno, avg(sal) AS avg_salary
from emp
group by empno)) INN where e.sal = INN.lowSal;

Output:

CLERK

b.) List the departments having employees with above average salary amounts

select DISTINCT D.dname from emp e , DEPT d ,
(select avg(sal) SAL from emp) AVGSAL
where e.sal > AVGSAL.SAL AND E.deptno = D.deptno;

Output:

c.) Enter data for two new employees who do not belong to any department. You can make up the data for these two records. Enter one record for a new department called Human Resources. Assign DeptNo as 50 and location as Poughkeepsie.

insert into dept  
values(50, 'Human Resources', 'Poughkeepsie');

insert into emp
values(
78391, 'QUEEN', 'WIFE', null,
to_date('17-11-1982','dd-mm-yyyy'),  
5020, null, 50
);
insert into emp
values(
78392, 'SOLDIER', 'DUTY', null,
to_date('17-11-1983','dd-mm-yyyy'),  
5200, null, 50
);

d.) Show employee name and department name for only those employees who have a department and those departments who have an employee. (Hint: Use Inner Join)

SELECT ename, DNAME FROM EMP E, DEPT D WHERE E.deptno = D.deptno;

Output:

e.) Show employee name and department name for all employees including those who have not been assigned to any department. (Hint: Use Left Outer Join)

SELECT ename, DNAME
FROM EMP e
LEFT JOIN DEPT d ON E.deptno = D.deptno;

Output: Here QUEEN and SOLDIER are newly inserted rows in employee which do not have department.

f.) Show employee name and department name for only those employees who have been assigned to a department but all departments including departments who do not have any employees at all. (Hint: Use Right Outer Join)

SELECT ename, DNAME
FROM EMP e
RIGHT JOIN DEPT d ON E.deptno = D.deptno;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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