- ·
Calculates average value excluding nulls-
**AVG**group function - ·
Returns the number of rows with non-null values for the
expression-
**COUNT**group function - ·
For two sets of data with approximately the same mean, the
greater the spread, the greater the standard deviation.-
**STDDEV**group function - ·
Operate on sets of rows to give one result per group- any
**group function** - ·
Returns minimum value ignoring nulls-
**MIN**group function - ·
Used with columns that store numeric data to calculate the
spread of data around the mean-
**VARIANCE**group function - ·
Calculates the sum ignoring null values-
**SUM**group function - ·
Returns the maximum value ignoring nulls-
**MAX**group function - ·
To gather into a sum or whole-
**Aggregate**functions return a single result row based on groups of rows. Aggregate is something that is formed by combining several separate elements.

**1.**Define and give an example of the seven group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE.

**AVG, COUNT :**

·
Calculates
average value excluding nulls-

**AVG**group function
·
Returns
the number of rows with non-null values for the expression-

**COUNT**group function
SELECT AVG(salary) || ' is Average Salary of ' || COUNT(salary) || ' employees.
This table has primary key employee_id which won''t be null. So, ' || (COUNT(employee_id) - COUNT(salary))
|| ' rows are skipped in Average Salary
calculation.' "Example"

FROM EMPLOYEES;

*(I may use COUNT(*) instead of COUNT(employee_id), if I want to)*

Output:

8775 is Average Salary of 20 employees. This table has primary key
employee_id which won't be null. So, 0 rows are skipped in Average Salary calculation.

*Please note that in this concatenation and minus has same precedence. So () were required.*

**MAX, MIN:**

·
Returns
minimum value ignoring nulls-

**MIN**group function
·
Returns
the maximum value ignoring nulls-

**MAX**group function
SELECT 'The maximum of ' || COUNT(salary) || ' salaries
in employees table is ' || MAX(salary)
||'. The minimum of '|| COUNT(salary)
|| ' salaries in employees table is '|| MIN(salary) ||'.' "Example2"

FROM EMPLOYEES;

Output:

The maximum of 20 salaries in employees
table is 24000. The
minimum of 20
salaries in employees table is 2500.

**STDDEV, VARIANCE**:

·
Used
with columns that store numeric data to calculate the spread of data around the
mean-

**VARIANCE**group function
·
For
two sets of data with approximately the same mean, the greater the spread, the
greater the standard deviation.-

**STDDEV**group function
SELECT 'The standard deviation of
' || COUNT(salary) || '
salaries in employees table is ' || ROUND(STDDEV(salary), 4) ||'. The variance of '|| COUNT(salary) || '
salaries in employees table is '|| ROUND(VARIANCE(salary), 4) ||'.' "Example3"

FROM EMPLOYEES;

Output:

The standard deviation of 20 salaries in employees
table is 5659.6331.
The variance of 20
salaries in employees table is 32031447.3684.

*If not rounded**the values would have been inconvenient to read.*

**SUM**:

·
Calculates
the sum ignoring null values-

**SUM**group function
SELECT 'The sum of ' || COUNT(salary) || ' salaries
in employees table is ' || SUM(salary)
||'.' "Example4"

FROM EMPLOYEES;

Output:

The sum of 20 salaries in employees table is 175500.

**2.**Create a query that will show the average cost of the DJs on Demand events. Round to two decimal places.

**SELECT ROUND(AVG(cost),2) as "Average Cost"**

**FROM d_events;**

*9000***3.**Find the average salary for Global Fast Foods staff members whose manager ID is 19.

**SELECT TO_CHAR(ROUND(AVG(salary),2), '$999999.99') as "Average Salary"**

**FROM f_staffs**

**WHERE manager_id = 19;**

*$8.38***4.**Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.

**SELECT TO_CHAR(ROUND(SUM(salary),2), '$999999.99') as "Total Salary"**

**FROM f_staffs**

**WHERE id in (12, 19);**

*$66.75***5.**Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.

**SELECT MIN(salary) "lowest salary", MAX(hire_date) "most recent hire date", MIN(last_name) "top last name", MAX(last_name) "bottom last name"**

**FROM employees**

**WHERE department_id in (50, 60);**

**6.**Your new Internet business has had a good year financially. You have had 1,289 orders this year. Your customer order table has a column named total_sales. If you submit the following query, how many rows will be returned?

SELECT sum(total_sales)

FROM orders;

**one**

**7.**You were asked to create a report of the average salaries for all employees in each division of the company. Some employees in your company are paid hourly instead of by salary. When you ran the report, it seemed as though the averages were not what you expected—they were much higher than you thought! What could have been the cause?

**SELECT AVG(NVL(salary, hourly_rate* hrs_worked_in_yr ))**

**This way the null fields beings ignored will also be counted in.**

**8.**Employees of Global Fast Foods have birth dates of July 1, 1980, March 19, 1979, and March 30, 1969. If you select MIN(birthdate), which date will be returned?

**March 30, 1969**

**9.**Create a query that will return the average order total for all Global Fast Foods orders from January 1, 2002, to December 21, 2002.

**SELECT 'Average of ' || COUNT(order_number) || ' orders is : ' || AVG(NVL(order_total, 0)) as "Average"**

**FROM f_orders**

**WHERE order_date BETWEEN TO_DATE('January 1, 2002', 'fmMonth DD, YYYY') AND TO_DATE('December 21, 2002', 'fmMonth DD, YYYY');**

*Average of 1 orders is : 103.02**(I may use COUNT(*) instead of COUNT(order_number), if I want to)*

**10.**What was the hire date of the last Oracle employee hired?

**SELECT MAX(hire_date) as "the last"**

**FROM employees;**

*29-Jan-2000***11.**In the following SELECT clause, which value returned by the SELECT statement will be larger?

SELECT SUM(operating_cost), AVG(operating_cost)

**SUM must be be ‘equal or greater than’ average.**

**12.**Refer to the DJs on Demand database D_EVENTS table:

Which clauses represent valid statements?

**_______a.**FROM event_date

**FALSE**, this is a column. It will say ORA-00942: table or view does not exist

**_______b.**SELECT SUM(cost)

**TRUE**

**_______c.**SELECT SUM(event_date)

**FALSE**. This column is not a number. ORA-00932: inconsistent datatypes: expected NUMBER got DATE

**_______d.**SELECT description, AVG(cost) AS "Expense"

**FALSE**. ORA-00937: not a single-group group function. Remove either description (to get single row output), or remove avg(cost)

**_______e.**WHERE MIN(id) = 100

**FASLE**. MIN is a group function. ORA-00934: group function is not allowed here

**_______f.**SELECT MAX(AVG(cost)

**FALSE**. ORA-00978: nested group function without GROUP BY

**_______g.**SELECT MIN(event_date)

**TRUE**

## No comments:

## Post a Comment