Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views. - Computer Programming

# Computer Programming

C C++ Java Python Perl Programs Examples with Output -useful for Schools & College Students

## Wednesday, October 12, 2011

Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN),    GROUP BY, HAVING and Creation and dropping of Views.

Aggregative operators: In addition to simply retrieving data, we often want to perform some computation or summarization. SQL allows the use of arithmetic expressions. We now consider a powerful class of constructs for computing aggregate values such as MIN and SUM.

1. Count: COUNT following by a column name returns the count of tuple in that column. If DISTINCT keyword is used then it will return only the count of unique tuple in the column. Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all the tuples of the column.
Syntax: COUNT (Column name)
Example: SELECT COUNT (Sal) FROM emp;

2. SUM: SUM followed by a column name returns the sum of all the values in that column.
Syntax: SUM (Column name)
Example: SELECT SUM (Sal) From emp;

3. AVG: AVG followed by a column name returns the average value of that column values.
Syntax: AVG (n1,n2..)
Example: Select AVG(10, 15, 30) FROM DUAL;

4. MAX: MAX followed by a column name returns the maximum value of that column.
Syntax: MAX (Column name)
Example: SELECT MAX (Sal) FROM emp;
SQL> select deptno,max(sal) from emp group by deptno;

DEPTNO    MAX(SAL)
------   --------
10        5000
20        3000
30        2850

SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;

DEPTNO     MAX(SAL)
-----      --------
30      2850

5. MIN: MIN followed by column name returns the minimum value of that column.
Syntax: MIN (Column name)
Example: SELECT MIN (Sal) FROM emp;

SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;

DEPTNO   MIN(SAL)
-----    --------
10     1300

VIEW: In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
A view is a virtual table, which consists of a set of columns from one or more tables. It is similar to a table but it doest not store in the database. View is a query stored as an object.
Syntax:     create view view_name AS SELECT set of fields FROM relation_name WHERE (Condition)
1. Example:
SQL>create view employee as select empno,ename,job from emp
where job = ‘clerk’;
view created.
sql> select * from employee;
empno      ename      job
----       ------     -------
7369       smith      clerk
7900       james      clerk
7934       miller     clerk

2.Example:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

drop view: This query is used to delete a view , which has been already created.
Syntax:                 drop VIEW view_name;
Example :        SQL> DROP VIEW EMPLOYEE;
View dropped