CLAUSES:
- clause is statement which is used to add to sql pre-define query for
providing additional facilities are like filtering rows, fetching top most
rows, sorting values, grouping similar values, finding subtotal and grand
total based on the given values automatically.
- sqlserver supports the following clauses. those are,
> where - filtering rows (before grouping data)
> top (n) - fetching top most rows from a table.
> order by - sorting values
> group by - grouping similar data
> having - filtering rows (after grouping data)
> rollup - finding subtotal & grand total (single column)
> cube - finding subtotal & grand total (multiple columns)
syntax:
<sql per-define query> + <clauses>;
Where clause:
> filtering rows in one-by-one manner before grouping data in table.
syntax:
where <filtering condition>
ex:
select * from emp where empno=7788;
update emp set sal=8500 where job='clerk';
delete from emp where deptno=10;
note: "where" clause can be used in "select “,"update" and "delete"
commands only.
Top (n) clause: this clause is used to fetch a top n number of records from a
table.
ex: select top (3) * from emp
ex: update top (3) emp set ename=’sai’
ex: delete top (3) from emp
Order by clause:
> sorting values based on columns. it can be used in "select"
command only.
> by default, order by clause arrange values in ascending order but if
we want to arrange values in descending order then we use "desc"
keyword.
syntax:
select * / <list of column names> from <tn> order by <column name1>
<asc / desc>,<column name2> <asc/desc>,............;
ex1:
waq to display employee salaries in ascending order?
sol:
sql> select * from emp order by sal;
(or)
sql> select sal from emp order by sal;
ex2:
waq to arrange employee names in descending order?
sol:
sql> select ename from emp order by ename desc;
ex3:
waq to display employee who are working in the
deptno is 20 and arrange those employee salaries in
descending order?
sol:
sql> select * from emp where deptno=20 order by sal desc;
ex4:
waq to arrange employee deptno's in ascending order
and those employee salaries in descending order from
each deptno wise?
sol:
sql> select * from emp order by deptno, sal desc;
Note:
order by clause not only on column names even though
we can apply on position of column in select query.
ex:
sql> select * from emp order by 6;
sql> select ename, job, sal from emp order by 3;
sql> select ename, sal from emp order by 2;
sql> select sal from emp order by 1;
Group by:
> grouping similar data based on columns.
> when we use "group by "we must use "aggregative functions" are
sum(),avg(),min(),max(),count().
> whenever we implement "group by" clause in select statement then first
grouping similar data-based columns and later an aggregative function/(s)
will execute on each group of data to produce accurate result.
syntax:
select <column name1>,<column name2>,......,<aggregative function
name1>,....... from <tn> group by <column name1>,<column
name2>,...........................;
group by
aggregative functions. |
sum (), avg (), job (no. of in each job)
min (), max (), count () |
clerk | analyst | president | manager | salesman
clerk analyst (1) manager salesman
clerk (2) manager salesman
clerk (3) salesman
(4) (4)
ex1:
waq to find out no. of employee working in each job?
sol:
sql> select job,count(*) num_of_employee from emp group by job;
ex2:
waq to calculate department number wise total salary ?
sol:
sql> select deptno,sum(sal) total_salary from emp
group by deptno order by deptno;
ex3:
waq to display [Link] employee working in each job along with
deptno wise ?
sol:
sql> select job,deptno,count(*) num_of_employee from emp
group by job,deptno;
ex4:
waq to calculate deptno wise totalsalary where deptno's are 10,20 ?
sol:
sql> select deptno,sum(sal) total_salary from emp
where deptno in(10,20) group by deptno;
ex5:
waq to calculate deptno wise avg,min,max salaries ?
sol:
sql> select deptno,avg(sal) avgsal,min(sal) minsal,max(sal) maxsal from
emp group by deptno order by deptno;
Having:
> filtering rows after grouping data in table. it can be used along with
"group by" clause.
syntax:
select <column name1>,<column name2>,......,<aggregative function
name1>,....... from <tn> group by <column name1>,<column
name2>,................................having <filtering condition>;
ex1:
waq to find out [Link] employee of each job in which job [Link] employee
are more than 3?
sol:
sql> select job,count(*) from emp group by job
having count(*)>3;
ex2:
waq to display sum of salary of deptno's from emp [Link] sum of salary
of deptno is less than 9000 ?
sol:
sql> select deptno,sum(sal) from emp group by deptno
having sum (sal)<9000;
Differences b/w "where” and “having" clause:
where having
1. where clause condition 1. having clause condition
is executed on each row of is executed on group of rows
a table. of a table.
2. it can be apply before 2. it can be apply after
group by clause. group by clause.
3. it cannot support 3. it can supports
aggregative functions. aggregative functions.
4. without group by 4. without group by
we can use where clause. we cannot use having clause.
using all clauses in a single select statement:
syntax:
select top (n) <col1>,<col2>,.................,<aggregative function
name1>,.....................
from <table name> [where <filtering condition>
group by <col1>,<col2>,......................
having <filtering condition>
order by <col1> [asc/desc],<col2> [asc/desc],..............
];
ex:
select top (1) deptno,count(*) from emp
where sal>1000
group by deptno
having count(*)>3
order by deptno;
order of execution:
> from
> where
> group by
> having
> select
> order by
> top (n)
rollup & cube:
> special clauses.
> to finding subtotal & grand total based on columns.
> working along with "group by" clause.
> rollup will find sub & grand total based on a single column.
> cube will find sub & grand total based on multiple columns.
syntax:
group by rollup (<col1>,<col2>,<col3>,........,<col n>)
ex. on rollup with a single column:
sql> select deptno, count (*) from emp group by rollup(deptno);
deptno count(*)
---------- ----------
10 3
20 5
30 6
14
ex. on rollup with multiple columns:
sql> select deptno, job, count (*) from emp group by rollup(deptno,job);
Note: in the above ex. rollup is finding sub & grand total based on a single
column (deptno). if we want to find sub & grand total then use "cube"
clause.
syntax:
group by cube (<col1>,<col2>,.....................,<col n>)
ex. on cube with a single column:
sql> select deptno, count (*) from emp group by cube(deptno) order by
deptno;
ex. on cube with multiple columns:
sql> select deptno, job, count (*) from emp group by cube (deptno, job)
order by deptno;
grouping_id ():
> it used more compact way to identify sub and grand total rows.
> id number 1: to represent subtotal of first grouping column.
2: to represent subtotal of second grouping column.
3: grand total row.
syntax:
grouping_id (<col1>,<col2>,.....)
ex:
select deptno, job, count (*), grouping_id (deptno, job) from emp
group by cube (deptno, job) order by deptno;