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;
SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;
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;
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)
SQL>create view employee as select empno,ename,job from emp
where job = ‘clerk’;
sql> select * from employee;
empno ename job
---- ------ -------
7369 smith clerk
7900 james clerk
7934 miller clerk
CREATE VIEW [Current Product List] AS
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;