Data Base Queries

Only available on StudyMode
  • Download(s) : 118
  • Published : February 3, 2013
Open Document
Text Preview
Data base
• What are Group Functions?
• Types of Group Functions
• Using Group Functions
• Using AVG and SUM function
• Using MIN and MAX function
• Using COUNT function
• Group functions and NULL values
• Using the NVL Function with Group Function
• Creating GROUP of DATA
• Creating GROUP of DATA:GROUP BY Clause
• Using the GROUP BY clause
• Grouping by more than one column
• Using the GROUP BY clause on multiple columns
• Illegal Queries Using Group Functions
• Excluding Group Results
• Excluding Group Results: HAVING Clause
• Using the HAVING clause
• Nesting GROUP Function
• Subqueries
• Using a Subquery
• Guideline for using subqueries
• Types of Subqueries
• Single-row subqueries
• Executing Single Row Subqueries
• Using Group Functions in a Subquery
• HAVING clause with subqueries
• Multiple Row Subqueries
• Using ANY operator in Multiple-row subqueries
• Using ALL operator in Multiple-row subqueries
Group Function:

"Group Functions" also known as "Multiple-Row Functions". They operates on set of rows to give one result per group. These set may be the whole table or the table split into groups.

Group Functions:
• SUM
• AVG
• COUNT
• MIN
• MAX
• STDDEV
• VARIANCE

(These are similar to the "aggregate functions" or "Group By" functions in Access)

GROUP BY clause

To identify groups of records to be processed

HAVING clause

To restrict the groups displayed

The data types for arguments may be CHAR, VARCHAR2, NUMBER, or DATE.

The Oracle Server implicitly sorts the result set in ascending order when using a GROUP BY clause. To override the default ordering, DESC can be used in an ORDER by clause. Some simple examples:

SELECT SUM(Salary)
FROM Employee;

SELECT AVG(Salary)
FROM Employee;

SELECT COUNT(*)
FROM Employee;

SELECT COUNT(Employee_city)
FROM Employee;

SELECT COUNT(DISTINCT Employee_city)
FROM Employee;

SELECT MAX(Salary)
FROM Employee;

SELECT MAX(EMPLOYEE_DOB)
FROM Employee;

SELECT MIN(Salary)
FROM Employee;

SELECT MIN(EMPLOYEE_DOB)
FROM Employee;

SELECT STDDEV(Salary)
FROM Employee;

SELECT VARIANCE(Salary)
FROM Employee;

General Syntax

SELECT * | column1, column2, …
FROM tableName
WHERE Condition

GROUP BY column1, column2, …

HAVING group condition

**The ALL keyword is the default (includes all values except Nulls)

**The DISTINCT keyword returns only unique values

**Group functions ignore NULL values

Exception: COUNT(*) which means "Count all rows"

Using SUM and AVG function

SUM function

Calculates the total amount in a numeric field for a group of records.

SUM(n)where n is a numeric column

SUM(ALL n)the same as above

SUM(DISTINCT n)returns only the unique numeric values

SELECT SUM(Salary) "Total Salary"
FROM Employee;

SELECT SUM(Salary) "Total Salary of E_CITY_001"
FROM Employee
WHERE Employee_city=’e_city_001’;

AVG function

Syntax: AVG(column containing numeric data)

AVG(DISTINCT [column containing numeric data] )

DISTINCT keyword returns only unique values

What's the average profit generated?...
tracking img