Hello, I am taking an Intro to Database Management Course and there are a few qu

Question

Hello,

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

SQL Code:

Questions (queries):

a.) Show all records from Employees and details of Department they belong using Natural Join operation.

b.) Show all records from Employees and details of Department they belong using Inner Join operation.

c.) Only show employee name and department name for all employees who belong to the Accounting Department.

d.) Show the average salary for each department. Using the Cast operator, restrict the decimal places to only two. Use the label “Average Salary” for the column showing the average salary. Sort the results on average salary in a descending manner.

e.) Show the average salary for each job. Using the Cast operator, restrict the decimal places to only two. Use the label “Average Salary” for the column showing the average salary. Only show the results for those jobs with the average salary less than 3000. (Hint: You will need to use the Having clause)

Explanation / Answer

Hi, Please find the answers below. Let me know in case of any issue. Thanks:

1. SELECT * FROM emp NATURAL JOIN dept; (select all records from Employees natural join department)

2. SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; (select all records from Employees inner join department)

** the only difference between Natural Join and Inner Join is inner join will return a repetative column on which join is made.

3. SELECT emp.ename,dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno where dept.dname='ACCOUNTING'; (select employee name and department name for all employees who belong to the Accounting Department.)

4. SELECT dept.dname,TRUNC(avg(sal), 2) as Average_Salary FROM emp INNER JOIN dept ON emp.deptno = dept.deptno group by dept.dname order by avg(sal) desc; (select dpartment name and average salary truncated to 2 decimal points in descending order of average salary)

5. SELECT job,TRUNC(avg(sal), 2) as Average_Salary FROM emp group by job having avg(sal)<3000; (select jobname and average salary where average salary is less than 3000)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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