Chapter 6 Application

Question 1

Prepare a summary query that uses COUNT(*) on Employee, and AVG(), SUM(), MIN(), and MAX() on the Employee.salary column.

Answer 1

select COUNT(*) as Number_of_Employees, round(AVG(salary),2) as avg_salary,SUM(salary) as Total_Salary, MAX(salary) as Highest_Salary, MIN(salary) as Lowest_Salary from employee;

Question 2

Code a summary query that works on a non-numeric column such as hiredate. Explain what the result indicates.

Answer 2

Select Min(hireDate) as Earliest_HireDate from employee;

Result shows the first person hired in the list of employees.

Question 3

Write a summary query on Employee using the DISTINCT keyword when counting the different jobs supervised by manager 7698 and also computes average salary for that manager’s employees.

Answer 3

select count(DISTINCT empno) as Number_of_Employees, round(avg(salary),2) as Avg_Salary from employee where manager = 7698;

Question 4

Prepare a summary query that calculates the maximum salary by department.

Answer 4

select deptno, max(salary) from employee group by deptno;

Question 5

Code a summary query that calculates average salary for those departments that have three or more employees.

Answer 5

select deptno, round(avg(salary),2) as Avg_Salary from employee group by deptno having count(deptno) > 3;

Question 6

Write a select statement to produce a summary query with a compound condition in the having clause to count the number of employees in department 20 with a job in the list (‘ANALYST’, ‘CLERK’).

Answer 6

select count(*) as NumEmpInDept, job, deptno from employee where deptno = 20 group by job having job = ‘clerk’ or job = ‘analyst’;