GROUP FUNCTIONS INTERVIEW QUESTIONS

Group functions work across many rows to produce one result.

True

Group functions include nulls in calculations. 

False. Group functions ignore null values. If you want to include null values, use the NVL function. 

The WHERE clause restricts rows prior to inclusion in a group calculation.

True

Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab5_6.sql.

SELECT   ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees;

Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. Resave lab5_4.sql to lab5_5.sql. Run the statement in lab5_5.sql.

SELECT   job_id, ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees GROUP BY job_id;

Write a query to display the number of people with the same job. 

Select job_id,count(job_id) from employees group by job_id;

Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers

select count( distinct manager_id) " Number of Managers" from employees;

Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

select Max(salary)-Min(Salary) " Difference" from employees;

Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $6,000. Sort the output in descending order of salary.

Select manager_id,min(salary)from employees where manager_id is not null group by manager_id having Min(salary)>6000 order by min(salary) desc;

Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places.

select d.department_name"NAME",d.location_id "LOCATIONS", count(*)"NUMBER OF PEOPLE" ,round(avg(e.salary),2) "SAL" from departments d, employees e  where e.department_id=d.department_id group by d.department_name,d.location_id;

Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.

SELECT  COUNT(*) total, 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),2005,1,0))"1995", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM    employees;