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 records of employees with the job title of either Manager, Analyst or Salesman. (Hint: you will need to use the IN condition).

b.) Modify the above query to show the records of all employees except those with the job title of either Manager, Analyst or Salesman. (Hint: you will need to use the NOT IN condition).

c.) Calculate the average salary for all employees. Now list only the names of employees with above average salary amount. (Hint: you will need to use the previous query that calculated the average salary as part of the nested query).

d.) Count the number of employees with above average salary amount. The count should have the title as “Number of Employees”.

Explanation / Answer

// the IN keyword is used to spcify more than one values in where clause
a) select * from emp where job in ('Manager', 'Analyst', 'Salesman')

// results opposite to IN
b) select * from emp where job not in ('Manager', 'Analyst', 'Salesman')

// Use of AVG() function
c)1) select avg(sal) average_salary from emp
2) select * from emp where sal > (select avg(sal) from emp)

// Use of COUNT() and AVG()
d) select count(*) Number_of_Employees from emp where sal > (select avg(sal) from emp)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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